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!
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.
Question
GAbbott
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
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.