Skip to main content

Need a formula to return a value from multiple records based on two variables

  • November 1, 2023
  • 2 replies
  • 41 views
K
Forum|alt.badge.img+1

Hi,

I have a base where we I need to return the lowest value possible value for items that share a unique identifier (this is generated for each record and only some overlap) AND the same quantity. For example, say I had 4 records that generated their identifier to 101A, with quantities of 200, 300, 400, 500. For All of these records, I need a field that returns the minimum quantity (200) for each record with that matching ID ; and will work with possibly thousands of unique identifiers. 

I thought this would be an easy formula, but cannot seem to make this function whatsoever. 

2 replies

Kenneth_Raghuna
Forum|alt.badge.img+20

I am a little confused about this wording: 

@kjhbjkljn5gfqrk wrote:

 

... for items that share a unique identifier (this is generated for each record and only some overlap) AND the same quantity. 

None of the items you gave in the example have the same quantity.

If I ignore that part, you can do this by linking all records with the same identifier, and using a rollup column that looks like this:
Source: Column with the linked records

Field You Want to Roll Up: Quantity

Aggregation Formula: MIN(values)

Arthur_Tutt
Forum|alt.badge.img+19
  • Arthur_Tutt
  • Brainy
  • November 1, 2023

Hey @kjhbjkljn5gfqrk can you share some screenshots of your current tables and fields? Seeing a visual example will help me to better understand what you're looking for. Thanks! 

Terms & ConditionsAccessibility statement