Limit on Nested IFS() Expression

[Peter_Kalu]

Was there a new update to limit nested IFS() expression? I used the expression in a virtual column to perform sums based on multiple criteria. This worked without issue for over a year, then suddenly I now get this error:

"Your expression uses more levels of nesting than are allowed."

For better insight, here's how I used it:

IFS( [CsbfaLoanReg] = "Underwriter", 1.50) +
IFS( [CsbfaAppDoc] = "Underwriter", 0.65) +

...
IFS( [CsbfaLandlord] = "Underwriter", 0.65) +
IFS( [CsbfaDemolition] = "Underwriter", 0.65)

Please help.

[Marc_Dillon]

I've never seen that error message before.

If you google that message, a bunch of excel results show up, are you using excel as a backend source?

I would probably send this issue to support.

[Peter_Kalu]

No, the app is using Sheets backend.

For more insight, I used the IFS() expression 60+ times on the virtual column. The idea was to calculate a score based values of 60+ different columns. With this error, there has to be a workaround.

[WillowMobileSys]

Unfortunately, I hate to be the bearer of bad news, but I don't think there is an easy way to get around this.

The problem is that the design is forcing operations across columns.  AppSheet, like most development platforms, is a row-based processing service.  This means all of the functions and operations are meant to operate across rows.  None operate across columns which is why you needed the long IFS() set of expressions in the first place.

If you can, I would recommend re-designing the app or at least this part.  Your data should be in a table something like:

ID	Type	SomeName	Score	...
a	CsbfaLoanReg	Underwriter	1.50
b	CsbfaAppDoc	Underwriter	0.65
c	CsbfaLandlord	Underwriter	0.65
d	CsbfaDemolition	Underwriter	0.65
...	...	...	...	...

With a table setup like this then you can sum up the values in a single expression:

SUM(SELECT(TheTable[Score], [SomeName] = "Underwriter"))

[Joseph_Seddik]

I'd like just to add that it is really a data base/modelling design principle rather than being conceived as a problem of development platforms which follow sound data modelling principles.

[Peter_Kalu]

Thank you. I'll try your recommendation and let you know how it goes.

[Cortex]

Hello, @WillowMobileSys 

Do you know when those newly limitaiton occured  [ 50 ] ? Work fine for years.... 

I totally understand the technical behind it, but honestly  who cares about client side performance issues if the user is fully aware.

Virtual column are device calculated, maybe that's now changed to the backend, so it would make sens to put some limitations.

Dunno since they do not communicate or warn of these changes.

Any Way, thanks for your post

[WillowMobileSys]

---

@Cortex wrote:

Do you know when those newly limitaiton occured  [ 50 ] ? Work fine for years.... 

---

I am not sure what you are referring to here.

Virtual Columns are predominantly calculated on the servers - always have.  If you make a change on your device that affects a Virtual Column, that column will re-calculate on the device.  But the majority of the Sync time for any app is due to calculations of Virtual Columns on the server side, every Sync.

[Steve]

---

@Cortex wrote:

Dunno since they do not communicate or warn of these changes.

Have you been reading the daily release notes?

[Steve · Accepted solution]

---

@Peter_Kalu wrote:

Was there a new update to limit nested IFS() expression?

---

Yep.

[WillowMobileSys]

There was?  I can't say I have been reading every release note but for what it's worth I tried searching for this info and couldn't find it.  Maybe I'm not using the correct search terms?