Our Preferred 5 Star Hosting Provider - Award Wining FileMaker Pro Hosting
Page 1 of 2 12 LastLast
Results 1 to 10 of 15
  1. #1
    FREE Member
    Join Date
    Oct 2012
    Posts
    15
    Downloads
    0
    Uploads
    0

    Question How to match adress with zip code?

    The Scarpetta Group- our preferred FMT Forum Consultants
    Hi there,

    As you will know by my question, I am a very fresh Filemaker user..

    I come from Norway and our adresses and zip codes are like this example:


    • 3200 Sandefjord


    I have imported all the combinations to a table with two fields called "Zip" and "CityName". There are 4711 records.

    After entering a Zip code in one field and entering the "CityName" field, I would like it to find it automatically by matching it to the table with every combination avaiable in our country.

    All help are appreciated
    Warning: This is an Old Thread
    This discussion is older than 420 days. information contained in it may no longer be current

  2. #2
    FREE Member
    Join Date
    Oct 2012
    Posts
    15
    Downloads
    0
    Uploads
    0

    Lightbulb Re: How to match adress with zip code?

    Since there may be more like me out there, I will answer the question myself..

    The key to this simple problem really is LOOKUP VALUE

    Example:
    - 'Customer' table with fields for Name, Zip and City
    - Another table 'ZipCity' with two fields for Zipcode and Cityname
    (This list you import from 'Bring' if you need the Norwegian on)

    1) Go File/Manage/Database and relate the two tables matching from Customer::Zip to ZipCity::Zip
    2) Then go to Field Tab and dobbel click on the Customer::Cityname field
    3) Then press 'Specify' to the right of the text 'Looked-up value'
    4) Choose then:
    • 'Starting with table:' Customer
    • 'Lookup from related table:' ZipCity
    • 'Copy value from field:' Cityname
    • 'If no exact match, then:' do not copy


    Just close up all the windows and try to make a new record.

    That should do the trick! I've used several hours on this issue today before I suddenly find it, and so darn easy hehe.

  3. #3
    FREE Member
    Join Date
    Oct 2012
    Posts
    15
    Downloads
    0
    Uploads
    0

    Re: How to match adress with zip code?

    About single-criteria relationships


    In a single-criteria relationship, data in one field is matched to data in another field.


    For example, a record in either table is related to any record in the other table when the values in TextFieldA and TextFieldB are the same.
    Using a single-criteria relationship
    Use a single-criteria relationship when you want to relate two tables based on a single common value, such as a serial number or an ID number. For example, a contacts database has two tables:
    Contacts, which stores names and addresses

    Phone Numbers, which stores phone numbers and phone types, such as work, home, fax, and so on

    The data is split between two tables because a single contact can have multiple phone numbers.
    The tables have these fields:

    Table Field Name Comment
    Contacts Name Text field
    Address Text field
    City Text field
    State Text field
    Postal Code Text field
    Contact ID Number field, auto-enter serial number; this will be the match field for the Contacts table
    Phone Numbers Phone Number Text field
    Phone Type Text field
    Contact ID Number field; this will be the match field for the Phone Numbers table
    The Contact ID field is the match field in the relationship between the two tables. In the Contacts table, the Contact ID field is set to auto-enter a serial number, giving each record in the Contacts table a unique ID number. In the Phone Numbers table, the Contact ID is left as a simple number field, and no auto-entry options are set.
    Because the relationship is defined to allow the creation of related records in the Phone Numbers table, the Contact ID number from the Contacts record will be automatically inserted in the Phone Number Contact ID field in each new related record. This allows many records in the Phone Numbers table to be related to a single record in the Contacts table.
    In the relationships graph, a relationship is created between the Contact ID field in each table.

    In the Edit Relationship dialog box, the Allow creation of records in this table via this relationship option is enabled. This means that the user will be able to enter a phone number in an empty portal row, and FileMaker Pro will automatically create a related record for that phone number. The Delete related records in this table when a record is deleted in the other table option is also enabled, because there is no reason to retain old phone numbers once the matching contact record has been deleted.
    Finally, a portal to the Phone Numbers table is added to the Contacts layout.

  4. #4
    Member
    Join Date
    Nov 2013
    Posts
    76
    Location
    Brussels, Belgium
    FileMaker Version
    FM 11 Developer
    OS
    Mac
    Skill Level
    Intermediate
    Downloads
    0
    Uploads
    0
    Mac OS X 10.9.1
    Hi FaxModem, I try to follow your solution, but I am struggling. Can you explain in a way I can follow you step by step. You say "Go File/Manage/Database and relate the two tables matching from Customer::Zip to ZipCity::Zip". Here is my first problem: How do I relate the 2 tables?
    Thanks for answering me.

  5. #5
    Good guy doughemi's Avatar
    Join Date
    Apr 2005
    Posts
    942
    Location
    Middleport, NY USA
    FileMaker Version
    FM 9, FM 9 Server, FM 11, FM 11 Server, FM 12
    OS
    Windows & Mac
    Skill Level
    Intermediate
    Downloads
    0
    Uploads
    0
    Mac OS X 10.8.5
    iMac1943, FaxModem asked and answered his question in October 2012, and that was the last time he was active on this forum.

    For more information about relationships, see this help page. To actually create the relationship, just drag from the desired field in Table A to the desired field in Table B. If you want change the relationship from "=" to some other operator, double-click the = sign in the connector and make the change in the resulting dialog.
    Give the gift of life. Register as an organ donor today!
    http://www.organdonor.gov/become.asp

  6. #6
    Member
    Join Date
    Nov 2013
    Posts
    76
    Location
    Brussels, Belgium
    FileMaker Version
    FM 11 Developer
    OS
    Mac
    Skill Level
    Intermediate
    Downloads
    0
    Uploads
    0
    Mac OS X 10.9.1
    Thank you doughemi for your help. Works fine now. Up to the next problem

  7. #7
    Member
    Join Date
    Nov 2013
    Posts
    76
    Location
    Brussels, Belgium
    FileMaker Version
    FM 11 Developer
    OS
    Mac
    Skill Level
    Intermediate
    Downloads
    0
    Uploads
    0
    Mac OS X 10.9.1
    Hi doughemi, I found the solution, but lost it again... I have 'Customers', 'Invoices' and also (new made up) 'Zip' with in there 'City' and 'Zipcode'. If I look at 'Relationships' in 'Customers' I have been able to connect 'zip' with 'Zipcode' in 'zip'. OK, fine, but now I want to do the same in 'Invoices' but 'zip' (as in 'Customers') is not present there. In the layout of 'Invoices' I added a field 'Zipcode' but it's still not present in 'Invoices'. Where am I wrong? I hope my explanation is clear...

  8. #8
    Good guy doughemi's Avatar
    Join Date
    Apr 2005
    Posts
    942
    Location
    Middleport, NY USA
    FileMaker Version
    FM 9, FM 9 Server, FM 11, FM 11 Server, FM 12
    OS
    Windows & Mac
    Skill Level
    Intermediate
    Downloads
    0
    Uploads
    0
    Mac OS X 10.8.5
    Assuming that there is a customer id field in invoices, you can create a relationship between invoices and customers:

    Code:
    Invoices::CustomerID>---- = ----< Customers::CustomerID
                                            Customers::zip >---- = ----< zip::Zipcode
    Then you can place a field zip::Zipcode on your invoices layout.
    Give the gift of life. Register as an organ donor today!
    http://www.organdonor.gov/become.asp

  9. #9
    Member
    Join Date
    Nov 2013
    Posts
    76
    Location
    Brussels, Belgium
    FileMaker Version
    FM 11 Developer
    OS
    Mac
    Skill Level
    Intermediate
    Downloads
    0
    Uploads
    0
    iOS  7.0.4 (iPad)
    Thanks Doughemi, but may I ask you something? I am not (yet) familiar with the way formules are written. Can you please write that formule in 'words', this will learn me how to read the formule and it will help me a lot in the past. Many thanks!

  10. #10
    Good guy doughemi's Avatar
    Join Date
    Apr 2005
    Posts
    942
    Location
    Middleport, NY USA
    FileMaker Version
    FM 9, FM 9 Server, FM 11, FM 11 Server, FM 12
    OS
    Windows & Mac
    Skill Level
    Intermediate
    Downloads
    0
    Uploads
    0
    Mac OS X 10.8.5
    Fields are always shown as TableName::FieldName. This is meant to be a text representation of the way the relationship diagram looks.

    So:

    The customerID field in Invoices is related with an equals operator to the customerID field in Customers.

    The zip field in Customers is related with an equals operator to the zip field in zip.

    or:

    snap.jpg
    Give the gift of life. Register as an organ donor today!
    http://www.organdonor.gov/become.asp

Page 1 of 2 12 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Field Match
    By memoriafuturi in forum FileMaker Pro 10
    Replies: 1
    Last Post: 01-01-2013, 05:48 PM
  2. No Match on Find
    By Rook2Q5 in forum FileMaker Pro 12 - All versions
    Replies: 3
    Last Post: 11-27-2012, 01:15 PM
  3. Replies: 1
    Last Post: 08-31-2010, 02:34 AM
  4. Making a adress box.
    By Ranjan in forum FileMaker Pro 10 Advanced
    Replies: 5
    Last Post: 07-23-2010, 06:38 AM
  5. Match Fields
    By beltra in forum Exporting and Importing
    Replies: 10
    Last Post: 01-30-2008, 09:58 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Welcome to the Original FileMaker Forum
With designated forums for almost every FileMaker topic...FileMaker Today is a free-to-join community where you can boost your FileMaker expertise, build better solutions and solve your technical challenges. If you're building FileMaker solutions, this is the place for you.
Follow FMT
We Are FMT
FMT is home of The World's leading FileMaker News Site, Worldwide Developer Directory, FileMaker Connect Community and so much more.