Virtual column changing values

Seth_Berman · 02-24-2020 08:35 PM

Not sure if this is expected behavior or server memory management or something. We have a table with 3 columns that users edit in detail view to set a date range and client selection in order to filter data from a separate data table containing a few thousand rows. There are half a dozen virtual columns that then recalculate based on these criteria to create a real time report. While working on the report this evening I happened to leave it up on my screen for 10 minutes while working in another window, when i went back to it the numbers had changed in the virtual columns! In this case the dollars being reported had gone down by over a couple hundred thousand dollars so i was a bit surprised to say the least. i recreated it a few times to be sure. In each case the virtual columns changed but the values entered into the 3 criteria columns did not. is this behavior due to the virtual columns losing track of rows? How long do users have to review a virtual column before it changes and they can no longer “trust” it?

Aleksi

It sounds that the app made a auto sync because it’s doing it for every 30 minutes if you have correct Sync options set as ON. Now if someone has changed values while your app was down, and when your app was resyncing, it used new values and result was different and you saw that change. Is this be possible in your case?

Seth_Berman

Im afraid not in this case. no other users made changes to the data table during that time.

Koichi_Tsuji

May I ask which browser you use ? Chrome or IE?

Seth_Berman

Chrome, and thank you.

Koichi_Tsuji

Okey, on IE, I see odd stuffs when my end user uses my apps, so just possibility.

Bahbus

Switch to the Chromium version. It’s much nicer, and I like it better than regular Chrome.

Seth_Berman

thanks for the suggestion but it would probably be to big an ask to have our end users all switch to chromium.

Bahbus

Oh, sorry, that was meant for @tsuji_koichi.

Seth_Berman

i dug a little deeper. the totals i am getting in the VCs are not correct even at the start before they shrink. this might mean my formula is wrong. however i added another VC for row count, even as the VC’s shrink their dollar amounts the row count VC stays the same, maybe the calculation is changing in the VCs?

Bahbus

Let’s take a look at the calculation.

Seth_Berman

thanks!
so the sales helper table contains the data and the sales helper report table contains the constraint columns and the VCs.

sales helper report first uses a vc to grab a list of records from the sales helper data table:
SELECT(
Sales Helper[Sales Helper ID],
AND(
([Timestamp] >= [_THISROW].[Start Date]),
([Timestamp] < [_THISROW].[End Date]),
IN([Client],[_THISROW].[Client])
)
)

then each VC that does calculations or shows a filtered list uses that select. it does this for Billable Supplies, Non-Billable Supplies, and Fixed Assets. I also added a row counter to see if it decreased when the other VCs changed. it did not.

Billable Supplies:
SUM(

SELECT(

[Select Sales][Cost],AND

(
[Timestamp] >= [_THISROW].[Start Date],
[Timestamp] < [_THISROW].[End Date],

[Client Billable] =“TRUE”,
[Category]<>“Client Startup Fixed Assets”,
TRUE
)
)

)

Billable Supply Orders:
FILTER(
“Sales Helper”,
AND(
[Timestamp] >= [_THISROW].[Start Date],
[Timestamp] < [_THISROW].[End Date],

		[Client Billable] ="TRUE",
[Category]<>"Client Startup Fixed Assets"
		)
	)

Count Records:
COUNT(
[Select Sales]
)

Steve

Reformatted for readability:

SELECT(
  Sales Helper[Sales Helper ID],
  AND(
    ([Timestamp] >= [_THISROW].[Start Date]),
    ([Timestamp] < [_THISROW].[End Date]),
    IN([Client],[_THISROW].[Client])
  )
)

Billable Supplies:

SUM(
  SELECT(
    [Select Sales][Cost],
    AND(
      ([Timestamp] >= [_THISROW].[Start Date]),
      ([Timestamp] < [_THISROW].[End Date]),
      ([Client Billable] = "TRUE"),
      ([Category] <> "Client Startup Fixed Assets"),
      TRUE
    )
  )
)

Billable Supply Orders:

FILTER(
  "Sales Helper",
  AND(
    ([Timestamp] >= [_THISROW].[Start Date]),
    ([Timestamp] < [_THISROW].[End Date]),
    ([Client Billable] = "TRUE"),
    ([Category] <> "Client Startup Fixed Assets")
  )
)

Count Records:

COUNT([Select Sales])

Steve

Bulk dereferences, like [Select Sales][Cost], seem to be a little flaky: I’ve seen numerous complaints here in the community that they don’t update in a timely manner. Try replacing them with a standard-format SELECT() and see what happens.

Seth_Berman

thanks Steve, ill give that a try to see if it helps. in the long run though, wouldn’t that force me to do a new query to the data table each time, increasing the cost of the VCs?

Steve

Theoretically, yes.

I would assemble a detail view that displays all of the column values from the Sales Helper Report table. When the sum changes to 0, has anything else changed?

Seth_Berman

Steve,

so I updated the Billable Supplies VC as below to do a direct query instead of using the [Select Sales] column. This produced a number that then changed to zero after a 2 or 3 minutes of being on the screen.

SUM(

SELECT( Sales Helper[Cost],AND
([Timestamp] >= [_THISROW].[Start Date],
[Timestamp] < [_THISROW].[End Date],
[Client Billable] =“TRUE”,
[Category]<>“Client Startup Fixed Assets”,
TRUE
)
)
)

Bahbus

That sounds like something in the AND expression is getting changed somehow and kicking the whole thing to FALSE.

Seth_Berman

yeah, its a funny thing. the app isn’t in full production and the table in question is a copy of a different data table to use for testing purposes for a new workflow. the data isn’t changing.