Forum Discussion

ElenaLarrabee1's avatar
ElenaLarrabee1
Qrew Captain
6 years ago
Solved

How to set up a child record to automatically select its parent?

Hi everyone! I've just started playing with a connected Quickbooks table. I'm trying to find a way to have the records in the Books table automatically select a parent record in my Jobs table. Each Books record has a Job Number field that should associate it with the proper Job record (each Job record has a unique Job Number), but I can't figure out how to create an automation that tells the Books record to "select the job with the matching 'Job Number' as the related Job for this record". I've tried to have it automatically write the Job Number to the Related Job field, but it just inputs some numbers and doesn't select the correct job. 

Any ideas? 

Thank you in advance!

- Elena


​

------------------------------
Elena Larrabee
------------------------------
Quickbase Automations
Relationships
  • EricMohlman's avatar
    EricMohlman
    6 years ago
    Elena,

    I've actually done exactly what you've described, but it requires two automations.

    You first need to create a numeric field on your parent table. With that in place, your first automation will be triggered off a child record being added, and its action will be to edit the parent table and the matching criteria is your job number with the actual action being to push the Record ID# into the numeric field I just mentioned. The second automation will then trigger of this numeric field being modified, and its action will be to edit the child table with the matching value being the Record ID# being the numeric field and its action to update the reference field of your relationship to receive the parent table's Record ID#.

    If my explanation doesn't make sense for any reason, please let me know and I can spin up a quick example app for you and I to review together.

    Eric

    ------------------------------
    Eric Mohlman
    ------------------------------
  • EricMohlman's avatar
    EricMohlman
    Quickbase Staff
    6 years ago
    Elena,

    I've actually done exactly what you've described, but it requires two automations.

    You first need to create a numeric field on your parent table. With that in place, your first automation will be triggered off a child record being added, and its action will be to edit the parent table and the matching criteria is your job number with the actual action being to push the Record ID# into the numeric field I just mentioned. The second automation will then trigger of this numeric field being modified, and its action will be to edit the child table with the matching value being the Record ID# being the numeric field and its action to update the reference field of your relationship to receive the parent table's Record ID#.

    If my explanation doesn't make sense for any reason, please let me know and I can spin up a quick example app for you and I to review together.

    Eric

    ------------------------------
    Eric Mohlman
    ------------------------------
    • ElenaLarrabee1's avatar
      ElenaLarrabee1
      Qrew Captain
      6 years ago
      Aha, brilliant!! I'll go try it now. Makes perfect sense.

      ------------------------------
      Elena Larrabee
      ------------------------------
    • ElenaLarrabee1's avatar
      ElenaLarrabee1
      Qrew Captain
      6 years ago
      This worked perfectly, thank you SO much for the suggestion!

      ------------------------------
      Elena Larrabee
      ------------------------------
  • AndrewFry's avatar
    AndrewFry
    Qrew Assistant Captain
    6 years ago
    Elena,

    Just by looking at your pics and the video, and reading your description, I think (and I could be wrong) that you want the ID for Line to be changed, for the matching Job, correct?

    If that is the case, it looks to me that you have the Filter and Select fields backwards.

    Meaning, you should have the Related Job and the Trigger: Payments Received | Related Job, in the filter, and your ID for Line is equal to  the value Trigger: Payment Received | ID for Line, in the Select field and specify values.

    Hopefully that makes sense.

    ------------------------------
    Andrew
    andrew.fry25@gmail.com
    ------------------------------
    • ElenaLarrabee1's avatar
      ElenaLarrabee1
      Qrew Captain
      6 years ago
      Hey Andrew, not quite... What I'm trying to accomplish is getting the records in the "Payments Received" (my Quickbooks table) automatically associate themselves with the correct parent record based on the value in the field "Job Number." I think the ID for line needs to remain the same, as it's a key field for the quickbooks table. I just used that as a filter to trigger the automation for every time a Payment record is edited.

      ------------------------------
      Elena Larrabee
      ------------------------------
  • AdamKeever1's avatar
    AdamKeever1
    Qrew Commander
    6 years ago
    You should be able to do this with a relationship. Set your Unique Job Number field as the key field. Create a one Job to many Books relationship using the Unique Job Number as your reference field. You can add summary and lookup fields as needed.

    ------------------------------
    Adam Keever
    ------------------------------
    • ElenaLarrabee1's avatar
      ElenaLarrabee1
      Qrew Captain
      6 years ago
      Well, I have a relationship set up between the two tables, but I'm kind of nervous to change the key field for my Jobs table. I was hoping to avoid that if possible...

      ------------------------------
      Elena Larrabee
      ------------------------------