Use of "Case" in an array

[George from Pittsburgh]

I am trying to work with an array of percentile values. The values are dependent upon BMI (Body Wt. Index) and age in half year increments.

I attempted to work with the case function:

If ( Gender = "M" and Age ≥ 4 and Age< 4.5;

Case (

BMI ≥ 14.1 and BMI < 14.3; "5-10" ;

BMI ≥ 14.3 and BMI < 14.8; "10-25" ;

BMI ≥ 14.8 and BMI < 15.7; "25-50" ;

BMI ≥ 15.7 and BMI < 16.4; "50-75" ;

BMI ≥ 16.4 and BMI < 16.9; "75-85" ;

BMI ≥ 16.9 and BMI < 17.3; "85-90" ;

BMI ≥ 17.3 and BMI < 17.8; "90-95" ;

This works just fine, but I need to set up many more "cases" in order to generate percentile values when "age" and "Gender" are different. I would like to add on to the next set of conditions:

If ( Gender = "M" and Age ≥ 4.5 and Age< 5.0;

Case (

BMI ≥ 13.9 and BMI < 14.3; "5-10" ;

BMI ≥ 14.3 and BMI < 14.7; "10-25" ;

BMI ≥ 14.7 and BMI < 15.5; "25-50" ;

BMI ≥ 15.5 and BMI < 16.4; "50-75" ;

BMI ≥ 16.4 and BMI < 16.8; "75-85" ;

BMI ≥ 16.8 and BMI < 17.3; "85-90" ;

BMI ≥ 17.3 and BMI < 17.8; "90-95" ;

and so on through "gender"="M" to "Age"= 20 and then through the female values.

I have not been able to get the correct syntax to do so. Am I on the right track? Should I be working with some sort of embedded "if-then" script?

The chart is at this site for male values.

http://www.cdc.gov/nchs/data/nhanes/growthcharts/set2/chart%2015.pdf

Thank you in advance for any help.

George

[T-Square]

George--

I would recommend setting up a separate table to hold the BMI values and number ranges, e.g.

Table Percentiles:

BMILower, number

BMIUpper, number

Gender, text

Percentile, text

Then, I would look into using a multiple-value relationship from People to Percentiles, based on:

1) People::Gender = Percentile::Gender,

2) People::BMI >= Percentile::BMILower, and

3) People::BMI < Percentile::BMIUpper

I'm only handling the Gender to BMI aspect, but the same principles hold for the age elements.

I'll note that it should be possible to represent your BMI and age ranges with only *one* value, making the relationships much less complex.

Using this structure, you'd end up with a large number of entries in the Percentile table, but after getting the info in, the records are simple and the mapping unambiguous. Assuming you name the related Table occurence BMIPercentile, you could refer to the percentile result directly as BMIPercentile::Percentile on your Person layout.

HTH,

David

Edited August 9, 2005 by Guest

[Ender]

I'd try a related table to hold each Age range-BMI range pair, along with the result, then use conditional relationships to pull out the correct related value for the specific person's Age and BMI.

[T-Square]

Wow, Ender--we had the same answer at the same time!

[Fenton]

I would be looking at the "lookup next higher" (or lower, whichever makes the most sense for your data. I would think higher). The "chart" data would be in its own little table, with the stop or start points (it only needs one of these, the other is implied).

Then you only need 2 relationships, one for male, one for female. Or just one relationship, with a compound relationship using the gender; 1 or 2 depends on your data, whether the male and female are on one record (row) or each on their own record.

If it's 2 relationships, then use the new and groovy LookupNext() function (in the Logical functions group), in an auto-enter by calculation, with the "Do not replace" option unchecked (in case of mistakes, it will recalculate).

It sounds complex, but it's so much easier than a long Case calculation. If you need help ask some more, and/or post a zipped FileMaker or tab-separated text file of your chart data.

[Slobey]

I think you are on the right track. I would start by only using the < symbol. Escept on the first one which you have no option for age under 4. But if the age is less than 4.5 it will return the 5-10 value and not go any further with the calc.

Secondly I would nest the case statements. The following is an example. There may be a shorter better way, but I don't know it. Maybe some of the more advanced users can help us both with this.

Case(

Gender = "M";

Case(

Age < 4;

"";

Age < 4.5;

Case(

BMI < 14.3; "5-10";

BMI < 14.8; "10-25");

Age < 5;

Case(

BMI < 14.3; "5-10";

BMI < 14.7; "10-25")

);

Gender = "F";

Case(

Age < 4;

"";

Age < 4.5;

Case(

BMI < 14.3; "5-10";

BMI < 14.8; "10-25")

)

)

[sbg2]

Wow lots of replies already...

Have you looked at the following yet?

Growth Chart Datafiles

[George from Pittsburgh]

Yes I didbut didn't think I could fashion an equation that I could use.

Any ideas?

[George from Pittsburgh]

I have attached an FMP table as you suggested. I need a more detailed explanation if you would. Thank you.

BMI.fp7.zip

[Fenton]

Here's a solution using LookupNext ( Percentile; Lower ). There is a logical flaw in the data however. There is an overlap between one level and the next. You cannot say the upper limit is a number, and the next lower limit is the same number. Which Percentile is it going to use?

For LookupNext you only need to use one limit or the other, lower or upper, but it doesn't work properly when the data has overlap; no method will.

BMI_fej.zip

[George from Pittsburgh]

I thank you and school nurses in Pennsylvania thank you. It works beautifully. It is as elegant as elegant gets.