Calculation to auto-fill largest value

[JD2775]

Hi there,

I have a checkbox list of items (30 or so), where the user can check multiple items for a record. With each item, there is an associated number with it in the table, (not visible in the checkbox). For example:

Item1 4

Item2 12

Item3 24...

There is a field above the checkbox that I want to automatically populate with the LARGEST number chosen. For example, if the user checks "Item1" and "Item2", the number "12" would populate in this field.

Anyone know what the calculation for this would be? Again, the items and the associated numbers reside in the same table.

Thanks

[comment]

I don't understand your description:

the items and the associated numbers reside in the same table.

You have - hopefully - a table of Items where each item is a record with fields for Name and Value. Then you have another checkbox field to select multiple items - in the same table? That seems very strange. What is the purpose here?

[JD2775]

Sorry, what you said is correct. I have a table with the fields Name and Value as you described. On another layout (not table) I have a checkbox set of the Names only (filled by value list). The user can click multiple names, and I want the single Number field to populate with the largest value related to the checkboxes chosen. Does that help?

BTW, you helped me with this database a lot a couple years ago (I changed the names to make it simplier to explain) If you are still confused I can contact you offline so you know what I am referring too. Thanks man!

[comment]

Well, if you define a self-join of the Items table as:

Items::CheckboxField = Items 2::Name

then the largest value among the selected items will be =

Max ( Items 2::Value )

If this is for display only, you can sort the relationship by Value, descending, and place the related field Items 2::Value on the layout.

[JD2775]

That worked perfectly, thanks man!