Data Transformation For Import

I am in the process of creating a flow to export out patients along with their active eligibility coverages (i.e. Medical, Dental, Vision, Critical Illness, Accident, etc.). I wrote a simple SOQL query to return the values I need for my CSV import for each eligibility and I am doing a transformation to attach them to the record for each patient. 

My issue is on the import, where I need to direct each eligibility (vision, dental, Critical Illness, etc.) to a specific column in my google sheet that is labeled respectively. Since all of these eligibilities are "randomly" placed in the Eligibility object (i.e. dental could be the first or fourth eligibility), whats the best way to extract and map (for example) the "Dental" eligibility to a dental column on the google sheet.

Sample JSON:

{
    "attributes": {
        "type": "IntPS__Patient__c",
        "url": "/services/data/v54.0/sobjects/IntPS__Patient__c/1111111111"
    },
    "Id": "1111111111",
    "IntPS__Last_Name__c": "Test",
    "IntPS__First_Name__c": "Test",
    "IntPS__Date_Of_Birth__c": "1800-09-20",
    "IntPS__Email__c": "test@test.com",
    "IntPS__Phone_Number__c": "(111) 111-1111",
    "IntPS__State__c": "TX",
    "Employer_ID__c": "TRIDNT",
    "Vendor_Member_Id__c": "111",
    "IntPS__Social_Security_Number__c": "111-11-1111",
    "IntPS__Street__c": "123 Test Ave",
    "IntPS__Street1__c": null,
    "IntPS__Zip_Code__c": "11111",
    "IntPS__City__c": "Test",
    "PlanMemberID": "1111111111",
    "Eligibility": [
        {
            "Name": "2878853-01 eXp Dental (09/01/23-)",
            "Begin": "2023-09-01",
            "Term": null,
            "PlanType": "Dental"
        },
        {
            "Name": "2878853-01 eXp Vision (09/01/23-)",
            "Begin": "2023-09-01",
            "Term": null,
            "PlanType": "Vision"
        },
        {
            "Name": "2878853-01 Accident 10000 (09/01/23-)",
            "Begin": "2023-09-01",
            "Term": null,
            "PlanType": "Accident"
        },
        {
            "Name": "2878853-01 Critical Illness 20000 (09/01/23-)",
            "Begin": "2023-09-01",
            "Term": null,
            "PlanType": "Critical Illness"
        },
        {
            "Name": "2878853-01 eXp Advanced ClearShare 1000 (09/01/23-)",
            "Begin": "2023-09-01",
            "Term": null,
            "PlanType": "Healthshare"
        },
        {
            "Name": "2878853-01 Hospital 2000 (09/01/23-)",
            "Begin": "2023-09-01",
            "Term": null,
            "PlanType": null
        }
    ]
}
0

Comments

2 comments
Date Votes
  • So you need one row in google sheets for each patient id and then you want a column per plan type? If so, what do you do if plan type is null?

    You could potentially use a postResponseMap script to flatten it out like this and then it's easier to use mapper. The postResponseMap script lives on your lookup step that gets eligibility.

    function postResponseMap (options) {
      
      for (let d of options.postResponseMapData) {
        if (d.Eligibility && d.Eligibility.length > 0) {
          let eligibility = {};
          for (let e of d.Eligibility) {
            eligibility[e.PlanType] = JSON.parse(JSON.stringify(e));
          }
          d.Eligibility = eligibility;
        }
      }
      
      return options.postResponseMapData;
    }
    0
  • Tyler-

    Thanks so much for this script. I think this is going to work perfectly for my use case! Thanks again!

    0

Please sign in to leave a comment.

 

Didn't find what you were looking for?

New post