scripting a count field

[KirstyB]

I have a field which has a list of Consultants, every record in the db is assigned to one consultant. I need a script that will go to this field and count if consulatnt is Mr X and ignore all other consultants.

Can anyone help?

[Søren Dyhr]

A selfjoin with a GTRR(SO) comes to mind ...please let me know if you understand what it is???

--sd

[KirstyB]

how did you guess...I have no idea what you mean can you describe it in simple language for fm dummies?

[transpower]

One way to do this (not necessarilty the most elegant) is to have two fields for each consultant: one field uses PatternCount(consultant_field ; "consultant name" ) to count the no. in a record; the second field is a summary field which counts the total number of records of each consultant for the file. On the other hand, you could write a script which loops over all records and increments a global for each consultant accordingly.

[Søren Dyhr]

In the graphic presentation - eventhough only one tabel might exist, draw a line from the consultants name into to the void outside, and then back again to the same field. A dialog pops up suggesting you to make a duplicate of the previous table occurence. This way is a selfjoin relation made in jif.

Then is it time for the scripting this is done by a single line Go To Related Record (Show Only) - This will single out the records with the same name!

--sd

[KirstyB]

still stuck on this one...all I want is a field which I will label Mr X then another field labeled Mr Y etc

then in each field I want a count of how many records relate to this person.

Can I do this useing a calculation fiels combining a count function and a if finction

ie something like: If consultant = Mr X; 1 else 0 then sum these scores. I havent used any count or sum functions yet so am getting a bit stuck...I am totally barking up the wrong tree with this?

Kirsty

[-Queue-]

If you create a self-relationship based on fieldX, then Count(rel::serial) will give you the total number of matching (related) fieldX records.

[Søren Dyhr]

This was more or less my suggestion, but second message points in another direction - I can't really get what it is ...is it just a popup that makes the user jump to first record which obviously contains a count( over a relationship, when arriving to it ... or is it FastSummaries as explained here: http://www.onegasoft.com/tools/fastsummaries/index.shtml

--sd

[-Queue-]

I don't think there's a need for the GTRR step though. Kirsty seems to simply want a count of related records, not to exclude all others from the found set. It's still difficult to understand the goal clearly though, so I could be mistaken.

[KirstyB]

Okay - have had a wee break from this over the festive period and am determined to crack it before the end of January...so let me see if I can explain a bit clearer what it is I want to do

I have a db which we record patient information for 3 different consultants. i want to be able to produce a report which has a row which states for how many patients on the db are from consultant x how many from consultant y and how many from consultant z. I dont want to do anything with these records I simple want to know the totals. each patients in the DB is assigned to only on consultanat and this is defined in a drop down box with te consultants name so at present I can do a fins on that field and select one conultant and it returns the records who are assigned to this consultant and I can see how many this is. Now rather thann running this find for each of the consultants whenever I want this info I wanted to have a new layout which has threes boxes (one for each consultant) and in these boxes it has the totall number of people assigned to that consultant. Does that make it any clearer?

[-Queue-]

Do you have a consultants file or table? If so, just create a relationship from the consultants::id to the patients::consultantid and use a calculation field of Count(relationship::id). If not, there is a technique I posted here that you may be able to incorporate.

I would recommend that you use a consultants table though.