most efficient method for dataverse complex join in powerapps?

[gmarzloff]

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!

[dpoggemann · Accepted solution]

Hi @gmarzloff ,

First thing, 500 records is very small from a data size perspective and the queries should be very quick against this size table.

From a table design, would this be a possibility to simplify your model?

1. Admissions Table - ID, Admission Date, Discharge Date
2. Referrals Table - ID, Admission (optional)

You can enable auditing on the Admissions table, specifically for you two fields for Admission Date and Discharge Date and this will maintain a history of any changes with who made the change, prior value and new value.  https://learn.microsoft.com/en-us/power-platform/admin/manage-dataverse-auditing 

If you did not want to use Auditing you could setup a real time workflow that would add records to another custom table anytime either of these fields change.

With the updated model above I would think you could populate the calendar view very easily with the Admissions table and the representative dates.

[gmarzloff]

Thanks @dpoggemann. Auditing is new information to me! Unfortunately I don't have sysadmin privileges to enable it, and I wonder if it is available within Dataverse for Teams, where this app lives.  Also, the tables of admission and discharge histories also have other columns such as the reason for the change, which i didn't mention in the original post. 

I think I will use part of your idea - when a change is made, update the admission and/or discharge dates in columns of the admissions table, and then Patch in a new record in the tbl_admission_date_updates or tbl_discharge_date_updates to record the reasons.  This will allow for a simpler join query in the app and still keep the changes data for analysis.