13 Replies Latest reply on Dec 15, 2013 6:28 AM by philmodjunk

    Autofill postcode based in city

    FilipePimentel

      Title

      Autofill postcode based in city

      Post

           Hi there, 

           I have a solution where I enter de zip code and the city comes up. That is good, but where we live, people know city names better than they know the zipcodes. 

           So I am trying to change so the user enters the city and the post code comes up. No...there is no city with two zipcodes, as the cities are very small (20-90k inhabitants), so there would be no two zips for one city. 

           I tried reversing the lookups but I am not getting anything. I have for the Solution "enter zip and get the city" I have:

           company::Zip >------< Zip::Zip

           then lookupNext: 

           lookupNext (Zip::City; Higher)

           The company::Zip field uses the value list from Zip::Zip. 

           ********

           For the Solution "enter city get Zip"I had to create a new table occurrence of the Zip Table

           MainTAble::ZIP >--------<Zip2::Zip

           And my lookupnexts:

           Zip: lookupnext (Zip2:Zip; Higher)

           and the city using value list Zip2:City

            

           Any help is appreciated

            

           F

            

        • 1. Re: Autofill postcode based in city
          philmodjunk

               If you are going to use the City to find the PostCode, you would match records by the City field instead of the Zip.

          • 2. Re: Autofill postcode based in city
            FilipePimentel

                 I am sorry, my mistake: I meant to say: 

                 on the "enter city and get zip"

                 MainTable::City > - - - - - < Zip2::City

                 I am still not getting anything...

            • 3. Re: Autofill postcode based in city
              philmodjunk

                   The relationship that you show should work.

                   As a test, put a portal to Zip2 on your main table layout and enter/select a city in the MainTable::City field. Do you see a record appear in the portal? Is it the correct record?

              • 4. Re: Autofill postcode based in city
                FilipePimentel

                     Shoul I have the allow record to be create via this relationship ticked?

                      

                     My Zip2 portal in my main table does not how any fileds, even though the fileds are in the portal...

                • 5. Re: Autofill postcode based in city
                  philmodjunk

                       No, it's just to see if the correct data appears as a test. If the portal is empty, as you have seen, it tells us that the relationship and the match fields need a closer look.

                       Make sure that the two "city" fields are both of type text. Check to make sure that both text fields have exactly the same text when they are supposed to match by city. The presence of a punctuation mark or an extra space character in one field that is not in the other will be enough to keep records from matching like you expect them to.

                  • 6. Re: Autofill postcode based in city
                    FilipePimentel

                         Got it. Right on the money....my fields were set differently...

                         Thanks once again

                         F

                    • 7. Re: Autofill postcode based in city
                      FilipePimentel

                           Now, what If I wanted to allow the user to add the ZipCode to the ZipTable...Say the city is not on the Table and it is a new Client. Can I keep the same structure or should I change the way all this is presented?

                           Thanks

                      • 8. Re: Autofill postcode based in city
                        philmodjunk

                             And how is it "presented" currently?

                             Are you using an auto-enter field option to copy the value or have you placed the zipcode field from Zips2 on your layout? With the second option, if you enable "allow creation..." in the relationship to Zips2, your user can simply type the zipcode into the empty field and you'll get a new record with the zipcode and city in the Zips table.

                        • 9. Re: Autofill postcode based in city
                          FilipePimentel

                               Currently it is presented with maintable fields only linked via lookupNext and relationships...

                               Now, if I want to add the data to the Zip table, should I add the fields from this table, or only the zipcode field?

                               thanks

                          • 10. Re: Autofill postcode based in city
                            philmodjunk

                                 If the fields are not on the layout, how will you edit them? You'll need to either include those additional fields on your layout or put a "new city" button on it that takes the user to a layout or opens a dialog where they can add the additional data.

                                 In FileMaker 13, this might be an interesting place to add a pop over button as you could place those additional fields inside the popover.

                            • 11. Re: Autofill postcode based in city
                              FilipePimentel

                                   Cool...I understand that I need the fields...just tested and it workeds with only having the zip and state fileds...when I filed the city, it asked if I wanted to allow this entry, once i said yes, it added all data to the zip2 table...

                                   But the idea of popover with filemaker 13 is a good one. Quite neat...

                                    

                                   Thanks again

                              • 12. Re: Autofill postcode based in city
                                FilipePimentel

                                     I have another question:

                                     I have found a file where I have the Address, Área, City, Zip, State. Is there a way I can enter the address and get the area, city, Zip and State?

                                     There are situations where the same address gets two different zips: say from 1 to 100, London Street = Zip X and from 101 to 200, London Street = Zip Y.  There are also situations where you can have the same addresses for different zips and citiss

                                     London Street, London

                                     London Street, Manchester

                                     How can I get a solution where entering the address, number I will get the correct zip, Area, City, State. 

                                     Also, It would be ideal if I could enter new addresses (complete). 

                                     Do I need to split the tables in two - say city, Zip, State and Address, Area? 

                                     Thanks

                                • 13. Re: Autofill postcode based in city
                                  philmodjunk

                                       To enter the address and get the rest of the data, you need a table with one record for each such address, but your post also identifies the key shortcoming to that approach--the fact that street addresses are not unique. The same street address can occur in multiple cities.

                                       To add to that, keep in mind that addresses can be entered differently for the same address when one types in an address: "2345 2nd Avenue, 2345 2nd street, 2345 second ave, and 2345 second st." might all refer to the same address.

                                       It would appear a better idea to set up a search feature that allows a user to enter just key words from the address such as the number and name and then, in the case of multiple matches, the system pops up a list of matching addresses with the additional data such as the name of the city where the user can click or tap to select the desired address.

                                       That will require a different relationship and some scripting to do.

                                       The relationship would be:

                                       MainTable::_fkAddressID = AddressRecords::__pkAddressID

                                       where __pkAddressID is an auto-entered serial number field.