Transform Maps Coalesce field

[reks]

Hi All,

I have table "X". in that we have 4 fields, Field A, Field B, Field C, Field D.

Field B is a reference field.

Here i want to make Field A and Some field in Field B referencing table as Coalesce true (unique identifier).

For example. Field B is referring to Company table and we have "Company ID " as a field in company table.

Now i want to make Field A and Company ID as unique identifier.

The Company ID field not existing in table "X", but am setting the value of Company ID in source table in Onbefore Transform script.

I made company ID and Field A as coalesce true, but its not working as expected.

Here i want to know like Service now detects that incoming record is for insert or update well before entering to Onbefore Transform script ?

Appreciate you inputs,

Thanks,

Rekha.

[Chuck Tomasi · Accepted solution]

The coalesce field(s) are used to determine whether a record is updated or inserted. All fields must have matching values in the database. In your case, the source has "Company ID", it will recognize it as a display value and use that to get the real sys_id. This is the same method it uses to import users. Active Directory, for example, doesn't have sys_ids, so it uses the 'name' from AD to match against the display value field (name) in sys_user to determine uniqueness on reference fields.

I hope that helps.

[reks]

ctomasi

Thanks a lot

[reks]

Hi ctomasi

i have made 2 fields coalesce true. Field A and Field B on Table X.

Field A is a string field.

In Table X we have a Field B which is referring to Company table. In Company table we have a Field C and it is string field.

Hence in mapping for Field B i mentioned Field C name under referenced value field name. So that users need to provide Field C value under Field B column in excel sheet.

I inserted the record with all values provided Field A and Field B since they are coalesce true.

Facing issue scenario:

Am trying to update   the record by giving just same Field A value created earlier but din't provide Field B value, as it is mandatory to provide both coalesce values for update.

Output: Updating the previously created record, not throwing error.

Issue is even if we provide the Field B value not existing or blank its getting record based on Field A and updating it, also Field B value empty in staging table.

Could you please help me out in this case.

Thanks,

Rekha

[reks]

Hi ctomasi

I have made 2 fields coalesce true, Field A and Field B.

Field A is string type

Field B is reference type.

For updating if i provide any 1 value among Field A or Field B, than it picks up the random record which satisfies that and updates record, But it should not update record.

Could you please help me out here

Thanks,

Rekha