I'm a LAN admin trying to do some development work. I created a form in our real estate database that contains a field for the name of a property we own. I use the @Dblookup to get a list of properties in a view called Properties. After I select the name of the property, I would like the following fields to fill in automatically: address, city, state, zip code and manager.
What is the best way to do this? Thanks in advance for any help. Please send me an example code or send me a link to a database that does a similar or the same function.
That is actually a very good question. Since you want to lookup five values at once, it would mean at first you would have to run five @Dblookup values to fill them in, based on the selected property. Very cumbersome and a waste of network traffic. Your answer lies in a concatenated string, which allows us to do only one lookup after the property has been selected.
Let's start first with the property selector. What we want is that once a user has selected a property, the five named fields are filled with the corresponding values. To make sure a refresh occurs after a property has been selected, set the properties of the selector field as follows:
- Check "Show field delimiters."
- Under choices, have "Use View dialog for choices," database set to "current database," view "Properties" and select column #1.
- Under options, have "Display entry helper button" and "Refresh fields on keyword change" checked, but leave unchecked "Refresh choices on document refresh."
An example form has been created called selector, which looks up the property name in the view properties from the first column. After a selection has been made in this field, the field "SelectString" is filled in by looking up the concatenated string from the view properties again, using the formula:
t:=@if(Selector!=""; @DbLookup("":"NoCache"; "";"Properties";Selector;7);""); @if(!@IsError(t) & t!=""; @Explode(t;";");"")First we check that the user has selected a property before the lookup is executed, to prevent error messages. And by storing the lookup into a variable, we can trap for @IsError to prevent any other errors. As you can see, we use @Explode to translate the string at this stage into a multi-value. The property "Allow multi-values" is also enabled for this field.
So, if all went well, we now have a field called "selectString" containing five values for the address, city, state, zip and manager of the property.
All that is left now is to put those values into the five display fields for those values. I have created five computed fields: address, city, state, zip and manager. Each contains the following formula: @if(SelectString!="";@SubSet(@SubSet(SelectString;2);-1);"").
This formula checks if there is a value in selectString. If there is we take out the nth value for the corresponding type of data. So, for the address field we need the first value, for the city the second value and so on. For each field, copy the above formula and change the first numeric value (in the example above, it is set to 2), into the value belonging to the value position you need.
When a user now selects a value from the property list, all five fields will be filled in with the proper values.