Search for unique records pipeline
Hello,
Does anybody know a way to search records in pipelines for unique values?
I have three tables - Projects, Boreholes, and Sections that are all related via Record ID. One Project has many ...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendFunny, I just had this exact conversation with the client yesterday. We came to the conclusion that the best solution would be a loop in callable pipeline that would call itself.
so there would be a manual trigger after your import, and that would trigger a Pipeline and the pipeline would just have two steps. It would sense the trigger and then it would call the COLA Pipeline.
then the color of a pipe line with start with the first step of pipeline called,
Then the next step would search for records, which needs a parent added, but limit the search results to one. Then branch on IF the search was not null, to create a Parent and then Call itself.
It is important to put in the test for the search not being null. Otherwise the pipeline will loop forever. Note that when you call a pipeline that will count as a step run. If the plan you are on limits your step runs it will cost you a step run per parent created.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
Untested but perhaps you can use a formula query to help. If you made a formula query that used the size function, and said find me records in this table that have the same project, but smaller record IDs (essentially, how many duplicate records exist that have a smaller record ID). This essentially ranks them (QB junkie has a great video on ranking). Those items with no duplicates will show a value of 0. Those with duplicates will show a value of 0,1,2...depending on how many duplicates.
Make a checkbox that says, if rank =0, true. Then only fire the pipeline off that checkbox being true.
I see 2 potential issues, and not sure if they are issues or not:
- If your table has more than a few thousand entries, it's possible the formula query will either break or slow down your app.
- I'm not sure if the pipeline or formula query will win the race. ie, the formula query hasn't done it's calculation until the records are imported, yet at the exact same time the pipeline is firing off. Will the formula query calculate before the pipeline scoops up all the data points?
------------------------------
Mike Tamoush
------------------------------
- MarkShnier__You
I think it's worth trying Mike's suggestion first.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------Thanks for the help Mark and Mike. I thought this would be a pretty niche thing. I ended creating two checkboxes, one for whether it has already been imported (gets checked during the pipeline) and another for the lowest record ID that hasn't been imported (formula field). The pipeline is first triggered manually with a button that changes a date/time field and at the end calls another pipeline that searches for the lowest record ID to be imported checkbox to be checked and changes the date/time field to trigger the main pipeline again.
That way it works iteratively through the records I imported. I probably made it more complicated than it needed to be, but it works.
Thanks again,
------------------------------
Rob Brown
------------------------------
