Forum Discussion

SCOTTALARCON's avatar
SCOTTALARCON
Qrew Member
6 years ago

sync two related parent tables from a child table

Hi there,

Here is the relationship of the tables:
  • Contacts has many shipments
  • Contacts has many billings
  • Shipments has many billings
Scenario: If I am looking at a shipment and want to add a bill, the billing form correctly pulls up the related shipment, but not the related contact, even though on the shipment form the related contact is there.  Is there a way to prefill both the related contact and related shipment fields so the data syncs to both tables without manually searching for the related contact?

Thanks for any help!

------------------------------
SCOTT
------------------------------
APIs and custom code
Relationships
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    no problem,
    but can you post the current code to Add Bill, when sitting on a Shipment, and let me know the fid# of the field to be populated which is probably called [Related Contact]

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • SCOTTALARCON's avatar
      SCOTTALARCON
      Qrew Member
      6 years ago
      Hey Mark, thanks for the reply.  I've attached a couple screen shots to clarify.  Basically when I'm on the shipment form, because the table is related with billing it automatically has a 'add bill' button. But the default code behind it is: 
      URLRoot() & "db/" & [_DBID_TABLE_4] & "?a=API_GenAddRecordForm&_fid_48=" & URLEncode ([Record ID#])& "&z=" & Rurl()
      When I click the button, the billing form only pulls up the related shipment.  The shipment is also related to the contact. (now patient as I've changed it), so I'm trying to get the 3rd table, billing, to know both the related patient and related shipment when a new bill is created from the shipment form.  Is this possible? 
      Thanks.


      ------------------------------
      SCOTT ALARCON
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        6 years ago
        Scott
        I will break down the explanation of the formula so you can understand what its doing.

        URLRoot() & "db/" & [_DBID_TABLE_4] 
        // the above takes the user to your table

        & "?a=API_GenAddRecordForm&_fid_48=" & URLEncode ([Record ID#]) 
        // the above puts up an Add Record form and populates field 48 with the value of the Record ID#
        // that you launched off of.  That way the Bill is connected to the Parent Shipment 

        & "&z=" & Rurl()
        The above will return the user back to the parent Shipment record after the bill is saved.


        So what we need to do to the above formula is also to populate the field on the Bill record for [Related Contact].


        URLRoot() & "db/" & [_DBID_TABLE_4]
        & "?a=API_GenAddRecordForm&_fid_48=" & URLEncode ([Record ID#])
        & "&_fid_99=" & ([Related Contact])
        & "&z=" & Rurl()

        // so we will add in an extra line.  You will need to look at your field list or relationships to 
        find the field ID to populate and put that where the 99 is.  Then you will have to see which
        //field in your Shipment table has the Record ID# of the Related contact, but it is probably 
        // called [Related Contact]


        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------