{"revision":"c10276e953456835cdbb1f3a60d58ae1980be353","document":{"id":22181,"path":"integrate-applications/system-import-sets/c_ImportSetCoalesce.md","title":"Updating records using coalesce","bundle":null,"doc_type":null,"release":"australia","body":"\n# Updating records using coalesce\n\nThe coalesce option allows you to update existing target table records when transforming import data.\n\nThe coalesce option on a field map allows you to specify if the selected **Target field** should be used to coalesce on when import set records are transformed. If the field map **Coalesce** checkbox is selected, when the import set row is transformed the instance checks for an existing record in the target table that has the same value in the **Target field** as the import set row **Source field**. For step-by-step instructions, see [Create a field map](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/integrate-applications/system-import-sets/t_CreatingAFieldMap.md).\n\nIf an existing record with a matching value in the target table is found, that record is updated. If no matching record is found, then a new record is created in the target table.\n\n**Note:** Choose fields in the target table to coalesce on only if those fields will have unique values. If more than one record in the target table matches the specified coalesce options, only the first matching record in the target table is updated.\n\n## Coalesce options\n\nThere are several possible configurations you can use to coalesce data in import sets.\n\n## No coalesce\n\nIf no coalesce is defined, all imported rows are treated as new records. No existing records are updated.\n\n## Single-field coalesce\n\nYou can coalesce on a single field to update an existing record.\n\nIf a target table record exists with the same value in the coalesce field as the staging table record, the target table record is updated using the import set record values.\n\n## Multiple-field coalesce\n\nYou can coalesce on multiple fields to update an existing record.\n\nIf a target table record exists with the same values in all coalesce fields as the staging table record, the target table record is updated using the staging table record values. All coalesce field values between the target and staging tables must match to coalesce with multiple fields.\n\n## Conditional coalesce\n\nYou can use a script to determine if a staging table row should coalesce to a target record.\n\nMost conditional coalesce scripts are defined in the **Source script** field of a field map for the sys\\_id field. To update a target record using the staging table record values, the script must return the sys\\_id of the target table record.\n\n## Example conditional coalesce scripts\n\nReview examples of conditional coalesce scripts.\n\n## Updates only\n\nTo only update records where a match is found, and skip records where a match is not found, specify a coalesce field and add the following script as an **onBefore** script to the transform map.\n\n```\nif (action == 'insert') \n  ignore = true;\n```\n\n## Conditional coalesce using dot-walking\n\nYou can use dot-walked fields in a conditional coalesce script, such as to match the email address of a user when importing incident data.\n\nIn this example, this script is defined in the **Source script** of a field map for the Incident target table sys\\_id field.\n\n```\nvar gr = new GlideRecord('incident');\ngr.addQuery('caller_id.email', source.u_email); //check if the incident caller's email matches the import row email value\ngr.query();\n\nif(gr.next())\n{\n    answer = gr.sys_id; //if a match exists, return the sys_id of the matching Incident record\n}\nelse\n{\n    answer= -1;\n}\n```\n\n## Conditional coalesce with an OR condition\n\nYou can use a conditional coalesce script to match source and target records based on multiple field values. Unlike multiple-field coalesce where all coalesce fields must match, you can specify a script to only require one of the fields to match.\n\nIn this example, this script is defined in the **Source script** of a field map for the User target table sys\\_id field.\n\n```\nvar gr = new GlideRecord('sys_user');\nvar qc = gr.addQuery('email', source.u_email); //first check if the user's email matches\nqc.addOrCondition('user_name', source.u_name); //alternatively, check if the username matches\ngr.query();\n\nif(gr.next()) \n{\n    answer = gr.sys_id; //if a match exists, return the sys_id of the matching User record\n}\nelse \n{\n    answer = -1;\n}\n```\n\n## Case-sensitive coalesce field values\n\nYou can control if an import set will coalesce on matching values with different cases.\n\nThe field map **Coalesce case sensitive** field enables you to coalesce field values by case sensitivity.\n\nBy default, fields marked as **Coalesce** in the field map are used in a case insensitive lookup for existing records. If the **Coalesce case sensitive** check box is selected, the instance attempts to match coalesce field values by case. The target table record is updated only if the value in the target record and the import set staging record have the same value and case. Otherwise, a new record is created.\n\n## Coalesce on empty fields\n\nYou can control if an import set will coalesce on fields with no value.\n\nThe field map **Coalesce empty fields** field allows you to coalesce on fields with no value.\n\nBy default, fields marked as **Coalesce** in the field map cause the import set to check for a target record with a field value that matches the value from the import set staging record. When **Coalesce empty fields** is selected for that field map, an empty value in the target record and import set staging record counts as a match for the purpose of coalescing.\n\nFor example, the User transform map coalesces on the email field. With the **Coalesce empty fields** option selected, a source record containing an empty email address coalesces to a target record containing an empty email address.\n\n"}}