Insert into new table using select_single lookup from other table - basic error

[James Corden]

OK - I thought I was doing this properly but.....

technol - table that contains details of different technologies (lots of columns including PRIMARYKEY, NAME, MANAGERNAME, DATECREATED etc...)
project_managers - table that contains names of project managers (only two columns PRIMARYKEY, PROJECT_MANAGER)

I have set up a form to insert new entries into the technol table.
I want the MANAGERNAME to be selected from the names in the project_managers table (the PROJECT_MANAGER column)

In the form configurator I have:
table: technol
field: MANAGERNAME

Field type: select_single
Lookup table name: project_managers
Lookup table primary key field: PRIMARYKEY
Lookup table linked fields: PROJECT_MANAGER

I thought this was working properly as it displays the list of project manager names present in the project_managers table in a drop down list . However when you insert a new entry into the technol table it is inserting the value of the PRIMARYKEY from the project_managers table into the MANAGERNAME field in the technol table and not the name of the project manager that was selected.

This is happening on all my select_single drop down lists so is a basic error / misunderstanding on my part.

Thanks

 

[James Corden]

If I set
Lookup table primary key field = PROJECT_MANAGER
Lookup table linked fields = PROJECT_MANAGER
then it fixes the problem - it inserts the name of the project manager into the MANAGERNAME field of the technol table and not the value of the primary key.
But this doesn't seem correct as PROJECT_MANAGER is not the primary key of the project_manager table

 

[James Corden]

Just checked the manual - always a good idea!

Section 7.2
Configuring the field in this way, for the field customers.city you save in your database the ID of the city but you show to your users the name of the city. Also, you limit the options available to the cities you have in the table cities. The field customers.city needs to have the same database type the field cities.ID has, so if the ID is INTEGER, customers.city needs to be integer as well.

So it seems that this is setup to save the primary key value rather than the name (of the city in this case) in the target table.

Is there anyway round this, can I setup a lookup field so it saves the selected name in my target table?

 

[eugenio]

Hello,
it is not possible and most of the time it is not correct because you might have duplicated values and technically you couldn't be sure to choose one (and only one) record from project_managers.

In this use case I don't see any advantage in denormalizing the schema and storing the name instead of the ID but if you need to do it for any reason, you could (if you are sure the field PROJECT_MANAGER is unique, for example because you added a UNIQUE constraint to your schema) create a view:

CREATE VIEW project_managers_2 AS SELECT project_manager FROM project_managers

then set in the admin>pages page project_manager as unique field and and use the VIEW (instead of the original table) for the lookup, setting for both Lookup table primary key field and Lookup table linked fields the field project_manager

Best,

 

[James Corden]

Thanks - I can definitely set PROJECT_MANAGER to be unique (there are only about 7 project managers). This could be trickier for one of my other look up tables (which has 2000+ entries) but I'll have a think about how to address this now I know that the field must be unique.

 

[eugenio]

If it's not unique and you don't care about selecting a specific record of your lookup table you can change the CREATE VIEW to something like

CREATE VIEW project_managers_2 AS SELECT distinct project_manager FROM project_managers

but if it's not unique, in most of the cases there is something wrong in the design of the schema.

Best,