Formula Search Loop - Search table for match of record in a different table

Question

Hello!!! I am seeking assistance with my formula. I am trying to do something like an Index/ Match kind of deal with my table. I have a table of people onboarded. I have another table that lists leaders and their position. the user will enter 2 fields for each person onboarded "Referred by" and "Primary Leader". I am looking to have a formula that will check to see if the name in the "referred by" section is in the leaders table. if so, then put their name. if not, check to see if the person in "primary leader" is in the leader table and if so return that name. if not, then search for the name that matches the primary leader in the people onboarded table and check to see if their primary leader is in the leader table and if so, return that name. and loop until it finds a name in the leader table. I was able to do it in Excel with a formula but cant figure out how to get it in QB. Any help would be appreciated!

------------------------------
Christina Derhay
------------------------------

blakeharrison · Answer

Formulas in Quick Base cannot do what you're describing. Instead, I would recommend looking into using Pipelines to perform the search. This allows you to define the trigger and parameters for the search.
You may also want to consider an alternate structure for your application where you have a single table of people - possibly "Resources". You can then setup a looping relationship for both "Referred by" and "Primary Leader" so that instead of someone typing in a name or email, these are presented as drop-downs.------------------------------Blake Harrisonbharrison@datablender.ioDataBlender - Quick Base Solution ProviderAtlanta GA404.800.1702 / http://datablender.io/------------------------------

christinaderhay · Answer

That's a bummer. Do you know how I would do this with Pipelines? I attempted it, but I am still a beginner.&nbsp;------------------------------Christina Derhay------------------------------

markshnier__you · Answer

Christina,
If you are able to set the key field of the Leaders table Be the name of the leader or the user ID of the leader then you can make a relationship to the on boarding table based on the referred by.&nbsp; You may or may not get a hit there.
Then make a second relationship based on the field for Primary Leader.&nbsp; Then, you may or may not get a hit there.&nbsp;&nbsp;
Obviously you can use an if statement to fill in a field with one or the other if they are not blank.
The next part of your test if you strike out on those two tries is to relate the records in the on boarding table to themselves.&nbsp; You can make a relationship of a table to itself and you would have to change the key field of the table to the user ID or name of the person on boarded.&nbsp;&nbsp;
I would think it would be possible to do several relationships of the on boarding table to itself. I'm not sure how deep you want to go but there's nothing really stopping you from going for example six levels deep. The issue will be more of a naming convention in order that you do not confuse yourself with these self-referential relationships.&nbsp;&nbsp;

Bottom line is I do believe it can be done without pipelines.

------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

christinaderhay · Answer

That sounds very interesting. I have attempted, but I don't think I have a full understanding of what you are describing. Can you please provide a little more detail?

thank you!!!!------------------------------Christina Derhay------------------------------

markshnier__you · Answer

What part are you stuck on? &nbsp;If you would like some one on one help to get this working, please contact me using my email below.

------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Forum Discussion

Formula Search Loop - Search table for match of record in a different table

Related Content

Add button to create record in different app

How to configure global app search

Delete Formula Button Error

Using GetRecords to query a table in a different application?

Formula Assistance