1 Reply Latest reply on Apr 8, 2011 9:58 AM by philmodjunk

    How to script? From a single column: Fill 2nd table with unique values, then count total of each...

    dmeriwether

      Title

      How to script? From a single column: Fill 2nd table with unique values, then count total of each value in second field

      Post

      I have Table A - 25,000+ names. It contains a field "County" for county residence. I want to fill an empty, Table B with the list of uniques in one field, then a Count of total occurrences for each county. Table B:CountyID as key.

      Ideally, a rerun of the script when name data updates are sent would generate a new list of counties and a new count.

      I do some FM scripting - enough to be dangerous. But this is over my head. Any help? Any examples or snippets so I can see clean code? Thanks in advance.

        • 1. Re: How to script? From a single column: Fill 2nd table with unique values, then count total of each...
          philmodjunk

          Don't actually need a script for this.

          1. Open Manage | Database | Fields and select Table B.
          2. Double click the County field and specify the Unique values, validated always options.
          3. Now you can use Import records to copy all the data from Table A to Table B. The validation options from the previous step will automatically filter out the duplicates. (Note: even one small typo that adds a space or punctuation will not be filtered out.)
          4. You can relate your fields by their County fields and then a calculation field in Table B can use Count ( TableA::County ) to count all records in Table A with that County name.

           

          There's another way you can get a count of all your county records without creating a second table and importing the data.

          1. Define a "count of" summary field in table A that counts your county field.
          2. Create a layout to this table and place a sub summary part "when sorted by" the county field. Place the county name and the summary field in this sub summary part.
          3. Delete the body from this layout.
          4. Show all records and sort them by County to see a list of all your counties and their counts.