Is this a "recurring import" set up for you by FileMaker such that it automatically deletes all records before importing new data from the csv file?
You'll need to change that script if you want to keep from deleting selected records each time the script detects a new copy of the csv file.
It is a recurring import. How would I change the script so it keeps just the records associated with existing records? I don't want the recurring import to keep on appending more data everyday since that will get very large over time.
I don't really have a clear enough understanding of your database and what you need to achieve with this import. There are a variety of import options that you can explore that change the results that you get.
You can import with options that match values on one or more identifier fields and then only matching records get up updated with data from the source file. Or you can include an "add new" option and all records that don't match are added as new records.
You can import into Table A, then use relationships and scripting to compare records between Table A and Table B, selectively updating fields in Table B based on the data found in the matching records of Tables A and B.
So I suggest a more detailed description of what you want to do with these records in order for others to be better able to assist you.
I’m attaching some Screen shots. I basically have a CSV file that has names/addresses. I have a relationship setup between that table and another one with weights/heights/etc that combine into one layout.
When a new record is created I can pick the name/address from the CSV file fine and save the record. The problem comes the next day when the CSV refreshes those name/addresses disappear from the layout since their no longer in the CSV file.
So I’m trying to figure out a way to preserve the name/addresses from the CSV file when they’re attached to a record. Since only about 40% of the names/addresses are used from the CSV file if it refreshes everyday that’s not an issue. It’s just how to preserve the CSV data linked to a record that is.
I have a relationship setup between that table and another one with weights/heights/etc that combine into one layout.
Please describe that relationship in more detail. How do you link the contacts data to the measurement data?
And is your layout based on the contacts table or the measurements table?
I rechecked.. at the moment I don't have a relationship between the two tables. I have a layout that has both fields from the contacts(CSV) file and the weights/heights/etc table. But no relationship between the tables.
You may need a relationship.
But first a much more critical detail:
When you examine the rows of data in your CSV file, is there any one column of data (or several columns in combination) that uniquely identify that row?
An email address, for example, would uniquely identify each contact in your csv file.
Here's a sample of the CSV file.. Thanks for your help so far Phil!
Which doesn't answer my question. Unless there is some column in the CSV file that uniquely identifies the rows of data that you want to import, you have no way to match up the imported data to the measurements data that you want to keep while importing the contacts data.
You are correct there isn't a "row number". Since the day of the import the data is in the record is there some way of locking that record even if the underlying CSV file disappears?
Let me ask you this.. If I can’t preserve an address when the CSV file refreshes, can I have each daily CSV file append to the previous days CSV file? Then when someone selects a record from the CSV file in a layout filemaker flags that CSV record and then at the end of the day all records in the CSV file that don’t have a flag are automatically deleted?
I didn't say that you needed a "row number". Just some value in some column or split over multiple columns so that you can match up a row of data being imported to one and only one record in the table of FileMaker records so that the correct data gets matched up to the correct record in your table.
Names, ID numbers, etc are all possible values that you might be able to use for such a purpose.
The standard Import Records script step or menu option will always append records to the existing file. That's the default setting. Recurring imports first delete all records from the FileMaker table so you would just need to find and remove that script step to keep the process from repeatedly deleting all records before re-importing the data.
I think I found a solution that may work but I do have a question on defining lookups..
So it looks like I can define a relationship with a field in the CSV file back to another table’s field and auto-copy the CSV fields data. Which will eliminate the problem of the CSV file updating and losing the layout/records data from that CSV file.
Is there a way to update multiple fields in the same defining lookup?
So in the CSV file
CSV FILE Field 1: “Company Name” > Company Table: “Company Name”
CSV FILE Field 2: “Company Address” > Company Table: “Company Address”
If a layout has a field for “Company Table: Company Name” and a user selects a company name which is setup as a defining lookup back to the CSV file it’s going to copy the Company name from the CSV file to the Company Table: Company Name. Can I also in that lookup bring over the company address from the CSV file?
I don't want to do a full transfer of company names/addresses from the CSV file and that's where the defining lookup seems perfect. But I need a way on the lookup to bring both a company name and address to the other table.