Skip to main content

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

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. 


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.

 

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.

 


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

Reply


Terms & Conditions