I am having a heck of a time getting a couple of calcs to work correctly. If anyone cares to take a look at these, I would be very appreciative!

This is part of a database managing chemical concentration data. One record per sample, with concentration data contained in fields. Fields I am referencing: sample type, certflag, conc, LOD, LCL, UCL

1) I am trying to flag compounds for a later calculation. This is what I want the field to do:

If the sample type field contains "RM" and the field certflag contains "c" then

When conc < LOD and LOD > LCL, set flag to 1

Otherwise set to 0

Else (when not 'RM' or "c") set to "".

2) I am trying to flag compounds for another later calculation. This is what I want the field to do:

If the sample type field contains "RM" and the field certflag contains "c" then

When conc > LOD and > UCL

or when conc > LOD and LOD

or when conc < LOD and LOD > LCL

Set the field to "0"

When the analyte conc is > LOD and between LCL and UCL, the field should be set to "1"

Else, when the sample type is not 'rm' or the certflag is not 'c' then set the field to "".

Basically, I'm using these to calculate how many certified compounds pass a QA evaluation (the second calc), but some of the compounds should be disregarded (the first calc). I have a logical error somewhere in the calcs I have come up with after several different approaches. I can post my latest spectacular failure in detail if anyone would find it helpful.

Thanks so much if you take a stab at this - might be fun for someone, but I keep bashing my head against the wall with this one!

The first calculation:

Case(

sample type = "RM" and certflag = "c";

Case(

conc < LOD and LOD > LCL ; 1 ;

0

)

)

For the second one, you need to specify this part:

When conc > LOD and [color:red] > UCL

That is basically the calc I have, but it fails unexpectedly.

paste a copy of your Calculation

Let me ask this question:

I am using some custom functions in here too, which complicates things. To make sure I'm comparing apples with apples... when you define a custom function, how do you set what data type is returned by the function. I have one in particular used in these calcs which has all number arguments, and it should return a number, but when I look at the edit screen in FM Developer, I don't see that a data type is set anywhere for the result. Maybe I think it should be returning a number, and instead it is returning text?

Actually, looking at this custom function some more, it uses Right(), which makes me suspect that it really is a text field, and I need to pull the number out to do my comparisons. I'll try that and let everyone know if that was the problem.

It is good to have people asking questions, it gets me looking at things I might be taking for granted.

It's not possible to answer this in general. Filemaker converts data type as required by each operation in a calculation. Each function outputs the type specified in the function's description in Help, and often data needs to be converted BEFORE a function or an operator can process it.

In addition to all that, if the result is output by a calculation field, it is converted to the type specified in the field's definition.

You can also control the data type by converting it explicitly using one of the GetAsXXX() functions.

I tell by the Sample Output for those that are at http://www.briandunning.com/filemaker-custom-functions/list.php

When in doubt, test the CF.

HTH

Lee

It looks like this was the problem. I had a custom function which I thought was returning a number, but was in fact returning text. So I was not making the comparisons that I thought I was.

Thanks for the help, folks.