Hello,
I need to create a complex join of Dataverse tables to use in Powerapps. Can you recommend an efficient method to minimize queries and times?
Use case:
a rehabilitation hospital with scheduled admission and discharge dates, both of which are usually changed for various reasons before the actual admission or discharge happens. The history of date changes needs to be maintained. Goal is to have a calendar that reports the daily census and be able to find out which patients are going to be there on any particular day.
Caching stats is not ideal because the team alters dates in a regular meeting and observes changes to the summary censuses immediately, so the calculations need to be updated in real time.
Existing tables with relevant columns
- tbl_referrals: id, admission [from tbl_admissions & optional]
- tbl_admissions: id
- tbl_admission_date_updates: id, admission [from tbl_admissions], new_date
- tbl_discharge_date_updates: id, admission [from tbl_admissions], new_date
I think Step 1 would be to flatten the date updates by creating a new collection with only the most recently changed dates tied to the admission. e.g.
col_admissions_latest_dates:
id, admission [from tbl_admissions], admit_date, discharge_date
Step 2: Filter this collection to just admissions that are present on a given day or date range
Step 3: Filter tbl_referrals records to those with admission.id's that are in the Step 2 results to get the rest of the patient info. (This isn't essential for calculating census counts but would be for drilling into details of who is actually present on a day.
This seems like a lot of queries to accomplish this. tbl_referrals will eventually be over 500 records, tbl_admissions will be less but still a significant proportion of the referrals. Open to any advice to makes this as fast as possible! Thanks!
