Establishing a 1:1 relation with a database join element

[burghard]

To implement a 1:1 relation between two tables I use a database join element. A user should be only able to select a record from the joined table if no other record is already assigned to it.

I tried to implement it with a "Data - where" clause and set the "Joins where and/or order by statement (SQL)". Here I can use the variable {thistable} in combination with "NOT EXISTS" to reference all records that are not already assigned to a record of this table.

It works for all records of my table not already assigned. But when I try to edit an already assigned record the joined field remains blank because the value of the field is not in the list of possible values in the select-input element. When I store that record the join is released in the database. On subsequent edits I can re-assign the record again because it it not assigned in the database.

I need something like all records which are not already assigned to another record OR is already assigned to THIS record. To establish this there is a need to have a second variable e.g. {thisrecord} which points to the actual value of the record.

Does anybody see another way to establish it?

 

[troester]

I just tested: it's possible to use element placeholders in the Data-where, so you can do something like
WHERE ... OR {thistable}.id = '{your-dbjoin-element_raw}'

 

[burghard]

COOL! it works. But why i have add the "_raw" suffix to the name ( I saw that without it does not work).

 

[troester]

'{your-element_raw}' contains the value/what is stored in DB, '{your-element}' the formatted output (label in case of dbjoin, maybe link to details for other elements...)