Discussions

 View Only
  • 1.  Check if a value is in a list of records

    Posted 11-19-2021 12:23

    I have two tables in a relationship and need to check if the value in a field in the entered in the parent table exists in a column in the child table.  The use case, the parent table contains order information and the child table contains verification data for some of the order data.  A ticket is created in the child table by an automation (soon to be replaced with a pipeline) when a new order is created in the parent table.  However, for various reasons the individuals doing the verification may not be able to find the ticket that was created by quickbase.  If they cannot find the system generated ticket, they will manually create a ticket.  The best I can hope for in this situation is to check if they project manager enters a valid ticket number in the parent table.  By valid I mean that the ticket number entered in the parent table is actually in the ticket field in the child table.

    I hope that made sense and am looking forward to your suggestions.



    ------------------------------
    Paul Peterson
    ------------------------------


  • 2.  RE: Check if a value is in a list of records

    Posted 11-19-2021 15:23
    Hi Paul,

    1) If the automation or pipeline is creating the child record, you can add into the automation/pipeline to enter the parent field value in the child field. This way there will be no difference between the two fields as it is copying the value entered in the parent field. 

    2) If the automation or pipeline doesn't create the child record, check for errors or run conditions that are preventing it from creating the record. 

    3) You can create a summary field via the relationship back to the parent record. If there is no child record or the values don't match, you can see that on the parent record. 

    Hope that helps!

    ------------------------------
    Johnny Lundgren
    ------------------------------



  • 3.  RE: Check if a value is in a list of records

    Posted 11-19-2021 16:22

    Johnny,

    your logic works fine for the instances where the verification team is able to locate the auto generated ticket.  However, that is not the issue.  The issue involves orders that the verification team cannot find the auto generated ticket (mainly due to missing data in the order).  In those cases there a manual ticket is created and there is no connection to the order, therefore the summary field will not return any results.



    ------------------------------
    Paul Peterson
    ------------------------------



  • 4.  RE: Check if a value is in a list of records

    Posted 11-19-2021 16:33
    Hi Paul,

    the GetFieldValues() function might work for this use case. They are a bit tricky to set up, and I don't have a ton of experience with it yet. Here is the QB documentation https://help.quickbase.com/user-assistance/formula_queries.html

    Their samples only use the record id as the query but we have been able to use other unique fields as well.

    ------------------------------
    Johnny Lundgren
    ------------------------------