Dataverse querying limitations

[PaulD1]

I'm pretty sure the answer is going to be 'you can't do that in Dataverse' but just in case...

The system has table Appointment with a polymorphic join to Job (there can be multiple appointments for each Job though only one should be 'live' at any given time), Job has a normal join to Job Payments (one Job can have multiple payments).

Further, Appointment has a join to Appointment Movement (one Appointment can have multiple movements) - the most recent movement is the current state of the Appointment.

I want to display in a canvas App, where performance is critical (so need Dataverse to perform the query and just return the relevant results) all past appointments where the most recent movement is not 'completed' and where the sum of the Job Payments are less than the cost of the Job (from the Job table).

It would take me a few minutes in SQL to create a view which I can consume in Power Apps.

Can anything similar be done in Dataverse without resorting to work flows, redundant relationships and so on?

[cchannon]

So, your Sum() operation there complicates things a bit here, but yes, you can do this in Dataverse, and you could probably get all your data in a single query without any structural changes, though it will be much easier to accomplish with some other tricks.

First, let's address it simply with queries since that's where you're coming from, then we'll talk about a different approach that will simplify things a great deal for you:

Query only: 

It seems to me you have a relationship structure like this:

And what you're trying to do is get a list of Appts based on a Max() value from Movement and a Sum() from Payments, that about right?

While you could use ODATA to make more sophisticated queries including the outer joins you're looking for, it is probably most intuitive to accomplish this through two queries:

- Movements where Appointment is Active

- Payments where Job is active

Assuming you're not dealing with huge data volumes, that should get you down to just the 'active' space of your jobs at which point you can sum your payments and max your movements and link the two through the Appointment.JobId (Sadly, this involves looping because PowerApps doesn't have really good multithreaded array operations)

Rollup Column Approach:

However, there is a much slicker and easier way to accomplish all this with a single query just by making a couple small schema changes. Key to this are "Rollup Fields" which let you assign operational aggregation logic to a field  which Dataverse will manage for you in the background based on related entity values. Go to your Job and right next to your 'cost of the job' column put another column for 'sum of payments'. Set the column type to currency and where it says, "calculated or rollup" click Add -> Rollup.

From there, I think you'll find the ui pretty intuitive. Select your related entity (Payments), Apply any appropriate filter (such as "not cancelled") and set the operation to SUM --> payment amount. Then you can do exactly the same thing on the other end with a MAX operator to pick the most recent movement and populate its appropriate data onto the Appointment.

Once that's done, your query becomes incredibly easy. You're just looking for appointments where appointment.movementfield ==XXX and !(appointment.Job.sumofpayments < appointment.Job.costofjob)

Fast, Easy, and when you go to implement your customer's next business rule related to these values, it is already calculated for you. This is definitely the approach I would recommend.

Enjoy!

[PaulD1]

Thanks very much - I'm going to run that past our D365 guys (they take care of the backend, I do the Canvas App) and see what they come back with. Hope to report further in a couple of days.

As mentioned, I really need the query to delegate as we are dealing with large volumes (hundreds of appointments per day) - ideally I want to use the capability of galleries, when used with a delegable query, to be able to page through 100 records at a time, so I don't think the OData method (which would involve using Power Automate to execute the OData query and pass back the data in a single block?) will work.

Thanks again and will try to update this thread shortly.

[Fubar]

What wasn't mentioned in the earlier explanation was that the out of the box Rollup functionality is not real-time, and so if you need it to always be up to date, there is additional customization required (usually a Plugin or Custom workflow activity, but depending on the situation could be JavaScript )

[PaulD1]

Ah - it really does need to be real-time. There are multiple users who may be recording payments and other users who would be checking to see if all payments are made before completing the appointment. As such, I think delays in this data being aggregated could result in issues / confusion among the users.

Fortunately, I think we have found a different way of meeting the requirement without having to try to implement a relational query from within a Canvas Power App, but I really appreciate the suggestions for trying to meet this requirement - THANK YOU!