Action tool to update Calgary Join in analytic app (dynamic number of fields)

[mst3k]

Relatively simple problem that I can't figure out. I want to make an analytic app to let a user search a Calgary database, but on a variable number of fields. A simple example below (I made a simple dummy database in the first alteryx attachment). Let's say the Calgary database is indexed on First name, and Last name. If the user searches a first name and a last name (Stephen Smith), the results come back instantly. But what if they only know the first name and they want results for all Stephens? I tried to put a condition on the last name box to check if it's null, and if it is then alter the Calgary join tool to join the indexes accordingly. But I can't get it to work. Here is what I tried- 1) I first tried to just make the action "deselect" last name as a joined field if the entry box was null. this is like if I changed the Calgary join tool by hand and changed the join to "[None]" in the selection box in the attached picture. That didn't seem to work 2) if I couldn't REMOVE the field as a join, maybe I could change the fields it's joining if the entry box is null. see the second attached workflow. I tried to make a dummy field in my data filled with "x", and a dummy indexed field in the Calgary database filled with "x". if the entry box is null, change the field that's joining to be dummy=dummy. That doesn't seem to work 3) I know I could just make the Calgary join only use the first name, and then use another tool later to join on last name if needed. but then it wouldn't make full use of the indexes if the user DOES know both first and last name. and what if last name is the only thing they know, not the first? So i'd like to be able to make the Calgary join tool itself join only on the fields the user has entered

[mst3k]

having trouble with the form, it removed my line breaks (sorry it's hard to read) and wasn't letting me attach. trying again here

[mst3k]

here's the workflow to create the simple example database

[mst3k]

Ok.... I attempted to attach the analytic app and the forum doesn't let me. "The contents of the attachment doesn't match its file type". So here's a screenshot of it. sorry that's all we have to work with. You can see the configuration of the Action tool on the left. If the condition is true (last name entry box is null), I was attempting to change the field and indexfield from "Last" to "dummy" which were my dummy columns in the text input and in the Calgary join. that was my #2 solution in the original post. But if I search for Stephen it doesn't work.

[danilang]

Hi @mst3k (Crow, perhaps?)

If you export your analytic app Options->Export Workflow you'll be able to attach the resulting .yxzp file

Dan

[mst3k]

ahhh thanks that did the trick! here is the workflow I was attempting. I tried to change the "left" and "right" side of the join to be dummy and dummy so they would always be equal, and bypass the last name, if the last name entry box was null. But it didn't work. i'm pretty new to analytic apps and Calgary databases, the Calgary db seems pretty straightforward and i'd like to make use of it what it has to offer.

[mst3k · Accepted solution]

ahhhh wow.. after a few hours struggling with this last night I think I finally solved it just now. I just tried something else and realized the problem was not with the action tools, my #2 method to replace the field it's joining on DOES work. the problem was with my condition tool - apparently if the text entry box is left blank, the value is NOT null. instead of the condition being isnull([#1]) I changed it to [#1]="" and it worked!