Forum Discussion

RobBrown's avatar
RobBrown
Qrew Trainee
11 months ago

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 Boreholes and many Sections, one Borehole has many Sections. We receive data in a spreadsheet of Sections metadata and I am trying to create a pipeline to check if the Project and/or Borehole exists, then add the Record ID to the Section if it does or create a new Project and/or Borehole if it doesn't. 

I set up the pipeline to be triggered when a record is created in Sections - Record Created -> Search Record -> If List of records is empty -> then Create Record, else Update Record.

However, it appears pipelines work asynchronously. Meaning, it hasn't went through the create record step before it searches Projects and Boreholes again for the next Section record that was created - leading to multiple Projects and Boreholes records being created with the same name.

My next thought is to create button to trigger a pipeline after everything is imported and then search the newly imported records, but I'm not sure how to limit my search to only unique values.

This became very long, but does anybody know a way to search records in pipelines for unique values?

Thanks,

-Rob



------------------------------
Rob Brown
------------------------------

5 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    11 months ago

    Funny, 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
    ------------------------------
    • MikeTamoush's avatar
      MikeTamoush
      Qrew Commander
      10 months ago

      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:

      1. If your table has more than a few thousand entries, it's possible the formula query will either break or slow down your app.
      2. 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's avatar
        MarkShnier__You
        Qrew Champion
        10 months ago

        I think it's worth trying Mike's  suggestion first. 



        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------
  • eduardovaldes's avatar
    eduardovaldes
    Qrew Member
    10 months ago

    what I do to identify a list of unique values is to create a kind of shadowTable.

     

    In your case shadowSection, with only one key field (for example sectionName).

     

    You create two formula relations between projects and boreholes, with this shadowTable.

    The key field should the sectionName. That way you know how many boreholes and projects you have in that section.

     

    When you receive the section name metadata you loop and do a prepare, add row, and commit bulk upsert in the same row.

     

    That way the shadowTable is updated and you have a table of unique sectionsNames

    After you can delete that shadowTable.

     

    Not a great expert, and don't know if this is optimal but I use it a lot and works for me.



    ------------------------------
    eduardo valdes
    ------------------------------