Analytic App - Blank Interactive Tools

[Aaron_Bowen]

Hi, 

I have an analytic app with several list boxes and text boxes. I want the user to be able to leave some of the tools blank if they don't apply, but if it left blank I need all of the data to flow through the filter tools. I need help with the formula syntax because I just can't seem to get it right. For the List Box (1), if the user selects one or more items, only those values are pushed through, but if they leave it blank then I want all of the values to go through. The same for the text box (2) - if there is a number entered then find that number in the field, otherwise push all of the data through. Any help would be greatly appreciated. I've attached screenshots of the items mentioned above. 

[NeoInfiniTech]

Hello @Aaron_Bowen,

When I look at the configurations, I see that for the NPI field you configured it to be like NPI IN ("5") for example (including double quotes). If NPI is a numeric field, I would suggest that you change it to a numeric up down or integrate an Error Message tool, and in addition to that (most importantly) do not include double quotes provided you are looking for a number with "equals to mode" (IN operator also acts as equals to mode when you provide a single value to it).

May I ask what data type is the NPI field and could you provide sample values from it? First I would like to understand what you mean by "number in the field", as in 'does a specific row for the NPI column contain that number in the string (such as ABC123 and you are looking for the "123")', or 'find the NPI rows that equal to this number value'.

Other than my question about the NPI field, I would expect the configurations to work fine based on the screenshots.

[Aaron_Bowen]

Hi - an NPI is a unique number that is assigned to healthcare providers. It is a 9-digit number like "999999999". The text box entry would find the matching NPI and exclude all others. If the box is left blank, I want to pass all NPIs. 

[NeoInfiniTech]

Based on the screenshots and the info regarding the NPI field (which based on your description I am assuming is a string field) you just provided, I would expect these parameters to work just fine. Are you getting an error or does data not flow when you filter it using the analytic app interface? I would suggest that you use the Test View and click Open Debug after settings all the parameters to debug the workflow and correct any possible errors. You can also share a screenshot here if you are encountering an error.

[apathetichell]

your missing a shot of what your action tool is doing to filter - having said that - I'd use my logic in the filter with the expectation that my action tool is only replacing whatever is in my IN function. I would not do formula work in the action tool here.

also your variable setting for [#1] is wrong. [#1] from listbox is a effectively an array. It's formatted as "value1","value2"..."valuen" by wrapping it in quotes - you have turned it into a string/singular value. you can try "(" +[#1] +")"

[Aaron_Bowen · Accepted solution]

Thanks for the replies. I was able to figure out the configuration. In the List Box tool I used a separator of "," with no start or end text. In the Action Tool I "updated value with formula" and used the following formula: 

IF !IsEmpty([#1]) THEN '[Field] IN ("' + [#1] + '")'
ELSE [Destination]
ENDIF

The formula in the formula tool that is receiving the information is: 

IsEmpty([Field]) OR !IsEmpty([Field])

This configuration allows one, multiple, or all values that are checked to pass into the filter. If no items are checked it will push all of the data through the filter. 

Thanks, 

[apathetichell · Accepted solution]

@Aaron_BowenI would strongly recommend that you swap your logic to the filter - not do it in the action tool - it's harder to troubleshoot and for other's to read. Also - what you did is literally what I said here:

also your variable setting for [#1] is wrong. [#1] from listbox is a effectively an array. It's formatted as "value1","value2"..."valuen" by wrapping it in quotes - you have turned it into a string/singular value. you can try "(" +[#1] +")"

you just changed it to remove the leading quotes in the listbox tool vs removing the extraneous quotes in the formula tool.