How do I lookup an indirectly related field in a table relationship with 3 tables?

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

I have 3 tables (Wines, Orders and Prices) with 2 relationships both using a Related Wines field:

many Orders to one Wine

many Prices to one Wine

I have a Quantity field in the Orders table and a Price field in the Prices table.

As I need to run a calculation of the value (Quantity x Price) in a report, how can I get the Price field from the Prices table to show up on the Orders report using the existing relationship with the Wines table?

Many thanks for helping me out here as I've been stuck for a few days with this now!

Photo of Benjamin

Benjamin

  • 20 Points

Posted 5 years ago

  • 0
  • 1
Benjamin,

If I understand correctly, you might try this:

4 tables:

Wines
Wine Prices
Orders
Line Items

Here are your relationships:

One Order can have many Line Items
One Wine can have many Line Items
One Wine can have many Wine Prices
One Wine Price can have many Line Items

You would not have a relationship between Orders and Wines or Orders and Wine Prices. The Line Items table will connect the Orders and the Wines / Prices together.

On the Line Items table, you would now have three reference fields: Related Order, Related Wine, and Related Wine Price.
You would want to make Related Wine Price Conditional on Related Wine, so that when you select a wine, you only see prices for that wine in the Related Wine Price field.

You would add a Quantity field to the Line items table. That allows you to say, for this order, I want X Quantity of this wine, at this wine price. Then you can add another line item to the same Order with X Quantity of another wine at one of its Wine Prices.

Let me know if you have any followup questions or need help - my contact info is in my profile.

Thanks and good luck!

Eric
Photo of Benjamin

Benjamin

  • 20 Points
Hi Eric

Thanks for your reply. This has been helpful and I believe I'm almost there.... but instead of having to choose from the conditional dropdown on the related wine price for a related wine, I would actually need to lookup the data automatically from the related price table (which has a date, related wine and price).

So for example, I would want to create a new order (with say a date, customer and order number) and then add the individual line items (each with say a quantity and related wine). I would then need the table to show/lookup the related wine prices corresponding to the related wine and also matching the order date.

Thanks for letting me know if this makes sense and if this is manageable.

Ben
Ok, I see.

This might actually be quite complicated, but doable.

Thinking only in terms of native quickbase functionality (there may be simpler ways to do this with javascript) this is what you would probably want to do:

To bring the most current wine price for a selected wine, you need fields on the wine table that can be brought to the order line items table as lookup fields.

In order to get those fields on the wine table, you need to summarize the most recent pricing from the wines prices table to the wines table. to be able to tell quickbase which record is the most recent, I would do a summary field that is the maximum (latest) value found for the child wine prices related to that wine. Then, send that value down from the wines table to the wine prices table as a lookup field. And finally, create a formula checkbox field that says: if the price date is equal to the maximum price date, true, if not, false.

Once you have this checkbox field, you can then create another summary field on the wines table that takes the total of the price field, only when the checkbox field is checked.

Now you have a price value you can send as a lookup field to the order line items table.

Hope that makes sense / helps.

Eric