Solved: Limit on Nested IFS() Expression

Peter_Kalu · 02-14-2022 12:01 PM

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.

Steve

@Peter_Kalu wrote:
Was there a new update to limit nested IFS() expression?

Yep.

View solution in original post

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

@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?

dowjonez

Sorry to re-open this. I too received an error for this. I have a questionaire that has 75 yes or no questions on it. I was using the nested IFs to add up the results of all 75 columns.

IF([Col1]=TRUE,1,0) + IF([Col2]=TRUE,1,0) on down the road all the way to 75. This was the only way I figured out how to add this in appsheet. This is an extremely easy caluculation in google sheets =Countifs(colA:colBD,"TRUE").

Now that there is a maximum number of nested ifs, is there any way to count values of all col's in a row?

Peter_Kalu

My solution was to have 3 virtual columns. Two of those virtual columns were used to spread out and calculate the nested IFS, then the 3rd virtual column was used to sum virtual columns 1 and 2.

Suvrutt_Gurjar

@dowjonez wrote:

Sorry to re-open this. I too received an error for this. I have a questionaire that has 75 yes or no questions on it. I was using the nested IFs to add up the results of all 75 column

You could try the below not so elegant workaround.

Divide those 75 columns into two groups of 37 and 38 and add up the totals computed with IF() or IFS() from these two columns. You could add the total first VC ( of 37 Qs answers) as a first argument of second addition VC followed by IF(), IFS() for the next 38 Qs in the second VC.

dowjonez

Thanks. I will do that, great thinking. I didn't know if the total amount of ifs applied to the entire table or just an individual formula. Thanks so much for clarifying!