Form setup help involving two listboxes on one filed

[goedible]

how to setup listboxes in libreoffce base.
listbox #1 should lookup cultivation.practice_detail_id and display practices.practice
listbox#2 should lookup cultivation.practice_detail_id and display practice_details.detail
see the image for table relations.

The idea is to be able to see the practice and detail. When changing or creating a new entry I want to select a practice from a listbox and then select the detail from a second listbox. Both listboxes are affecting cultivation.practice_detail_id.
I’ve been working with chatgpt for a couple days and am now looking for some human help.

This needs to be distinct or so that I only see ‘sew_seed’ once

This has to only show “detail” for the ‘sew seed’ “practice”.

Here is a dropbox link to the database. I broke the last version and had to restart from this backup. https://www.dropbox.com/sh/iolsg619yubvgiw/AAB6ty7f_LMuItQLcGFDVKo_a?dl=0

If the above is not possible perhaps this could work. I added a new “cultivation_detail_id” table as slave to details and practice. Then I can copy paste “id” into the cultivation table. Can this copy-paste be automated with a push buttonj or something?

I need a button to put the “id” in to the “cultivation” table.

[RobertG]

Code for the listbox might be:

SELECT "practices"."practice"||' → '||"practice_details"."detail" AS "field",
"practice_details"."practice_detail_id" AS "key" 
FROM "practices", "practice_details"
WHERE "practices"."practice_id" = "practice_details"."practice_id"
ORDER BY "field"

Might be the ORDER BY definition has to contain the whole content of “field”, not only the alias. Depends on the database you use.
So you will get a listbox, which will show both contents - but as you see in your second screenshot the content couldn’t be shown well, because it isn’t possible to show content with a linebreak.

[goedible]

Here is a copy you can see if you can get it working. https://www.dropbox.com/sh/iolsg619yubvgiw/AAB6ty7f_LMuItQLcGFDVKo_a?dl=0

[RobertG]

Have forgotten a comma. Have corrected the code. See above.
The code you have copied seems to be only a part of the code I posted. It ends with a colon (;). Delete all breaks in the code to get it working.

[skyandrews]

The “practice” listbox is indeed Unique as it is bound to the “practice_detail_id” data field.
In other words, each listed “sew seed” represents a Unique “practice_detail_id” where “practice”.“practice_id” (one side) is related to the data sets in “practice_details” table (many-side). I will return with some options… need a little time.

• Combine the “practice” and “practice_detail” as reference text for the “practice_details_id” listbox column. (see RobertG post) The two seperate columns are using the very same referential data field anyway, meaning they can not be independantly changed nor their text edited, hence the logical concatenation.
• If independant changes are desired, add a “practice_id” column to “cultivation” table, then create relationship to parent “practices”.“practice_id”.
  • Add “practice_id” listbox column or Edit the “practice” listbox column in form “cultivation”
  • Set data field = “practice_id” - content type = “table” - content =“practices” - bound column = 1. This column record value can now be independantly modified. By the way, in my copy of the sample there are two (2) “sew seed” records in table “practices” (1 and 102).

[goedible]

BlockquoteCombine the “practice” and “practice_detail” as reference text for the “practice_details_id” listbox column. (see RobertG post) The two seperate columns are using the very same referential data field anyway, meaning they can not be independantly changed nor their text edited, hence the logical concatenation.

I think this is what I want. How to do it? I’m starting to think about another table called “practice_detail_id” to hold a value created with master form practices, subform practice_details and then a “button” to put the value from the new form into cultivation.practice_detail_id.
I don’t know how to make the “button”. I posted a link to download the database.

[skyandrews]

LANdpLAN.odb (666.9 KB)
This is the “cultivation” frontend only, with examples of the existing table control and an alternative table control.