Hello! I have a form with two fields - the first field is a dialog list of about 40 codes (tells us which department a person is in), and I was hoping to get the second field to populate either ‘Individual Business’ or ‘Institutional’ based on whichever code the user chooses in the first field.
The only way I can think of to do this is to add an If statement to the second field, but since there are 40 different codes and no common way to group them (meaning, if they start with a certain digit, that doesn’t necessarily mean it’s Individual Business or Institutional), it’s going to be a really long formula.
I’m thinking there’s got to be an easier way to do this…I was hoping to find a way that I could list all of the codes with the group next to them, and somehow have the second field reference this list and pull back the correct group based on the code in the first field (exactly how a vlookup would work in Excel). But I don’t know where to put this list…in Excel? in another field or form, or page? And I wouldn’t know the formula to go with it.
There are LOTS of ways to do what you want. Here is how I would do it.
First create a form:
With your code and the institute name
Then design a view which list these codes (sorted) and the corresponding name. I personally recomment you have 2 views - one for person who will maintain the system to have an New, Edit options to maintain the list and a hidden for the lookup (the last column being hidden with code + “~~” + name as the value (also incase people want to change how the first view looks).
Finally, this is how I would do it (there are lots of other ways), I would have an action button which brings up the list of codes and names view. The person selects what they want and it then populates the code and names field based upon what they selected:
You want to look at @PickList (to get display the view) and then based on what the selected pull the last column
This will give you a value “code~~name”
The you want to tmp := @explode(value;“~~”);
Finally
Field Code := tmp[1];
Field Name := tmp[2];
Should be lots of examples of the above through out this system with mode code details.
Of course…been searching for a week to get this. All the help function in designer tells me is what a field is, and that I have the option to make it computed. It does not tell me how to do a lookup other than the simple If statement that I mentioned. I can’t find it in any of my books, and couldn’t find an answer here that resembles what I’m looking for, other than the If statements and validation.