I am sure this is not very complicated, but I have not done it before and do not want to risk screwing up existing data in my database.
I have a situation where I have a Customers table.
I have a second table called Trips that contains child records for customers.
Users create records in the Trips table and assign them to a customer record in the Customers table.
But every week I also have to import trips created in another software into my FM solution and combine them with the records entered directly into the FM solution and then run reports on them. I would export the data from other software into an Excel sheet and then import into FM solution.
So how do I "relate" the imported records into the Trips table to their parent records in the Customers table? I need to write a script that goes through the imported records using some common field and matches that to the records in the Customers field and then assign the Customer_ID field to the new Trips records.
There is one a few common fields between my FM solution and the other software. The trips coming in from other software have Customer Last Name, Customer First Name and State ID. Technically it is possible that 2 customers could have same First and Last name, so I think I would want to use the State ID as it is just one field and can not be duplicated.
So how do I step through imported data, use the State ID field as a lookup to find its matching parent record in the Customers table and then copy the parent record's Customer_ID to the imported record?