In DataBase filter and interface editor workflow question

mikedesena

Novice question here:

I have an in data base work flow that needs to be ran for all states or a select few states. I am currently running the workflow "manually" and updating a in DB filter tool if certain states are needed (example state code = "NV" or state code = "DC") The database is very large so I was trying to do the filter steps in DB. I want to make this an application where you could pick between running for all or states you ask for.

Attachment #1 is what I currently do and this is manual. I don't know how to construct interface boxes to update the filter tool: for all states - make no text in the filter; for certain states get the formula to format properly for each state needed.

There is also a box in the query builder we could use interface options to update which is mentally similar to a filter tool. Attachment #2 is for all states and #3 works for certain states (similar theory for the filter in attachment #1).

Does anyone have 5 minutes to help me learn something? I really appreciate your time

#1 current.PNG

#2 all states.PNG

#3 certain states.PNG

CharlieS

Hi @mikedesena 

I take it you're looking for a List Box for the user to easily select the states of interest and have it update the In-DB filter. This can definitely achievable and I'll show how this can be done.

1. Set up a List Box tool that prepares a list of Name:Value pairs for each state like "Texas:TX" so the user can just select "Texas" from a list and "TX" is returned.

    Ok version: This could be a list of manually set values like in the attached example

    Better version: Use the External Source option and query the list of potentially options from the database.

2. Configure the List Box tool to "Generate Custom List" where you adjust the concatenating strings to be the syntax you need for your query. 

Start Text:

"StateCode" = '

Separator:

' OR "StateCode" = '

End Text: 

'

So if the user selects "Colorado" and "Texas" the values of "CO" and TX" will be output like this:

"StateCode" = 'TX' OR "StateCode" = 'CO'

Now that the List Box is producing the filter string we need, just have that update the Filter In-DB tool. I gave this example in the syntax of your first version above. This method can be adapted to produce the filter syntax for the '"StateCode" in (' version, or other DB requirements/applications. 

Check out the attached example to see these tools and let me know if you have any questions. 

20200714-ListboxToFilter.yxwz