Reference tables quandary
I have 3 (Parent) reference tables: Dx Codes Reference, Pcs Codes Reference, and MS-DRG codes. Then there is a Charts table, and two connected tables: Dx Codes and PCS Codes.
The relationships between them are as follows:
- Dx Codes Reference < Dx Codes
- Charts < Dx Codes
- Pcs Codes Reference < Pcs Codes
- Charts < Pcs Codes
- MS-DRG Codes < Charts
Each year the Reference tables get an updated codes list. My plan had been to just add 3 new reference tables each year for the new codes list. but my quandary is how to have a field that picks from the appropriate reference table dependent on the chart discharge date. Is there a way of doing this?
Thanks for any insight you can provide.
-Audra
You can use a Conditional Drop Down to limit what is available from a related record. For your case, I would not build new tables. Add the records to the existing table with a new field for the Valid Year and set it to something consistent like Dec 31st every time. Create Formula Date field in your Charts table to give you Dec 31s of the year of the Chart Discharge.
Then make the conditionals in the other tables match the Valid Year Formula Date with the Valid Year.
