Pulling a value from a repeating table
Hi, I want to find a value from a table based on another value in the same row... is this doable?
Row 1: weight = 100, density = 25
Row 2: weight = 200, density = 10
I want to find the density where the weight was highest.
Hi Ilana,
Yes, this is something we've helped other users accomplish! Here's how.
If weight and density values are inside a repeating table, include a hidden field with a dynamic value:
Hidden Field 1: {{weightDensity}}
JOIN('&', {{elementWeight}}, {{elementDensity}})
// this concatenates the weight and density values from the repeating table as a text value, combining them with an ampersand. The data names names can be changed to whatever data names are in use in your table, of course.
Then insert 3 more hidden fields outside the table with these formulae:
Hidden Field 2: {{maxWeight}}
MAX({{elementWeight}})
// this extracts the highest value for weight from the various table entries.
Hidden Field 3: {{maxWeightDensity}}
STRING( TOLIST( JOIN('|', {{weightDensity}}), '|', 'SPLIT({{this}},"&",0) = {{maxWeight}}') )
// this creates a new text string, returning the value of the density where the first part of the string equals the previously-determined highest weight.
Hidden Field 4: {{densityOfMaxWeight}}
split({{maxWeightDensity}},'&',1)
// this splits the data captured by the above hidden field, isolating just the value of the density after the weight.
The value returned by the 4th hidden field, {{densityOfMaxWeight}} in this example, is a text value of the density in this row with the greatest weight. If you'd like this in numeric format for calculations or otherwise, simply add another Numeric field that has the name of this text field as a Dynamic Value.
With just these fields, table will essentially look like this:
Hope this helps!