14 Replies Latest reply on Jul 10, 2016 12:36 AM by greatgrey

    Finding Records by Zip Code

    DanHarris

      So, I'd like to pull a subset of facility records within my solution based on zip code from a narrowly defined local area. I've created a list of the zip codes within this local area, but I'm unsure how to integrate this list into the solution and then find the records that match this list.

       

      What suggestions do you have for accomplishing this?

        • 1. Re: Finding Records by Zip Code
          bigtom

          Have a Table with a global field that will store your list, ZIPLIST. Relate that to the zip code field for table you wish to see.

           

          By script go to a layout based on the table that holds the global field and then set the global to the list of Zip values.

          then use go to related records scripts step to go get the records with matching zip codes in the needed records table.

           

          If there are other criteria this will change a little. More details about your actual use will allow some more detailed ideas.

          • 2. Re: Finding Records by Zip Code
            coherentkris

            DanHarris -

            Go to a layout with the zip code field.

            Go into find mode.

            Enter the first zip code in the zip code field.

            Create new request

            Enter the n zip code from the list

            Crete new request/enter zip code until all zip codes have been entered as requests.

            perform find.

             

            Alot depends on how you "integrate this list into the solution"

            • 3. Re: Finding Records by Zip Code
              bigtom

              Repeated finds can work I just try to avoid them when possible.

              • 4. Re: Finding Records by Zip Code
                coherentkris

                Bigtom -

                No argument there. I was just trying to show another method.

                • 5. Re: Finding Records by Zip Code
                  dtcgnet

                  If the list is fairly static, and you want to be able to quickly find all records from the list, you could create an auto-enter (or calc) field something like:

                   

                  IsKeyZip=

                  Case (

                     Zip = 12345 or Zip = 12346 or Zip = 12347 ; "Yes" ;

                     "" )

                   

                  Then you could just do a find for "In Key Area" in that field.

                  • 6. Re: Finding Records by Zip Code
                    DanHarris

                    Thanks for the suggestions everyone.  I think I've decided to add a Local Facility radio button to the solution and will perform the suggested find, then use the Replace Field Contents to Yes on the radio button field.

                     

                    Do you see any issues with this method?

                    • 7. Re: Finding Records by Zip Code
                      dtcgnet

                      Do this:

                       

                      1. Create LocalFacility as a field. Set it up as a calculation field, using a formula similar to what I outlined.

                      2. Because the two fields will be in the same table, the field will default to a Stored calculation, and all records will automatically calculate the proper value.

                      3. Either leave it as a stored calculation, or change it to a text field with that calculation as an auto-enter value.

                       

                      Done.

                      • 8. Re: Finding Records by Zip Code
                        coherentkris

                        if your script does a find and then does a replace field contents you must trap for errors in the find or you run the risk of altering data that you do not intend. Replace field contents is not forgiving and once its is started it will change all the records in the current found set even if the found set is wrong.

                        • 9. Re: Finding Records by Zip Code
                          DanHarris

                          Thank you all for the help. I got this working using dtcgnet's advise (calculation field) a few weeks ago. It's great to enter new facilities and have the field auto enter Yes into the Local Field based on the calculation.

                           

                          But, as I've continued to build out this solution, I've run into a few issues.

                           

                          1) If a facility is imported that contains a nine digit zip code, the auto enter Local Facility calculation does not correctly identify it as local. Is there a method of including four wildcard characters to the tail end of the list of local zip codes within the calculation field? Or maybe another option?

                           

                          2) I'm implementing a dashboard layout to automate user search of facilities. The dashboard uses a script to find records based on a number of checkbox fields a user can select based on the facility parameters they wish to search for. When performing the find based on the "Local Facility > Yes" field, the find takes over 30 seconds to pull the local facilities out of a total of 6700 records. Performing a similar find using other parameters is instantaneous, so I'm assuming the local zip code calculation is likely the cause. I attempted to change it to Stored, but received a pop-up saying that this isn't possible b/c it references a related field, a summary field, an unstored calculation field, or a field with global storage.

                           

                          Any help to point me in the right direction to address these issues is much appreciated.

                          • 11. Re: Finding Records by Zip Code
                            greatgrey

                            I would store the Plus 4 digits in their own field. Otherwise you will need a very detailed find/lookup info. If you need it restricted by box number, carrier route or county, then use ranges on the Plus 4 part of the find.

                            While you may use one field for entry I then use a script with an exit trigger to separate the basic 5 digits from the Plus 4 digits. Along with other checks, length 5 or 9 or 10. if not probably an error in the zip info.

                            • 12. Re: Finding Records by Zip Code
                              DanHarris

                              Thank you for your response greatgrey, that idea hadn't even crossed my mind and it really should have. Now I'll just need to learn how to parse those last four digits and bring them into a new field.

                               

                              That should be a lot easier than the alternatives I had considered. Thanks again.

                              • 13. Re: Finding Records by Zip Code
                                greatgrey

                                Left(text;5); Right(text;4)

                                edit If you are processing your current zip field get the Plus 4 digits first.

                                • 14. Re: Finding Records by Zip Code
                                  greatgrey

                                  Zipcode fields should always be text fields because of leading zeros.