Jump to content
  • 0

Left Join to Add/Update Child Records


mgredmond

Question

I have two files: A parent file and a child file in a one-to-many relationship. Like a Purchase Order that has a header (parent file) and the individual lines of the Purchase Order  (child file).

I am using a left outer join (all records in Parent file and matching records in Child file), filtered on Parent File ID. I use this because I want to be able to display even those records with no detail so that I can add the details.

I pass the Parent File ID in the URL to the Child File and everything comes up the way I expect it to, except the Foreign Key does not get populated with the passed parameter. I am clearly missing something in the process.

I am new to Caspio but not new to databases. Can someone point me in the right direction?

Link to comment
Share on other sites

5 answers to this question

Recommended Posts

  • 0

@MayMusic, I have a  SUBMISSION datapage to create the parent file, with a GUID as the Primary Key. Upon submission,  the child file is called via URL with the Primary Key passed as a parameter. In the TABULAR REPORT child datapage, I receive the parameter as a filter using the Parent Primary Key portion of the View. The URL shows that the correct value is being passed and eve received, but I don't know how to get the received value into the foreign key portion of the View.

This is the relationship between the files

image.png.4579be58ad79a6faf48c2d2d72046907.png

 

image.thumb.png.209e191d2d051757c6667327d3ac5d1f.png

 

image.thumb.png.b22ce39b9923b4ed2c0d6af009c88a7c.png

I hope this makes sense. 

Edited by mgredmond
Corrected attribution.
Link to comment
Share on other sites

  • 0

Hello @mgredmond,

 

The way you pass and receive parameters seems correct. If you see the correct hdrid on your URL, you should have been able to filter your DataPage properly using [@hdrid].

If not, you may want to check the following:

 

 1.) Make sure that Bridge and External Parameters are allowed

image.png.fc30b956461a5d1ba21fa21085e83a67.png

 

2.) Make sure that there are existing records in your View. Try opening your view, Copy and Paste the hdrid on your URL.

image.png.6196b3b51b9dd210fff73e3f8d47a9d4.png

 

3.) You might want to check these references to see what you might have missed or just request the Contact Management Ready-Made App:

https://www.caspio.com/apps/

https://howto.caspio.com/tech-tips-and-articles/tech-parameters/how-to-create-a-one-to-many-relational-datapage/

https://howto.caspio.com/pre-recorded-live-training/database-relationships-and-passing-parameters/

 

Finally, you may want to get in touch with Caspio Support so they can better assist you: https://www.caspio.com/support/. You seem to be doing it correctly and this might be a bug/glitch, if we didn't miss a tiny detail in your setup.

 

DN31337

Link to comment
Share on other sites

  • 0

@DefinitelyNot3133, thank you for comments.

Regarding your comment #1, I do have the correct settings for that. And I do see the parameter being passed, because I display it in a HEADER section of the form just to verify that it passed.

Regarding comment #2, I know that is my issue, but I don't know how to fix it. That is the reason I am using a left outer join, because I know I have a header record, but may not have a child record. I want to go to the new form to add the child record. If I filtered the view on the hdrid  on a regular join, I would not get the header or the child because there would be no match in the child table. 

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...
×
×
  • Create New...