12 Replies Latest reply on May 11, 2012 2:41 AM by disabled_morkus

    Zip Code Validation?

      Title

      Zip Code Validation?

      Post

      For this question, assume I've downloaded and created a CityZip table (in the FMP database) using the free CSV data found at: "http://federalgovernmentzipcodes.us/"

      Here's how I am thinking zip code validation would work for a couple of use-cases. Look forward to hearing back if FMP would do these types of validations easily.

      --------------------------------------------------

      Use Case 1: Zip code validation when entering zip code:

      When the user tabs off the zip code field after entering it (or maybe FMP can detect when he's entered 5 digits in the zip code field),  I would want to lookup the entered city in the CityZip table on that layout and verify that the zip code entered matches it.

      If the zip code doesn't match the city the user entered, I'd like to give the user a pop-up showing zip codes for the city entered (assuming there's more than one zip code for that city in the CityZip table).

      The user could pick the correct zip from the popup and FMP would replace the incorrect zip code in the layout with the one from the pop-up list.

      The popup would be populated, I assume, by creating a script that would take the user's entered city from the layout and do a lookup on the CityZip table. The script would build a list of all zips, in a list, for the entered city. FMP would also display the "found city" for the zip code entered (perhaps the city was the incorrectly-entered data) using the same type of lookup.


      Alliteratively, if FMP can't give a dynamic pop-up list with correct possible zip codes for the entered city, then at least a dialog box listing possible zip codes for that city.


      ---------------------------------------

      Use Case2: Validating a FMP table with cities and zip codes already entered (after the fact data validation):

      Here, I'm assuming I would need to write a script so FMP would scan through the entire table of customer addresses. For each address, it would verify, again using the CityZip table, that the entered city matches the entered zip code. If a particular customer address record has a problem, FMP would add this problem to some type of output report.

      Are these use-cases both doable in FMP?

      Look forward to any input.

      Thanks,

      -- m

        • 1. Re: Zip Code Validation?

          Cities and Zip Codes don't always match in the PO tables and are often based on the location of the PO and not by the name of the city. So several small suburban city/towns can share the same Zip Code. 

          Likewise a City name can exist several times in one state but seldom in one State/County matchup.

          Zips are not only based on city but on address and large cities may have many Zip Codes.

          It's not an easy problem but you can link tables with multiple fields say City/State/County/Zip...

          • 2. Re: Zip Code Validation?

            Hi Jack,

            My question wasn't really about whether cities and zips don't match, or really even the zip code thing specifically, and I appreciate the point you're making, but rather, from a programming point of view, if FM could do what I'm describing.

            To generalize what I was asking, if the user enters something in a field and you have a validation table out there, how difficult is it, how do you do it, to SEEK in the validation table for the value th user entered and build a list of values from the validation table to present back to the user.

            In the zip code example database I refereenced above, there are > 80K rows. Therefore, an index would probably be needed.

            What I'm asking is about the Find mechanism and a rough idea of the scripting that would be involved. Also, what hooks do you have to run a script when a user "tabs off" a field?

            Would you execute some type of Find statement on the lookup table for the user entered value? What if the lookup table had multiple values you wanted to present to the user where he could pick one that would fill in over his just-made entry.

            A related question would be how would you do something like this in FM:

            Possible bogus zipcode: SELECT zipcode FROM userlayout where zipcode not in (SELECT zipcode from validationtable)

            ?

            ----------

            Sorry for any confusion.

            Thanks,

            -- m

            • 3. Re: Zip Code Validation?
              philmodjunk

              FileMaker can do each of the tasks that you describe.

              Take validation first as it applies to both tasks:

              Define this relationship between the two tables:

              Yourtable::City = CityZipTable::City AND
              YourTable::Zip = CityZipTable::Zip

              With this relationship, you can use IsEmpty ( CityzipTable::City ) to check for a matching entry in your table of city and zipcodes.

              For Task #2, you can put CityZipTable::City on a layout based on YourTable, enter find mode, put * in this city field, click the omit button and perform the find to get a list of all records that fail this validation test.

              For using this table to assist user input, I suggest a conditional value list, though you can do exactly what you describe if you prefer. With a conditional value list, the user inputs a city and then the zipcode field can list only the zipcodes found in your zipcode table for that city.

              Forum Tutorial: Custom Value List?

              Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

              Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html

              Hierarchical Conditional Value lists: Conditional Value List Question

              Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

              • 4. Re: Zip Code Validation?

                Thanks much as always. :)

                • 5. Re: Zip Code Validation?

                  These TOs will cover many of your questions. Make this links and then create an address entry form and add a portal to each of these TOs. Watch what happens in the TOs as you enter hour information.

                  You can create a Lookup for the Zip Code field to the TO with city and state or city, state and county.

                  You might consider a conditional color for the field perhaps

                  Light Blue = Needs Data  isempty(self)

                  Red = count(relatedzipcodeto) > 1

                  Brown = count(relatedzipcodeto) = 0

                   

                  If the Zip Code portal shows zip code, city, county, state then you can make the portal row a button that can be clicked and has the Set Field (Zip Code; Me::ZipCode) and your user can select one to enter. If you do this, use the simple connection of city to city.

                  You can also make the ZipCode field a popup menu and assign it so that two fields show up. The second field could be a calculated field containing city and state.

                  The simplest method would be to have the zip code looked up and to show a Red Background if there are more that two Zip Codes so your user will know to click in the field to see the choices.

                  Mentor Question: is there an actual need for all of this or are you on a walkabout trying to discover as many cool things as you can design? The walkabout is a tour we all take when we first discover Filemaker. It can last several years as we build our toolkit of things we can do.

                  • 6. Re: Zip Code Validation?

                    Thanks for the great reply, Jack.

                    Yes this is partially a "walk about" question. I'm playing with the evaluation version now and thinking of how it maps to my Java-SQL background. Things that I take for granted in SQL seem puzzling in FMP often. Yet, the user interface in FM is there where using MySQL and even some SQL front end, I'd still have to do the UI myself.

                    As a senior software developer, I'm wondering if I should get the video series (which may be too simplistic, I don't know) or just get the new FM 12 books listed on Amazon.

                    Suggestions?

                    -m

                    • 7. Re: Zip Code Validation?

                      So I tried using the City and State linked TO for a lookup and found that it would not be reliable since my city Hollywood, FL has 14 or so zip codes that appear. However, if I use that dual link to create a portal, then I see all of the links but don't know which one to use...  :)

                      Zip Code needs the street address to find the actual Zip Code, especially when there are 20 Zip Codes within a city.

                      ...

                      Moving to a new application always has a period of frustration for several reasons most of which is how the software is designed to work and to be used by the scriptor.

                      Tools are missing, named differently and have slightly different functions. Filemaker is severely limited in its tool set, compared to other software, and complexity but it offers enough to do a lot of work. It took me a long time to stop crying over the missing tools when I moved from 4D back to Filemaker. But today I think Filemaker is ahead of the pack in many ways.

                      What's really cool and now attracts me is putting Filemaker Go on an iPhone and have data to go. 25 years ago I had a Tandy 100 and learned to peek and poke in basic and began dreaming of a more powerful hand held computer and now I have it. Now I am dreaming of a hardwareless computer I can see and move my hands in 3D..oh wait, they do that in the movies...a biochip implanted in the frontal lobes...

                      Hmm... just had another idea. I've watched one of the, darn can't think of the name, interactive streaming videos where someone yacks on incessantly and it takes forever to learn something, for about 5 minutes then gave up. So, this gives me the idea of a training video that is speed adjustable showing the steps only with little chat and with a drag bar where you can speed up the flow and move back and forth in the video. Like a fast forward and reverse. Maybe tap in a bookmark, etc. Sadly I don't have the skills to create this.

                      Your welcome to browse my blog to see if I have anything of interest: http://www.gofm.biz

                      • 8. Re: Zip Code Validation?
                        philmodjunk

                        I didn't think that was what the OP had in mind Jack. I don't think he wanted to look up the correct zipcode, just confirm that the zipcode entered was consistant with the city the user entered--a much simpler proposition.

                        • 9. Re: Zip Code Validation?

                          PMJ is correct. :)

                          Still enjoyed reading Jacks' chat.

                          What I'm not sure _I_ can live without with FMP is just being able to really get my hands dirty with SQL and coding (not just scripting). And, with coding, the ability to do my expected things, like write low-level files if I need to (a byte at a time). I don't mind a "dumbed-down" tool if there are ways around that so you can do whatever you need. If it weren't for the very cool UI that FMP has, I wouldn't even be pondering it. I'm really not saying FMP is a dumbed down tool; it's more likely that I just don't get the power it has yet.

                          FoxPro and Visual FoxPro, often compared to FMP, have incredible capabilities in SQL and power programming. Having said that, however, Visual Foxpro has been dead-ended by MS and won't be around much longer... VFP has a very cool command window where you can type in commands, have a command history, and go back and re-execute any command. Plus, for the $500 pricetag, you can create network applications that 20 or more users can use. I'm not saying VFP is the way to go. Hardly. Just putting things in perspective based on my experience and hence my hopes/expectations for any DB environment.

                          Java and JDBC gives me the power to do anything I need no contsraints.

                          I still, however, like FMP and am actively considering it.

                          Thanks.

                          - m

                          • 10. Re: Zip Code Validation?

                            Macintosh users have been integrating AppleScript and Filemaker for years. There is a web application that works directly with Filemaker and its fields and scripts but I forget its name. Filemaker has Instant Web Publishing which was created to make people appreciate PHP which Filemaker now uses also.

                            There are a few steps near the bottom which might mean a lot to you and I'll let you explore them as the open a channel to operating system functions and you might find them interesting. In fact, if you get them to work you might send me an email about how to do it...  :)

                             

                            • 11. Re: Zip Code Validation?

                              " just confirm that the zipcode entered was consistant with the city the user entered--a much simpler proposition."

                              When the question is reduced to something that doesn't make my eyes cross, I can provide an answer very quickly...

                              Link Zip, City and State ( 3 links ) and if count (id of zip file) > 0, ok.

                              You could use that idea to conditional color the field, say red if wrong.

                              • 12. Re: Zip Code Validation?

                                Hmmm, interesting.

                                I'm hoping that one of the forthcoming books on FM 12 (perhaps the functions reference due out in August) will go into detail on these script functions you referenced above.

                                AppleScript, while not really in my wheelhouse, so to speak, would be a reasonable compromise. Something else to learn. From my looking at AppleScript so far, it's natural language approach really makes it (ironically) confusing.

                                Again, hopefully one of the forthcoming books will go into AppleScript (or PHP) integration. I didn't see any mention of AppleScript in the single FileMaker Pro 11 book I saw.

                                Do you know if there is some type of monthly magazine for FMP with examples of how to do things (for example, with something like the recent zip code validation would be a good article entry)?

                                Just wondering...

                                -m