Auto-Populating Foreign Key Via Relationship

[messadua]

Hello everybody,

 

I am working on a Metanalysis DB and I have currently 2 tables: STUDY (Parent) and REFERENCE (Child). The STUDY table has a primary key __pK_StudyID, while the REFERENCE Table has a primary key __pK_RefID and a foreign Key _fK_StudyID.

I have set up a relationship one to many between STUDY and REFERENCE so that __pK_StudyID (STUDY) --> _fK_StudyID (REFERENCE), and I have allowed record creation through that relation.

Now, as expected, when I create a Portal of REFERENCE fields in the STUDY layout table, I am able to create new records (that is new references for the same study) and fK is automatically populated.

What I would like to do though is to be able to auto-populate _fK_StudyID and add new records to the REFERENCE Table using the same layout (i.e. the STUDY layout) but without a portal.

If I move the REFERENCE table fields into the STUDY layout (without a portal), everytime I create a new Study, a new reference is created and _fK is auto-populated as expected.

The (hopefully silly) problem come when I try to create a new record in the REFERENCE Table (that is to add a new Ref. for the same Study):

I have created a simple script that:

- Create a variable: $StudyID to store the specific __pK_StudyID

- Go to the REFERENCES layout

- Create a New Record

- Set field _fK_StudyID (of the REFERENCE Table) to have the value stored in $StudyID

- Go back to the original (Study) Layout.

 

Now the script works flawlessly, and the new records (i.e. new references) for the specific study are correctly created, so that when I go to the REFERENCES layout I can see them. Unfortunately, in the Study Layout I can see only the first reference I have created, not the additional ones. 

Of course, when I put the same fields of REFERENCE Table in the STUDY layout inside a portal I can see all new records I have created....

 

Anyone can give me a reason fro that ? At this time, the only way I have to create study related references (and see them) is changing layout and that is not the most efficient way to face the problem.

 

Thank you always in advance for your help

 

 

 

 

[eos]

What I would like to do though is to be able to auto-populate _fK_StudyID and add new records to the REFERENCE Table using the same layout (i.e. the STUDY layout) but without a portal.

 

Obviously you can do that (and are doing it with your script); what you cannot do is display either multiple or a specific related Reference record(s) without a portal. You'll only ever see data from the first record via the relationship's sort order (or. lacking an explicit sort order, the first one created). 

 

If you want to see a specific child record, you need an additional field in Study, say, Study::mostRecentReferenceID, and a new TO of References, say, References_mostRecent. Then create a new relationship 

 

Study::mostRecentReferenceID = References_mostRecent::__pK_ReferenceID

 

When you create the new Reference record, capture its primary key, and on your return to the Study layout, set the mostRecentReferenceID field to its value. So you have

Set Variable [ $StudyID ; Study::__pK_StudyID ]
Go to Layout [ References (References) ]
New Record/Request
Set Field [ References::_fK_StudyID ; $StudyID ]
Set Variable [ $ReferenceID ; References::__pK_ReferenceID ]
Go to Layout ( original layout ]
Set Field [ Study::mostRecentReferenceID ; $ReferenceID ]

[messadua]

Great EOS !!

 

Thank you so much for your help, really appreciated

Now the question is: if I have understood your perspective, by appropriate scripting, creating an appropariate field and an additional TO, I can decide what record (other than the first one) of the REFERENCE Table to show in the Study layout, but there is no way to show ALL child records (i.e. references) for the same study in the original layout without a portal...is that correct ?

 

Anyhow, you have been extremely helpful, as usual !

[eos]

there is no way to show ALL child records (i.e. references) for the same study in the original layout without a portal...is that correct ?

 

This depends on what you define as “record”; you can list data from the related records, but not the records themselves (whatever that may mean exactly).

 

I can decide what record (other than the first one) of the REFERENCE Table to show in the Study layout,

 

Not really – the way this is set up now, it will just be the last one you created. But read on …

 

but there is no way to show ALL child records (i.e. references) for the same study in the original layout without a portal...is that correct ?

 

There are a number of options (using List() with a calc field in Study, or in the code of a Web Viewer, or as a value list) – but what do you have against a portal? This would also allow you to use a script to set the new ID field* in Study to the referenceID of the clicked portal record, then show a detail view in the Study layout via the new relationship (and use Conditional Formatting to highlight the current References record in the portal).

 

This would actually be “deciding which related record to show in detail”.

 

And since you create new Reference records via script, you can disallow the creation of related records for the relationship and get rid of the spare row in the portal.

 

A way totally without a portal would be to create a value list from the primary referenceID and some human-readable field, and make it a conditional value list by using your Study TO as the starting point; then format the new ID field with that value list. Not always the best choice, user interface-wise. 

 

(* which then should best be renamed to “selected” or “current” ReferenceID; ditto the TO)

[messadua]

Dear Eos,

 

Again, thank you so much for the time you have spent for me and for the thorough explanation provided.

I do not have anything against portals, actually I love them, the only issue is the user interface: my envision for this project was a unique Layout (starting from the Study Table, but that is not critical) where I could insert all fields from related tables (indeed REFERENCES is just one of the several tables I will have to ultimately include) so that when  a specific study is selected I could scroll all related references, as well as all results (many for each study) outcomes and so on, going from the first related record (i.e. the first related reference) to the following.

Portals of course could make that very easy, though at the cost of having a tabular layout for related fields (i.e. a table where all related fields of interest in the REFERENCE table are listed for each study)....that is fine when the fields are not many, but could become a pain for many related fields.

So I guess now the question is: it is possible to define a portal so that the fields are not necessarily in tabular format but could be viewed as a form ? I think you can reach an almost-there result just increasing the height of each row in the portal, and organizing fields inside the portal as a form but that is still not quite what I would have liked to get (I am not even sure I can make myself clear here).

Anyhow, I will study your suggestions, and, in the worst case, I will rely on different layouts for STUDY-related tables, and on appropriate script to populate fK on different tables.

 

Have a great day !