Yes, I know the title of this tip doesn't mean much. But I couldn't come up with a meaningful title in just a few words. So let me describe the situation. Let's say you have a multi-value keyword fields that contains state names. And another multi-value keyword field that contains city names. The choices for the city names come from the selected state names. If you choose some cities, and then go back and remove a state, how do you eliminate those selected cities that are no longer choices?

The form starts out with an Editable, Dialog List field, that allows multiple values, called State. The choices for this field come from a @DbColumn. The field has the option "Refresh fields on keyword change" enabled.

The second field is also an Editable, Dialog List field that allows multiple values. The second field is called City. The choices are based on the selected state. For example, here's the "Use formula for choices" option in an example application:

Lookup := @DbLookup(""; ""; "vwLookupCityState"; State; 2);
@If(@IsError(Lookup); ""; Lookup)

The City field has the options "Refresh fields on keyword change" AND "Refresh choices on document refresh" enabled. So, when the user selects one or more states (e.g. California, Washington), the choices for the City field are refreshed (e.g. San Diego, Los Angeles, San Francisco, Seattle, Olympia).

Under normal situations, if the user selects all of the cities, and then goes back and changes the state choices (only California remains selected, for example), then the choices for the City field will be refreshed, but the old selected values will not. So the cities Seattle and Olympia are still in the field values even though they are no longer valid options. That's the situation this code corrects.

It is actually pretty easy to handle this situation. Because both the State and City fields have the option "Refresh fields on keyword change" enabled, the document will be refreshed when either field changes. An Input Translation formula on the City field can remove the selected values that are no longer options.

The first thing to do in the Input Translation formula is duplicate the "Use formula for choices" code so the form gets a list of the valid choices.

Lookup := @DbLookup(""; ""; "vwLookupCityState"; State; 2);
ValidChoices := @If(@IsError(Lookup); ""; Lookup);

Then, to take the current field values and keep only the valid ones becomes a two step process. The first step is to get a list of all the ones that shouldn't be in the field, and the second step is to remove all the ones that shouldn't be there.

InvalidEntries := @Trim(@Replace(@ThisValue; ValidChoices; ""));
@Trim(@Replace(@ThisValue; InvalidEntries; ""))

That is it for the Input Translation formula. So, when the user goes back and removes a state from the list, the document will refresh. The refresh executes the Input Translation formula. The formula does a lookup to get a list of what the choices are going to be. Since the exact same lookup formula was used as in the keyword choices, and because I did NOT use the "NoCache" option, performance will not be a problem. (The cached results will be used by whichever runs second). From the current values in the City field, all the ones that are still valid are replaced with empty strings. This leaves a list of all the invalid options. Those invalid options are then removed from the current values and that is the new result for the field.

There is a which can be downloaded so you can see the code in action.