How can I create a table-to-table relationship where the common/joining fields are not the key fields?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I am looking for help creating a table-to-table relationship that is not based on a common Key Field. 

I need to import table data from an external source on a regular basis. For each of the imported records to Table #1, there is a text field (not the Key Field) that will match a text field (also not a Key Field) from another table (Table #2). There may be multiple record in Table 1 that relate to each record in Table 2 (and not vice versa). I am having trouble linking these tables since I am not able to use the Record ID / Key Fields which is what QB wants to use to join them. The data source that I am importing from does not know the relevant Record ID of the matching Table 2 record in my QB database. Any suggestions on how to achieve this relationship? TIA

Photo of Yoni

Yoni

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,704 Points 50k badge 2x thumb
If that Text field in table 2 is in fact unique, then consider changing the Key field in table 2 to that field.  If you have never changed a Key field you may want to make a copy of your app first to test this.if that is not possible, then post back and I will try to think of other suggestions, involving a third table - but it would be a whole lot easier if you can change the Key field of table 2 to be that unique Text field.
Photo of Yoni

Yoni

  • 0 Points
Thanks Mark - the text field in Table 2 is in fact unique, however the values may change over time - and the table (#2) has many other relationships that I need to maintain even when this text field changes, so making it the Key Field is unfortunately not an option. (In the case where the text field value in Table 2 does change, will not be a problem for the Table 1 relationship I'm trying to establish, since the resulting broken/new joins will make logical sense)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,704 Points 50k badge 2x thumb
I'm not sure I can solve this on this forum but if i was taking this on as a consulting project I think my solutions would be along these lines:

Make a new table called table 2 import scratchpad with Key field of the text field.
Create a saved table to table Import to copy all records from table 2 into the table 2 Scratchpad.  Make a URL formula button to be able to click and do that Copy after deleting the existing scratchpad entries. when the records are copied across, include the Record ID# of the real table 2.

Next make a table for the table 1 import scratchpad.  Make that be a child table of the table 2 scratch pad based on the [related text field] they have in common.

Do a lookup of the Record ID# down to Table 1 scratchpad.

Now make a table to table copy from the table 1 scratchpad back to tale 1 and map the Record ID# of the Table 2 scratchpad lookup of the real record ID of the table 2 record into the field [Related table 2 parent].  Make a formula URL button to click to copy those newly imported table 1 scratchpad entries records into table 1 using that saved import.

Hence the table 1 import will only ever get loaded via this two step process, never directly into table 1.

The API to do a table to table copy once its built looks like this

urlroot() & "db/" & [_DBID_of the target table] & "?act=API_RunImport&ID=10"