3 Replies Latest reply on Nov 9, 2009 9:56 AM by philmodjunk

    Will this require a script?



      Will this require a script?


      (I'm making this example simple.) 

      I have a table of customers.

      I have a table of orders. 

      The tables are joined on a Customer ID key. 

      I want to create a customer from the orders table.  So far, so good.

      But when the customer calls for a second order, I want Filemaker to recognize that her phone number already exists and pull up her (formerly created) Customer ID. (so that I still only have one customer record instead of a duplicate with two different customer IDs.)




        • 1. Re: Will this require a script?

          No need for a script! Make sure the orders and customers are related by telephone number. Once you type in an existing telephone number, the remaining customer information will appear.


          There is a weakness with this model, however. What if a customer moves? The new address information changes old orders in your current model. What if the customer changes his/her telephone number? You have duplicate customer information. And what happens when that old telephone number gets re-assigned by the telephone company to a new person? The old customer information on old orders gets replaced with the new customer information.


          The customer information should be copied (via lookups) to the order every time a new order is created. That would avoid the above-mentioned issues. However, now you will need a button to create new clients when no information copies because the client record does not exist.

          • 2. Re: Will this require a script?



            Never thought of using a "natural key" of phone number to join.

            That's the ticket!



            • 3. Re: Will this require a script?

              I added a PhoneMessage database linked to our contact manager DB so that our employees could take and route messages electronically. In doing so, we found another issue with using a phone number in searches or as a relational key.


              We found that the phone numbers weren't unique due to out of date contact information. Customers move and someone else is issued their phone number. Numbers get mistyped and (this may not apply to you) the same person, may represent two or more different businesses where the business is the "customer" we needed info on.


              I ended up adding scripted support so the message take could enter a phone number and click a button if they wanted to search by phone number. If the search produced multiple "hits", it then pops up a list of matching records from which to choose.