I have created the base tables for my application…companies,people, projects and companycontacts.
companycontacts(CC) assigns a company to a project and amongst others things gives its role in the project…Builder,Client,Engineer etc. I have a combo box called companyname.
I have configured a relationships from cc.companyid to company.companyid and cc.projectid to project.projectid
My problem is when I select the combo box to select the company name the name is available on entry but on exit I end up with a blank field. If I key in the companyid into the cc.companyid field the companyname field populates correctly.
If I understand you right, you have configured the value list to show the company name and return its ID. If that is the case, the field itself has to be set to non-editable.
The cc.companyname field has a valuelist and should return the companyname into the companyname field and then update the companyid into the cc.companyid field…but when I enter the companyname field the companynames are displayed and when I select one of the companynames the companyname field remains blank…does not show the company name I selected…and it doesn’t update the cc.companyid field either.
On the same form I have the companyid field (1,2,3,4 etc) for testing and if I create a new record and type in the id for a company into the id field the companyname field populates with the correct data. This to me shows the relationships are OK.
I want to be able to select the company name and have it remain in the cc.companyname field and also populate the cc.companyid field with the correct id for the company. Eventually the companyid field will be removed from the form and only be kept in the background for the key to companies
Now I have really read your post But I don’t fully understand your setup.
What I understood is this:
you have a table with companies, that have an ID and a name (among other things)
you have a table called projects that has the company ID as foreign key
now you want to be able to choose a company when entering a new project
If that is the case, you simply define a value list that is based on the company table. The value list returns the company ID and shows the name. So you setup the value like this:
in the first of the 3 columns you select company_id and check “return in dataprovider” and uncheck “show in field/list”.
in the second column you select company name and check “show in field/list” and uncheck “return in dataprovider”
You attach this value list to the company_id field of the projects table and make the field non-editable. The value list should show all companies. If you choose one company, the ID of that company will be returned in the field. Note that company names have to be unique in this case. If they are not, you may choose to also show the ID in the value list…
companycontacts to companies [companycontacts.companyid to companies.companyid]
companycontacts to projects[companycontacts.projectid to companies.projectid]
The companycontacts table is to assign a company to a project, with a different role, fees etc. Multiple companies can be assigned to a project.
I need to select the company, project and role on the form.
On the form I have an companyid field. I have created a valuelist with companyid and companyname hilighted with dataprovider and show in field selected respectively…as per your last post
I also have a projectid field. I have created a valuelist with projectid and projectname hilighted with dataprovider and show in field selected respectively…as per your last post
projectrole field is a unrelated valuelist…no problem
relatedvalues are:
company - companycontacts to no NM relation
projects - projects to no NM relation
How do you show that form / how a records created? Is the form loaded by a relationship somehow or is it just there and you hit “new record”?
If you still have a problem and don’t mind sending me the solution, I am sure I can help out. If you do send the solution, please include some sample data so I have a starting point.
Your problem is that the value list is based on the relation companycontacts_to_companies. At the time where you enter a new record, there is no company_id (yet), because that is the one you try to populate. When you build the value list on the relation, Servoy shows only related companies (so by default either zero - if the company_id is empty - or one - if it is filled). So just change your value list from the relation to “All values from” (instead of “related values”), and Servoy will offer you all companies and you can choose one. The same for projects.
Note that this is only a useful approach if you don’t expect to have too many companies. If there is 5000 companies, the value list approach for choosing a company is not very good. Then you should show rather show a dialog or have some filter on first letter or something similar.