I have three fields that i call: apples, bananas and oranges... and there's a fourth field called: basket... I want to know which fruit or fruits is/are present in the greatest quantity in the basket.
Now I'll give you some examples:
1st case: apples = 3, bananas = 2 and oranges = 1. In the basket I would like "apples" will be written down because apples are more than bananas and oranges. The same needs to be if bananas are more then apples and oranges.
2nd case: apples = 3, bananas = 1 and oranges = 3. In the basket now I would like "apples and oranges" will be written down because they are the fruits in greater quantities and are in equal number.
3rd case: apples = 3, bananas = 3 and oranges = 3. In the basket it shows "apples and oranges and bananas" because they are all in equal number.
Must show empty when everything is 0
I know I have to use Cases and it works for me if one fruit is greater than the others, but not if there are two more fruits.

Help me please 😭

Well, you could do:

Let ( 
max = Max ( Apples ; Bananas ; Oranges )
;
If ( max ;
Substitute ( List (
If ( Apples = max ; "apples" ) ;
If ( Bananas = max ; "bananas" ) ;
If ( Oranges = max ; "oranges" )
) ; ¶ ; " and " )
)
)

I believe it satisfies all your requirements. However, this doesn't seem like a good solution overall. Properly, you should have a separate record for each fruit in a child table (with fields for Type and Quantity). And then you could use, for example, a filtered portal to show those that are at maximum. Most importantly, you would not have to change your schema when you add melons to the mix.

See also: https://www.briandunning.com/cf/908

 

 

 

Edited December 16, 20223 yr by comment

Quote

Let ( 
max = Max ( Apples ; Bananas ; Oranges )
;
If ( max ;
Substitute ( List (
If ( Apples = max ; "Apples" ) ;
If ( Bananas = max ; "Bananas" ) ;
If ( Oranges = max ; "Oranges" )
) ; ¶ ; " and " )
)
)

Great!! That exatlly what i was looking for!