Update field values based on a field via import file

PoorvaB
Tera Contributor

Hi Community,

 

I have an import file which contains employee number and 2 other fields. Based on employee number, i need to update 2 other fields values. 

 

What is the best approach to update those fields based on employee number? Do i need to use import sets or background script to do this?

 

What exact steps i need to follow for this?

 

Thanks,

Poorva Bhawsar

1 ACCEPTED SOLUTION

Debasis Pati
Tera Guru

Hello @PoorvaB ,

  • Create an Import Set Table:

    • Navigate to System Import Sets > Load Data.

    • Create a new Import Set Table and upload your file containing the employee number and the two other fields.

  • Create a Transform Map:

    • Navigate to System Import Sets > Administration > Transform Maps.

    • Create a new Transform Map and map the fields from your Import Set Table to the target table (e.g., sys_user if you're updating user records).

    • Ensure you map the employee number field to the corresponding field in the target table.

  • Define Field Mapping:

    • In the Transform Map, define the field mappings for the two fields you want to update based on the employee number.

    • Use the Coalesce field to identify the unique record. Set the employee number field as the coalesce field. This ensures that the existing records are updated rather than creating new ones.

  • Script for Field Update:

    • If you need to perform additional logic or transformations, you can use a Transform Script. For example:

(function transformRow(source, target, map, log, isUpdate) {
// Update the target fields based on the source fields
target.field1 = source.field1;
target.field2 = source.field2;
})(source, target, map, log, isUpdate);


 

  • Run the Transform:

    • Navigate to System Import Sets > Load Data.

    • Select your Import Set Table and click on Transform.

    • Choose the Transform Map you created and run the transform.

  • Verify the Data:

    • After the transform is complete, verify that the fields have been updated correctly in the target table.

      Please Mark it as correct/helpful if this resolves your query.

      Regards,
      Debasis

 

 

View solution in original post

18 REPLIES 18

Debasis Pati
Tera Guru

Hello @PoorvaB ,

  • Create an Import Set Table:

    • Navigate to System Import Sets > Load Data.

    • Create a new Import Set Table and upload your file containing the employee number and the two other fields.

  • Create a Transform Map:

    • Navigate to System Import Sets > Administration > Transform Maps.

    • Create a new Transform Map and map the fields from your Import Set Table to the target table (e.g., sys_user if you're updating user records).

    • Ensure you map the employee number field to the corresponding field in the target table.

  • Define Field Mapping:

    • In the Transform Map, define the field mappings for the two fields you want to update based on the employee number.

    • Use the Coalesce field to identify the unique record. Set the employee number field as the coalesce field. This ensures that the existing records are updated rather than creating new ones.

  • Script for Field Update:

    • If you need to perform additional logic or transformations, you can use a Transform Script. For example:

(function transformRow(source, target, map, log, isUpdate) {
// Update the target fields based on the source fields
target.field1 = source.field1;
target.field2 = source.field2;
})(source, target, map, log, isUpdate);


 

  • Run the Transform:

    • Navigate to System Import Sets > Load Data.

    • Select your Import Set Table and click on Transform.

    • Choose the Transform Map you created and run the transform.

  • Verify the Data:

    • After the transform is complete, verify that the fields have been updated correctly in the target table.

      Please Mark it as correct/helpful if this resolves your query.

      Regards,
      Debasis

 

 

Yeah thats what i am thinking. After importing the data, can we run a background script for this?

No need of background script you can write the script on transform map.

DebasisPati_0-1740551784270.png


you can change the when depending on your requirement on start /on complete etc.

No need of any background script.
In your transform map you can create a transform map script.
i think on complete transform map will help you.
Sample Transform map script:
(function transformRow(source, target, map, log, isUpdate) {
// Update the target fields based on the source fields
target.field1 = source.field1;
target.field2 = source.field2;
})(source, target, map, log, isUpdate);

Please mark it as correct if this resolves your query.

Regards,
Debasis


I am not sure i am confused which transform map script i have to use.