Blightman,
Attached is a Mass Update showing an example of doing this. Retrieve your records, sorted by violation code. Run this Mass Update.
Note that about 80 percent of this code is only necessary because the State is buried in the City value and must be parsed out. Because of this, we have to do a bunch of bookkeeping to track the occurences, rather than just being able to sort by State.
This can also be adjusted so that you click a command button, or so that the output goes to a printer.
The Mass Update will produce something like this in the WriteLn window:
Violation: CIN
3
HI 2
Total Violations for CIN: 5
----------
Violation: GEN
WI 1
1
Chile 1
Total Violations for GEN: 3
----------
Violation: INS
Chile 1
TX 1
Total Violations for INS: 2
----------
Violation: NCK
NY 1
MI 1
IN 1
Total Violations for NCK: 3
----------
Violation: NCL
VA 1
NC 1
NY 2
CO 2
IL 2
NV 1
TX 1
TX 1
FL 1
HI 1
PA 1
MO 1
OK 1
AR 1
OH 1
Total Violations for NCL: 18
----------
Violation: SHC
MA 1
GA 1
Total Violations for SHC: 2
----------
Violation: SHP
LA 1
Total Violations for SHP: 1
----------
Violation: SPC
MS 1
NJ 1
Total Violations for SPC: 2
----------
Violation: TMP
SD 2
AZ 3
VT 1
PA 1
LA 2
VA 1
CO 1
CA 1
Total Violations for TMP: 12
----------
---------------------------------
---------------------------------
Total Violations: 48
Here's the code:
** PROGRAMMING SECTION: [ GLOBAL CODE ] [] **
// Declare variable to hold the compare values
stat gvState as String
stat gvViolation as String
// Declare variable to hold summary
stat gvGrandTotal as Int
// Declare array to hold state totals
stat gvaStateCounts as Array[100, 2] of String
// Function to parse the State out of the City value.
// The State is whatever follows the comma and space.
Function GetState(vVal as String) As String
var vPos as Int
var vLen as Int
var vRet as String
vRet = ""
vLen = @Len(vVal)
// Find the comma
vPos = @InStr(vVal, ",")
// Advance past the comma and space
vPos = vPos + 2
// Make sure there is something there
If(vLen >= vPos)
{
// Parse out the State.
vRet = @Mid(vVal, vPos, vLen)
}
Return(vRet)
End Function
// Subroutine to clear state totals array
Subroutine ClearCounts()
var vOuterLoop as Int
var vInnerLoop as Int
For vOuterLoop = 1 To 100
For vInnerLoop = 1 To 2
gvaStateCounts[vOuterLoop, vInnerLoop] = ""
Next
Next
End Subroutine
// Function to find a state in the state totals array
Function FindState(vVal as String) As Int
var vLoop as Int
var vRet as Int
vRet = 0
vLoop = 1
While((vLoop <= 5) And (vRet = 0))
{
If vVal = gvaStateCounts[vLoop, 1]
{
vRet = vLoop
}
vLoop += 1
}
Return(vRet)
End Function
// Function to find an empty slot in the state totals array
Function FindStateSlot() As Int
var vLoop as Int
var vCount as Int
var vRet as Int
vRet = 0
vLoop = 1
While((vLoop <= 100) And (vRet = 0))
{
vCount = @ToNumber(gvaStateCounts[vLoop, 2])
If (gvaStateCounts[vLoop, 1] = "") And (vCount = 0)
{
vRet = vLoop
}
vLoop += 1
}
Return(vRet)
End Function
// Subroutine to write summary to WriteLn Window
Subroutine WriteSummary()
var vLoop as Int
var vCount as Int
var vTotal as Int
WriteLn("Violation: " + gvViolation)
For vLoop = 1 To 100
vCount = @ToNumber(gvaStateCounts[vLoop, 2])
If (gvaStateCounts[vLoop, 1] <> "") Or (vCount > 0)
{
WriteLn(gvaStateCounts[vLoop, 1] + Tab(2) + @Str(vCount))
vTotal += vCount
}
Next
WriteLn("Total Violations for " + gvViolation + ": " + @Str(vTotal))
WriteLn("----------")
gvGrandTotal += vTotal
End Subroutine
// Initialize variables to values in first record
gvState = GetState(City0)
gvViolation = Violation Code
// Initialize Summary
gvGrandTotal = 0
// Initialize State Counts
ClearCounts()
gvaStateCounts[1, 1] = gvState
gvaStateCounts[1, 2] = "0"
** PROGRAMMING SECTION: [Courier] [Mass Update] **
var vCurState as String
var vCurViolation as String
var vPos as Int
// Initialize variables to values in current record
vCurState = GetState(City0)
vCurViolation = Violation Code
If vCurViolation <> gvViolation
{
WriteSummary()
ClearCounts()
gvaStateCounts[1, 1] = vCurState
gvaStateCounts[1, 2] = "0"
gvViolation = vCurViolation
}
vPos = FindState(vCurState)
If vPos > 0
{
gvaStateCounts[vPos, 2] = @Str(@ToNumber(gvaStateCounts[vPos, 2]) + 1)
}
Else
{
vPos = FindStateSlot()
If vPos > 0
{
gvaStateCounts[vPos, 1] = vCurState
gvaStateCounts[vPos, 2] = @Str(@ToNumber(gvaStateCounts[vPos, 2]) + 1)
}
}
If @ResultSetCurrentPosition() = @ResultSetTotal()
{
WriteSummary()
WriteLn("---------------------------------")
WriteLn("---------------------------------")
WriteLn("Total Violations: " + @Str(gvGrandTotal))
}
Pages: [1] 
Send Topic 
Print
Counting fields with Sesame (Read 4272 times)
