Script to count similar records

[T.J.]

Hello,

I'd like help writing a script that will count the number of similar records in a field. Something like this.

text - count

a 2

a 2

b 3

b 3

b 3

c 2

c 2

d 1

In my file I have 3 fields

(text) - which holds the information

(count) - which is empty and needs to be populated with the count of the number of similar records in the text field

(found_count) which is the calculation Get(FoundCount) and lists the number of records in a found set.

Any help would be greatly appreciated.

[aldipalo]

Had a similar problem. Easy answer thanks to John Mark Osborne.

http://fmforums.com/forum/showtopic.php?tid/179999/post/220235/hl//

[T.J.]

Thanks journeyman.

Works like a charm.

I established a new table occurence and set the relationships between the text fields. Created a new calc field with your equation and it's exactly what I needed.

It would be nice if someone could take a stab at the script. That's an area I'm very weak in and just seeing how they would handle it would probably be very enlightening.

Thanks

[aldipalo]

When you say script what do you want the script to do? Just come back with the count for each record or group of records? You could probably use the same formulas and have the answer display in a custom dialog box or a small pop up layout.

Please explain further.

********************************************

Would this help?

Al

Edited September 14, 2006 by Guest

[T.J.]

Al,

See the script I had been working on below before you offered your much better idea on how to accomplish what I needed.

I'm curious as to why my script didn't work. It's obvious that I probably don't have a good understanding of how loops and if and else scripts work. But if someone could tell my why my script wasn't working as I think it should that would be great.

See below

// #set the count for the first record

Show All Records

Sort Records [ Specified Sort Order: test::text; ascending ]

[ Restore; No dialog ]

Go to Record/Request/Page

[ First ]

Go to Field [ test::text ]

[ Select/perform ]

Copy [ test::text ]

[ Select ]

Enter Find Mode [ ]

Paste [ test::text ]

[ Select ]

Perform Find [ ]

Set Field [ test::count; test::found_count ]

Loop

Go to Record/Request/Page

[ Next; Exit after last ]

Set Field [ test::count; test::found_count ]

End Loop

#script works up to this point but from here it breaks down

Loop

Show All Records

Sort Records [ Specified Sort Order: test::text; ascending ]

[ Restore; No dialog ]

Go to Record/Request/Page

[ First ]

If [ test::count > "0" ]

Go to Record/Request/Page

[ Next; Exit after last ]

Else

Go to Field [ test::text ]

[ Select/perform ]

Copy [ test::text ]

[ Select ]

Enter Find Mode [ ]

[ Pause ]

Paste [ test::text ]

[ Select ]

Perform Find [ ]

Loop

Go to Record/Request/Page

[ First ]

Set Field [ test::count; test::found_count ]

Go to Record/Request/Page

[ Next; Exit after last ]

End Loop

End If

End Loop

[aldipalo]

Here's a simple script that gives you a pop up with the answer you want. Of course you can expand it from there.

[aldipalo]

I THINK this will do it. You may need to play around with it a bit since I can't test it without having your file.

// #set the count for the first record

Show All Records

Sort Records [ Specified Sort Order: test::text; ascending ]

[ Restore; No dialog ]

Go to Record/Request/Page

[ First ]

Set Variable[$Counter;0]

Enter Find Mode [ ]

Set Field [ test::Text;"A"]

Perform Find [ ]

Set Field [ test::foundcount;$Counter + 1]

Loop

Go to Record/Request/Page

[ Next]

Set Field [ test::foundcount;$Counter + 1]

Go to Record/Request/Page

[ Next; Exit after last ]

End Loop

End If

There's probably a few errors. I never get my scripts to work on the first shot. That's why I bought 8 advanced it has a debugger

If you would like to upload a clone of your app with no records or a few records I'll play around with it as well.

But I must say that I prefer to keep things simple and the Count Script gives you what you want without getting crazy. Also, whenever you do a loop you must add "Allow User Abort (off). I read somewhere on this forum that if a user aborts in the middle of a loop or a find and replace you could get some very strange data. Anyway, it's just a good habit to get into.

Al

[T.J.]

Al,

Attached is my sample file.

Thanks for all your help. I will play around with your suggested script to see if I can get it to work.

TJ

[aldipalo]

The file was not uploaded. It needs to be a zip file.

Also, looking at the script I would also make this change which should find the correct set of records based on the letter you choose from your valuelist.

// #set the count for the first record

Show All Records

Sort Records [ Specified Sort Order: test::text; ascending ]

[ Restore; No dialog ]

Go to Record/Request/Page

[ First ]

Set Variable[$Counter;0]

Set Variable[$Letter;test::Text]

Enter Find Mode [ ]

Set Field [ test::Text;$Letter]

Perform Find [ ]

Set Field [ test::foundcount;$Counter + 1]

Loop

Go to Record/Request/Page

[ Next]

Set Field [ test::foundcount;$Counter + 1]

Go to Record/Request/Page

[ Next; Exit after last ]

End Loop

End If

Again, there are probably some errors that need to be worked out.

Al

Edited September 15, 2006 by Guest