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

Idea created by lygismav on Jan 19, 2017
    • lygismav

    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.



    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.



    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.



    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.



    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.