Last_Name Field & First_Name Field & z_Company_ID Field & Company::Company_Name field.
Last_Name and First_Name are working fine so I know I have connectivity to the Contact table. “So I got that going for me!!!” (Bill Murray - Caddyshack)
Now all I want is to have a drop-down showing Company.z_PRIMARY_KEY with Company.Company_Name. I believe this field needs to be ‘displayType = Combobox’. Now I would like the numbers to be a simple string ‘21021105031234560’. Then when I populate the field, I would like to see the related Company_Name.
I don’t know what I am doing wrong, but I feel like Bill Murray hunting that damn gopher .
Thanks in advance for the time and consideration!!!
Dino
So, if I understand correct, you fill the valuelist with the company id field and company name field but you want only the company name to fill your combobox??
If so, I have posted a question/bug on that, as far as I know this doesn’t work correct yet.
What I did was seperate the selection with ‘stringname.split(delimiter)’ and fill the combobox with the correct part of the new array.
Check out attached solution.
I assume that’s what you want.
note:
Valuelists are limited to 500 items.
We’ve done this intentionally because pulling a complete(large) table into a valuelist can slow down your system.
It’s better to use ForminDialog technique
(Show companies list in a popup window)
This way the query only runs when user asks for it.
I have an issue that is related to this thread that I would like some help with.
I have set up a valuelist based on a self-relation to a table “timekeepers” the pri key is the caclulated value “Yes” the foreign key is a calculated value that will return “Yes” if it belongs to this relation.
I want to return the recordid of timekeeper if selected from the list, but show the name field from a separate file in the combobox. I set it up according to your valuelist.servoy solution and all works well as long as I’m working with a form in the timekeeper table.
When I use the value list in another form, the combobox shows up but is not populated with names.
I need to have this valuelist show throughout the solution on many different forms. any suggestions?
I experienced this issue too until someone pointed out to me that the relationship needs to be one of the current form in order to use it. So if your valuelist is based on a relationship you probably need to define a new relationship/valuelist…
Valuelists should be globally available. (when using “all values from table”)
When relaitonship comes in, your form needs to be part of that relation).
Maybe there’s a workaround by filling your valuelist through a query?
You can send me your solution, maybe I can help you out.
var maxReturedRows = 100;
var query = ‘select clastnamefirstmiddle, timekeeperid from timekeeper where tkstatus = “Active” and tkcategory = “Partner” or tkcategory = “Associate”’;
var dataset = databaseManager.getDataSetByQuery(currentcontroller.getServerName(), query, null, maxReturedRows);
var nameArray = dataset.getColumnAsArray(1);//put 1st column in array
var idArray = dataset.getColumnAsArray(2);//put 2nd column in array
application.setValueListItems( “billingresponsibility”, nameArray , idArray )
I get an error message in dataset variable "…SQL error code = -206 Column unknown Active A…I cant read the rest of the value from the value in the variables tab of the debugger.
if you click on the value , you can copy paste the query into a sql editor.
(ems has some good tools http://ems-hitech.com/index.phtml )
BTW since strings in sql queries have to be between single quotes,
it’s better to build your query between double quotes
var query = "select clastnamefirstmiddle, timekeeperid from timekeeper where tkstatus = ‘Active’ and tkcategory = ‘Partner’ or tkcategory = ‘Associate’ ";
OK, now the query works but the valuelist is not populated. It finds the right number of records and the idArray is populated, but the nameArray is null. The field that is to populate the nameArray is a calculation field based on a relation to another file. Could this be the reason it is not loading? Also, will this valuelist show up in the tools/valuelist menu automatically, or do I need to do something else to get it there?
Also, will this valuelist show up in the tools/valuelist menu automatically, or do I need to do something else to get it there?
Looking at your script , you should create a custom valuelist “billingresponsibility”.
You can trigger the method through for instance onShow property.
did you check the result of the query in a sql editor?
Is your calculation a stored field?
If the two columns show up with data, it definitely should show up in your valuelist as well.
note:
You probably don’t really need the calculation field(for the valuelist at least) because you can join to other tables through your query.
var query = select OT.clastnamefirstmiddle, TK.timekeeperid
from timekeeper TK, otherTable OT
where TK.timekeeperid = OT.timekeeperid //the relation to other table
and TK.tkstatus = “Active”
and TK.tkcategory = “Partner”
or TK.tkcategory = “Associate”