Advice on rollup fields

[kennypurnomo]

Hi,

i am creating 3 tables
INVENTORY, SUPPLIER, PRICE

INVENTORY contains every items 

SUPPLIER is just supplier name

PRICE contains
Linked table INVENTORY, linked table SUPPLIER, currency field.
thefeore i have:

SupplierA ProductA usd10

SupplierB ProductA usd9

I want to design my INVENTORY table so that i can see the min price with the corresponding supplier. I did rollup the price and it is easy. How about the corresponding supplier? How do i rollup this?

Thanks

[Megan_Bandy · Accepted solution]

Hi,

If you don't want to venture into scripting you'll want to create a formula with a working field to achieve this - I agree it seems like more work than it should be.

Create a rollup field in the PRICE table called MIN: 

Select rollup source
Inventory

Inventory field you want to roll up
[Name of existing MIN Field]

Formula
IF(values=Price,'MIN')

Now you can go back to the INVENTORY table and create your final rollup field:

Select lookup source

prices

price field you want to look up

Supplier

Only include linked records from the price table that meet certain conditions

Where {MIN} is 'MIN'

This could get messy depending on how complex you want your analysis to be (eg MIN, MAX, AVERAGE) but the theory is you can put a marker against the values which meet your criteria and then use this to filter your results. 

[Ben_Young1]

Hey @kennypurnomo!

@Megan_Bandy's breakdown is going to be the most direct way to achieve what you're looking for if you need that output to appear formatted in a rollup field.

Another alternative way to do this that does not require creating any new fields is to leverage my favorite view type: List Views.

I quickly replicated your data structure and built out a list view that sorts all my price records by the lowest value. From there, all I have to do is set the related supplier to be visible. This is the end result:

It's incredibly slick. Better yet, it doesn't require the creation of any new fields.
Let me know if you have any questions or want a further breakdown of how it all comes together. I'd be happy to provide additional insight.

[kennypurnomo]

Thank you for the alternatives! But Megan's solution is the most suitable for me at the moment. Thank you very much @Megan_Bandy