Calculated text

[Amanco]

Hello, 

I need help please calculating texts from a portal. (Image attached)

 

I have questions with answers (Positive, negative, N/A), 

I am trying to report the questions and answers in a sentence, but having difficulty. The positives in 1 paragraph and the negatives in a second paragraph.

I am using the following calculation:

If 
( 

Current SOAP Answer::AnswerHistory = "Positive"

; "John has"  & " " & Substitute (List (Current SOAP Questions::QuestionHistory) ; "¶" ; ", " )

; "John does not have"  & " "  & Substitute (List (Current SOAP Questions::QuestionHistory) ; "¶" ; ", " )

)

My issues

(1) The sentence only responds to the answer in the first row in the portal

i.e. when the answer is "positive" it uses "John has" for all the questions, and if the answer is "Negative", it uses "John does not have" for all the questions, regardless if the other questions are positives or negatives.

 

(2) how can I add the word "and" before the last word if the answers are "Positives" in the paragraph. 

and add the word "nor" before the last word if the answers are "Negatives" in the paragraph.

 

Thank you,

 

[comment]

So what is the actual result (or results) you are trying to get?

 

[Amanco]

Hi comment,

I am looking to get the following result based on the answer selection on the first attached image for example:

John has Question 1, Question 2 and Question 3.

John does not have Question 4, Question 5 nor Question 6.

 

Edited June 19, 2021 by Amanco

[comment]

I think we are still missing the result for a question marked as N/A. Also, is there a field in the Answers table that holds the question's name? Or is that in a related Questions table?

I should add that this is not easy at all. You cannot use simply List ( Answers::Answer ) because if this field is empty in one of the records, the list will go out of sync with the list of question names. Why does this need to be a calculation? I believe it would be considerably easier to produce a report from the Answers table, sorted by Subject and by Answer, then have the 2 required paragraphs in a sub-summary by Answer part. 

If you must have it as a calculation, consider the attached demo file. Note that this could get even more complex if the question names need to be fetched from another table.

 

 

 

SummarizePortal.fmp12

[Amanco]

 

5 hours ago, comment said:

I think we are still missing the result for a question marked as N/A. Also, is there a field in the Answers table that holds the question's name? Or is that in a related Questions table?

I should add that this is not easy at all. You cannot use simply List ( Answers::Answer ) because if this field is empty in one of the records, the list will go out of sync with the list of question names. Why does this need to be a calculation? I believe it would be considerably easier to produce a report from the Answers table, sorted by Subject and by Answer, then have the 2 required paragraphs in a sub-summary by Answer part. 

If you must have it as a calculation, consider the attached demo file. Note that this could get even more complex if the question names need to be fetched from another table.

 

 

 

SummarizePortal.fmp12 152 kB · 2 downloads

Thank you comment, it is working like charm as usual

If the question is N/A, then it is not answered and I don't want to include it.

I have the questions in a related table to the answers.

I will also try the reporting way you suggested.

 

Thanks again