In one of my apps, I have a process where I choose an Employee and a Date using QuickEdits in a Detail view. This table also has a set of calculations run against Slices on several tables. The idea is than when I change the Employee and/or Date, the Slices collate the rows for that Employee and Date and then the calculations produce SUMS on the gathered rows.
When I change Employee or Date, several Views will show the changed collection of rows in each Slice instantaneously, However, it appears that the SUM calculations are not run after the Slice data has changed - I assume it happens before. If there is a change to another Employee again, THEN I will see the calculation results from the previous Employee selection but will be out of sync with the rows shown in the Views.
When I move the calculations to Virtual Columns, the results do not show immediately but do eventually appear. I might have to wait several minutes for the background Sync to run and re-compute the VC’s.
I don’t recall seeing this behavior in the past. For the most part Slices are treated like tables. It seems for the purposes of re-calculating, Column calculations should wait for any Slices updates first and then recalc the columns. So it seems like order of processing would be,
Table/row updates
Slice updates
Recalc of table columns
Recalc of VC’s
Am I missing something? Is there some way to force the column re-calc immediately after Slices have updated. Does the odd processing order have anything to do with the changes made in a QuickEdit?
If no one knows, I will test in a new app when I get time.
EDITED: I goofed!! Explained in the resolution post below. Thank you to all who offered help!!!
I noticed it also. Actually, we used to think/know that virtual columns are calculated on the fly.
The reality is slightly different: it will be calculated on the fly, provided that the current record is related to your change.
Here is an answer I got from the support a few months ago:
The behavior you’re seeing is expected. Virtual columns are only recalculated locally when:
The row itself is directly updated.
Another row that references the current row is updated.
If your virtual column uses an expression like SELECT() over another table, the result may not immediately update between syncs if changes occur in the other table. This conservative approach helps avoid performance issues, as recomputing virtual columns across a large number of rows could make the app unresponsive.
To ensure up-to-date information, you can try one of the following methods:
Sync the app manually or wait for an automatic sync.
Update the row containing the virtual column or update a referencing row.
Move the virtual column expression into a slice filter if your slice depends on the virtual column, which will force the filter to recompute as local data changes.
After this, I used a grouped actions to force some records to be recalculated. Not perfect, add more syncs, but at least it filled my purposed.
Slices:
Slice 1 - Gathers rows on Table A for Selected Employee/Date for Sum 1
Slice 2 - Gathers rows on Table B for Selected Employee/Date for Sum 2
Slice 3 - Gathers rows on Table C for Selected Employee/Date for Sum 3
The expression to calculate Sum 1, Sum 2 and Sum 3 follows pattern:
_**totalhours(sum(Slice 1[Duration column]))**_
I tried implementing Sum columns in 2 ways:
Table column - Views show rows immediately but Sums are not correct. Select NEXT Employee, Views show rows for the next Employee immediately BUT Sums reflect values for first selected Employee.
This tells me that the Sums are being calculated BEFORE the Slice Rows have been changed based on the new selection
Virtual Column - Same basic behavior as in 1) EXCEPT after a few minutes the correct values will be shown.
This corroborates the theory that the Sum calculations are being performed BEFORE the Slice rows have been updated.
Obviously, I am trying to leverage the Slices used in the Views to calculate Sums. But it seems to me there is a timing issue with when the column calculations are performed and the Slice Rows are made available.
As I was typing this out, it occured to that I could probably replicate the Slice collations in the columns. But that’s not really the point.
It seems to me in ALL regards, Slice updates should be performed before any calc or recalcs are done. Am I wrong?
I will setup a test app to demonstrate this and submit to support OR it will highlight there is something wrong in my setup in the app.
However, my complaint isn’t about Virtual Columns. Rather it’s about the timing of when calculations are performed in regard to Slice updates.
I probably didn’t explain it well…but it seems that when a change is made that impacts Slice content on other tables, calculations within columns of the changed row are being performed BEFORE the new Slice content is made available.
I am suggesting that the reverse should be happening…i.e. on any row change ALL Slice updates should be performed first and then column calculations ran.
I get it, thanks. Basically, you are suggesting that calculation propagation should just make sense
Suggestion1:
Because you are using a quick edit, you can’t use an “on form save event” sequence of actions, but you may want to use a bot that will trigger the summary result to be updated - still a few seconds of delay, but could avoid the whole sync/resync/recalc procedure.
Suggestion2:
Do you think a different expression that would include the slice row filter condition for your sum1, sum2, sum3 would be calculated more efficiently? Such as:
Shame on me!!! I have realized, after digging into this deeper that I had a sort-of circular reference thing going on which is why the calculations were not updating after the Slices were re-populated.
When changing Employee and or Date, the rows are updated PER app user. I used a Slice in the app to isolate the current User’s filter row. The various data Slices use the Filter Slice to identify the included rows. That Filter Slice row was then trying to use the data Slices to perform calculations.
So basically the data Slices were dependent on the Filter Slice and then the Filter Slice row calcs were dependent on the updated data Slices.
I corrected by having the calculation columns replicate the filter logic from the data Slices and go directly to the datasources to filter rows and Sum up that list of values.
The Slice are still necessary for visualization of the rows.
Thanks for the follow-up. I’m just seeing it now. Your Suggestion #2 is exactly what I ended up doing after I realized I was suffering a kind of circular reference problem - explained in the resolution post.
I am always impressed by your posts! It’s nice to know that others are thinking outside the box as well!!
