Virtual Column output is wrong on Test, right after saving, and wrong again after syncing

[bysshe]

Problem: I have a virtual column [d2aRaw] on the table accs. The formula for this column is:

`SUM(SELECT(students[d2aPoints],[cy_campus]=[campus]))’

Note: I have also tried using the above with * 1.0 at the end to force decimalization, but to no avail.

The formula is supposed to pull the values from the virtual column [d2aPoints] on the table students where the column [cy_campus] on students matches the column [campus] on accs, then Sum those values.

What’s happening is that when I input the formula above and click the Test button, I get 170.75 (171 if I leave it as a number) as a result for only the last row. The other two rows have outputs as zero.

When I save the formula, however, the App Preview window shows the correct values (in id/value pairs) of:

1/191.25 2/325.75 3/197.73

This totals 714.75.

When I save in the editor, or sync in the app preview, these results revert to the incorrect ones shown in the test preview. Clicking into any row and editing the entry will show the correctly calculated value, but it will not appear in the prior views.

What I’ve tried: I tried fixing this first by creating the [d2aPoints] column on the student table so I didn’t have multiple VCs on the same table trying to work off each other. That did not work, but was probably better in the long run anyway.

I have run the formula with and without appending [_THISROW] to [campus]. When I made the screenshots, I was already trying anything.

I’ve also tried mixing up the ways I’m bringing in the data from the student table, such as using the same Select() expression, and then splitting it with Split(), then summing, but that also did not work.

What I know: The Select() function is behaving correctly on Test, save, and sync. It’s when I go to actually add those values together that I get problems. I had a similar issue yesterday with the d1 columns you can see in the pictures and I solved that problem by putting VCs onto other tables and referencing them from the accs table. I might be trying to do the same thing with my d2 columns, but doing it incorrectly. I just am getting so frustrated by this weird problem.

[Steve]

You should always use [_THISROW] to avoid potential ambiguity.

Please post screenshots of the configuration screens for the d2aRaw column in accs and d2aPoints column in students.

[CPAB]

I agree, I presume @bysshe summed the whole thing from the first row.

You should do this instead @bysshe ,

SUM(SELECT(students[d2aPoints],([cy_campus]=[_thisrow].[campus])))

[bysshe]

I have since corrected that mistake, I was literally trying anything at the time.

Here is the expression for accs[d2aRaw]

(SUM(SELECT(students[d2aPoints],[cy_campus]=[_THISROW].[campus])))

The following the expression for students[d2aPoints]:

DECIMAL(IFS(
  [elar_pliCY]<[elar_pliPY],0,
  AND([elar_pliPY]=1,[elar_pliCY]=1),0,
  AND([elar_pliPY]=1,[elar_pliCY]=2),1,
  AND([elar_pliPY]=1,[elar_pliCY]>2),1.25,
  AND([elar_pliPY]=2,[elar_pliCY]>2),1.25,
  AND([elar_pliPY]=2,[elar_pliCY]=2),0.5,
  AND([elar_pliPY]=3,[elar_pliCY]=3),0.5,
  AND([elar_pliPY]=4,[elar_pliCY]=4),0.5,
  AND([elar_pliPY]=3,[elar_pliCY]>3),1,
  AND([elar_pliPY]=4,[elar_pliCY]>4),1,
  AND([elar_pliPY]=5,[elar_pliCY]>4),1,
  AND([elar_pliPY]=6,[elar_pliCY]=6),1,
))
+
DECIMAL(IFS(
  [math_pliCY]<[math_pliPY],0,
  AND([math_pliPY]=1,[math_pliCY]=1),0,
  AND([math_pliPY]=1,[math_pliCY]=2),1,
  AND([math_pliPY]=1,[math_pliCY]>2),1.25,
  AND([math_pliPY]=2,[math_pliCY]>2),1.25,
  AND([math_pliPY]=2,[math_pliCY]=2),0.5,
  AND([math_pliPY]=3,[math_pliCY]=3),0.5,
  AND([math_pliPY]=4,[math_pliCY]=4),0.5,
  AND([math_pliPY]=3,[math_pliCY]>3),1,
  AND([math_pliPY]=4,[math_pliCY]>4),1,
  AND([math_pliPY]=5,[math_pliCY]>4),1,
  AND([math_pliPY]=6,[math_pliCY]=6),1,
))

And for full transparency, here’s the forumla for the columns referenced above which are VCs, which include anything anything ending in CY, so students[elar_avgCY], students[math_avgCY], students[elar_pliCY], and students[math_pliCY]:

SUM(SELECT(results[percent],AND([student_id]=[_THISROW].[student_id],[test_category]="local",[test_sub]="ELAR")))
/COUNT(SELECT(results[percent],AND([student_id]=[_THISROW].[student_id],[test_category]="local",[test_sub]="ELAR")))//elar_avgCY

SUM(SELECT(results[percent],AND([student_id]=[_THISROW].[student_id],[test_category]="local",[test_sub]="Math")))
/COUNT(SELECT(results[percent],AND([student_id]=[_THISROW].[student_id],[test_category]="local",[test_sub]="Math")))//math_avgCY

IFS(
[_Thisrow].[elar_avgCY]<ANY(SELECT(pli[DNH],AND([test_grade]=[_THISROW].[current_grade],[test_subject]="ELAR"))),1,
[_Thisrow].[elar_avgCY]<ANY(SELECT(pli[AL],AND([test_grade]=[_THISROW].[current_grade],[test_subject]="ELAR"))),2,
[_Thisrow].[elar_avgCY]<ANY(SELECT(pli[AH],AND([test_grade]=[_THISROW].[current_grade],[test_subject]="ELAR"))),3,
[_Thisrow].[elar_avgCY]<ANY(SELECT(pli[ME],AND([test_grade]=[_THISROW].[current_grade],[test_subject]="ELAR"))),4,
[_Thisrow].[elar_avgCY]<ANY(SELECT(pli[MA],AND([test_grade]=[_THISROW].[current_grade],[test_subject]="ELAR"))),5,
[_Thisrow].[elar_avgCY]>=ANY(SELECT(pli[MA],AND([test_grade]=[_THISROW].[current_grade],[test_subject]="ELAR"))),6
)//elar_pliCY

IFS(
[_Thisrow].[math_avgCY]<ANY(SELECT(pli[DNH],AND([test_grade]=[_THISROW].[current_grade],[test_subject]="Math"))),1,
[_Thisrow].[math_avgCY]<ANY(SELECT(pli[AL],AND([test_grade]=[_THISROW].[current_grade],[test_subject]="Math"))),2,
[_Thisrow].[math_avgCY]<ANY(SELECT(pli[AH],AND([test_grade]=[_THISROW].[current_grade],[test_subject]="Math"))),3,
[_Thisrow].[math_avgCY]<ANY(SELECT(pli[ME],AND([test_grade]=[_THISROW].[current_grade],[test_subject]="Math"))),4,
[_Thisrow].[math_avgCY]<ANY(SELECT(pli[MA],AND([test_grade]=[_THISROW].[current_grade],[test_subject]="Math"))),5,
[_Thisrow].[math_avgCY]>=ANY(SELECT(pli[MA],AND([test_grade]=[_THISROW].[current_grade],[test_subject]="Math"))),6
)//math_pliCY

I appreciate any help and will happily show anything else needed.

[bysshe]

I have tried with that same formula (see the reply to Steve), but still same problem.

[Steve]

In an IF() or IFS() expression, the data type of the expression’s result is determined by the data type of the (first) “then” result. In this expression of yours, the result type will be Number because the result type of the first “then” expression is Number:

All other results of the same IF() or IFS() expression are then coerced to the primary result type. So in your expression, these Decimal values are converted to Number values:

When a Decimal is converted to a Number, the fractional component is lost, so 1.25 becomes 1 and 0.5 becomes 0.

Your use of the DECIMAL() function takes effect only after the IFS() expression has returned its Number result. The fractional part is already lost at that point.

In the expressions you shared, change the Number values to Decimal values by adding a fractional component (e.g., 0 becomes 0.0, 1 becomes 1.0, etc.). You should then remove the DECIMAL() function wrapping the IFS() expressions.

IFS(
  [elar_pliCY]<[elar_pliPY],0.0,
  AND([elar_pliPY]=1,[elar_pliCY]=1),0.0,
  AND([elar_pliPY]=1,[elar_pliCY]=2),1.0,
  AND([elar_pliPY]=1,[elar_pliCY]>2),1.25,
  AND([elar_pliPY]=2,[elar_pliCY]>2),1.25,
  AND([elar_pliPY]=2,[elar_pliCY]=2),0.5,
  AND([elar_pliPY]=3,[elar_pliCY]=3),0.5,
  AND([elar_pliPY]=4,[elar_pliCY]=4),0.5,
  AND([elar_pliPY]=3,[elar_pliCY]>3),1.0,
  AND([elar_pliPY]=4,[elar_pliCY]>4),1.0,
  AND([elar_pliPY]=5,[elar_pliCY]>4),1.0,
  AND([elar_pliPY]=6,[elar_pliCY]=6),1.0,
)

Why the differences in behavior? Because the app that runs on your device or in your browser is different from that on the server–they’re even written in different programming languages! There are a number of inconsistencies in their behavior. This appears to be one of them.

The handling of Decimal and Number values is a common source of confusion. Ideally, AppSheet would treat Number values as Decimal values with the fractional part merely hidden, not dropped entirely, but that’s not how it works.

[bysshe]

I completely follow the logic here. I’ve read your post about why decimal and number values don’t cooperate a lot, and I’ve used it in other places in this app. I changed what you instructed on students[d2aPoints], but I’m still getting the same output in accs[d2aRaw]. I am not sure this is a Number/Decimal problem as much as it is a problem with multiple columns pulling on each other. If the Number/Decimal discrepancy was causing the problem, why would I be getting totally incorrect values for rows 2 and 3 and not just kind of incorrect ones? I will keep experimenting, because part of me thinks it’s how the students[cy_campus] is relating to accs[campus], if that makes sense. Regardless, here are some more expressions to consider after updating:

students[d2aPoints] - Decimal type

IFS(
  [elar_pliCY]<[elar_pliPY],0.0,
  AND([elar_pliPY]=1,[elar_pliCY]=1),0.0,
  AND([elar_pliPY]=1,[elar_pliCY]=2),1.0,
  AND([elar_pliPY]=1,[elar_pliCY]>2),1.25,
  AND([elar_pliPY]=2,[elar_pliCY]>2),1.25,
  AND([elar_pliPY]=2,[elar_pliCY]=2),0.5,
  AND([elar_pliPY]=3,[elar_pliCY]=3),0.5,
  AND([elar_pliPY]=4,[elar_pliCY]=4),0.5,
  AND([elar_pliPY]=3,[elar_pliCY]>3),1.0,
  AND([elar_pliPY]=4,[elar_pliCY]>4),1.0,
  AND([elar_pliPY]=5,[elar_pliCY]>4),1.0,
  AND([elar_pliPY]=6,[elar_pliCY]=6),1.0,
)+
IFS(
  [math_pliCY]<[math_pliPY],0.0,
  AND([math_pliPY]=1,[math_pliCY]=1),0.0,
  AND([math_pliPY]=1,[math_pliCY]=2),1.0,
  AND([math_pliPY]=1,[math_pliCY]>2),1.25,
  AND([math_pliPY]=2,[math_pliCY]>2),1.25,
  AND([math_pliPY]=2,[math_pliCY]=2),0.5,
  AND([math_pliPY]=3,[math_pliCY]=3),0.5,
  AND([math_pliPY]=4,[math_pliCY]=4),0.5,
  AND([math_pliPY]=3,[math_pliCY]>3),1.0,
  AND([math_pliPY]=4,[math_pliCY]>4),1.0,
  AND([math_pliPY]=5,[math_pliCY]>4),1.0,
  AND([math_pliPY]=6,[math_pliCY]=6),1.0,
)

accs[d2aRaw] - Decimal Type

SUM(SELECT(students[d2aPoints],([cy_campus]=[_THISROW].[campus])))

I appreciate any further help I get. Sorry to be a pain!

[Steve]

Are results and pli slices? If so, on which tables?

[Steve]

Do you have any security filters on any of the tables involved? Is so, please post screenshots of their expressions.

[bysshe]

results and pli are not slices; they are their own tables.

I have no security filters on any tables yet.

I also think something is still not right with my way of thinking and modifying decimals and numbers. In this test on accs[d2aRaw], I am receiving values presenting as decimals, but the actual output continues to be numerals. I have changed every single relevant arithmetic column in the entire database to decimal, just in case. I’m going to also double-check every formula within the app to make sure there isn’t a single value working as an integer.

[Steve]

Make sure the column types are Decimal, too.

[bysshe]

Yes sir, have done so. Double-checking.

I’m trying to work backwards from the problem. Do you think the root problem could be in [d2aPoints] comparing a real column such as [elar_pliPY] to a virtual column such as [elar_pliCY]?

[Steve]

Nope, it’s perfectly fine to compare virtual and real columns.

[Steve]

Do any of the tables involved (directly or indirectly) contain only a single row? If so, add a second row to it/them.

[bysshe]

No sir. I’ve also gone into the spreadsheet to turn any integers into decimals as well.

[Steve]

Well, this is definitely weird. If it were my app, I might create a new app and try to reproduce only the problematic functionality. Does the problem occur in the new app, too? If so, troubleshoot there; if not, continue replicating functionality from the original app until the new app breaks. Or duplicate the existing app. In the copy, you can make some pretty radical changes to troubleshoot.

[bysshe]

Well, this is the most odd badge of honor I could ask for. I’ve stumped Steve, haha. I appreciate all your help. I’m going to try doing exactly that with a significantly smaller dataset and see what happens. I’ll keep you updated!

[Steve]

@bysshe

I’ll keep you updated!

Please do.

[bysshe]

Update 1: I’ve rebuilt the parts of the app that matter for this problem. The first discovery I’ve made is that the problem 100% lies with the expression calculating points, students[d2aPoints]. By the time I’d get to check that column out, I’d already changed and saved a different one somewhere. That column in the new app is resetting on sync and save, so of course the other column would be too. I even got crazy with it as below:

IFS(
  [_THISROW].[elar_pliPY]>[_THISROW].[elar_pliCY],0.0,
  AND([_THISROW].[elar_pliPY]=1.0,[_THISROW].[elar_pliCY]=1.0),0.0,
  AND([_THISROW].[elar_pliPY]=1.0,[_THISROW].[elar_pliCY]=2.0),1.0,
  AND([_THISROW].[elar_pliPY]=1.0,[_THISROW].[elar_pliCY]>2.0),1.25,
  AND([_THISROW].[elar_pliPY]=2.0,[_THISROW].[elar_pliCY]>2.0),1.25,
  AND([_THISROW].[elar_pliPY]=2.0,[_THISROW].[elar_pliCY]=2.0),0.5,
  AND([_THISROW].[elar_pliPY]=3.0,[_THISROW].[elar_pliCY]=3.0),0.5,
  AND([_THISROW].[elar_pliPY]=4.0,[_THISROW].[elar_pliCY]=4.0),0.5,
  AND([_THISROW].[elar_pliPY]=3.0,[_THISROW].[elar_pliCY]>3.0),1.0,
  AND([_THISROW].[elar_pliPY]=4.0,[_THISROW].[elar_pliCY]>4.0),1.0,
  AND([_THISROW].[elar_pliPY]=5.0,[_THISROW].[elar_pliCY]>4.0),1.0,
  AND([_THISROW].[elar_pliPY]=6.0,[_THISROW].[elar_pliCY]=6.0),1.0)

I still get the same output. Part of me wonders if the multiple AND() statements are causing reversion to integer.

[bysshe]

Problem Identified, but not really “solved” I guess.

I spent the morning testing every single variable I could find until I discovered the problem does appear to be a virtual column to virtual column problem. What I did to find this was change the expression in [d2aRaw] to instead Sum based on a non-virtual column, as follows:

SUM(SELECT(students[elar_pliPY],[cy_campus]=[_THISROW].[campus_name]))

[elar_pliPY] is a column with real data in it, and the output was correct in the Test, app preview, and after saving and syncing. To verify, I added a physical column and manually input the values for students[d2aPoints], then replaced [elar_pliPY] above with the new column. Correct output through all views.

The solution then for me is to just have an automation update the [d2aPoints] column whenever I input data that would change it. Not the most elegant way, but it gets the job done and is probably better for redundancy since the data will be housed on the spreadsheet and I won’t be so reliant on virtual columns.