Import--updating matching records, want to skip fields

[Archit3kt]

Hey guys, I'm trying to import contacts/customers from an excel spreadsheet, and I want it to ONLY add new records and SKIP matching records. This seems simple enough, but it seems the import options allow everything BUT that....

So instead, I thought I would settle with updating matching records as long as I don't lose data. This may actually be preferable since peoples' email addresses may have changed, etc. However, the source file is like swiss cheese, and I want it to skip empty fields when updating a record, but still update the rest of the fields in a record. Now I'm seeing that in Validation options, the "not empty" validation is THE ONLY TYPE that causes the import to skip an entire record!!! This is so frustrating.

I'm close to just letting it skip records that find empty fields and calling it a day, but do you guys know how to perform an import either of these two ways??

1) import while skipping matching records

2) update matching records, skipping empty fields BUT updating the rest of that record

thanks very much, everybody

[dansmith65]

I don't know of a way to do what you're asking via the Import process. A work-around would be to import records to a separate table, then do the comparison/update with a script and a relationship.

The only other suggestion I have is to re-evaluate your workflow. If, for example, users were able to edit the database directly rather than editing an Excel file, this may not be an issue.

[Archit3kt]

workflow is a good point. if what I "need" to do is difficult, maybe re-evaluate why i got to the point that I think I need that in the first place.

But I've put myself in this place for now, and I need a short-term bandaid before re-evaluating the entire workflow. I like the idea of importing to a separate table, then it's inside FMP and i can run all sorts of scripts on the data. heck yeah! I'll start giving that a shot tomorrow morning, any script suggestions are appreciated in the meantime! to search for matching records with some logic loop, and then go to each field and evaluate if empty or not, and update, etc.

[dansmith65]

I would run the looping script from the context of the newly imported records, and create a relationship on the primary key field to the main table.

If[ IsValid(main::primarykey) ]

    // imported record already existed, compare each field to determine if it's value should be copied to the 'main' table

    // this will either take lots of 'If' statements, or some trickery using abstraction methods to 

       // compare fields without hard-coding each field name in an if statement

Else

    // imported record is new; copy all fields to the 'main' table as a new record

End If

[LaRetta]

  On 1/11/2012 at 1:17 AM, Archit3kt said:

I want it to ONLY add new records and SKIP matching records. Now I'm seeing that in Validation options, the "not empty" validation is THE ONLY TYPE that causes the import to skip an entire record!!!

Hi Archit3kt,

Actually, validation 'unique' on the key will also prohibit new records from importing if validation fails. But I suggest that you:

1. Import into a temp importing table as suggested and create a relationship between the two keys.
   
2. Run *looping script to check each field. You must decide also what to do if values are different. The script in this demo can be easily revised if you wish to also update different values.
   
3. Once the comparison is done and you have updated the data records then add any new records by using import based upon unique ID validation (which will only import new records).
   

Here is a file which does what you want. It also displays the data fields in yellow if they have a related 'importing table' field with data. No **hard-coding of field names is required either with the looping script or with the conditional formatting. In this sample, only record ID 4 (in the Importing Table) will be added.

* all fields to compare must be placed on the 'layout to loop' for it to work

** the word "Importing table" is 'hard-coded' in the conditional format so Self knows where to look for its counterpart.

If you do not wish to loop, just create the relationship and then attach the same conditional format (select all data fields at once) like in this demo and you will quickly see any data fields which are empty but have a value in importing table.

Only caveat is that the field names must match between the tables but it is easy to change the field names in the temp table so they match.

Comparative Import.zip

[dansmith65]

Nice demo file LaRetta. You went to the trouble of creating what I only hinted at.

The only thing I would change is to not hard-code the import table name. I modified it to use a global variable instead. In a real-life scenario, the global variable would probably be set by a script after performing the import, but since this demo file does not have that script, I set it via an object with conditional formatting on the import layout. The idea being; the user goes to the import layout to start this process, then the variable is automatically set.

So, to make this modified demo work, go to the import layout first.

Comparative Import v2.zip

[LaRetta]

You are right ... the hard-code was only in conditional formatting but I removed it. I do not however agree that jumping to the other table is the way to handle it because, in a way, that is still hard-coding it within the script and it would not work for simply displaying through conditional format if script wasn't run.

The User should specify the table name (either via global field or custom dialog) so conditional formatting will work as well and I have modified my file appropriately.

Comparative ImportV3.zip

[LaRetta]

  On 1/11/2012 at 8:10 PM, dansmith65 said:

In a real-life scenario, the global variable would probably be set by a script after performing the import, but since this demo file does not have that script...

Hi Dan,

My demo does indeed have the import but it only needs to import NEW records and not all of them and that import takes place last so that the looping script does not have to loop through newly added records. :^)

[dansmith65]

The part I was trying to avoid was accessing a Table Occurrence by text name; which is why I used Get(LayoutTableName) to set the global variable. I avoided hard-coding the text string "Import Table" into the database (or saving it in a field). I always avoid referring to an element by text name whenever possible, to prevent breaking something if the element is re-named. It is also difficult to trace references to an element that is accessed by text name (I use BaseElements to analyze a DDR).

Regarding Importing...

I like how you used an import within the same file rather than manually adding records one-at-a time like I hinted to in my sample looping script above.

  On 1/11/2012 at 8:10 PM, dansmith65 said:

In a real-life scenario, the global variable would probably be set by a script after performing the import, but since this demo file does not have that script...

What I was referring to here is the importing of the Excel file into the "Importing table" in the database.

[LaRetta]

I do not like hard-coding either but I saw the data comparison also between several different tables (or files) and, by allowing User to specify, script would not need to be modified at all because it would be handled in browse mode by the User. I work with a lot of data migrations, text anomolies and twisted data and the abiity to compare several 'idential' tables' data (even just viewing any highlighted 'differences') saves a lot of work. I use BaseElements as well; it is a powerful tool!

Great discussion, Dan. :laugh2:

[dansmith65]

  On 1/11/2012 at 10:34 PM, LaRetta said:

I saw the data comparison also between several different tables (or files)

That's a great argument for using the global field to store the table name! I'd never thought about that. I was thinking more along the lines of building it for a single repetitive task.