Jump to content
  • 0

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


GAbbott
 Share

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

 

 

Link to comment
Share on other sites

4 answers to this question

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.

Link to comment
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
image.png.8ee30afd2841f340b301d338f7a99956.png

But not here,
image.png.0c5f7e815e82fe9fc0e2c5b0c191b815.png

 

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

Link to comment
Share on other sites

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.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...