[QUESTION] - Text-Field with Profile Id

[Schuchie]

Hey! I love using sfdmu :) We are using it quiet often and hardly integrated in our CI-Pipeline. We are using most of the time the csv to org feature. Most of the time I find a good solution by myself but now I need to transform an column in one of my object-exports. Somehow the thirdparty-app has a column with profile-ids included. These profile ids in the text-column needs to be replaced with the profile id from the target org. I firstly thought about a value mapping, which replaces the profile ids with the profile names and then try to use the record transformation feature https://help.sfdmu.com/examples/record-transformation .

Any better way? I mean the same behaviour as for recordtypes would be nice :D

[hknokh]

Hello, @Schuchie

Thank you for reaching out. I will do my best to assist you as quickly as possible and will keep you updated on my progress.

Cheers

[hknokh2]

Hello, I'm glad to hear that SFDMU is working well for you. Regarding your question, value mapping isn't ideal because it only uses source values. The best solution would be to use the Records Transform Core Add-On Module: Records Transform Core Add-On Module. However, I'm not certain you can achieve your goal. I understand you want to replace the source ID with the target ID in the same field, regardless of the object. If you only have the source ID, you can't select the corresponding target record by this ID, as there is no external ID to link records on both sides. Without a matching record on the target side, you can't use its value for replacement.

[Schuchie]

Hey @hknokh, thx for the answer. Is there a way to do an transformation on the pull of the records? So replace the profile ids with profile names on pull of the source org? And ist the value mapping executed before the records transform starts?

[hknokh2]

You don't need to worry about transforming records on pull; it won’t be helpful in this case. Instead, focus on ensuring that you have the correct external ID defined for the profile and that this column is populated in the CSV. If your source CSV includes a profile name, you can specify the name field as the external ID. This will match the profiles with IDs from the target organization. You can then use this for transformation using the Records Transform Add-On Module. Just make sure there is a field with unique and matching values on both sides (in the CSV and the target organization).

[Schuchie]

But when I would prepare a value mapping csv, which contains the Id to Profile Name Mapping, would that be replaced in the csv via value mapping before the Records Transform Add-On Module runs?

[Schuchie]

I've found the part in the documentation:

[Schuchie]

And how would an example config look like for "You can then use this for transformation using the Records Transform Add-On Module. Just make sure there is a field with unique and matching values on both sides (in the CSV and the target organization)." If my column has the profile names. In the examples from the documentation, I cannot find an query example

[Schuchie]

@hknokh Idea would be todo something like the following to set already at the export the profile name instead of the profile-id. But somehow my column clm__OrgProfileId__c gets emptied and I got no error. And one site note: the field clm__OrgProfileId__c is not a lookup. Its just a text-field

{
    "objects": [
        {
            "query": "SELECT Id, Name FROM Profile",
            "operation": "Read"
        },
        {
            "query": "SELECT Id, Name, clm__OrgProfileId__c FROM clm__CTMSettings__c",
            "operation": "Upsert",
            "master": true,
            "externalId": "Name"
        }
    ],
    "dataRetrievedAddons": [
        {
            "module": "core:RecordsTransform",
            "args": {
                "fields": [
                    {
                        "alias": "profileName",
                        "sourceObject": "Profile",
                        "sourceField": "Name",
                        "lookupExpression": "source.clm__OrgProfileId__c == target.Id"
                    }
                ],
                "transformations": [
                    {
                        "targetObject": "clm__CTMSettings__c",
                        "targetField": "clm__OrgProfileId__c",
                        "formula": "formula.profileName"
                    }
                ]
            }
        }
    ]
}

[hknokh2]

Unfortunately, your transformation is not possible using SFDMU. The process requires two separate field configurations, each performing its own lookup search. First, you need to configure a field to find the source Profile Name using the Profile Id provided in the clmOrgProfileIdc field. Then, you need to configure another field to locate the target Profile Id based on that Profile Name. However, SFDMU does not support reusing the result of one lookup search in another. Each lookup generates its own value, and the 'formula' expression in the 'transformations' setting combines them when updating the target record.

[hknokh2]

In other hand, I can recommend you using the following configuration, which leverages an undocumented (unofficial) SFDMU feature that allows the use of a "formula" in the field definition to accomplish your task:

{
    "objects": [
        {
            "query": "SELECT Id, Name FROM Profile",
            "externalId": "Name"
        },
        {
            "query": "SELECT Id, Name, Profile_Id__c FROM Profile_Settings__c",
            "operation": "Upsert",
            "beforeUpdateAddons": [
                {
                    "module": "core:RecordsTransform",
                    "description": "Updates Profile_Id__c with target ProfileId",
                    "args": {
                        "fields": [
                            {
                                "alias": "sourceProfileName",
                                "sourceObject": "Profile",
                                "sourceField": "Name",
                                "lookupExpression": "source.Id== target.Profile_Id__c",
                                "lookupSource": "source",
                                "includeFields": []
                            },
                            {
                                "alias": "targetProfileId",
                                "sourceObject": "Profile",
                                "sourceField": "Id",
                                "lookupExpression": "formula.sourceProfileName == source.Name",
                                "lookupSource": "target"
                            }
                        ],
                        "transformations": [
                            {
                                "targetObject": "Profile_Settings__c",
                                "targetField": "Profile_Id__c",
                                "formula": "formula.targetProfileId"
                            }
                        ]
                    }
                }
            ],
            "externalId": "Name"
        }
    ]
}

My configuration uses two files (just for example purposes):

Profile.csv (contains source profiles)

Id,Name
00e8d0000023PtgAAE,SDO-Service
00e8d0000023PthAAE,SDO-Sales
00e8d0000023PtiAAE,SDO-Partners
00e8d0000023PuUAAU,Marketing User
00e8d0000023PuVAAU,Contract Manager

Make sure you have profiles with the same names in the both orgs.

Profile_Settings__c.csv

Id,Name,Profile_Id__c
a1NJ7000000S00wMAC,Profile - SDO-Service,00e8d0000023PtgAAE
a1NJ7000000S00xMAC,Profile - SDO-Sales,00e8d0000023PthAAE
a1NJ7000000S00yMAC,Profile - SDO-Partners,00e8d0000023PtiAAE
a1NJ7000000S00zMAC,Profile - Marketing User,00e8d0000023PuUAAU
a1NJ7000000S010MAC,Profile - Contract Manager,00e8d0000023PuVAAU

• The Profile_Settings__c.csv file contains source profile IDs, which will be replaced with the corresponding target profile IDs during the transformation.
• The Profile_Id__c field holds the source profile IDs and will be transformed into target profile IDs before the update.
• The Name column is included and necessary for update the target, since it's defined as the externalId field, but is not necessary for the value transformation. Profile_Settings__c Name values are not the same as Profile names, the search made using record IDs.

To ensure this works, please update SFDMU to the latest version, as I applied some fixes to the Add-On engine.

[Schuchie]

Hey @hknokh , I found for both my problems a solution, thanks to your guidance:

{
    "objects": [
        {
            "query": "SELECT Id, Name, clm__OrgProfileId__c FROM clm__CTMSettings__c",
            "operation": "Upsert",
            "master": true,
            "externalId": "Name",
            "beforeUpdateAddons": [
                {
                    "module": "core:RecordsTransform",
                    "description": "Updates clm__OrgProfileId__c with target Profile-Id",
                    "args": {
                        "fields": [
                            {
                                "alias": "sourceProfileName",
                                "sourceObject": "clm__CTMSettings__c",
                                "sourceField": "clm__OrgProfileId__c"
                            },
                            {
                                "alias": "targetProfileId",
                                "sourceObject": "Profile",
                                "sourceField": "Id",
                                "lookupExpression": "formula.sourceProfileName == source.Name",
                                "lookupSource": "target"
                            }
                        ],
                        "transformations": [
                            {
                                "targetObject": "clm__CTMSettings__c",
                                "targetField": "clm__OrgProfileId__c",
                                "formula": "formula.targetProfileId || formula.sourceProfileName"
                            }
                        ]
                    }
                }
            ]
        }
    ],
    "dataRetrievedAddons": [
        {
            "module": "core:RecordsTransform",
            "description": "Updates clm__OrgProfileId__c with target Profile-Name",
            "args": {
                "fields": [
                    {
                        "alias": "sourceProfileName",
                        "sourceObject": "Profile",
                        "sourceField": "Name",
                        "lookupExpression": "source.Id == target.clm__OrgProfileId__c",
                        "lookupSource": "source",
                        "includeFields": []
                    },
                    {
                        "alias": "defaultValue",
                        "sourceObject": "clm__CTMSettings__c",
                        "sourceField": "clm__OrgProfileId__c"
                    }
                ],
                "transformations": [
                    {
                        "targetObject": "clm__CTMSettings__c",
                        "targetField": "clm__OrgProfileId__c",
                        "formula": "formula.sourceProfileName || formula.defaultValue"
                    }
                ]
            }
        }
    ]
}

That thing sets the profile name on retrieve to csv and on upsert in the org the script replaces the profile names with the correct ids!

[hknokh]

Cool! I understand your idea I will document the 'formula' usage as it looks good.