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

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
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 
Photo of Sam Krausz

Sam Krausz

  • 470 Points 250 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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.
Photo of Sam Krausz

Sam Krausz

  • 470 Points 250 badge 2x thumb
First thanks for your help 

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?
Photo of Paul

Paul

  • 42 Points
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'. :)
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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.
Photo of Sam Krausz

Sam Krausz

  • 470 Points 250 badge 2x thumb
Is this method called "reverse relationships"?
Photo of Sam Krausz

Sam Krausz

  • 470 Points 250 badge 2x thumb
Why is the reason that you don't like to do the reverse relationship?

What are the disadvantages?
Photo of Matthew Neil

Matthew Neil

  • 31,418 Points 20k badge 2x thumb
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.
Photo of Sam Krausz

Sam Krausz

  • 470 Points 250 badge 2x thumb
Thank you for your help, it's it's working excellent