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.
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
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!
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.