Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

In DataBase filter and interface editor workflow question

mikedesena
7 - Meteor

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

4 REPLIES 4
CharlieS
17 - Castor
17 - Castor

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. 

 

mikedesena
7 - Meteor

I am going to test this today but is there any way that "all" would just leave everything blank? our database has USA and international items - so if i was to select all usa states - i would miss a ton of foreign items. there are too many foreign countries/states to try to track and keep the list updated. 1 time a year i need to pull and reconcile everything world wide. Then quarterly i have to run different batches of states.

 

i think maybe your "better" version would help with this as it would list all items in the database possible (i am going to look into all this today).

thank you either way so much for your time. i learned a lot in how to use that box from your example below. stay blessed

mikedesena
7 - Meteor

charlie charlie charlie, you are the best!!! so i took what you taught me and did a work around. used the check box like you said and set it up like the attached image. and i just learned i can just put the image in this text box 😄 today is a good day

Capture.PNG



CharlieS
17 - Castor
17 - Castor

Nice work!

 

I think the dynamic list from the external source would still be a nice enhancement for the future (so the List Box stays updated with the data), but I'm glad you got this up and running.

 

Happy Alteryx-ing!

Labels