# Discussions

View Only

## How can we see on the Master table, the cheapest company from the Details Table?

• #### 1.  How can we see on the Master table, the cheapest company from the Details Table?

Posted 06-26-2017 21:19
Our Master table consists of products & our details table consist of Suppliers which they all have different pricing

We want to see the cheapest supplier name on the master table?

https://www.screencast.com/t/ytpM2wvD2

Is this possible  to do?

Any workarounds?  We need it badly & I know very smart people are here with a lot of knowledge

• #### 2.  RE: How can we see on the Master table, the cheapest company from the Details Table?

Posted 06-27-2017 04:46
I'm going to assume you have 3 tables in this set up.

(1) Products
(2) Vendors

and the joined table of call it (3) Product Sources

Each vendor & each product will have many product sources.

Then you can enter the prices for each supplier on the items.  Then you can summarize the cheapest supplier cost.  And really do anything else you need to.

If you need more details let me know.

• #### 3.  RE: How can we see on the Master table, the cheapest company from the Details Table?

Posted 06-27-2017 15:31

Your assumption was good, we have 3 Tables, but I want the cheapest Supplier NAME to show up in the products table, not the Cheapest PRICE

Can this be done?

• #### 4.  RE: How can we see on the Master table, the cheapest company from the Details Table?

Posted 06-27-2017 18:10
if you use the summary field  pull over the Record ID of the cheapest supplier cost, you can set up a separate relationship based on that RecordID and pull over whatever from that record.

might be a simpler way to go, but it works for us 'mere mortals'. :)

• #### 5.  RE: How can we see on the Master table, the cheapest company from the Details Table?

Posted 06-27-2017 19:12
Yep, you are on the right track.

You will need to summarize the cheapest PRICE first,
>
then send that price back down to the joined table as a lookup field
>
Then summarize the minimum Record ID# with the condition of [Price] is equal to the value in the field [New summarized lookup price field].  Say its something like [Cheapest Joined Record ID#]

This will give you the record ID# for that joined record.
>
Then ( you create something that I really don't like doing, but for this case it might be the best option)  you create a reverse relationship.
>
Each "Joined Table Record" will have many "Products", but for the field of the relationship be sure to use the [Cheapest Joined Record ID#]
>
then you can pass the name, and any other information as lookup fields to the Product table.

• #### 6.  RE: How can we see on the Master table, the cheapest company from the Details Table?

Posted 06-28-2017 00:35
Is this method called "reverse relationships"?

• #### 7.  RE: How can we see on the Master table, the cheapest company from the Details Table?

Posted 06-28-2017 00:48
Why is the reason that you don't like to do the reverse relationship?

• #### 8.  RE: How can we see on the Master table, the cheapest company from the Details Table?

Posted 06-28-2017 05:30
I'm not a fan for a few reasons;
- It can really become a beast to keep track of over time.
- It can cause performance issues if you have any reporting built off of the reverse portion
- Imagine all of the processing that has to go into, checking the summary field, then all the lookups, and possible summaries off of that.
-  Especially when you get tens of thousands of records.
- Most if not all the reporting you need from a reverse relationship, can be done in the joined table with some preparation.
- Most people take it too far, and end up making multiple sequential reverse relationships, and then the issue just compounds itself.

I've inherited an app that had 10 reverse relationships between 2 tables, and the app almost couldn't run it was so bogged down.

• #### 9.  RE: How can we see on the Master table, the cheapest company from the Details Table?

Posted 06-28-2017 01:28
Thank you for your help, it's it's working excellent