Case & Case &

[blissland]

I don't know how to explain this other than comment on the following code:

Case(

Assay="F";

Case(F < Low; "Low"; F > High; "High";"Excellent");

Assay="A";

Case(A < Low; "Low"; A > High; "High"; "Excellent");

Assay="C";

Case(C < Low; "Low";C > High; "High"; "Excellent");

Assay="All";

"F:" &

Case(F < Low; "Low"; F > High; "High";"Excellent");

& " A:" &

Case(A < Low; "Low"; A > High; "High"; "Excellent");

& " C:" &

Case(C < Low; "Low";C > High; "High"; "Excellent")

)

IF I run the above with Assay = F or A or C, I get results like "low" and "high". But if I run it with Assay=All, I get "F:Excellent A:Excellent C:Excellent"

I should get the same results, and the fact that i get 'excellent' suggests that the variables aren't being read, even though its the same code that works right above it.

Is there a problem with putting Case logic between '&' symbols? FMP doesn't complain about it.

[Vaughan]

"if I run it with Assay=All, I get "F:Excellent A:Excellent C:Excellent"

That appears to be what you've asked the function to do.

This part of the function...

Case(F < Low; "Low"; F > High; "High";"Excellent")

... will return "Excellent" if none of the other conditions are true. What possible values can the field F have, and what values can the fields Low and High have? Do they contain numerical data?

What do you expect to happen if F = Low or F = High?

[Søren Dyhr]

The problem is probably that the syntax perhaps is posible, becasue it performs a typecast. However is the need to stuff a case( statement with inner case( statements is a little daft, and makes the logic flow pretty tough to digest.

It doesn't make the confusion less that you have variables and values look identical, where does the A, F, C, Low and High variables come from, they seems to be values as well??

What do you wish the calc to achieve?

--sd

[comment]

Is there a problem with putting Case logic between '&' symbols? FMP doesn't complain about it.

No, but it WOULD complain about your code - specifically about the semicolons preceding the ampersands.

Without these, the calculation should perform as expected.

Which means that the actual formula you are using is different from the one you posted. No doubt, another attempt at "simplifying" which ends up wasting more time than it saved.

[David Jondreau]

Putting "&" between Case() statements is permissible...but not likely to give you the results you want in this situation. "AND" is the "logical operator while "&" is a, for want of a better term, the "joining operator" which combines (not tests) the elements on either side of it.

[blissland]

here is the ACTUAL formula, nonsimplified. When the F, A, and C tests run by themselves, they produce a set of 3 answers that do not match the 3 answers that come out when they are put together.

Assay="Protozoa";

"F:" &

Case(

csub_sample_SampleData::ProtozoaF < csub_Sample_SampleResults_TestRanges_ref::Low; "Low";

csub_sample_SampleData::ProtozoaF > csub_Sample_SampleResults_TestRanges_ref::High; "High";

"Excellent")

& " A:" &

Case(

csub_sample_SampleData::ProtozoaA < csub_Sample_SampleResults_TestRanges_ref::Low; "Low";

csub_sample_SampleData::ProtozoaA > csub_Sample_SampleResults_TestRanges_ref::High; "High";

"Excellent")

& " C:" &

Case(

csub_sample_SampleData::ProtozoaC < csub_Sample_SampleResults_TestRanges_ref::Low; "Low";

csub_sample_SampleData::ProtozoaC > csub_Sample_SampleResults_TestRanges_ref::High; "High";

"Excellent");

[blissland]

When the individual tests come out low, high, excellent, I want this to say "F:low A:high C:excellent". just like that. so i think that's &, not AND, yes?

[blissland]

Yes, A, F, and C, as well as Low and High, are numerical values from other related tables. a value lower than Low should give me the text "low".

[Søren Dyhr]

Why are the fields postfixed, they should be the same field but in different records?

--sd

[blissland]

I don't know what you mean by postfixed, but they ARE the same field in different records.

See, there are 14 TESTS performed. 3 of them are protozoa. Each test has as a NumericalValue which is then determined to be low or high. Then each Test has 1 or more 'text recommendations' that relate to the low or high result. Unfortunately, the protozoa ends up with ONE recommendation, rather than 3. So, I have to create a "protozoa" record that summarizes the 3 results so I can relate that one record to the text recommendations.

[comment]

they ARE the same field in different records

No, they are not. A reference to a related field ALWAYS returns data from the FIRST related record*. Nothing has changed in this aspect since your last thread:

http://fmforums.com/forum/showtopic.php?tid/197505/

---

(*) Except when you use Last(), one of the aggregate functions or GetNThRecord().

[blissland]

They are in different records. It looks like this, where the first two fields are already present when this script is run:

SampleID ASSAY RESULT

4 Bacteria Low

4 Fungi Excellent

4 Ecoli High

4 *Flagellates Low

4 *Amoebae High

4 *Ciliates Excellent

4 Protozoa F:Low A:High C:Excellent

So this is why I have to run the Case & Case & Case in the Protozoa record.

[blissland]

You are correct that I haven't changed the data structure since that last post. It has taken me many solid days to figure out why I should not change it. Putting the low and high values for every assaytest on one record made TOTAL sense at first, but then I had to add the Text Recommendation for each 'low/high/excellent' result, and I couldn't think of any way to generate those recommendations since each field would have to be in a relationship, separately. So by having each test in the same field in a different record, rather than a different field in the same record, i can make a Type-Assay-Result relationship to acquire the comments.

This leaves me with the problem of having to extract those data in the daughter table and TestRanges table into a single report thru a script and copy values into multiple tables, but I think its better than changing the data structure.

And if this makes no sense to read, i apologize. i don't know how to explain something this complex clearly.

[comment]

Why is this so difficult?

In the previous thread (and this is nothing but a sequel to it) I said that you cannot achieve this with your current structure, and recommended that you add a related table for the test results (the second option).

Now you say that you haven't changed your structure, but you describe a table where each test result **IS** a separate record. Except for Protozoa, which seems to have three results. Very confusing.

I believe the problem can be solved by having a a separate record for each SINGLE test performed, and a corresponding record in Ranges for the test's boundaries (and descriptions, if required). If there are three Protozoa measurements to be performed, then put them in three separate records.

This way, you can calculate the assessment in each test record, in the form of:

TestName & ":" Case ( TestResult < Ranges::Low ... etc. ... )

and summarize the results in the parent Sample record by using the List() function.

[blissland]

I'm very impressed that you can see this so clearly in just a few posts.

I didn't change the structure to the data in the TestRanges table, per your first suggestion. I think I have followed your second suggestion by adding a daughter table (SampleResults), related by SampleID, on which each record is a different AssayTest, like you suggest.

Of the 14 different tests, 3 are protozoa. All 14 tests have a low/high value in TestRanges. The result of that comparison leads to TextRecommendations, and some have a 1-1 relationship while others have 1 to many (user has to choose manually in the latter case). TestRanges and Recommendations are related to SampleResults as separate reference tables--the former creates teh result field that the latter relates with. The 3 protozoa results are grouped for 1 textrecommendation so while all the other tests follow that same TestREsult > Ranges::Low calc that you mentioned, I have to create a Protozoa assessment (F: A: C:) in one field for that recommendation to grab onto.

I am curious about your List() suggestion. I've been writing a script to pull all of this out of the daughter Results table, but it would be great if there was a calculation that would do it.

[comment]

This part is not quite clear:

The 3 protozoa results are grouped for 1 textrecommendation

To begin with, they need to be separate. Then you can group them - for example, by defining an auxiliary relationship to the results table, one that will include only protozoa results. This way, the grouping is done in the parent sample record. Or you could group them in a report produced from the results table.

BTW, this can (and should) be done purely by relationships and calculations. No scripts are required to manipulate the data - see the attached example.

TestResults.fp7.zip

[blissland]

OK, i will look at this example when i wake up tomorrow. thanks.

It would be great if this all occurred via calculations. Currently sample records are made via scripts and when that occurs the 14 Results records are created as well, to be filled in later. I think those scripts are necessary. But it would be great for the final numbers to work with calcs.

The protozoa are separate for the numerical data and for the low/high analysis, but they have to group for the recommendation. I don't see how the grouping can occur in the parent record. The recommendations table has to relate to the daughter table to provide relevant choices to the user for each Test, so that can't happen in the Parent Samples table.