cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cfebvre
Kudo Collector
Kudo Collector

Model Driven App with Virtual Tables - How to Calculate Total of Column

Hi All,

 

I'm constructing a model driven app using Virtual tables (from SQL). I've got the virtual connector working fine, and have 2 tables with relationships.

 

Now what I'd like to be able to do is calculate the total figure (based on view) of a column in one of the tables and display that total somewhere (or store it in a column if required).

 

For context, the primary table is a list of clients, and a related table is the advertisement bookings associated with that client. The advertisement bookings contains a cost column against each ad booking, and it's the cost column I'd like to total.

Figure 1: This is a subgrid inside my client table form. I'd like to total the cost column and display the figure somewhere. It could be written to the Total Revenue field.Figure 1: This is a subgrid inside my client table form. I'd like to total the cost column and display the figure somewhere. It could be written to the Total Revenue field.

The problem I'm facing is that because the tables are virtual, it's not possible to create calculated or rollup columns.

 

I've seen it mentioned that a javascript web resource could potentially calculate columns, but I'm not certain how to go about this, or even if it is a possible solution in my case.

Is someone able to tell me if javascript could do what I need? Don't necessarily need a full solution here, just a nudge in the right direction would be great if anyone knows.

 

Cheers in advance.

 

Chris.

1 ACCEPTED SOLUTION

Accepted Solutions
SBax
Impactful Individual
Impactful Individual

Hi @cfebvre

 

I will be clear, I haven't tried this with a virtual table before, but I have managed it with a standard Dataverse table using getRows

 

The method is detailed in this documentation 

 

It's fairly simple to do, so certainly worth a try, no guarantee that it will work with a virtual table though

 

Hopefully this works for you!

 

If it doesn't, you could consider using a Dataflow to get your details from the SQL table, with a periodic refresh. As this would be a standard table, you would be able to leverage rollups, calculated fields, etc

 

Both options are potential solutions for you 

View solution in original post

3 REPLIES 3
SBax
Impactful Individual
Impactful Individual

Hi @cfebvre

 

I will be clear, I haven't tried this with a virtual table before, but I have managed it with a standard Dataverse table using getRows

 

The method is detailed in this documentation 

 

It's fairly simple to do, so certainly worth a try, no guarantee that it will work with a virtual table though

 

Hopefully this works for you!

 

If it doesn't, you could consider using a Dataflow to get your details from the SQL table, with a periodic refresh. As this would be a standard table, you would be able to leverage rollups, calculated fields, etc

 

Both options are potential solutions for you 

cfebvre
Kudo Collector
Kudo Collector

Hi @SBax - thank you very much for the assistance and suggestions. I'll give these a whirl this week and let you know how I go. Cheers.

cfebvre
Kudo Collector
Kudo Collector

Hi @SBax , just a (late) response to this, sorry. Had to go on leave. I gave this a try but didn't get very far. One problem with it was the the getRows only return results for the current page of a subgrid (as far as what I could tell by what I read), not all pages, so totals would not be accurate - but I did not get this far with it. I suspect it wasn't working because I'm operating with a virtual table, not not entirely certain about that.

However, I realised the the Reports functionality in model driven apps and solutions would accomplish what I was after in terms of allow me to sum the values of columns (and provides a bunch of other options as far as reporting on data is concerned) - so I opted not to worry about displaying a sum total with the web forms themselves.

I'll mark your answer as the solution, though, as there could be other people needing what you describe who aren't using virtual tables (or might be able to get it to work with virtual tables anyway).

 

Cheers!

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Kudoed Authors
Users online (1,729)