Is there a limit on the number of functions i...

expressions
(Daniel Armstrong) #1

Is there a limit on the number of functions in an app or in an expression? I have fighting with a persistent error that is blank and the only reason I can image I am getting this error is because my expression is very very long.

(Praveen Seshadri (AppSheet)) #2

There are some limits that usually reflect as parsing errors (so you should see the error when you save the app definition).

Usually, if you have a very long function, it becomes difficult for a human to read/understand/debug. Could you share the problematic formula here? Maybe we can help you simplify it

(Daniel Armstrong) #3

I already removed some of the IF functions by putting some of the lines in vertebral columns. I am using all the “if” and “len” functions to try to format a text show column to be a summary of an order.

=Concatenate(

“Retail”, " (",

IF(CONTAINS([Estimated Product Cost] , “.”), [Estimated Product Cost] , Concatenate([Estimated Product Cost] , “.00”)) , “)” , "

",

IF(CONTAINS([Retail Price], “.”),[Retail Price], Concatenate([Retail Price], “.00”)

), " ", "

“,[Retail Line Show],”

“,[Discount line show],”

", "

Discount", " (", if(Left(text([Discount]([Estimated Product Cost]/[Retail Price])),4)="", “0.00”,Left(text([Discount]([Estimated Product Cost]/[Retail Price])),4)), “)”, "

", " -

“, IF(CONTAINS([Discount], “.”),[Discount], Concatenate([Discount], “.00”)),” ",

LEN(concatenate(“Discount”, " (" , if([Discount]="", “0.00”,Left(text([Discount]*([Estimated Product Cost]/[Retail Price])),4)), “)” , IF(CONTAINS([Discount], “.”),[Discount], Concatenate([Discount], “.00”))

)),

,"

", "

Referral Cr.", " (", if([Substantiated Referral Cost]=0, “0.00”, text([Substantiated Referral Cost])),

“)”, "

", " -

“, IF(CONTAINS([Referral Credits Used], “.”),[Referral Credits Used], Concatenate([Referral Credits Used], “.00”)),” ",

Len(Concatenate(“Referral Cr.”, " (", if([Substantiated Referral Cost]=0, “0.00”, text([Substantiated Referral Cost])),

“)”,

IF(CONTAINS([Referral Credits Used], “.”),[Referral Credits Used], Concatenate([Referral Credits Used], “.00”)))), ,"

", "

Rewards Cr.", " (", if([Substantiated Rewards Cost]=0, “0.00”, text([Substantiated Rewards Cost])), “)”, "

", " -

“, IF(CONTAINS([Rewards Credits Used], “.”),[Rewards Credits Used], Concatenate([Rewards Credits Used], “.00”)),” ",

Len(Concatenate( “Rewards Cr.”, " (", if([Substantiated Rewards Cost]=0, “0.00”, text([Substantiated Rewards Cost]))")", IF(CONTAINS([Rewards Credits Used], “.”),[Rewards Credits Used], Concatenate([Rewards Credits Used], “.00”)))), ,"

“, “Subtotal”, " (”,

[Estimated Product Cost], “)”, "

“, IF(CONTAINS([Product Subtotal], “.”),[Product Subtotal], Concatenate([Product Subtotal], “.00”)),” ",

Len(Concatenate(“Subtotal”, " (", [Estimated Product Cost], “)”, "

", IF(CONTAINS([Product Subtotal], “.”),[Product Subtotal], Concatenate([Product Subtotal], “.00”)))), " “, Len(Concatenate(“Subtotal”, " (”, [Estimated Product Cost], “)”,

IF(CONTAINS([Product Subtotal], “.”),[Product Subtotal], Concatenate([Product Subtotal], “.00”)))),

," ", "

S&H", " (", [Shipping Expense], “)”, "

", " +

", IF(CONTAINS([Shipping & Handling Charged], “.”),[Shipping & Handling Charged], Concatenate([Shipping & Handling Charged], “.00”)), " ",

Len(Concatenate("

S&H", " (",[Shipping Expense], “)”, "

", " +

", IF(CONTAINS([Shipping & Handling Charged], “.”),[Shipping & Handling Charged], Concatenate([Shipping & Handling Charged], “.00”)))), " “, Len(Concatenate(“S&H”, " (”,[Shipping Expense], “)”, IF(CONTAINS([Shipping & Handling Charged], “.”),[Shipping & Handling Charged], Concatenate([Shipping & Handling Charged], “.00”)))),

," ", "

Taxes", " (", [Taxes], “)”, "

", " +

", IF(CONTAINS([Taxes], “.”),[Taxes], Concatenate([Taxes], “.00”)), " ",

Len(Concatenate("

Taxes", " (", [Taxes], “)”, "

", " +

", IF(CONTAINS([Taxes], “.”),[Taxes], Concatenate([Taxes], “.00”)))), " “, Len(Concatenate(“Taxes”, " (”, [Taxes], “)”, IF(CONTAINS([Taxes], “.”),[Taxes], Concatenate([Taxes], “.00”)))),

," “, “Customer Total”, " (”,

([Estimated Product Cost]+[Shipping Expense]+, [Taxes],[Merchant Processing Cost]),")",

"

" ,

IF(CONTAINS([Customer Total], “.”),[Customer Total], Concatenate([Customer Total], “.00”)), " ",

Len(Concatenate(“Customer Total”, " (",

([Estimated Product Cost]+[Shipping Expense]+, [Taxes],[Merchant Processing Cost]),")", "

" ,

IF(CONTAINS([Customer Total], “.”),[Customer Total], Concatenate([Customer Total], “.00”)))), " “, Len(Concatenate(“Customer Total”, " (”,

([Estimated Product Cost]+[Shipping Expense]+, [Taxes],[Merchant Processing Cost]),")", IF(CONTAINS([Customer Total], “.”),[Customer Total], Concatenate([Customer Total], “.00”)))),

," “,”

“,”

“,”

Rewards/Referral Summary","

“,”

",“Rewards Credits Earned”, "

“,[Rewards Credits Earned],”

","Referral Credits Earned By “,” ",[Referral Credits Earned By Referrer] )

(Dinh Nguyen Nguyen) #4

@Daniel_Armstrong Hi Daniel, I highly recommend breaking down your expression into multiple intermediate results, each of which can be computed and stored using a virtual column. In the “master” expression, the intermediate results can then be used by referencing the virtual columns. If you give the virtual columns meaningful names, such as “Estimated Cost”, the “master” expression will become much easier to read and test.

(Daniel Armstrong) #5

@Harry That’s what I ended up doing, I just wasn’t sure if there was a limit. Thank you for the recommendation.