Quote:I am going to give a simplified example of what I want to do. I have two tables, Orders, and Clients. When I am adding a new Order to the Orders table, I want to be able to look up and populate fields based on a particular client.
Multiple clients may have the same last names, first names, etc - the only field guaranteed unique is the clientID - the problem is that the end user does not want to select a client based on client ID, but rather, based on the name or some combination of fields (say, first name/last name, which is usually unique) that is user friendly.
Is the combination of first name and list name unique? Are there ever two "Bob Smith"s?
Quote:Is there a way to have a popupbox with a hidden column (with ClientID) so that when I select a First Name / Last Name combination, I can then do an XLookup based on the ClientID?
I can imagine storing the contents of an Xlookup-type function in a multidimensional array.
Or possible use the @GlobalValues/GlobalValues functions, or you might be able to do something with the StringArray functions.
Quote:More specifically, lets say I want to add client information to an order. The client's name I want to add is Glenn Davis. I also happen to have a client named Albert Davis. The Clients database only has three fields - First Name, Last Name and Client ID.
So, from the form to add an order, I ask to give me a choice of clients. I want to be able to display both Albert Davis and Glen Davis, and then use that look up other information about this client, such as address, using the Client ID (which the user does nto see).
What is the simplist way to do this?
It all depends if all the whole name (first name and last name) combinations are unique. If they are you can use the whole name to index GlobalValues, returning the ClientID.
var s_index as string
var WholeName as string
WholeName = FirstName + " " + LastName
s_index = @GlobalValues(WholeName)
if(s_index <> "")
{
ClientIndex = @ToNumber(s_index)
}
[quote]
As a side note, I can't figure out a nice way to get a PopupMenu to display non-unique entries. I read to add a "/" to the end, but the result is not what I would want. For example, a string of "a;b;c;a;b;c;" -- I want to display either choices: a b c a b c, in that order, or in the order a a b b c c, but I cannot get this behavior. [/quote]
Adding the slash will make a submenu off of the main menu. So if you create:
[code]
aa = @PopupMenu("A/Albert;A/Alfred;A/Allie;B/Bernard;B/Benson", "Test")
Your main menu will say:
A
B
Under the A menu item there will be a submenu that says:
Albert
Alfred
Allie
Under the B menu item there will be a submenu that says:
Bernard
Benson
The slashes will prevent the menus from sorting, but it will not prevent them from eliminating entirely duplicate items. But you can put one Albert under one main menu item and an identical Albert under a different main menu item. Or you can put identical main menu items that have different subitems. So if you have two people named "Smith" you could say:
aa = @PopupMenu("Smith/John;Smith/Larry", "Test Title")
And that would create a "Smith" main menu item with subitems for John and Larry. In 1.1.2 it will return the subitem selected (which may not be unique across main items). In the example above, if you selected "Larry" and there is another Larry (say, "Larry Barrimore") it will be impossible to tell which Larry was selected.
You might consider populating a list of last names that are non-unique, and then based on the selected last name, populate a second list of first names based on the last name selected. See PopulateListElement in the programming manual.