Joins with Pipelines
Hi all,
Is it possible to do joins (like SQL) with Pipelines? Can anyone point me to a training or discussion that fleshes this out?
Thank you!
Forum Discussion
I am no SQL expert but joins get complicated, Inner, Outer. Are you trying to do a complex query with parameters from one table matching another? I do that all the time for unrelated tables where the [Date] is the common piece.
The query itself is not complex, there are just multiple phases for it. For simplicity I am trying to do a cartesian join and several inner (or left) joins.
To start, I have two tables, a list of shirt colors and a list of names. The first step is to create a new third table, with every possible combination of color and name. So for example, 3 colors and 3 people would yield a table with 9 rows.
Does quickbase support this functionality?
- MarkShnier__You
Qrew Legend
Most of us this Community Forum don't come from an SQL programming background, so as soon as you talk inner and outer joins, we get lost. So we appreciate you dumbing down the ask for us here.
One solution is to is to create that third table and have a Custom key field which is say a hyphenated concatenation of the name and the size like Fred Flintstone-M
Then have a Pipeline run create a Bulk upsert, and then search the Table of names, and then inside the resulting For Each Loop, search the Table of sizes, and then ad a row to the Bulk upsert.
Then outside those loops Commit the upsert.
You would have to decide when to trigger the pipeline.
Quickbase will not automatically create table three for you with all possible combinations. If you create the table, it is not hard to write a Pipeline to iterate through the steps of creating those records.
For your specific example I would populate the data in Shirts and Names and then create a Pipeline that you fire manually.
Step A is a Search of Shirts
Step B is in the loop of A to Search Names
Step C is in the loop of B to Create a record in the Shirt Name Table with the data from A and B
Then it will loop through all your records.
Initial data creation is easy. Keeping it correct as you add additional records to either parents will take additional Pipelines. How much more complex does the process get after that?
