3 Replies Latest reply on Mar 3, 2010 9:47 AM by philmodjunk

    Counting occurences for the same name in a file



      Counting occurences for the same name in a file


      I have a layout that requests the user to select a name from a pull down list.  Once selected, I need a script that will count how many occurences of that name are in the database table/file before moving on to the next layout field (the selection of the next field the user tabs into is based on the count result).  I have had limited success writing a script that works; the only time I get anything close to what is needed is if I commit the record immediately after making the name selection (which poses other issues since I need to be in edit mode throughtout the process).  Anyone have a simple script to use (I suspect a loop is involved but I sure can't figure out how...)?

        • 1. Re: Counting occurences for the same name in a file

          Method 1: Use a relationship and the count function


          Define a relationship between a text field and your name field. Exactly how you do this depends on your table design. I'll use a global text field in the same table for my example, but other options also work. We'll label the field where the name is selected, gName defined in the table, MainTable. I'll call your field where you want to count instances of this name, Name.


          Define gName as text and use field options to specify global storage.

          In Manage | Database | Relationships, drag from gName to a blank part of the screen and back to Name. Name the new instance of a Table Occurrence, MatchingNames.


          Go back to field definitions and add a calculation field: cNameCount, defined as:


          Count ( MatchingNames::Name )


          If you don't need to display the count, just use it in your script, you can just use the above count expression in your script without defining a field.


          Option 2: Perform a find and count the number of records in your found set.


          Using the same gName global text field, a script could count records like this:


          Enter Find Mode[]

          Set Field [MainTable::Name ; MainTable::gName] //This step only works if gName is a global field

          Set Error capture [on]

          Perform Find []


          At this point you can use Get ( FoundCount ) to count the number of instances of the name selected from the drop down.

          • 2. Re: Counting occurences for the same name in a file

            Phil:  Method 1 works like a charm.  The only change I made was not to use the gName field (which required gName on the layout but would not save the selected name to the Name field unless scripted to equal the gName selection) but instead join the Name back to itself (Name to Name) which created the selfjoin table you described.  That kept my layout looking neater without the use of the temporary GName field and my selection directly populated the Name field to be commited to the record when ready.  I've made links before but never to the same field for a join table; you do not see any problems making a join from a field to itself, do you?


            Many thanks!

            • 3. Re: Counting occurences for the same name in a file

              I use that kind of "self join" all the time. I can't conceive of any problems with your approach. (You can, by the way, use the global field option without actually placing the field on the layout, your script can use set field to copy the data into this field, but that makes your script needlessly complicated.)