4 Replies Latest reply on Mar 14, 2014 1:52 PM by philmodjunk

    zip code sorts

    jayGamel

      Title

      zip code sorts

      Post

           I need to sort and print labels by the following:

           1. bundles of 10 or more matching 5-digit zips (print "5" on each label)

           2 bundles of 10 or more matching 3-digit zips (print 3 on each label)

           3. bundles of non-matching zips (print "MXD ADC" on each label)

           I have a field zips with 5-digit info

           I have a field zip3 with the first three digits of the zip field

           I'm stumped on sorting these out so they print correctly. I may only have 9 in a 5-digit zip but they may need to be 3 or "MXD-" depending on the existence of additional shared 3 digit zips.

            

           I don't know how to return a set of records depending on a count.

            

           Jay Gamel

            

           Any help appreciated. 

        • 1. Re: zip code sorts
          philmodjunk

               Can you explain that in more detail? "Bundles of 10 or more" means what exactly?

               I may only have 9 in a 5-digit zip but they may need to be 3 or "MXD-" depending on the existence of additional shared 3 digit zips.

               What does that mean? "additional shared 3 digit zips"???

          • 2. Re: zip code sorts
            jayGamel

                 The post office requires wherever we have 10 or more labels containing a single 5-digit zip; i.e., 22 labels to 95409, that they be bundled together with a "5" on the top label.

                 Where we have 10 or more labels with the first three matching digits, say 8(95345),4(95322) and 6(95321), that we bundle them together and put "3" on the top label.

                 All the rest get "MXD-ADC" on the top label.

                 I figure if I print the applicable indicator on all labels, it doesn't matter which one is on top. Post office doesn't care. It would be nifty to just put it on the first iteration of each group, but that's cake icing. bundles are about 20 papers in size, but some can be less, obviously (i.e., 10 copies to one 5-digit zip). 

                 I have fmpro11 and it doesn't seem to have SQL ability, not that I'm very good at SQL, but I know that might provide a nifty solution. 

                 Thanks for getting back so quick,

                  

                 Jay

            • 3. Re: zip code sorts
              jayGamel

                   BTW, with less than 200 labels to deal with, I made a report to count each zip and zip3 field (I figured out how to make that one) and I have a radio button for 5,3,MXD on each record, but people come and go and it's a PIB to recount them every time and maybe have to change  bunch of 5's to 3s or MXD. This feels like something a db should be able to do.

                    

                   Maybe there's some way to have the correct choice entered automatically every time a count check is done? This is where I wander off the map.

              • 4. Re: zip code sorts
                philmodjunk

                     It is something a script could do, but it won't be a simple script due to the criteria for how a given record becomes a member of one of the three types of groups.

                     On the one hand the number of zip digits in common, 5, 3 or any is a factor, but you also need 10 or more for all but the MXD bundle.

                     To avoid making multiple loops, I suggest setting up a related table and linking each record of a given group to the same related record, when the count and type of zipcode matching determines a label for the group, you set that label in the related record and show that field from the related table on your labels.

                     The Relationship:

                     Labels>-----GroupType

                     Labels::_fkGroupID = GroupType::__pkGroupID

                     Define __pkGroupID as an auto-entered serial number field
                     Define GroupLabel as a text field in GroupType

                     Enable "allow creation of records via this relationship" for GroupType.

                     The script:

                     Sort Records [no dialog ; Restore ]  ---> Sort records by zipcodefield
                     Go to Record/Request/Page [First]
                     Loop
                        #New Group
                        Set Field [GroupType::GroupLabel ; 5 //creates new record in GroupType and links current Labels record to it ]
                        Set Variable [$GroupID ; value: Labels::_fkGroupID ]
                        Set Variable [$Type ; value: 5 ]
                        Set Variable [$Count ; value: 0 ]
                        Loop
                            #Identify group
                            Set Variable [$Count ; value: $Count + 1 ]
                            Set Variable[$NextZip ; Value: Let ( R = Get ( RecordNumber ) ; If ( R < Get ( FoundCount ) ; GetNthRecord ( Labels::Zipcode ; R + 1 ) ) ) ]
                            Set Field [Labels::_fkGroupTypeID ; $GroupID ]
                            IF [Left ( $NextZip ; 3 ) ≠ Left ( Labels::Zipcode ; 3 ) // end of possible 5 or 3 group ]
                                 If [ $Count > 10 // you have a group 5 or group 3 ]
                                    Set Field [GroupType::GroupLabel ; $Type ]
                                    Go to Record/Request/Page [next ; exit after last ]
                                    Exit Loop If [True // Exit the loop so script can process next group ]
                                 Else
                                     #MXD Type
                                     Set Field [GroupType::GroupLabel ; "MXD" ]
                                    Go to Record/Request/Page [next ; exit after last ]
                                 End IF
                             Else If [$NextZip ≠ Labels::Zipcode And Left ( $NextZip ; 3 ) = Left ( Labels::Zipcode ; 3 ) //may be end of 5 group or 3 ]
                                 If [ $Count > 10 // you have a group 5 or group 3 ]
                                    Set Field [GroupType::GroupLabel ; $Type ]
                                    Go to Record/Request/Page [next ; exit after last ]
                                    Exit Loop If [True // Exit the loop so script can process next group ]
                                 Else
                                    #Still possible group 3
                                    Set Variable [$Type ; value: 3 ]
                                    Go to Record/Request/Page [next ; exit after last ]
                                 End IF
                              Else
                                 #Still possible Group 5
                                 Go to Record/Request/Page [next ; exit after last ]
                            End IF
                         End Loop
                         Exit Loop IF [ Get (RecordNumber ) = Get ( FoundCount ) ]
                     End Loop
                     #Last Group may be unidentified
                     Set Field [ GroupType::GroupLabel ; If ( $Count > 10 ; $Type ; "MXD" ) ]
                     Sort Records [no dialog ; Restore ] ----> Sort by GroupLabel, then by Zipcode to group all "MXD" records into a single group.
                               
                     Note: I have run this script with a simple set of test records and it seems to work, but more extensive testing might not be a bad idea...