• 0

Valid formula in Datapage calculated field, yet "Error in formula" on page display

Question

Hello!

I'm working on adding iterations to a calculated field in my app and I'm running into a confusing error. Essentially, my formula serves to look at how many students a family is sending to a private elementary school along with their income and ultimately coming to a conclusion of what to charge the family.  So, I have statements for when a family is new with 1, 2, 3, 4, 5, and 6 students; then when a family returns from a previous year with the same number of students, or more students, or fewer students. Each situation gets treated a bit differently so I need different case statements to address each situation.

So in this instance I'm looking at when a family returns with more students than a previous year (but not brand new to the school):

CASE

WHEN [@field:FF_Students_LY]=1
AND [@field:FID_NStudentsTY]=2
AND [@field:MTI_EFC]>=[@field:ST_Two_Student_Rate]
THEN [@field:ST_Two_Student_Rate]

WHEN [@field:FF_Students_LY]=1
AND [@field:FID_NStudentsTY]=2
AND [@field:MTI_EFC]<[@field:ST_Two_Student_Rate]
AND [@field:MTI_EFC]>=[@field:ST_Catholic_Tuition]+[@calcfield:1]
THEN [@field:MTI_EFC]

WHEN [@field:FF_Students_LY]=1
AND [@field:FID_NStudentsTY]=2
AND [@field:MTI_EFC]<[@field:ST_Two_Student_Rate]
AND [@field:MTI_EFC]<[@field:ST_Catholic_Tuition]+[@calcfield:1]
THEN [@field:MTI_EFC]+[@calcfield:1]

WHEN [@field:FF_Students_LY]<[@field:FID_NStudentsTY]
AND [@field:FF_Students_LY]>0
AND [@field:FID_NStudentsTY]=3
AND [@field:MTI_EFC]>=([@field:ST_Two_Student_Rate]+[@field:ST_Three_Student_Rate])
THEN [@field:ST_Two_Student_Rate]+[@field:ST_Three_Student_Rate]

WHEN [@field:FF_Students_LY]<[@field:FID_NStudentsTY]
AND [@field:FF_Students_LY]>0
AND [@field:FID_NStudentsTY]=3
AND [@field:MTI_EFC]<[@field:ST_Two_Student_Rate]+[@field:ST_Three_Student_Rate]
AND [@field:MTI_EFC]>=[@field:ST_Two_Student_Rate]+[@calcfield:1]
THEN [@field:MTI_EFC]

WHEN [@field:FF_Students_LY]<[@field:FID_NStudentsTY]
AND [@field:FF_Students_LY]>0
AND [@field:FID_NStudentsTY]=3
AND [@field:MTI_EFC]<([@field:ST_Two_Student_Rate]+[@calcfield:1])
AND [@field:MTI_EFC]>=([@field:ST_Catholic_Tuition]+(2*[@calcfield:1]))
THEN [@field:MTI_EFC]

WHEN [@field:FF_Students_LY]<[@field:FID_NStudentsTY]
AND [@field:FF_Students_LY]>0
AND [@field:FID_NStudentsTY]=3
AND [@field:MTI_EFC]<([@field:ST_Catholic_Tuition]+(2*[@calcfield:1]))
AND [@field:MTI_EFC]>=([@field:ST_Catholic_Tuition]+[@calcfield:1])
THEN [@field:MTI_EFC]+[@calcfield:1]

WHEN [@field:FF_Students_LY]<[@field:FID_NStudentsTY]
AND [@field:FF_Students_LY]>0
AND [@field:FID_NStudentsTY]=3
AND [@field:MTI_EFC]<([@field:ST_Catholic_Tuition]+[@calcfield:1])
AND [@field:MTI_EFC]>=[@field:ST_Catholic_Tuition]
THEN ([@field:ST_Catholic_Tuition]+(2*[@calcfield:1]))

WHEN [@field:FF_Students_LY]<[@field:FID_NStudentsTY]
AND [@field:FF_Students_LY]>0
AND [@field:FID_NStudentsTY]=3
AND [@field:MTI_EFC]<[@field:ST_Catholic_Tuition]
THEN ([@field:MTI_EFC]+(2*[@calcfield:1]))

WHEN [@field:FF_Students_LY]<[@field:FID_NStudentsTY]
AND [@field:FF_Students_LY]>0
AND [@field:FID_NStudentsTY]=4
AND [@field:MTI_EFC]>=[@field:ST_Two_Student_Rate]+[@field:ST_Three_Student_Rate]+[@field:ST_Four_Student_Rate]
THEN [@field:ST_Two_Student_Rate]+[@field:ST_Three_Student_Rate]+[@field:ST_Four_Student_Rate]

WHEN [@field:FF_Students_LY]<[@field:FID_NStudentsTY]
AND [@field:FF_Students_LY]>0
AND [@field:FID_NStudentsTY]=4
AND [@field:MTI_EFC]<[@field:ST_Two_Student_Rate]+[@field:ST_Three_Student_Rate]+[@field:ST_Four_Student_Rate]
AND [@field:MTI_EFC]>=[@field:ST_Two_Student_Rate]+[@field:ST_Three_Student_Rate]
THEN [@field:MTI_EFC]+[@calcfield:1]

WHEN [@field:FF_Students_LY]<[@field:FID_NStudentsTY]
AND [@field:FF_Students_LY]>0
AND [@field:FID_NStudentsTY]=4
AND [@field:MTI_EFC]<[@field:ST_Two_Student_Rate]+[@field:ST_Three_Student_Rate]
AND [@field:MTI_EFC]>=[@field:ST_Two_Student_Rate]
THEN [@field:MTI_EFC]+[@calcfield:1]+[@calcfield:1]

WHEN [@field:FF_Students_LY]<[@field:FID_NStudentsTY]
AND [@field:FF_Students_LY]>0
AND [@field:FID_NStudentsTY]=4
AND [@field:MTI_EFC]<[@field:ST_Two_Student_Rate]
AND [@field:MTI_EFC]>=[@field:ST_Catholic_Tuition]
THEN [@field:MTI_EFC]+[@calcfield:1]+[@calcfield:1]+[@calcfield:1]

WHEN [@field:FF_Students_LY]<[@field:FID_NStudentsTY]
AND [@field:FF_Students_LY]>0
AND [@field:FID_NStudentsTY]=4
AND [@field:MTI_EFC]<[@field:ST_Catholic_Tuition]
THEN [@field:MTI_EFC]+[@calcfield:1]+[@calcfield:1]+[@calcfield:1]

END

The red text is what seems to be triggering the datapage to display "Error in formula" -- all the prior statements work fine until I add in the red text statements.

Is there something in my syntax that is wrong or some sort of character limitation that could be triggering this error?? I'm at my wit's end. I've checked the data types and they all appear to be compatible (all are number or currency types or formula fields producing number results). I've re-written the statements multiple times to ensure I'm referencing the correct field names, as well. I'm lost. Please help!

Thank you,

Gina

Recommended Posts

• 0

Hello

Im kinda bit shocked when I saw your formula. TLDR,

Anyway, what I can suggest for you on that case is, try not to use all of the formula in one go.

For example,

Use only the first when and then, then go preview it and see if the issue persisted. Basically, try the formula part by part to trace which part of it is causing the issue.

Share on other sites

• 0

Thanks! I did try piece by piece, and as far as that goes the red text is the part that is triggering the error. I've tried adjusting those parts in every way I can think of and nothing I've tried has worked.

Share on other sites

• 0
On 9/4/2021 at 12:22 PM, GAbbott said:

Thanks! I did try piece by piece, and as far as that goes the red text is the part that is triggering the error. I've tried adjusting those parts in every way I can think of and nothing I've tried has worked.

Better to use parentheses when your doing calculations or concatenations of multiple fields.
You're using one here

But not here,

Also make sure your THEN results all have the same DataType outputs, and you're not concatenatin/adding text field with a number

Share on other sites

• 0

Thanks for catching the lack of parentheses! I went through and made sure they were included across the formula and it still isn't working... so it must be something else.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.