Evalute works in R7 but fails in R5

HCL Domino Domino Forum
1

A client asked us to Export a view to Excel.The view has a few columns and the LotusScript gets some data from another document and then creates the export.

The script uses Evaluate to establish the values/text to export to Excel, using the same formulas that are in the view columns.

Our client only has Notes R5.

Two of the columns use huge formula that work in the R7 and R5 views.

The Evaluate fails in R5 with the message " Operation Failed."

We copied the exact document from R5 to R7 and the export works in R7.

So, it’s not the document and it’s not the views in R5.

What could cause such an error in R5?

Please don’t try to debug the formula - it works in R7, so it’s not the Evaluate according to R7.

formula = |DeathBen := 0; |+_

|TraumaBen := 0; |+_

|NonTPDBen := 0; |+_

|DisabilityBen := 0; |+_

|OtherBen := 0; |+_

|Display := 0; |+_

|REM {Determine the death benefit to use}; |+_

|@If(@Contains(P_Type; “Life Insurance”); @Set(“DeathBen”; @If(@IsNumber(P_Life_Basic_Sum_Ass); P_Life_Basic_Sum_Ass; @TextToNumber(P_Life_Basic_Sum_Ass)));“”); |+_

|@If(@Contains(P_Type; “Whole Of Life”); @Set(“DeathBen”; @If(@IsNumber(P_Wol_Sum_Ass); P_Wol_Sum_Ass; @TextToNumber(P_Wol_Sum_Ass)));“”); |+_

|@If(@Contains(P_Type; “Whole Of Life”); @Set(“DeathBen”; DeathBen + @If(@IsNumber(P_Wol_Add_Death_Ben); P_Wol_Add_Death_Ben; @TextToNumber(P_Wol_Add_Death_Ben)));“”); |+_

|@If(@Contains(P_Type; “Personal Superannuation”:“Allocated Pension”); @Set(“DeathBen”; @If(@IsNumber(P_PS_Death_Ben); P_PS_Death_Ben; @TextToNumber(P_PS_Death_Ben)));“”); |+_

|@If(@Contains(P_Type; “Group Superannuation”); @Set(“DeathBen”; @If(@IsNumber(P_GS_Death_Ben); P_GS_Death_Ben; @TextToNumber(P_GS_Death_Ben)));“”); |+_

|@If(@Contains(P_Type; “Group Life”); @Set(“DeathBen”; @If(@IsNumber(P_GLife_Basic_Sum_Ass); P_GLife_Basic_Sum_Ass; @TextToNumber(P_GLife_Basic_Sum_Ass)));“”); |+_

|@If(@Contains(P_Type; “Group TRAUMA”); @Set(“DeathBen”; @If(@IsNumber(P_GTrauma_Death_Ben); P_GTrauma_Death_Ben; @TextToNumber(P_GTrauma_Death_Ben)));“”); |+_

|@If(@Contains(P_Type; “Trauma”); @Set(“DeathBen”; @If(@IsNumber(P_Trauma_Death_Ben); P_Trauma_Death_Ben; @TextToNumber(P_Trauma_Death_Ben)));“”); |+_

|REM {Determine the Trauma Benefit to use}; |+_

|@If(@Contains(P_Type; “Trauma”); @Set(“TraumaBen”; @If(@IsNumber(P_Trauma_Sum_Ass); P_Trauma_Sum_Ass; @TextToNumber(P_Trauma_Sum_Ass)));“”); |+_

|@If(@Contains(P_Type; “Life Insurance”); @Set(“TraumaBen”; @If(@IsNumber(P_Life_Trauma_Ben); P_Life_Trauma_Ben; @TextToNumber(P_Life_Trauma_Ben)));“”); |+_

|@If(@Contains(P_Type; “Group TRAUMA”); @Set(“TraumaBen”; @If(@IsNumber(P_Trauma_Sum_Ass_1_1); P_Trauma_Sum_Ass_1_1; @TextToNumber(P_Trauma_Sum_Ass_1_1)));“”); |+_

|@If(@Contains(P_Type; “Stand Alone TRAUMA”); @Set(“TraumaBen”; @If(@IsNumber(P_Trauma_Sum_Ass_1); P_Trauma_Sum_Ass_1; @TextToNumber(P_Trauma_Sum_Ass_1)));“”); |+_

|@If(@Contains(P_Type; “Group Life”); @Set(“TraumaBen”; @If(@IsNumber(P_GLife_TPD_Trauma_Benefit); P_GLife_TPD_Trauma_Benefit; @TextToNumber(P_GLife_TPD_Trauma_Benefit)));“”); |+_

|@If(@Contains(P_Type; “Whole Of Life”); @Set(“TraumaBen”; @If(@IsNumber(P_Wol_Trauma_Ben); P_Wol_Trauma_Ben; @TextToNumber(P_Wol_Trauma_Ben)));“”); |+_

|REM {Determine the T&PD Disability Benefits to use}; |+_

|@If(@Contains(P_Type; “Life Insurance”); @Set(“DisabilityBen”; @If(@IsNumber(P_Ins_TPD_Benefit); P_Ins_TPD_Benefit; @TextToNumber(P_Ins_TPD_Benefit)));“”); |+_

|@If(@Contains(P_Type; “Trauma”); @Set(“DisabilityBen”; @If(@IsNumber(P_Trauma_TPD_Ben); P_Trauma_TPD_Ben; @TextToNumber(P_Trauma_TPD_Ben)));“”); |+_

|@If(@Contains(P_Type; “Group TRAUMA”); @Set(“DisabilityBen”; @If(@IsNumber(P_GTrauma_TPD_Ben); P_GTrauma_TPD_Ben; @TextToNumber(P_GTrauma_TPD_Ben)));“”); |+_

|@If(@Contains(P_Type; “Whole Of Life”); @Set(“DisabilityBen”; @If(@IsNumber(P_Wol_TPD_Ben); P_Wol_TPD_Ben; @TextToNumber(P_Wol_TPD_Ben)));“”); |+_

|@If(@Contains(P_Type; “Group Life”); @Set(“DisabilityBen”; @If(@IsNumber(P_GLife_TPD_Benefit); P_GLife_TPD_Benefit; @TextToNumber(P_GLife_TPD_Benefit)));“”); |+_

|@If(@Contains(P_Type; “Personal Superannuation”:“Allocated Pension”); @Set(“DisabilityBen”; @If(@IsNumber(P_PS_TPD_Ben); P_PS_TPD_Ben; @TextToNumber(P_PS_TPD_Ben)));“”); |+_

|@If(@Contains(P_Type; “Group Superannuation”); @Set(“DisabilityBen”; @If(@IsNumber(P_GS_TPD_Ben); P_GS_TPD_Ben; @TextToNumber(P_GS_TPD_Ben)));“”); |+_

|REM {Add up the NON TPD disability types}; |+_

|@If(@Contains(P_Type; “Disability Insurance”); @Set(“NonTPDBen”; @If(@IsNumber(P_Dis_Ben_Amount); P_Dis_Ben_Amount; @TextToNumber(P_Dis_Ben_Amount)));“”); |+_

|@If(@Contains(P_Type; “Business Expenses”); @Set(“NonTPDBen”; @If(@IsNumber(P_BE_Ben_Amount); P_BE_Ben_Amount; @TextToNumber(P_BE_Ben_Amount)));“”); |+_

|REM {Add up all of the possible Other Benefits}; |+_

|@If(@Contains(P_Type; “Annuity”); @Set(“OtherBen”; @If(@IsNumber(P_Anu_IPCP); P_Anu_IPCP; @TextToNumber(P_Anu_IPCP)));“”); |+_

|@If(@Contains(P_Type; “Group Salary Continuance”); @Set(“OtherBen”; @If(@IsNumber(P_GSC_Ben_Amount); P_GSC_Ben_Amount; @TextToNumber(P_GSC_Ben_Amount)));“”); |+_

|REM {Add ALL the sums assured together}; |+_

|SATotal := @If(DeathBen = “”; 0; @If(@IsNumber(DeathBen); DeathBen; @TextToNumber(DeathBen))); |+_

|@If(TraumaBen = “”; “”; @Set(“SATotal”; SATotal + (@If(@IsNumber(TraumaBen); TraumaBen; @TextToNumber(TraumaBen))))); |+_

|@If(DisabilityBen = “”; “”; @Set(“SATotal”; SATotal + (@If(@IsNumber(DisabilityBen); DisabilityBen; @TextToNumber(DisabilityBen))))); |+_

|@If(NonTPDBen = “”; “”; @Set(“SATotal”; SATotal + (@If(@IsNumber(NonTPDBen); NonTPDBen; @TextToNumber(NonTPDBen))))); |+_

|@If(OtherBen = “”; “”; @Set(“SATotal”; SATotal + (@If(@IsNumber(OtherBen); OtherBen; @TextToNumber(OtherBen))))); |+_

|REM {Set up the display line(s)}; |+_

|@If(SATotal = “”;@Set(“Display”; 0);@Set(“Display”; @TextToNumber(@Text(SATotal;“F0,”)))); |+_

|@If(DeathBen = “”; “”; @If(DeathBen > 0;@Set(“Display”; Display : @TextToNumber(@Text(DeathBen;“F0,”)));“”)); |+_

|@If(DisabilityBen = “”; “”; @If(DisabilityBen > 0;@Set(“Display”; Display : @TextToNumber(@Text(DisabilityBen;“F0,”)));“”)); |+_

|@If(NonTPDBen = “”; “”; @If(NonTPDBen > 0;@Set(“Display”; Display : @TextToNumber(@Text(NonTPDBen;“F0,”)));“”)); |+_

|@If(TraumaBen = “”; “”; @If(TraumaBen > 0;@Set(“Display”; Display : @TextToNumber(@Text(TraumaBen;“F0,”)));“”)); |+_

|@If(OtherBen = “”; “”; @If(OtherBen > 0;@Set(“Display”; Display : @TextToNumber(@Text(OtherBen;“F0,”)));“”)); |+_

|Display |

result = Evaluate(formula,docchild)

2

Subject: Evalute works in R7 but fails in R5

Are you making fun of us? Did it come to your attention, that the formula language has been completely rewritten (adding tons of new functionality) for Notes 6?

If the evaluate fails, it fails because the @Functions you use are not supported in Notes 5. So the ONLY way to determine, why it fails is to debug that formula. You’re right in assuming, that I don’t feel like doing this for you. :slight_smile: However, right on top, this is the first line that must fail in R5:

REM {Determine the death benefit to use};

Curly braces are not allowed as string delimiters prior to Notes 6. At a first glance, the rest looks OK, but given the complexity of the formula there could easily be more.

3

Subject: RE: Evalute works in R7 but fails in R5

Duh!!! Did I not say that the formulas work in R5 and R7???

What part of the “view works in R5” didn’t you understand.

The column formula in the R5 view has exactly what I posted - including the curly braces and the kitchen sink.

I didn’t write the formula - we only used it to facilitate the Evaluate Statement.

Sorry to be so scathing, but I gave you all the salient clues I know.

The formulas in the view work in R7 and R5 - so it’s not the formulas from the view.

The same document copied from the R5 Db works in R7 - so it’s not the document.

The Evaluate works in R7 but fails in R5 - so it’s not the syntax of the Evaluate (at least not in R7).

Anyway, thanks for your answer.

Ray Green.

4

Subject: RE: Evalute works in R7 but fails in R5

You’re welcome.

5

Subject: RE: Evalute works in R7 but fails in R5

I’d check that if I were you – curly braces were NOT a permissable string delimiter in R5 Formula Language. In any case, that formula is WAY out of control length-wise, so it wouldn’t surprise me if you were overflowing the evaluate. Why not rewrite it in LotusScript, particularly as there are no elements in the formula that are more efficient in Formula Language than they are in LotusScript (like, say, @DbColumn).

6

Subject: RE: Evalute works in R7 but fails in R5

Thanks for your reply, Stan.

If curly braces weren’t allowed in R5, then how come the formulas work without a problem in the R5 views? Perhaps Notes just ignores them anyway? It’s been a while since I worked with R5 and I never used curly braces anyway.

As the Evaluate works well in R7, and I’ve seen no fixes to Evaluate in Version 6 or 7 Release Notes related to size, we didn’t consider this was the problem.

We thought or converting to Script, but it would have taken too long as there are many columns with huge formulas like the one I posted in my original post. And then there would be debugging issues and the doubt if the conversion was correct.

Thanks anyway for your answer.

7

Subject: RE: Evalute works in R7 but fails in R5

If the formula was COMPILED in a 6 or 7 Designer client, it will run in the R5 formula engine – and if opened in Designer R5, the braces will be replaced with quotation marks (and any enclosed quotes will be escaped). The stored byte code is not the same thing as source code. In the Evaluate statement, though, you are trying to pass Formula Language source code, not compiled byte code, to the formula engine (the COMPILE module, not the COMPUTE module), so the curly braces cannot evaluate.

Does that make any sense to you now?

Oh – you might still want to reconsider computing anything like that in a column formula. Store the results on the documents instead – your application will perform a LOT better.