How do I identify what field(s) should be the coalesce when migrating a table?

[Stephan_P4M]

I want to migrate a number of tables from one instance to another. When looking at the data, I can make an educated guess what field or fields should be the coalesce. However, how do I make sure? When I look in configure > table, but do not see any identifier that shows me a particular field or set of fields need to have a unique value. E.g. it might be just the "Number" field, or might be the "Name" field, or a combination of both. But how do I find out without manually checking if there are any duplicates in any of these fields?

[Tony Chatfield1]

Hi, coalesce is an import\transform concept and records within an instance have the sys_id as a unique identifier, so it is possible to create cosmetic 'duplicates' in most tables.
If you are importing data from 1 instance to another you will need to evaluate the data from each table (or table type) and decide what can\can't be mapped. If I was importing numbered records eg Incidents from 1 instance I would expect that there would be duplicate numbers unless the initial design\development of both instances allowed for unique numbering in number maintenance, but based on your post I suspect this is unlikely.
So you have 2 options,
Renumbering during import, which may cause confusion if users are referencing original numbers.

Or adding an additional identifier during import to ensure the numbering is not duplicated, but that your users can easily identify 'numbers' from the other platform. IE if numbering is INC0000001, then add another digit during import so the imported duplicate becomes INC9000001, users can still search on 000001 and will get both results with the additional digit making the original source clear.

For other records with unique 'cosmetic' identifiers you will need to follow similar evaluation process;
Do you import as is, rename or merge records...

Note if merging reference data like User, Group, Location etc or using records from the target instance, your import would also need to update reference fields like assignee, assignment group, location,  etc.

[Stephan_P4M]

Thanks for explaining. That in itself is helpful information that I'll keep in mind while working at it, thanks!

However, this would only be for system generated numbers for example. What if it has a coalesce on another field, like Name. Will I be able to find out somehow by looking at the source table?

[Tony Chatfield1]

If you are coalescing against a field then the platform will match the record you are processing to existing records, when your source record is being transformed. If you have duplicates that you want to keep IE 'names', then you will not be able to coalesce against the field involved, unless you use a field mapping script to make the field value unique for the coalesce evaluation, and then manipulate the field back to original value using a before transform script.