I have an order form for an order/purchase database. The tables in question here are agents (agentID, lastName, firstName, initialsCode), agents_accounts (agentID, accountID) and accounts (accountID, accountName).
Most employees act as agents for a single account, but some do purchases for multiple accounts. To enter an employee/agent on the form, the user enters an id code based on that person’s initials. Once the initials are entered, a method is triggered (using onDataChange) that finds the person’s name, and lists the account(s) they are associated in a pulldown menu that displays the first account. It looks something like this:
controller.find()
agents_accounts_to_agents.initials = globals.AgentInitials
controller.search(true, false)
globals.AgentID = forms.Agents_Accounts.agent_id
//get account name(s) and populate account pulldown menu
var account_Array
account_Array = databaseManager.getFoundSetDataProviderAsArray(foundset, 'agents_accounts_to_accounts.account_name')
application.setValueListItems( 'Accounts', account_Array)
globals.AgentAccountName= account_Array[0]
Of course this requires that the data entry people know the initials for each person. What I would like to do is use this initials field as a type-ahead field to assist data entry staff. Using a value list, it can display a menu with initials, lastName, and FirstName. But the above method will not work when I do this. Nothing gets entered into the name and account fields. How can I get this to work? I would also entertain any suggestions for other ways to accomplish this.
TIA, Peter
PS - I’ve been trying to wean people off the initials code method, but they insist that they need it. Unless, perhaps, if I can provide them with an easier way to do it.
I have a similar situation, but in my employee’s table I have several classifications (checkbox) that determine which value list that a person shows up on based on global relationships that run from form load methods. You can then create dynamic valuelists this way.
I have a code associated with each employee, and employee code fileds located throughout the solution. You can enter the code, via a value list that displays the name and retuns the code.
I also use a similar method to load employee codes via a password relationship , that the employee code field uses as a look up, to create a relationship to display their name.
Hope this helps.
Reading your reply prompted me to think harder, and the problem turned out ot have a simple solution. In the valuelist “Initials” I had checked “Return in DataProvider” checked for the last name and first name. As soon as I unchecked these, the method worked. Now, if only there were a way to have more than one customer separator character in the typeahead list – one separating items 1 and 2, and another separating items 2 and 3, that would be neat.
Example: rsm - Smith, Robert
But I suppose that would be getting greedy.
Peter
ebrandt:
I have a similar situation, but in my employee’s table I have several classifications (checkbox) that determine which value list that a person shows up on based on global relationships that run from form load methods. You can then create dynamic valuelists this way.
I have a code associated with each employee, and employee code fileds located throughout the solution. You can enter the code, via a value list that displays the name and retuns the code.
Hope this helps.
![:twisted:]( "Twisted Evil")
In my solution I created a stored calc called ‘full_name’ in the people table :
return last_name + ', '+ first_name;
You can then display the calc in you value list.
That’s a great suggestion, but I can’t seem to get the calculation to display. I created a stored calculation, and it shows up as an an optional field for displaying/showing in the data provider in the valuelist editor. I checked “show in field/list” and leave “return in dataprovider” unchecked. The initials field gets displayed as it did previously, but the calc does not. I can’t figure out why. I must have missed a step or something – do you have any suggestions/ideas?
Thanks, Peter
ebrandt:
In my solution I created a stored calc called ‘full_name’ in the people table :
return last_name + ', '+ first_name;
You can then display the calc in you value list.
Does the calc display correctly in the table is calculated on? Plus you might need to loop through the records to update the calc to the backend.
Ah, I didn’t realize how stored calcs worked, and that the backend needs to updated. Now the valuelist works beautifully. Hey, I learned something cool in Servoy this morning. Again. 
Thanks a lot,
Peter
ebrandt:
Does the calc display correctly in the table is calculated on? Plus you might need to loop through the records to update the calc to the backend.