Baffled by calculation error

[kathrynfw]

I am trying to create a field that will generate a list of phrases according to a criteria. It is based on input from a questionnaire, and designed to pull up the phrase whenever the answer to the corresponding field was higher than a one. Even though I set all the questions to two or three, the only one that is working here is the first one. In the merge field, it should list all of the phrases in quotes, but it only gives me "functional math." Can anyone see what is going on here? I am self-taught and really a novice on the calculation side of things.

Thanks so much.

 

If (Functional math deficits >=1; "functional math”,If (Functional reading deficits >=1; "functional reading”,If (Functional written language deficits >=1; "functional written language”,If (Impaired Theory of Mind >= 1; "theory of mind”,If (Difficulty reading social cues >=1; "social skills”,If (Difficulty sitting  attending >= 1; "learning behaviors”,If (Lacks functional living skills >= 1; "functional living skills”,If (Lacks functional play skills >= 1; "play skills”,If (Lacks independent leisure skills >= 1; "independent leisure”,If (Is over 14 lacking vocational skills >= 1; "vocational skills”,If (Student has challenges with expressive language >= 1; "expressive language”,If (Student has significant delays in receptive language >= 1; "receptive language”,If (Gross motor deficits >= 1; "gross motor skills”,If (Fine motor deficits >= 1; "fine motor skills”,If (Need for schedule >= 7; "use of visual schedule”,If (Need for work systems >=7; "using work systems and task strips for independent routines”))))))))))))))))

 

I am not getting any errors when I save the calculation parameters (Probably not the right lingo...)

 

[LaRetta]

Hi Kathryn, welcome to FMForums!

None of this should be necessary if you are properly structured.  Can you attach your file or provide us a copy of your relational graph?  It seems you have questions as FIELDS when questions should be records.  If questions were records then one field in the Questions table could handle this translation for you.  So until we know what we are working with, we can't really assist you.

In general, your calculation should be using Case() instead of If() so it can handle multiple tests.  However, that truly is not the best direction ... the best direction is restructuring.  We can help you with it.  :-)

[comment]

I agree with LaRetta: you clearly have a structural flaw. It seems you need three tables here, let's say:

Students -< Assessments >- Deficiencies

In the Deficiencies table you would have fields for both a threshold and the phrase to return when the threshold is reached or exceeded. In the Assessments table, you would have a field for the individual numerical assessment and a calculation field to return the corresponding phrase if the assessment is equal to or greater than the corresponding threshold. And in the Students table you would use the List() function to collect all the returned phrases from Assessments. Thus you would avoid the need to hardcode data into field names and calculation formulas.

Still, as quick fix for your current structure, try the following calculation:

Let (
phrases = List (
If ( Functional math deficits >=1 ; "functional math" ) ; 
If ( Functional reading deficits >=1 ; "functional reading" ) ; 
If ( Functional written language deficits >=1 ; "functional written language" ) ; 
If ( Impaired Theory of Mind >= 1 ; "theory of mind" ) ; 
If ( Difficulty reading social cues >=1 ; "social skills" ) ; 
If ( Difficulty sitting  attending >= 1 ; "learning behaviors" ) ; 
If ( Lacks functional living skills >= 1 ; "functional living skills" ) ; 
If ( Lacks functional play skills >= 1 ; "play skills" ) ; 
If ( Lacks independent leisure skills >= 1 ; "independent leisure" ) ; 
If ( Is over 14 lacking vocational skills >= 1 ; "vocational skills" ) ; 
If ( Student has challenges with expressive language >= 1 ; "expressive language" ) ; 
If ( Student has significant delays in receptive language >= 1 ; "receptive language" ) ; 
If ( Gross motor deficits >= 1 ; "gross motor skills" ) ; 
If ( Fine motor deficits >= 1 ; "fine motor skills" ) ; 
If ( Need for schedule >= 7 ; "use of visual schedule" ) ; 
If ( Need for work systems >=7 ; "using work systems and task strips for independent routines")
)
;
Substitute ( phrases ; ¶ ; ", " )
)

This will return a comma-separated list of all the phrases whose corresponding field has passes the listed threshold. The problem with your approach, using nested Ifs, is that the calculation exits at the first test that return true. Thus it would have never returned more than one phrase.

[kathrynfw]

I see! This is a dizzying learning curve for me. I did use Case a few years back for a simple count of fields marked "y" for a behavior data piece. My file is a mess because I am editing an old application, and perhaps I should have started fresh. I can upload that when I am on my computer later. I have not used relational databases yet, but hope to learn, as my vision always exceeds my skill level. Thank you both!

[kathrynfw]

It works! The relational database option, although out of my league, seems like it might be important to learn about. I have output based on user input that guide users to certain options, so it might look like this.

Input of user: selected "3" on scale for question signifying that the attribute of the student cause significant impact on the functioning of the child. Let's say it is "Gets lost on the way to transitions"

On a page about selecting supports, I want to create a merge dialogue that guides the user without forcing the choices, since there will be multiple things to consider. Is it possible to have a merge field within a merge field within a merge field, and a calculation that prevents the dialogue from even appearing if the item is not an issue? I will use parentheses to show how my mind is organizing it.

3 rating:

<<(Based on the survey, <<name>> is <<(highly impacted)>> by <<(characteristic, suggesting that <<name>> may benefit from the use of a ticket schedule)>>)>> Yeah... I am out of my league on this and should probably hire someone, no?