Choose selecting two values

[Enigma20xx]

I have a numeric field that I use to set different types of record, and another field to see the text that each option means.

0, 1, 2, 3 -> Orange, apple, peach, lemon...

Is there any way to make it work if I use a check box and select two values? Using a third field?

0, 2 -> Orange, peach

I know it’s easy if I set a value list with the four types of fruits, and then use (substitute(list)). But I’d like to keep using numbers.

Any advice?

Thank you.

[comment]

Can you explain what is the advantage of selecting/storing numeric codes, if in the end they need to be converted to their textual meanings? What don't you select and store the actual values to begin with?

Note also that if you intend to store more that one value in a field (as a checkbox field does), then the field should be a Text field - even if the individual values are numeric.

Now, to answer your question as asked, basically you have two options: 

1. Create a table for these options, where each record stores one numeric code and its meaning.  Define a relationship between this table and your existing table, matching the Code field to your checkbox field. Then any record whose code was selected will be related, and you can show the related values in a portal or in a calculation field using the List() function;

2. Use a calculation field to translate the selected numeric codes directly to their textual meanings. if you have 10 codes or less, you could use the Substitute() function for this, e.g.

Substitute ( CheckboxField ; 
[ "0" ; "Orange" ] ;
[ "1" ; "apple" ] ;
[ "2" ; "peach" ] ;
[ "3" ; "lemon" ]
//...
)

If you expect to have more than 10 codes then it gets more complicated.

Which option is better for you depends on the answer to my opening question (IOW, what is this really about). Note that the 2nd option stores data inside the calculation formula, which is generally regarded as bad practice.

 

Edited October 13, 2023 by comment

[Enigma20xx]

As always I’m pretty sure I’m over engineering 🤦‍♂️, but I think I’ve accomplish what I want.

Let (
    [
        ~var1 = If ( not IsEmpty ( GetValue ( FruitType ; 1 ) ) ; Choose ( GetValue ( FruitType ; 1 ) ; "Orange" ; "Apple" ; "Peach" ) )
    ;     ~var2 = If ( not IsEmpty ( GetValue ( FruitType ; 2 ) ) ; Choose ( GetValue ( FruitType ; 2 ) ; "Orange" ; "Apple" ; "Peach" ) )
    ;     ~var3 = If ( not IsEmpty ( GetValue ( FruitType ; 3 ) ) ; Choose ( GetValue ( FruitType ; 3 ) ; "Orange" ; "Apple" ; "Peach" ) )
    ]
;
    List ( ~var1 ; ~var2 ; ~var3 )
)

15 minutes ago, comment said:

Can you explain what is the advantage of selecting/storing numeric codes, if in the end they need to be converted to their textual meanings? What don't you select and store the actual values to begin with?

Note also that if you intend to store more that one value in a field (as a checkbox field does), then the field should be a Text field - even if the individual values are numeric.

Now, to answer your question as asked, basically you have two options: 

1. Create a table for these options, where each record stores one numeric code and its meaning.  Define a relationship between this table and your existing table, matching the Code field to your checkbox field. Then any record whose code was selected will be related, and you can show the related values in a portal or in a calculation field using the List() function;

2. Use a calculation field to translate the selected numeric codes directly to their textual meanings. if you have 10 codes or less, you could use the Substitute() function for this, e.g.

Substitute ( CheckboxField ; 
[ "0" ; "Orange" ] ;
[ "1" ; "apple" ] ;
[ "2" ; "peach" ] ;
[ "3" ; "lemon" ]
//...
)

If you expect to have more than 10 codes then it gets more complicated.

Which option is better for you depends on the answer to my opening question (IOW, what is this really about). Note that the 2nd option stores data inside the calculation formula, which is generally regarded as bad practice.

 

Seen my calculation compared to yours with the substitute function, mine is just ridiculous 🤣🤣

I don't expect to have more than 4 values or so in the field.

I prefer using numbers so is easier to use in relationships.

The calculation is just a text tag.

As always, you nail it.

[comment]

Just a minor addition to this:

12 hours ago, comment said:

If you expect to have more than 10 codes then it gets more complicated.

Actually, if all your codes are strictly numeric (i.e. digits only) and none of the textual values contain any digits, then the number of codes you can have with this method is unlimited - provided that you order the substitution pairs so that all 2-digit codes come before single-digit codes, and all 3-digit codes come before 2-digit codes, and so on.

There is a limit of 999 substitution pairs within a single Substitute() function, but it can be easily exceeded by nesting one function inside another.

 

Edited October 14, 2023 by comment