DatabaseJoin Dropdown Issue

[cire.nosravlah]

Database = Inventory
Table = Stockrooms
Stockroom_ID Stockroom_Name
1 North
2 East
3 South
4 West
Table = Inventory_Workers
Worker_ID Worker_Name Warehouse_Assigned
1 Jack Stecker 1
2 Kenneth McCoy 1
3 Malcolm Pickering 1
4 Ernestine Sage 2
5 Edward Banning 3
6 Hon Son Do 4
7 Ellen Feller 4
8 Charles Galloway 2
9 Carolyn McNamara 3


Connection = Inventory
List Database table = Inventory_Workers
Join = Right Join, from Inventory_Workers to Stockrooms from column Warehouse_assigned to Stockroom_ID

The Element Options for Warehouse_Assigned was changed to Render As Dropdown
Connection= Inventory
Table Stockrooms
Value = Stockroom_ID(recommended)
Label = Stockroom_Name

When I view data and use Edit, the drop down box is there and functions correctly for the most part. Say for example i try to change worker_id 4 Ernestine Sage from Stockroom 2 to stockroom 4
I get a SQL error telling me: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 SQL=UPDATE `Stockrooms` SET `Stockroom_ID`='4',`Stockroom_Name`='East' WHERE

If I go back to the list, it updated the entry correctly under Warehouse_Assigned... but for some reason it is trying to update both tables, instead of just the one.

I have tried Inner, Left and Right joins with the same results.

Any ideas how to fix?

 

[Bauer]

Why do you need to set a join in the database? It's farting out wit the error because you are trying a may-to-one join. (many workers might have the same warehouse assigned)

You don't need to create a join in the actual table. Just add a databasejoin element to the Workers group named 'Warehouse_Assigned' which uses the id of Stockrooms table as it's stored value. When you select a value from that Stockrooms select list - that value is stored in the Workers table - and that is the value that will remain as 'Warehouse_Assigned' unless/until you select a different one in a later edit of the row.

 

[cire.nosravlah]

Why do you need to set a join in the database? It's farting out wit the error because you are trying a may-to-one join. (many workers might have the same warehouse assigned)

You don't need to create a join in the actual table. Just add a databasejoin element to the Workers group named 'Warehouse_Assigned' which uses the id of Stockrooms table as it's stored value. When you select a value from that Stockrooms select list - that value is stored in the Workers table - and that is the value that will remain as 'Warehouse_Assigned' unless/until you select a different one in a later edit of the row.

Thank you! That was it. I am used SQL queries and having to do joins to get the data you want to display.