A Counting Loop - I think

[amypaulsmom]

okay - here is what I have:

I need to create some sort of counter. Thought it sounded easy, but I am getting stuck. I am using FMP6 on OSX.

I have a script that does a find. On each set of criteria it will find no more then 100 records. On this set of records - I sort by price ascending.

Now - I need to take those sorted and found records and break them down by how many there are of each price. For example - if there are two books at $200 - I need a field to populate with the number 2, and then another field would populate with the number 5 if there were 5 books available at $1000 and so on.

My ultimate goal is a graph of how many books at the different price levels. I actually think I have the graphing portion, if I can just figure out how to group the books by price.

Would anyone be able to help me with how to script this counting loop for me.

I thank you in advance. If you need more detail - please let me know.

[BobWeaver]

You don't need a loop or even a script really. Just create a summary field defined as count of Price. Then make a subsummary part (sorted on price) on your layout. Put the summary field in there. Sort by price and print, or look at it in preview mode.

[amypaulsmom]

"You don't need a loop or even a script really. Just create a summary field defined as count of Price. Then make a subsummary part (sorted on price) on your layout. Put the summary field in there. Sort by price and print, or look at it in preview mode."

This is all and good - except that I don't want to print or preview the data. It is going to be used to create a graph of how many books there are at different price ranges.

That's why I was thinking a looped script - that would go through the records compare it to the one before and then set a field with a counter number when the price field changes. I can picture it in my head, but can't get it down on paper or the screen. I know that I could possibly need up to 100 count fields.

Any direction would be greatly appreciated.

[BobWeaver]

You can create a self-join relationship based on the price field. Then create a calculated field called cCountOfPrice with the formula:

Count(selfjoinRel::Price)

That will give you the number of books at each price.

In order to do the graph, I assume you will need one record of each price in the found set. So you can use the FIND DUPLICATES example script on page 10-13 of the FM manual. Change the last perform find step to omit the duplicates instead of finding them.

[amypaulsmom]

HI -

Yes - this looks more like what I am looking for. I am currently working on creating scripts for calculating the mode for each group.

If your solution does not work - I will be back in touch.

Thanks you

[amypaulsmom]

Hi -

"You can create a self-join relationship based on the price field. Then create a calculated field called cCountOfPrice with the formula:

Count(selfjoinRel::Price)

That will give you the number of books at each price."

The above solution works well - but i must have forgot to say one small part. There is a criteria in here. The find is per author - so the calculated number needs to be per author not over the whole database.

If I do a find on William Poe - the calculated field should bring up how many books at each price for William Poe only, not for all the authors in the database. Yes - i am doing a Find, but my calc field is still pulling information for the whole database.

Thanks so much for your help with this step in my project.

[BobWeaver]

Then you need to make a compound key field "cKeyAuthorPrice" with this formula:

Author & "-" & Price

Then use this in the relationship definition instead of Price.