Populate/Update child record's date field based on parent relationships

[ehwalker]

I have a table DRUG.ADMINISTRATION with 2 table occurrences relating to the tables EXAMS, and ANAESTHESIA, both of which I'm considering as parents as DRUG.ADMINISTRATION records need to be created from each of these tables via a portal on their layouts. Within DRUG.ADMINISTRATION there is a field DATE which should capture the DATE of the parent.

Currently, I have two table occurrences of DRUG.ADMINISTRATION; one setup with a relationship to EXAMS via DATE and EXAM.UUID and one setup with a relationship to ANAESTHESIA via DATE and ANAESTHESIA.UUID. This works fine when creating records, but if the DATE field is modified in the parent table DATE is not updated in the child table and the relationship is broken. I need this DATE field to update automatically if the DATE field is modified in the parent table.

I had thought to try an auto-enter calculation/lookup or calculation field to capture this date, but because I have the two occurrences of DRUG.ADMINISTRATION there's no way (that I know of) to set this calculation to evaluate in the context of the appropriate table occurrence. 

Any solutions to this? Perhaps this setup is not best practice as I'm struggling to find a solution (not requiring scripts), so any advice is welcome! Thanks.

[comment]

33 minutes ago, Eli Walker said:

I need this DATE field to update automatically if the DATE field is modified in the parent table.

I believe the simple solution is to base the relationships on the UUID fields only. Then just show the date of the corresponding parent record.

 

[ehwalker]

Thanks Comment! Very simple, but my concern there is with data export. When exporting, would one then need to export records from each table occurrence individually to ensure that the DATE field is included in the export? This is why I was trying to populate the DATE field with the actual date rather than just showing the date through the relationship... as I thought it would cause issues with data export?

[comment]

If you want to have a local Date field, and you want it to automatically update when the date of the parent record is modified, then you must make it an unstored calculation field - say something like::

If ( not IsEmpty ( EXAM.UUID ) ; EXAMS::DATE ; ANAESTHESIA::DATE )

Alternatively, you could just include the two parent DATE fields in the export field order - but then they would show in separate columns.

 

[ehwalker]

Thanks! As I have two table occurrences of DRUG.ADMINISTRATION (one to EXAMS and another to ANAESTHESIA), does this calculation work? It would have to be set to evaluate based from one of those occurrences, yes?

[comment]

Sorry, I assumed you had dedicated TOs of the two parent tables attached to the "main" occurrence of DRUG.ADMINISTRATION (by "main"  I mean the one on which the layout of the table is based and the one you would export from). Without this, you would need another calculation field evaluated from the context of the "other" TO of DRUG.ADMINISTRATION.

 

Edited September 9, 2019 by comment

[ehwalker]

Ah, well that's certainly an easier solution that's quite obvious now that I think about it 🤣. Thanks for the help, really appreciate it!