Grouping

[jpac]

I am trying to create a script. In the attached file there are fields that list the location names and inspection dates. In some cases there are muptle dates for one location. I want to count Location Names as 1 inspection and show a total of all inspections. HELP

1-6-200.fp7.zip

[transpower]

Well, you have Count(Location_Names) setup as a summary field, so I don't understand the question.

[jpac]

The count in that field is not accurate. It counts all location names I want to count location names that have two or more of the same date assigned to them as one.

[transpower]

Oh, so there could be records with the same location name, but different date. This is easy on Windows, with MSQUERY, using the SQL statement COUNT DISTINCT. Otherwise, make another table with location requiring a unique validation, then count the records there. To begin with, you could script a process to post locations to the new table, then later on you could run a process each time you add a record in the first table to post the location (if unique) to the second table.

[jpac]

Hi Fenton, I tried the solution but I lack the expertise to get it to work. I hand counted the attached file the correct result I'm looking for is 31 total inspections for 34 violations.

[Fenton]

Well, the Locations really should be in their own table. That would eliminate the problem of counting them. It could be done with ValueListItems. That would be easiest. It must be Unstored however, and it always counts the entire file (could get slow). Best to put Locations in their own table.

1-6-2005.fp7.zip

[jpac]

Thanks, is it best to set-up as much as possible in seperate tables and then join them. Just a general question.

[Fenton]

In this case Locations should definitely be its own table. Locations should probably have an auto-entered serial ID. This makes a much better key for relationships, as it allows you to edit the name slightly later without breaking the relationship; it also takes up less space.

The Inspections should be their own table. They can be entered in a Portal on a form view of the Location record. The relationship would be on the LocationID (or Name) to the same in the Inspections table. If "allow creation of related records" is checked, then the ID will be automatically entered in Inspections as soon as you type anything else into the row.

The above structure would make "counting" locations easy as pie; just the record count of the Locations table. The "inspections per location" would be just: Count (portal's relationship::date).

You mentioned an earlier file of mine? I would think that would have shown it as 2 tables. I don't have it anymore.

[CoZiMan]

Only one table. Less complexity.

Define a calculation field, global storage, as this:

PatternCount ( ValueListItems ( fileName ; valueList ) ; "

[-Queue-]

Note that PatternCount( ValueListItems( Get(FileName); "valueList" ); "