Importing new rows using Transform Map, but trying to ignore duplicates and updates

[AndyMaier]

4/18/14 ... I have updated my question because I misphrased it.   I created an Import Set and Transform Map trying to import new rows from an excel spreadsheet to a Security Access Table.   My goal is to only import new rows and not do any updates to existing rows.   I can get rows to insert, but I am having   problems with my onBefore script trying to check and skip/ignore duplicate rows.   If I find a match to the Target table records for u_name & u_func_access_display, I want to skip the record and not insert.  

This is my onBefore script:

//CHECK TO SEE IF RECORD ALREADY EXISTS IN THE LLB SECURITY ACCESS TABLE

//COMPARE WITH NAME AND FUNC ACCESS DISPLAY FIELDS

    var ast = new GlideRecord('u_llb_security_access');

    ast.addQuery('u_name',source.u_name);

    ast.addQuery('u_func_access_display',source.u_func_access_display);

    ast.query();

//IF RECORD ALREADY EXISTS SKIP THE RECORD AND GO TO THE NEXT

    if(ast.next()){

            ignore = true;

  }

My onBefore script does not skip/ignore any duplicate records.   Any ideas on what I am doing wrong?

Thanks,

Andy

[James_Neale]

Hi Andy,

What you are doing here is essentially the same as setting coalesce true on u_name and u_func_access_display.

I think there must be something else going on here? Did you check the data in the spreadsheet you are importing? Do you get duplicates EVERY time you run the import with a field that is coalescing and have you tried it with one field, say u_name, set to coalesce = true?

What happens if you manually created a record in your import set table and coalesce on name?

Another tip for you is to start your import table names with "Imp" or something similar. This makes it a lot easier when looking at dictionary records or tables and columns, and makes it immediately clear it is an import table. E.g. "Imp LLB Sec Access".

Unfortunately, there isn't a lot else to go on really without looking at it directly.

- James

[AndyMaier]

Hi James,

After doing more testing I realized I misphrase my question and needed to repost my question.   My goal is to import new rows into the LLB Security Access table, but I need to skip/ignore duplicate rows based on match a record with the same u_name and u_func_access_display field values.   Also, I need to prevent updates to any rows on the LLB Security Access table.

Thanks again for your help,

Andy

[DrewW]

In your column mapping set those columns to coalesce = true and then in an onBefore script do this

if(action != "insert")

        ignore= true;

[AndyMaier]

Hi Drew,

I made the change, but it did not completely work.   2 rows that were already in the LLB Security Access table get ignored ... Row transform ignored by onBefore script, but I use rows that I just inserted that last time get inserted again.

Thanks,

Andy