Jump to content
  • 0

How to add records to three tables at once


cigarprofiler
 Share

Question

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?

Link to comment
Share on other sites

6 answers to this question

Recommended Posts

  • 0

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

 

 

Link to comment
Share on other sites

  • 0
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

Link to comment
Share on other sites

  • 0

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!

Link to comment
Share on other sites

  • 0
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?

Link to comment
Share on other sites

  • 0
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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...