SQL select-object saves primary_field_id Topic is solved

[ChrisW]

Hi,

first of all, I would like to thank you for the good work with nuBuilder

I'm a little bit stuck and I'm afraid that the following question is a bit too stupid for asking it ...

Reference Post (locked):
viewtopic.php?p=16324#p16324

I have two tables.
The (browse-)edit-form calls a user-select(ion) from table2 and is saving that selection and other field data into table1

Like in the reference-Post actually the select-object looks like this:

Code: Select all

SELECT
    field1_id,             ##-- hidden value that will be saved.
    field2		   ##-- value that will be chosen by the user
FROM table2
ORDER BY field2

After saving the form, the user gets the field1_id in browse-form (and that looks not user-friendly )

Assuming that everything is working as designed, I would like to save field2 (lets say the chosen value) into table-1, not field1_id

Does anybody has a hint for me ?

Many thanks and best regards,
Chris

[kev1n]

Hi,

It is generally recommended to store the identifier rather than the text itself in terms of database normalisation rules.

To display the value in the Browse form, you will need to utilise a JOIN. Similar to what is shown here:
https://github.com/nuBuilder/nuBuilder-4.5/issues/28

Alternatively, if you choose to store the value in the db, write your SQL statement as outlined below:

Code: Select all

SELECT
    field2,             ##-- hidden value that will be saved.
    field2		   ##-- value that will be chosen by the user
FROM table2
ORDER BY field2

Also see: viewtopic.php?p=28737#p28737

[ChrisW]

Hi Kev1n,

thanks a lot, I will try to change the query like stated in the github issue. I'll keep you updated.

Best regards,
Chris

[ChrisW]

Hi Kev1n,

I implemented the demand like stated in the Github-Case and its working fine.

Code: Select all

table1 		table 2
field1t1_id	field1t2_id
field2t1	field2t2

The browse-form calls table2.field1t2 instead of table1.field2t1

The edit-form for posting in table1.field1 selects the list of table2

After save the table2.field1t2_id will be inserted in table1.field2t1

As a result, to have table2.field2t2 correctly displayed in the browse-form I have the following Query:

Code: Select all

SELECT *

FROM
    table1

LEFT JOIN table2
ON table1.field2t1 = table2.field1t2_id

And thats working. However, I didn't need an additional column.

Thank you very much for your hint

Best regards,
Chris