Forum Discussion

Yurij's avatar
Yurij
Qrew Member
2 years ago

Table Relationships - Connected CSV Table

Hello Community,  I'm relatively new to QB and hoping this is an easy one for someone.   Background: Parent Table - Orders Child Table - Actuals  Relationship One-To-Many Orders -> Actuals ...
Yurij's avatar
Yurij
Qrew Member
2 years ago

Hi Chayce, 

Thanks for the reply. I feared this may be more complex and I was hoping to avoid "2-way " data uploads. That being said I haven't used the Pipelines or Table to Table imports functions but will educate myself further to see if this automation is doable for day to day users. 

That being said, is there perhaps any way to create a Third table in Quickbase (ie. Join Table) that summarizes the Order field and via formula populates the Parent Record ID.  Then the Third Table is related to the Parent Order table via that record ID?   Brainstorming here. Maybe its not possible. 

Thanks!



------------------------------
Yurij
------------------------------
ChayceDuncan's avatar
ChayceDuncan
Qrew Captain
2 years ago

You could, sure. It's a bit redundant in nature to solve the 2-way data upload as you called it, but not unheard of given the importance that QB puts on its key field. It's a round about way to get there but you'd still need some automation to get it populated. If I understand what you're saying correctly - you'd be creating what essentially amounts to a copy of the orders where you flip the key field to be Order # and have a field for Order Record ID#, and you simply import those two fields into that table so that you have your table where the primary key is Order # that you can then make a relationship to the Actuals using the Order # as the Reference / Foreign Key field. In that relationship you can then add a lookup or the Order Record ID# and then point the 'Related Order' field to it so that you've closed the gap. 

I personally wouldn't go that route - I would instead recommend if you're wanting to leave the structure and key fields as they are that you create a formula query in your Actuals Table that uses the Order # to search for the Record ID# of the Order. With that value you can do a table to table import or a simple pipeline that just copies that formula query into the value of Related Order in your actuals. This is the same idea as the vlookup just in an automated way. I recommend that over the first option as you suggested given that you still have to make a pipeline and do an import - the only addition is the formula query which would essentially look like the below as a starting point: 

ToNumber(ToText(GetFieldValues(GetRecords("{'FIELD ID OF ORDER # in ORDERS'.EX.'" & [Order #] & "'},[_DBID_ORDERS]),3)))



------------------------------
Chayce Duncan
------------------------------
  • Yurij's avatar
    Yurij
    Qrew Member
    2 years ago

    Hi Chayce, 

    Thinking out aloud here again. Can I simply create a formula field in the Parent and use query to lookup and sum up the fields from the Child? 

     If this is an option what i is the downfall?



    ------------------------------
    Yurij
    ------------------------------
    • ChayceDuncan's avatar
      ChayceDuncan
      Qrew Captain
      2 years ago

      My answer will be that you can, but whether or not you should is up for debate. Formula Queries are a great tool in Quickbase but they're also one of the most taxing features if used wrong or at too large a scale. If you boil it down - if you've got 2000 orders and you have 3-4 queries just to sum, each field is doing it's own query for each record and doing the logic each time, to which if you were looking only at 1 record on a form then there would be no issue - but if you run reports and look at large sets of orders this can be and will be very taxing on Quickbase to where it might not run the report at all if you processing gets too heavy. Leveraging the relationship and using summary fields versus formula queries is the way QB has always intended for this type of info to be processed and thus it's just more efficient. 



      ------------------------------
      Chayce Duncan
      ------------------------------
      • Yurij's avatar
        Yurij
        Qrew Member
        2 years ago

        Hi Chayce, 

        Here's where I ended up and it seems to be working. 

        I created a formula numeric field 'Lookup Order Record ID' and used the query you suggested to lookup the Parent table Record IDs based on the Child table Order field. It worked like a charm. 

        I then modified the Related Order field type created by the relationship from Numeric to Formula Numeric. In the formula I simply pointed it to the Lookup Order Record ID field.  

        The summary fields in the Parent are now populating. 

        Let me know if this approach is not recommended or if it should be okay. 

        Thanks for your help!



        ------------------------------
        Yurij
        ------------------------------