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

[cfebvre]

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.

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.

[SBax · Accepted solution]

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]

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]

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!