Left Join to Add/Update Child Records

[mgredmond]

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?

[Vitalikssssss]

Hi @mgredmond,

Do you use View as a Datasource for the Datapage? 

Perhaps you trying to write a parameter into the table which is not editable.

Hope this helps.

Regards,

vitaliksssss

[MayMusic]

How are you passing the parameter? Are you passing an auto-generated value after submission?

[mgredmond]

@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

 

 

I hope this makes sense. 

Edited July 22, 2019 by mgredmond
Corrected attribution.

[DefinitelyNot31337]

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

 

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

 

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

[mgredmond]

@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.