Forum Discussion

Yurij's avatar
Yurij
Qrew Member
3 months 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

The Child table (Actuals) is a timecard export (csv) from our enterprise software that I plan to update periodically using manual refresh as part of the Connected Table functionality. The child table has many timecard entries for several orders. Ultimately, I'd like to create a report from the Parent table that summarizes fields from the Child table. eg. Sum of JTD Hours related by the Order #. 

So, I've created a relationship between the two tables and added summary fields to the parent table. When I run the report and use the summary field in the report the data is blank.  After reading several discussions I've played around with Reference IDs, Keys, Proxy fields, formulas pointing to other fields, join tables but I haven't been successful.   From what I gather the relationship isn't established properly but I can't figure out why?  So, I'm starting fresh, hopefully with the help of this community. I'm sure I'm missing something super basic here. 

Here's a snapshot of the simplified table relationship where I've stripped it down only to one target Summary Field Total Sum of JTD Hours although there are several in reality. I'm hoping someone can step me through this. 

Note the following: 

A text field Order exists in the connected child table that identically matches the Order # field in the parent table. 

Order # is a formula text field in the Parent table that essentially concatenates several fields in the Parent table. 

Appreciate any help I can get on this. Please advise if more information required.

Sample Table Data

Parent Table Report with Order # field and Target Summary field Total Sum of JTD

Child table report with QB Key field, Order field and Sum of JTD Hours



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

16 Replies

Sort By
  • ChayceDuncan's avatar
    ChayceDuncan
    Qrew Captain
    3 months ago

    So the main issue here is that your 'Orders' table is not using Order # as the primary key. The Order # is a field that matches in both tables but as it shows in your relationship screenshot the primary key in the Orders table is the Quickbase built in Record ID#. Why this matters then is that Quickbase is trying to match the that value from the Orders table to a matching value in the Child table - in this case the foreign key or 'Related Order'. To demonstrate: 

    Order #2023029.0D0 - lets say in QB has a Record ID# of 100 as an example. With your relationship - Quickbase is trying to match the Record ID# value of 100 to the Related Order value in your Actuals table and a matching value of 100. 

    How you solve that then is a matter of how you want to handle your keys. Option 1 is to change the primary key of your orders table to be the Order #. Doing so can dramatically change your app - and requires that you always keep that # unique and that you have a way to populate it when you're creating new orders. If you do this though - then the Key field of Orders will no longer be Record ID# and instead be the actual Order # and you can then use the matching Order # in the child table to be the foreign key that links them together. 

    Your other option - is to leave the key field structure as is, and instead either manually update the value of 'Related Order' or automate the process to update it when you sync the data. What this looks equates to downloading into Excel the Orders data including the Order # and Record ID# as the 2 columns. You can then download the child Actuals data Order # column - and do a vlookup from your orders data to get the associated record ID# of that Order to then populate the value of Related Order. I'll try and demonstrate below with an example: 

    Export of Orders Data:

    Order #        Record ID #

    ABC             1

    DEF             2

    GHI              3

    JKL              4

    Import of Actuals Data: 

    Order #             Related Order

    ABC                          1

    ABC                          1

    ABC                          1 

    DEF                           2

    DEF                           2

    DEF                           2

    GHI                            3

    You can manually do this and do the vlookup yourself to find the Record ID# value of the order to import into Related Order - or you can use any combination of formula queries, pipelines, table to table imports to automate that process for you. 



    ------------------------------
    Chayce Duncan
    ------------------------------
    • Yurij's avatar
      Yurij
      Qrew Member
      3 months 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
        3 months 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
        ------------------------------