[Solved]Form/subform with different foreign keys to same tbl

Creating and using forms
Post Reply
xkanuk
Posts: 5
Joined: Tue Feb 26, 2019 8:28 am

[Solved]Form/subform with different foreign keys to same tbl

Post by xkanuk »

I have a slight twist on the recipe database concept. Here’s a quick example:

In my ingredients table I have things like:
Salt
Oregano
Garlic
Onion
Tomato puree
Spaghetti
Napoli sauce


Napoli sauce is an in-house ingredient that has a recipe. Let’s say the recipe includes:
Salt
Oregano
Garlic
Onion
Tomato puree

Now I want to have a recipe table for my in-house ingredients.
So I imagine the table construct would look like:

Ingredient table:
id_Ingredient: unique key
Ingredient_Name
some other fields

Recipe table:
id_Recipe: unique key
fk_Ingredient: the inhouse ingredient this recipe is for
Recipe_Description
Yield
Comments

Recipe_Ingredient table: (consists of multiple rows per recipe)
ID: unique key
fk_Recipe: referencing the main recipe table
fk_Recipe_Ingredient: this one refers to any specific ingredient in the ingredient table
Amount
Comments

I am struggling with creating a form/subform construct whereby mainform is the Recipe “header” and subform will be a table control that allows me to select ingredients from the Ingredient table.

When I insert a list box column in the table control I can’t seem to get it to select an Ingredient without it changing the “header” ingredient as well.

Not sure how better to describe this conundrum, but am happy to provide what ever extra details required to better describe this problem.

as usual, thank you in advance for your patience, understanding and assistance.
Last edited by xkanuk on Wed Mar 20, 2019 2:03 am, edited 1 time in total.
LibreOffice 6.0.7.3 on Mac
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Form/subform with different foreign keys to same table

Post by UnklDonald418 »

Please save us all some time and effort, upload a sample of your database.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
xkanuk
Posts: 5
Joined: Tue Feb 26, 2019 8:28 am

Re: Form/subform with different foreign keys to same table

Post by xkanuk »

So sorry that I didn't think to provide a sample. In creating the sample I actually fixed the problem. On the table control I had "inserted" a listbox column instead of replacing the id_Ingredient column with a listbox column. I have the form working as intended and am now working out how to add additional information. I am attaching the working sample for closure.
Attachments
odb_test.odb
(21.38 KiB) Downloaded 215 times
LibreOffice 6.0.7.3 on Mac
Post Reply