Jump to content

Find/Summary in same Calc?


This topic is 7338 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 7338 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.