Transform import in two different target table

[Snowman15]

Hi all,

I’m looking to import data from an Excel file into ServiceNow using transform maps. The goal is to conditionally route records to different target tables based on the value of a specific column.

For example, if a column in the Excel file contains the value "Texas", those records should be imported into Target Table B, with all other columns mapped accordingly. Records where the column value is not "Texas" should be imported into Target Table A, also with all other columns mapped.

Please let me know the best way to achieve this within a transform map configuration.

Thank you.

[palanikumar]

You can create two Transform map for the same import set table each pointing to the respective Target table.

Add a onBefore transform script in each transform map with below code (I have added two different condition. Use the correct set of lines in specific transform map):

// Below code should be added for Table B. It will move only entries with texas

if(source.u_field_name.toString().toLowerCase().indexOf("texas") < 0){

// replace u_field_name with actual field Name from Transform Map

    ignore = true;

}

// Below code should be added for Table A. It will move only entries which does not contain texas

if(source.u_field_name.toString().toLowerCase().indexOf("texas") >= 0){

// replace u_field_name with actual field Name from Transform Map

    ignore = true;

}

[Snowman15]

Hi @palanikumar Thank you!! This actually works correctly when uploading a file in the data source. Do you know if I could automatically trigger the excel import via flow designer? So it can be automatically done via catalog item attachment? Thank you

[Ankur Bawiskar]

@Snowman15 

refer to my blog on how it can be done using workflow

Data load and transform via Catalog Item 

you can attach the file to data source and then trigger the transformation using custom flow action and then call that flow action within your flow

you can use Custom Action with script

check this link and script shared by Fabian

Usage of GlideImportSetLoader & GlideImportSetTransformerWorker API's from scoped Application

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Snowman15]

@Ankur BawiskarI did some tests and I was already able to attach the file to the data source and run the transform via flow designer. However it's only processing one transform map and not the other one. Do you have any idea? Thanks

Steps made:

1. Get attachment from cat item

2. Look up data source record

3. Move attachment to data source record

4. Then used the "Load data source file to import set" inbuilt action

5. Then used the "Transform import set" inbuilt action