Linking external data from multiple tables direct from an external source

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

I have external data coming into QuickBase directly from sql tables (via Qunect).

There is a companies table and 2 additional tables which hold Region, Territory and Buyers data.

eg.  companies, , spaccanl, spanlidn

In the companies table there is a ref for each company.  

In the spaccanl table the ref exists agains each type of entity...  Region, Territory, Buyers etc.

In the spanlidn table there are fields Region, 01, SouthWest

I can’t figure out how to link it?

companies :  
REF: XC001

spaccanl:
REF: XC001, ANLTYPE: REGION, ANLID: 01
REF: XC001, ANLTYPE: TERRITORY, ANLID: 22
REF: XC001, ANLTYPE: BUYER, ANLID: JB

spanliidn:
ANLTYPE: REGION, ANLID: 01, DSP: SouthWest
ANLTYPE: TERRITORY, ANLID: 22, DSP: Essex
ANLTYPE: BUYER, ANLID: JB, DSP: Julie Brown

How can I show on the Companies form or reports that:

The region for XC001 is SouthWest
The territory for XC001 is Essex
The buyer for XC001 is Julie Brown

Bearing in mind that the tables are synced in directly.

Any help appreciated.

Photo of Michael Graham | Insight Global UK

Posted 4 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 64,648 Points 50k badge 2x thumb
Can you tell us what the Key field is for each table and if it is not the [Record ID#] give an example of the data in a record for the Key field.
The Key fields in QuickBase are the RecordID
In the SQL Database they are all just RowID, 1,2,3 etc

Companies and Spaccanl have the REF in common.
Spanlidn and spaccanl share the ANLTYPE AND ANLID
Any ideas with this I just seem to have a mental block with it.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 64,648 Points 50k badge 2x thumb
I can probably get this working for you. My approach would be to set up additional three additional  Tables where the key field would be respectively, the two character codes from the Spanliidn table such as 01, 22, JB

Then float up the values from the spaccini table up to the companies table via reverse relationships. Then lookup the values from these three new tables down to the Companies table to translate, for example, the JB to Julie Brown.

I would then set up. QuickBase Sync to run every hour to keep those three new tables updated.

Contact me off line via the information in my Profile if you would one on one assistance to get this working.
Thanks for the info.  I've been away for the week but I'll see if I can get my head round it and let you know how I get on.  Tks