How to add records to three tables at once

[cigarprofiler]

Hi all,

My project is a database of cigars. The cigar name consists of three parts: brand name, line name and model name. Most info is pertinent to the model and stored in that table. It is linked to the line table with lineid as foreign key. In turn, the line table is linked to the brand table with brandid as foreign key.

To add a cigar, a user would typically start with selecting a brand , then a line (if applicable, can be blank), then add the model and input the data. But from a db perspective, I have to start with the model table, which contains no reference to a brandid (which is stored in the line table only).

How can I resolve that?

[Vitalikssssss]

Hi cigarprofiler,

 

I would suggest passing brand ID parameter from "brand" upon submission of new brand to "line" submission form.

"Line" submission form can have hidden field Brand_ID which will receive parameter on load.

 

Here is some useful links on parameter passing:

Passing-parameters

Receiving-parameters

 

 

[Mathilda]

21 hours ago, cigarprofiler said:

Hi all,

My project is a database of cigars. The cigar name consists of three parts: brand name, line name and model name. Most info is pertinent to the model and stored in that table. It is linked to the line table with lineid as foreign key. In turn, the line table is linked to the brand table with brandid as foreign key.

To add a cigar, a user would typically start with selecting a brand , then a line (if applicable, can be blank), then add the model and input the data. But from a db perspective, I have to start with the model table, which contains no reference to a brandid (which is stored in the line table only).

How can I resolve that?

From your description it seems that you need to use many-to-many relationship and store brand name as well. I described how to build a many-to-many relationship here

[cigarprofiler]

Hi Mathilda,

I saw your post about many-to-many relationships when I was searching the forum, but I didn't think it applied. The three tables are linked by two one-to-many relationships:

One brand can have many lines

One line can have many models

It's more like a cascading thing than a many-to-many relationship. It works for me in Caspio for viewing data, but not for adding. I don't know if linking is allowed, but you can see my first attempt here:

http://www.cigarprofiler.org/p/find-cigar.html

A good example would be to search for the term "kristoff".

Thanks for listening!

[cigarprofiler]

Thank you, Vitalikssssss, I'll give this a try.

[cigarprofiler]

On ‎31‎/‎03‎/‎2017 at 11:55 AM, Vitalikssssss said:

Hi cigarprofiler,

 

I would suggest passing brand ID parameter from "brand" upon submission of new brand to "line" submission form.

"Line" submission form can have hidden field Brand_ID which will receive parameter on load.

 

Here is some useful links on parameter passing:

Passing-parameters

Receiving-parameters

 

 

Hi Vitalikssssssss,

It worked, thank you for your help. I have one additional question:

From the first form, I pass the lineid to link to the next form. So far, so good.

From the first form, I also pass the brandid.

In the second form (which is based on the models table), I can then retrieve the brandid as a virtual field. My question is: is there a way to look up the brandname in the brands table? That would be a "nice to have", not strictly necessary to make the submission work.

In more general terms, I guess my question is: can I automatically look up a value based on a passed parameter in a table that is not in the table on which the DataPage is based?

[Vitalikssssss]

16 hours ago, cigarprofiler said:

Hi Vitalikssssssss,

It worked, thank you for your help. I have one additional question:

From the first form, I pass the lineid to link to the next form. So far, so good.

From the first form, I also pass the brandid.

In the second form (which is based on the models table), I can then retrieve the brandid as a virtual field. My question is: is there a way to look up the brandname in the brands table? That would be a "nice to have", not strictly necessary to make the submission work.

In more general terms, I guess my question is: can I automatically look up a value based on a passed parameter in a table that is not in the table on which the DataPage is based?

 

 

 

 

Hi cigarprofiler,

You can retrieve the brand name by using Cascading elements.Cascading element will filter the brand table by value in the virtual field (brand_id).

Here is a link to the tutorial article: Cascading-elements

Hope this helps