Find/Summary in same Calc?

[Micah DJ]

What I want to do is this, I have a field which will contain a value from 1 to 5.

What I'd like to do.. is create Calculation fields that will show me the number of

records holding the value 1, the number holding the value 2, the number holding the value 3 and so on.

I know I can Create A calculation field Calc1=If(fieldvalue="1",1,0) and then create another summary field that says Total of Calc1 for each of the five values.. but I guess what I'm looking for is more something like:

Give me the count of All records in which this field holds the value of 1

Give me the count of All records in which this field holds the value of 2

And so forth.

Is it possible to do that in a single calculation?

I'm using FM 5.5 and I have to be able to access the data from the web so from what I understand I can't do what I want with a script.

Thanks in advance!

Micah

[Mariano Peterson]

Create a self-relationship from a constant-1 field to a calculation such as the one you mentioned above: calc1 = if(fieldvalue = 1, 1, 0)

Your relationship could be called "self.calc1", and could be defined as:

Files: myFile.fp5 -> myFile.fp5

Fields: constant1 -> calc1

Then you can use the Count() function in your calc field to determine how many records exist where fieldvalue = 1. Ex.:

recordsEqualTo1 = Count(self.calc1::constant1)

Make sure that before you reference this field, the found set is not empty. Also note that this calc may take a long time to generate, causing your web page to load very slowly. An alternative to this would be to make one search request to the database for each field value you need a record count for. You can get the number of records from the found count after the search. The syntax for this would of course depend on the web language you're using. Here is an example with PHP:

require_once("fx.php");

$query = new FX("127.0.0.1");

$query->AddDBParam("field1", "1");

$query->SetDBData("yourFile.fp5", "yourLayout");

$result = $query->FMFind();

$count1 = $result['foundCount']; [color:"silver"]// Stores the number of records where field1 contains 1

$query->AddDBParam("field1", "2");

$result = $query->FMFind();

$count2 = $result['foundCount']; [color:"silver"]// Stores the number of records where field1 contains 2

print $count1."<br>n".$count2; [color:"silver"]// Prints the two counts to the browser