Interface app - filter portion not working

[Gina2021]

Hi All,

@phottovy & @patrick_digan helped me out with this earlier today....but then I somehow messed it up.

When I run as an analytic app, and enter something into one of the text fields, all results are still output instead of the should-be filtered result.

I'm also wondering if anyone can help me with a different design.  My input alteryx db has 4M records, and going through all 7 of these filters, when only one filter is entered into, takes 22 seconds.  I think this will drive the end users nuts.

Thanks tons!

Gina

[apathetichell]

Hi,

One reason you app isn't doing what you want it to do is that you don't have any action tool's attached to your text interface tool - You'll need those to update your filter(s).

[danilang · Accepted solution]

hi @Gina2021 

@patrick_digan's method is great, but you have to adapt it according to the type filter criteria that you have.  When the input question is blank, [#1] is an empty string.  This works fine when your filter criteria is "=".  However, if your criteria is "Contains" you need to handle the empty string case case separately, since Contains(s,t) always returns True if t is empty, thereby passing all the records through the filter.  Change the formulas in your two filters to something like this

if [#1]='' then
	"False"
else
	Contains([SupplierName],[#1])
endif

This will handle the default empty string case by returning False.  If not empty, it applies your Contains criteria  

Also regarding speed:  I can be fairly certain that the slowness that you're seeing is related to the speed of reading all the records form the db.  Once loaded into Alteryx a simple workflow like yours should run in under a second.    Your options here depend on what you're referring to when you say "input Alteryx db"

1. Are you referring to an alteryx yxdb?  If so look into Calgary dbs and the associated tools.  A Calgary db is similar to a yxdb with the addition of indexes.  Set up indexes for the various fields you're filtering on.  You can then code the logic from your 7 filters into a single Calgary input tool query, using OR between the criteria, connect the interface tools to the Calgary Input and use Replace a specific string to have each one update the correct criteria.  

2. If you're connecting to a traditional back-end database, SQL Server, Oracle, Mongo, etc consider using In-DB tools, specifically Filter In-DB.  That way the back end database will filter the records before returning them to you, speeding up the retrieval process.

Dan

[Gina2021]

@apathetichell thanks for your reply! I always thought an action tool was needed to, but @patrick_digan & @phottovy proved to me that they aren't always needed with a workflow here:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Interface-App-with-8-column-filters-in... 

Now to learn when and when they are not needed.....I have no idea.

[Gina2021]

@danilang 

Thank you tons tons TOOONNNSSSS, Dan! You not only fixed my issue, but laid it out in such a great way that made the WHY understandable to a relative newbie.  Thank you!!!!  My input is an Alteryx yxdb (which I definitely should've been more clear about), so I'll be reading your Calgary db links this morning. THANKS AGAIN SOOO MUCH for taking the time to help the community, for being a great teacher, and for the further learning resources!!!!!

[apathetichell]

Thanks for the reminder - I've used it in a few workflows but went back to the old standby Action - glad to see your workflow worked out!

[Gina2021]

@apathetichell Thanks so much! This community is awesome.

[phottovy · Accepted solution]

Hi @Gina2021,

It looks like your issue may have already been solved but I put together a similar approach as @danilang where I account for empty filters. My main difference is I remove the union at the end since you will be adding back rows that were previously filtered out. As I mentioned in my earlier post, action tools are not always needed but I think they can be useful when you are first building apps. It can be a little harder to follow the logic of your filters without an action tool so go ahead and use action tools if you are more comfortable using them. 

[Gina2021 · Accepted solution]

@phottovy This is sooo goood!!!  Thanks for your continued support!  A co-worker helped me come up with this concept yesterday where the true's keep flowing down the line so that multiple filters can be used at once and I LOVE IT!  

I'm going to compare the slight difference to help me think it through:

IIF(IsEmpty([#1]), 1=1, Contains([PO-VMI/External],[#1]))

interpretation: If interface textbox is empty, keep the empty and carry it forward on the true line, otherwise if the PO-VMI field contains the textbox entry, return the row

if [#1]=" " then "True"
else Contains([PO-VMI/External],[#1])
endif

interpretation: If interface textbox is empty, carry return true and carry the empty forward on the true line, otherwise if the PO-VMI field contains the textbox entry, return the row

Conclusion: 1=1 and then "True" are both carrying forward the empty on the True output line.

Awesome. Thanks tons everyone (@phottovy , @danilang , @patrick_digan , @apathetichell ) for helping me call this project DONE!!!! My final is attached. 

[phottovy]

@Gina2021 It looks like your interpretations are correct and our formulas are basically the same with different syntax. I think I started using "1=1" because I never thought to put "True" in quotes so I'm learning new things too. I'm glad you were able to finish your project!