Hello @hknokh,
I am trying to export to CSV and then load into the target org SkillRequirement records, but only for Work Types (the Related Record is a polymorphic field that also includes Job Profiles, Work Orders, etc.). Since this object is similar to a junction object and there is not any field on it that can be used for matching, I want to use the concatenation of parent Work Type code (custom field) and parent Skill developer name as the externalId. I also want to not include IDs on the CSV files because I want them on a repository.
Attempts
My first attempt was this one:
{
"allOrNone": true,
"excludeIdsFromCSVFiles": true,
"promptOnMissingParentObjects": false,
"objects": [
{
"query": "SELECT FS_Code__c FROM WorkType ORDER BY FS_Code__c",
"operation": "Readonly",
"externalId": "Code__c",
"master": false
},
{
"query": "SELECT DeveloperName FROM Skill ORDER BY DeveloperName",
"operation": "Readonly",
"externalId": "DeveloperName",
"master": false
},
{
"query": "SELECT RelatedRecordId$WorkType, SkillId, SkillLevel FROM SkillRequirement WHERE RelatedRecord.Type = 'WorkType'",
"operation": "Upsert",
"externalId": "RelatedRecord.FS_Code__c;Skill.DeveloperName"
}
]
}
But the retrieval failed with this error:
[ERROR] Error during execution of the command:
RelatedRecordId, SkillId, SkillLevel, RelatedRecord.FS_Code__c, Skill.DeveloperName
^
ERROR at Row:1:Column:50
No such column 'FS_Code__c' on entity 'Name'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.
Then I changed the external Id to be:
"externalId": "RelatedRecordId;Skill.DeveloperName"
With that, the error was gone, but then there were IDs on the CSV files for Work Types and Skill Requirements, even though the excludeIdsFromCSVFiles setting is true:
FS_Code__c,Id
Pickup,08qVc0000002rzVIAQ
Pickup(judicial),08qVc0000002s17IAA
Delivery,08qVc0000002s2jIAA
Delivery(judicial),08qVc0000002s4LIAQ
...
$$RelatedRecordId$Skill.DeveloperName,Skill.DeveloperName,SkillLevel
08qVc0000002rzVIAQ;Recogidas,Recogidas,1
08qVc0000002s17IAA;Recogidas,Recogidas,1
08qVc0000002s2jIAA;Recogidas,Recogidas,1
08qVc0000002s4LIAQ;Recogidas,Recogidas,1
...
At this point, I tried a different approach, so I used the Work Type standard name field instead of the custom code field:
{
"allOrNone": true,
"excludeIdsFromCSVFiles": true,
"promptOnMissingParentObjects": false,
"objects": [
{
"query": "SELECT Name FROM WorkType ORDER BY Name",
"operation": "Readonly",
"externalId": "Name",
"master": false
},
{
"query": "SELECT DeveloperName FROM Skill ORDER BY DeveloperName",
"operation": "Readonly",
"externalId": "DeveloperName",
"master": false
},
{
"query": "SELECT RelatedRecordId$WorkType, SkillId, SkillLevel FROM SkillRequirement WHERE RelatedRecord.Type = 'WorkType'",
"operation": "Upsert",
"externalId": "RelatedRecord.Name;Skill.DeveloperName"
}
]
}
It didn't fail, but the parent Work Type Name was not included as a column nor included in the external Id for the Skill Requirement CSV file, but it was in the Work Type CSV file:
$$RelatedRecord.Name$Skill.DeveloperName,Skill.DeveloperName,SkillLevel
Recogidas,Recogidas,1
Recogidas,Recogidas,1
Recogidas,Recogidas,1
Recogidas,Recogidas,1
...
Name
Recogida
Recogida Judicial
Entrega
Entrega Judicial
...
I tried to explicitly include the Work Type Name in the query, but I got the same result:
"query": "SELECT RelatedRecordId$WorkType, RelatedRecord.Name, SkillId, SkillLevel FROM SkillRequirement WHERE RelatedRecord.Type = 'WorkType'"
My last attempt to make this work was to not use the $WorkType notation so the object configuration looked like this:
{
"query": "SELECT RelatedRecord.Name, SkillId, SkillLevel FROM SkillRequirement WHERE RelatedRecord.Type = 'WorkType'",
"operation": "Upsert",
"externalId": "RelatedRecord.Name;Skill.DeveloperName"
}
And now the retrieval worked as expected! The Work Type Name was included as a column and in the external ID on the CSV file:
$$RelatedRecord.Name$Skill.DeveloperName,RelatedRecord.Name,Skill.DeveloperName,SkillLevel
Recogida;Recogidas,Recogida,Recogidas,1
Recogida Judicial;Recogidas,Recogida Judicial,Recogidas,1
Entrega;Recogidas,Entrega,Recogidas,1
Entrega Judicial;Recogidas,Entrega Judicial,Recogidas,1
...
However, my joy didn't last much. When I executed the command to deploy from the CSV files to the target org, the records could not be inserted due to this error (from target CSV file):
Errors,SkillId,SkillLevel
"To add, update or delete required skills, you need permission to edit the related record. Contact your Salesforce admin for help.",0C5Vc000000A2YPKA0,1
...
At first I thought it was a permissions issue, but then I realized that the RelatedRecordId column was not included. I modified the query so it was added:
"query": "SELECT RelatedRecordId, RelatedRecord.Name, SkillId, SkillLevel FROM SkillRequirement WHERE RelatedRecord.Type = 'WorkType'"
I run again the retrieval and I saw that a new CSV file was added for the Job Profile object (JobProfile.csv) that was empty. I run the load and same result, except that now the target CSV file included the RelatedRecordId column, but it was empty:
Errors,RelatedRecordId,SkillId,SkillLevel
"To add, update or delete required skills, you need permission to edit the related record. Contact your Salesforce admin for help.",,0C5Vc000000A2YPKA0,1
...
Also, there were some warnings for missing parent records and the MissingParentRecordsReport.csv file looked like this:
Date update,Lookup field name,Lookup reference field name,Missing parent External Id value,Parent ExternalId field name,Parent SObject name,Record Id,sObject name
2025-03-18 18:09:52.415,RelatedRecordId,RelatedRecord.Name,Recogida,Name,JobProfile,AV5NOZXYGZRRWFWRRM,SkillRequirement
2025-03-18 18:09:52.415,RelatedRecordId,RelatedRecord.Name,Recogida Judicial,Name,JobProfile,0GTWIQ17VKFM3IJWEQ,SkillRequirement
2025-03-18 18:09:52.415,RelatedRecordId,RelatedRecord.Name,Entrega,Name,JobProfile,TUEN3N5BQQGWURFPXK,SkillRequirement
2025-03-18 18:09:52.415,RelatedRecordId,RelatedRecord.Name,Entrega Judicial,Name,JobProfile,FTTNMC5DAQ5ISMEUKO,SkillRequirement
...
However, there were some existing records and it was able to identify them and put them on the update target file, without errors (but the RelatedRecordId field was also missing):
Errors,Id,SkillId
,0HxVc000000SFnJKAW,0C5Vc000000A1HNKA0
,0HxVc000000SFiTKAW,0C5Vc000000A1HNKA0
,0HxVc000000SFlhKAG,0C5Vc000000A1HNKA0
,0HxVc000000SFk5KAG,0C5Vc000000A1HNKA0
...
Conclusions
- When the polymorphic notation is used, the parent fields for the external Id don't seem to work
- When it's not used:
- Object-specific fields raise errors
- The external Id is correctly set
- The matching on the target org with existing records work
- The parent references are not filled for the target files
I need some guidance and help here, please. Thank you!
Hello @hknokh,
I am trying to export to CSV and then load into the target org
SkillRequirementrecords, but only for Work Types (the Related Record is a polymorphic field that also includes Job Profiles, Work Orders, etc.). Since this object is similar to a junction object and there is not any field on it that can be used for matching, I want to use the concatenation of parent Work Type code (custom field) and parent Skill developer name as theexternalId. I also want to not include IDs on the CSV files because I want them on a repository.Attempts
My first attempt was this one:
But the retrieval failed with this error:
Then I changed the external Id to be:
With that, the error was gone, but then there were IDs on the CSV files for Work Types and Skill Requirements, even though the
excludeIdsFromCSVFilessetting is true:At this point, I tried a different approach, so I used the Work Type standard name field instead of the custom code field:
It didn't fail, but the parent Work Type Name was not included as a column nor included in the external Id for the Skill Requirement CSV file, but it was in the Work Type CSV file:
I tried to explicitly include the Work Type Name in the query, but I got the same result:
My last attempt to make this work was to not use the
$WorkTypenotation so the object configuration looked like this:And now the retrieval worked as expected! The Work Type Name was included as a column and in the external ID on the CSV file:
However, my joy didn't last much. When I executed the command to deploy from the CSV files to the target org, the records could not be inserted due to this error (from target CSV file):
At first I thought it was a permissions issue, but then I realized that the
RelatedRecordIdcolumn was not included. I modified the query so it was added:I run again the retrieval and I saw that a new CSV file was added for the Job Profile object (
JobProfile.csv) that was empty. I run the load and same result, except that now the target CSV file included the RelatedRecordId column, but it was empty:Also, there were some warnings for missing parent records and the
MissingParentRecordsReport.csvfile looked like this:However, there were some existing records and it was able to identify them and put them on the update target file, without errors (but the
RelatedRecordIdfield was also missing):Conclusions
I need some guidance and help here, please. Thank you!