4 Replies Latest reply on May 24, 2011 10:44 AM by philmodjunk






      Good day, one and all.

      I have just converted to Mac and thoroughly thrilled with all things Apple now! I have been using MS ACCESS for several years at I guess an intermediate level...

      I have a table “CUSTOMERS” and a table “ORDERS” (there are more, these are the two I wish to compare)

      I receive purchase orders for travel passes from a local authority, I need to identify:

      • New Customers (Those that are in ORDERS but not CUSTOMERS)
      • Customers that have moved address (they already exist in CUSTOMERS but their address has changed)

      Fortunately they all have a unique [Passenger ID] which is always provided in each purchase order

        Table CUSTOMERS [Passenger ID] [Add1] [Add2] [Add3] [Add4] [Add5] [Route] (& some other record fields not compared)

        Table ORDERS         [Passenger ID] [Add1] [Add2] [Add3] [Add4] [Add5]

      Each [Passenger ID] could have many orders over a period of time

      I would also like for the new customer details to be imported into the “CUSTOMERS” tablem, that way they will be there for the next time round we get a purchase order & there are other fields in the customer record that I will use.

      For both instances I would like to pause at each record that’s flagged as new or changed in a form layout to either amend or populate the [Route] field.

      This would take me a hundred years to figure out, I’m sure this would be a walk in the park for some of you clever Guys n Gals!

       I really look forward to hearing from someone soon... ;@)

        • 1. Re: Comparisons

          What is the "route" field and how is it used, that you'd need to pause on each record to update it?

          The reason that I'm asking that is that you can do an update import from your file of new orders into Customers matching on Passenger ID with the Add if no match option and it would update all the address fields and add your new customers all in one import of your data. If there's a way for the system to update the route field for you automatically, then you no longer have to do this manually one record at a time.

          • 2. Re: Comparisons

            Thank you PhilModJunk for showing an interest in this.

            I  work in Passenger transport and the Route in real world terms is the bus route that they are allocated to based on their home address and proximity to the nearest collection point, where they are going, yadda yadda! In the tabbed form view I have googlemaps so I can allocate each passenger to a route with my knowledge of where our buses run & where the the Customer is traveling to, (all of which are schools or colleges)

            Each customer MUST be allocated to a particular bus route hence the importance of me knowing if they are a new customer so they need a route allocated & be added to the customer table or whether they're an existiing customer but their current adress has changed at all and i need to re allocate them to a new bus route.

            If they already exist on the customer table and they haven't changed address, when I receive a new order then no intervention is required from the database operator,(that'll be me, then!)

            So, the purpose of the pause would be to make a human descision of that individuals routing requirement based on their transport need and to  manually populate empty RouteID foreign key in the Customer table in a methodical manner.

            "Oh, here's Fred Blogs, he's a new customer, I see he lives in New St, in Anytown... Hmm, yeah he'll need the 621 rote" 'I'll type that into the Route field and hey, he's done! "

            I have a seperate table called "Route" , RouteID in that table is the primary key,  other fields have information about that bus route like a timetable, I have a field RouteID as a foreign key in the CUSTOMERS table.

            A Route can have many Customers, but a customer only one route

             I guess that's a clear as mud know !! Surprised

            • 3. Re: Comparisons

              Of course, the next thing will be for efficiency, I wouldn't need their address data stored in both the order and the customer,  I think we should keep the record in the Customer table up to date and store/ use that one only

              Oi yoi, that's my head in a whole more load of knots!

              • 4. Re: Comparisons

                I agree that Orders should not store the address.

                New customers are easy to find as they will have empty route fields. The changed addresses, are what are tricky here. I was just playing with using a file with a modification date field to see If I could tell the difference that way but it doesn't work as FileMaker flags the record as updated if a matching record exists in the import file even if all of the data is the same.

                I think that you'll need to import your records into an intermediate table and then use a relationship to compare address fields.

                Define this relationship:

                Customers::Passenger ID = Import::Passenger ID

                In Import, define this calculation field, cNewAddressFlag:

                ( Add1 & Add2 & Add3 & Add4 & Add5 ) ≠ ( Customer::Add1 & Customer::Add2 & Customer::Add3 & Customer::Add4 & Customer::Add5 )

                Set this calculation to evaluate even if the referenced fields are empty.

                To find changed addresses and new customers, Perform a find where you enter a 1 in this calculation field. After assigning routes, you can do the import with update option from Import, or you can use a script that uses these records to update the Customers table.