Get data from multiple tables

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I am creating the report to extract the data from multiple tables. Now i just need the data from a  table which i am not able to figure out.
I have 4 tables Order, Shipping, transactions and numbers
relation is 
order < shipping < transactions > numbers  (1 order has many shipping , 1 shipping has many transactions, 1 number has many transactions)

i want to get the related data from numbers in order table 
how can i do it?
Photo of chetan patil

chetan patil

  • 210 Points 100 badge 2x thumb

Posted 1 year ago

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

QuickBaseCoach App Dev./Training, Champion

  • 51,326 Points 50k badge 2x thumb
Can you clarify?

What if there are 10 transactions and each one comes from a different number. What data are you needing to get up to the Order.  How do we know from which number to pull from?
Photo of chetan patil

chetan patil

  • 210 Points 100 badge 2x thumb
Actually one transaction has only one number associated with it. I am working on other person's app and don't know the exact logical flow of data.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,326 Points 50k badge 2x thumb
If you would be satisfied with a report link field on an order record to show an embedded report of the numbers fields on the order record, then you just need to get the [Record ID#] across to the numbers table. 

So the Shipped table already has a field likely called [Related Order].  Look that up down to the Transactions table and call it [Record ID of Order].  Then do a Summary Maximum of that field up to the Numbers table. call it [Record ID of Order]

Then on the Orders table make a Report Link field called [Numbers report Link]  On the left side use [Record ID#]. On the right, navigate back to your app and the numbers table and the field [Record ID of Order].  Then put that field on the Orders form and set the form to show the records on the form.
Photo of chetan patil

chetan patil

  • 210 Points 100 badge 2x thumb
Thank you.
I got it figured out. I just added lookup fields in shipping and transactions tables to get the needed data. Then i just created the report in transaction table instead of Order table.
Photo of Ammar

Ammar

  • 312 Points 250 badge 2x thumb
You can use api_genResultsTable and <ifram> tag to set an html page and add report from whatever table you want.