Related Tables: Copy the most recent value from a related table when a new record is added.

330
0
01-19-2017 06:44 AM
Status: Open
MarkVolz
Frequent Contributor

I created a relationship with a carnality of 1:M between Table A and Table B.  Table A contains a list of septic systems, owners, septic types etc.  Table B contains the inspection results fields such as pass/fail, notes, pdf links, etc for each septic system.  Some systems may have one inspection result, while others may have half a dozen or more.

Objective:

My objective is to create a map of the septic systems that displays the most recent result for each inspection.  It is my understanding that we cannot symbolize using related records.

Request:

Whenever a new record is added to table B, it would be useful if we could push the values for one or more of the columns back to table A.

Example:

1) Table A contains septic id, owner, last inspection id

2) Table B contains the septic id, inspection id, inspection pdf link

3) A 1:M relationship is created between table A and B

4) When a new inspection record is added to table B, the inspection ID should be pushed from table B to table A.

Results:

Table A will contain the most recent inspection result ID.  The inspection result ID then could be used in a regular join that in turn could be used to symbolize a map.  The map will be symbolized based on the most recent (entered) inspection result.