1 2 Previous Next 15 Replies Latest reply on Jul 16, 2014 8:20 AM by philmodjunk

    Auto Import data and preserving Data

    MattW_1

      Title

      Auto Import data and preserving Data

      Post

           I have a CSV file that I have auto-imported everyday into a database. The data in the CSV file changes daily. The problem I have is when we use the CSV file records in an existing layout that data disappears the next CSV refresh.

           I like that the data does a complete refresh since we don’t always use all the data in the CSV file. So having the refresh keeps the list short. My question is how can I preserve the records we do use from the CSV file, before it refreshes? 

            

           Thanks!

        • 1. Re: Auto Import data and preserving Data
          philmodjunk

               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.

          • 2. Re: Auto Import data and preserving Data
            MattW_1

                 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.

            • 3. Re: Auto Import data and preserving Data
              philmodjunk

                   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.

              • 4. Re: Auto Import data and preserving Data
                MattW_1

                     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.

                     Any Ideas?

                • 5. Re: Auto Import data and preserving Data
                  philmodjunk

                       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?

                  • 6. Re: Auto Import data and preserving Data
                    MattW_1

                         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.

                    • 7. Re: Auto Import data and preserving Data
                      philmodjunk

                           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.

                      • 8. Re: Auto Import data and preserving Data
                        MattW_1

                             Here's a sample of the CSV file.. Thanks for your help so far Phil!

                        • 9. Re: Auto Import data and preserving Data
                          philmodjunk

                               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.

                          • 10. Re: Auto Import data and preserving Data
                            MattW_1

                                 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?

                                  

                                  

                            • 11. Re: Auto Import data and preserving Data
                              MattW_1

                                   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?

                              • 12. Re: Auto Import data and preserving Data
                                philmodjunk

                                     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.

                                • 13. Re: Auto Import data and preserving Data
                                  philmodjunk

                                       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.

                                  • 14. Re: Auto Import data and preserving Data
                                    MattW_1

                                         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?

                                         Recap:

                                         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.

                                         Thanks!

                                    1 2 Previous Next