Looping through an Object Array For Import

[daveguderian]

I am wanting to loop through the following array and import into SF each dependent record. Is there a way to do this without any scripting (i.e. on the import level)?

"DependentInformation": [
    {
      "FirstName": "HealthshareSpouseFirst",
      "LastName": "HealthshareSpouseLast",
      "MiddleName": null,
      "MiddleInitial": "N",
      "NamePrefix": null,
      "NameSuffix": null,
      "Relationship": "Spouse",
      "DateOfBirth": "1981-08-09",
      "Gender": "Female",
      "SocialSecurityNumber": "444-55-6645",
      "TobaccoUser": "Yes"
    },
    {
      "FirstName": "Child1First",
      "LastName": "Child1Last",
      "Relationship": "Child",
      "DateOfBirth": "2000-01-01",
      "Gender": "Male",
      "SocialSecurityNumber": "999-00-8888",
      "TobaccoUser": "No",
      "MiddleName": null,
      "MiddleInitial": "g",
      "NamePrefix": null,
      "NameSuffix": null
    },
    {
      "FirstName": "Child2First",
      "LastName": "Child2Last",
      "Relationship": "Child",
      "DateOfBirth": "2021-01-07",
      "Gender": "Male",
      "SocialSecurityNumber": "999-66-4444",
      "TobaccoUser": "No",
      "MiddleName": null,
      "MiddleInitial": "J",
      "NamePrefix": null,
      "NameSuffix": null
    },
    {
      "FirstName": "Child3First",
      "LastName": "Child3Last",
      "Relationship": "Child",
      "DateOfBirth": "2009-02-09",
      "Gender": "Female",
      "SocialSecurityNumber": "555-44-8888",
      "TobaccoUser": "No",
      "MiddleName": null,
      "MiddleInitial": "L",
      "NamePrefix": null,
      "NameSuffix": null
    },
    {
      "FirstName": "Child4First",
      "LastName": "Child4Last",
      "Relationship": "Child",
      "DateOfBirth": "2006-06-20",
      "Gender": "Male",
      "SocialSecurityNumber": "888-00-5555",
      "TobaccoUser": "Yes",
      "MiddleName": null,
      "MiddleInitial": "E",
      "NamePrefix": null,
      "NameSuffix": null
    },
    {
      "FirstName": null,
      "LastName": null,
      "Relationship": "Child",
      "DateOfBirth": null,
      "Gender": null,
      "SocialSecurityNumber": null,
      "TobaccoUser": null,
      "MiddleName": null,
      "MiddleInitial": null,
      "NamePrefix": null,
      "NameSuffix": null
    },
    {
      "FirstName": null,
      "LastName": null,
      "Relationship": "Child",
      "DateOfBirth": null,
      "Gender": null,
      "SocialSecurityNumber": null,
      "TobaccoUser": null,
      "MiddleName": null,
      "MiddleInitial": null,
      "NamePrefix": null,
      "NameSuffix": null
    },
    {
      "FirstName": null,
      "LastName": null,
      "Relationship": "Child",
      "DateOfBirth": null,
      "Gender": null,
      "SocialSecurityNumber": null,
      "TobaccoUser": null,
      "MiddleName": null,
      "MiddleInitial": null,
      "NamePrefix": null,
      "NameSuffix": null
    },
    {
      "FirstName": null,
      "LastName": null,
      "Relationship": "Child",
      "DateOfBirth": null,
      "Gender": null,
      "SocialSecurityNumber": null,
      "TobaccoUser": null,
      "MiddleName": null,
      "MiddleInitial": null,
      "NamePrefix": null,
      "NameSuffix": null
    }
  ],

Here is what I am trying (example) but I am not sure if this is going to actually create a new record for each dependent?

[tylerlamparter]

@daveguderian use the one-to-many path on the import settings. Your path would be:

DependentInformation

[daveguderian]

Hey Tyler-

So I actually tried just that and didnt have anything populate. This is my first time using the one-to-many, so likely user error here, but can you pinpoint from these screenshots what might be the issue?

Interestingly, when I select DependentInformation on the one-to-many, I no longer see that object in the input any longer. Is that to be expected?

[tylerlamparter]

@daveguderian yes the DependentInformation array "disappears" because you are setting the import to loop through it. Your context changes to what is inside the array. You'll notice that the main fields at the top level are the fields within DependentInformation and then you can access fields outside of the array with the _PARENT reference.

[daveguderian]

Thanks Tyler for the help on this. I am still working through this, but one question I did have is if I would need to set anything specifically in Celigo if for example there is only one dependent (say a spouse). The payload will still have "placeholders" for the other dependents (up to 9 total dependents), but they would obviously be null values for each dependent (as in the example above).

Do I need to do anything specifically here? I don't want a bunch of dependents getting imported with null values.

[tylerlamparter]

@daveguderian you can use an input filter. The context for input filters change when one-to-many is used.

[daveguderian]

Thanks Tyler. This worked exactly like you said. I was receiving errors on the "null" values, but once I set the input filter to only process records that had a "firstname" the other records (with "null" firstname) were ignored and didn't throw any errors.

Another question on one-to-many mappings! I am wanting to use one import step to import multiple insurance policies onto one member (assuming they have selected more than one policy for example a dental, vision, and hospital policy). The issue is that unlike the initial example in the post where each dependent was coming in as its own object, the policies come in via strings in one object similar to below:

{
  "PlanInfo": {
    "DentalPlanCode": "boomy-dent",
    "VisionPlanCode": "boomy-vis",
    "HospitalPlanCode": "hospital-2000"
  }
}

I was thinking that I could group these strings into an object array similar to what I did with dependents and use that as the path for the import, but it doesn't look like transformation 2.0 will allow that.

Whats the best way to transform these so that I can use one-to-many when importing and don't have to use multiple import steps in my flow for each insurance policy?

[tylerlamparter]

@daveguderian you can't use one-to-many with an object, so you will need to transform the data before hand to be something like this:

{
    "PlanInfo": [
        {
            "code": "boomy-dent",
            "type": "DentalPlanCode"
        },
        {
            "code": "boomy-vis",
            "type": "VisionPlanCode"
        },
        {
            "code": "hospital-2000",
            "type": "HospitalPlanCode"
        }
    ]
}

You can use transformation 2.0 to build the array or use scripts. See examples here: https://docs.celigo.com/hc/en-us/articles/12581205330587-Transformation-2-0#Complex

[daveguderian]

Tyler - I'm still a bit confused on this and how to transform my input into what you have above. Are you saying I can use Transformation 2.0 get the data into the array above to use one-to-many on the import? Perhaps I am structuring something incorrectly below?

[tylerlamparter]

@daveguderian you'll want to look at the "Create object array sourcing from multiple different places in the input." in the link I shared above. In your case here, Healthshare would be an [object] and then it will point to $.PlanInfo.DentalPlanCode,$.PlanInfo.VisionPlanCode,$.PlanInfo.HospitalPlanCode. Since you have it pointing to multiple places, it lets you then map to individual indexes within the array that you are creating.

[daveguderian]

That makes sense, but my issue (I think) is with the input type being a string and mapping to the HealthShare array. Am I doing something wrong in the mapping because I keep receiving errors indicating that the strings cant be mapped to an array.

[tylerlamparter]

@daveguderian you have to go to $.PlanInfo, $.PlanInfo, $.PlanInfo. Though I am having an issue setting the mappings to be different between each index. Working on that internally with some colleagues. In the meantime, you can also use Celigo AI with transform script.

/*
* transformFunction stub:
*
* The name of the function can be changed to anything you like.
*
* The function will be passed one 'options' argument that has the following fields:
*   'record' - object {} or array [] depending on the data source.
*   'settings' - all custom settings in scope for the transform currently running.
* The function needs to return the transformed record.
* Throwing an exception will return an error for the record.
*/ 
function transform(options) {
  const { record } = options;
  const { PlanInfo } = record;

  if (!PlanInfo) {
    throw new Error('PlanInfo is missing in the record');
  }

  const transformedPlanInfo = Object.entries(PlanInfo).map(([planType, planCode]) => ({
    planType,
    planCode,
  }));

  return {
    ...record,
    PlanInfo: transformedPlanInfo,
  };
}

Prompt:

change the PlanInfo to be an array of each plan type and its plan code

[steveklett]

Holy awkward and difficult to read JavaScript, Batman!

[tylerlamparter]

@steveklett then you can have Celigo AI describe its own code, so no need to read it yourself :).

[tylerlamparter]

@daveguderian this setup works:

[daveguderian]

That worked! Thanks so much Tyler for taking the time to figure that out! Hopefully the dialogue in this thread will help others as well if they run into issues transforming data for one to many!

Thanks again!