I am tussling with how to work a problem with a database that I am building and I was hoping to call upon some more senior and experienced advice.
To put my problem into context, I run an event where a customer creates a product and I finish it for them after the event. At the end of the event, all of the customers enter information about how they want it finishing and where they want it returning to.
> A user completes a Google Form with data. This includes product data and customer data.
(It has to be Google Form or an online form for portability and easy setup - we run multiple events a day across the country)
So ... After this event, I'd like to import the CSV table from the Google Form into my FMP database. To simplify, let's say I have 2 tables - Customers and Products.
Could somebody help me build a script that does the following:
> Imports the data to the database. This could be to a temporary import table for example.
> Transfer segments of data to each of the two tables and relate the records to one another
...I'd also like to look into de-duplicating the import with a matching email address. So how could I add logic to the script to say that if the email address exists, update this record, otherwise, create a new customer record?
My confusion is with building an automated import task that links segments of data. Could someone guide me with a scripting advice?
I've attached the basic relationship below!