SnapLogic - Integration Nation

Deepti · ‎11-15-2023

Hi there,

I have one input json like as follows:

{
    "isValid": true,
    "logId": "9IE9BkOwxvrCwn_Ot7p4o-1699858392",
    "contractId": 1,
    "erpVendorId": "VID123",
    "erpVendorName": null,
    "erpVendorLocationId": "VSID789",
    "vendor": [
      {
        "Id": "2432",
        "id": 2432,
        "DBNo": "121212",
        "Name": "",
        "Website": "",
        "hubPath": "",
        "Instance": "331",
        "LegalName": "",
        "LongLabel": "",
        "SourceApp": "kahua_CompanyManager",
        "entityDef": "kahua_CompanyManager.kahua_Company",
        "InstanceId": "11501463",
        "ShortLabel": "",
        "DetailLabel": "Company: ",
        "IsPublished": "True",
        "PartitionId": "0",
        "AppNameLabel": "[AppLabel]",
        "CompanyEmail": "",
        "GovernmentId": "1234",
        "VendorNumber": "1234",
        "MarkupEnabled": "False",
        "IsPrequalified": "False",
        "CreatedDateTime": "\"2018-02-26T11:54:51.153\"",
        "IsDomainCompany": "True",
        "CompanyEntryType": "Construction",
        "IsExchangeEntity": "False",
        "ModifiedDateTime": "\"2021-07-01T15:12:16.71\"",
        "DomainPartitionId": "0",
        "IsPreferredVendor": "False",
        "outwardReferences": [

        ],
        "PortableViewInstance": "331",
        "IsInSyncWithCommunity": "False"
      }
    ],
    "description": "PO3",
    "createdDt": "2023-11-09T16:34:41.065Z",
    "sourceDocumentDate": null,
    "sourceStartDate": null,
    "sourceFinalCompletionDate": null,
    "shipToCode": "33857616",
    "sourceContractId": 34372838,
    "currencyCode": "USD",
    "sourceNumber": "KPO000000003",
    "totalAmount": "0",
    "operatingModel": "MP",
    "billingScope": "16",
    "projectId": 2,
    "project": {
      "projectId": 2,
      "clientCode": "LOCAL",
      "sourceDomain": "",
      "currencyCode": null,
      "erpProjectId": "ERPPOIO78945",
      "billingScope": "16 - Major Project Cost Plus EXP",
      "projectErpDetails": {
        "glBu": "12090",
        "budRefId": 89965
      },
      "location": {
        "financeId": "33857616",
        "country": "US"
      }
    },
    "requestorId": "TMacon",
    "targetCloseDate": "2023-12-31T23:59:59.999Z",
    "message": "This PO request has been successfully submitted to myBuy",
    "items": [
      {
        "description": "Architecture Fees",
        "quantity": null,
        "unitPrice": "1000",
        "total": "1000",
        "sourceId": "34372839",
        "sourceNumber": 1,
        "scopeOfWork": null,
        "projectActivity": {
          "chartCodes": [
            {
              "chartCode": {
                "uniqueCode": "7845",
                "supplementalType": "BOMA"
              }
            }
          ]
        },
        "type": "Item"
      },
      {
        "description": "02 Site Utilities",
        "quantity": null,
        "unitPrice": "1000",
        "total": "1000",
        "sourceId": "34372840",
        "sourceNumber": 2,
        "scopeOfWork": null,
        "projectActivity": {
          "chartCodes": [

          ]
        },
        "type": "Item"
      }
    ]
  }

and the desired output is as follows:

{
   "order": {
      "messageID": "9IE9BkOwxvrCwn_Ot7p4o-1699858392",
      "source": "",
      "entity": "US",
      "client": "LOCAL",
      "orderId": 34372838,
      "orderNumber": "KPO000000003",
      "orderName": "PO3",
      "version": 1,
      "orderType": "Requisition",
      "externalOrderReferenceNumber": "1",
      "externalOrderReferenceType": "PJM contract ID",
      "relatedOrderReferences": {
         "relatedOrderReference": {
            "relatedOrderNumber": "",
            "relatedOrderSystem": "",
            "relatedOrderType": ""
         }
      },
      "buyerCustomerParty": {
         "buyerId": "",
         "buyerCustomerExternalId": "",
         "buyerCustomerExternalReferenceTypeId": "",
         "buyerCustomerExternalReferenceTypeName": ""
      },
      "sellerSupplierParty": {
         "sellerID": "12090-VID123",
         "sellerSupplierExternalId": "",
         "sellerSupplierExternalReferenceTypeId": "",
         "sellerSupplierExternalReferenceTypeName": "",
         "sellerSupplierName": null,
         "sellerSupplierSiteId": "VSID789"
      },
      "delivery": {
         "deliveryLocationId": "917",
         "deliveryLocationDescription": "",
         "deliveryStartDate": "",
         "deliveryEndDate": "",
         "deliveryAttention": ""
      },
      "orderCreatedDate": null,
      "orderAmount": "0",
      "commercialModel": "MP",
      "currencyCode": {
         "code": "USD",
         "description": ""
      },
      "exchangeRates": {
         "exchangeRateDate": "",
         "baseTotalCost": "",
         "exchangeRate1": "",
         "exchangeRate2": "",
         "baseCurrencyCode": ""
      },
      "orderStatus": {
         "code": "Open",
         "description": ""
      },
      "paymentTerms": "",
      "shippingTerm": "",
      "requestedBy": {
         "contactName": "",
         "contactId": "US/TMacon"
      },
      "preparedBy": {
         "contactName": "",
         "contactId": ""
      },
      "comments": "",
      "orderLineItems": {
         "orderLineItem": [
            {
               "originatingOrderLineID": 1,
               "relatedOrderLineReference": {
                  "relatedOrderNumber": "",
                  "relatedOrderLineNumber": "",
                  "relatedOrderSystem": "",
                  "relatedOrderType": ""
               },
               "lineItemName": "Architecture Fees",
               "lineItemType": "Item",
               "lineItemNumber": "34372839",
               "lineClosedDate": "",
               "lineItemDetails": {
                  "modelName": "",
                  "manufacturer": "",
                  "sellerItemCatalogId": "",
                  "sellerItemId": "",
                  "commodityGroupCode": "",
                  "commodityClassificationCode": "",
                  "commodityDescription": ""
               },
               "locationId": "33857616",
               "buildingName": "",
               "lineDeliveryDetails": {
                  "deliveryLocationId": "33857616",
                  "deliveryLocationDescription": "",
                  "deliveryStartDate": null,
                  "deliveryEndDate": null,
                  "deliveryAttention": ""
               },
               "quantity": null,
               "quantityReceived": "",
               "quantityInvoiced": "",
               "quantityRemaining": "",
               "unitCost": "1000",
               "baseUnitCost": "",
               "receivedUnitCost": "",
               "lineItemAmount": "1000",
               "lineItemBaseAmount": "",
               "unitofMeasure": {
                  "code": "EACH",
                  "description": ""
               },
               "currencyCode": {
                  "code": "USD",
                  "description": ""
               },
               "lineItemDescription": "Architecture Fees",
               "lineItemStatus": {
                  "code": "",
                  "description": ""
               },
               "termsConditions": "",
               "lineItemComments": null,
               "contractDetails": {
                  "contractNumber": "KPO000000003",
                  "contractScope": "",
                  "activity": "20_7845_9999",
                  "billType": ""
               },
               "glAccountCode": {
                  "glAccountCode": null,
                  "bomaCode": 7845
               },
               "additionalCoding": {
                  "originatingProjectNumber": "KPO000000003",
                  "workOrderNumber": 89965,
                  "workOrderDetails": {
                     "workOrderClass": "",
                     "workOrderType": "",
                     "workOrderTypeDescr": "",
                     "workOrderDescription": "",
                     "woAgreementType": "",
                     "woContractNumber": "",
                     "woBillType": "",
                     "woClientReference": "",
                     "woCustomer": "",
                     "woCustomerParent": "",
                     "woCustomerAR": "",
                     "woLocation": "",
                     "woClientCostCenter": ""
                  }
               }
            },
            {
               "originatingOrderLineID": 2,
               "relatedOrderLineReference": {
                  "relatedOrderNumber": "",
                  "relatedOrderLineNumber": "",
                  "relatedOrderSystem": "",
                  "relatedOrderType": ""
               },
               "lineItemName": "02 Site Utilities",
               "lineItemType": "Item",
               "lineItemNumber": "34372840",
               "lineClosedDate": "",
               "lineItemDetails": {
                  "modelName": "",
                  "manufacturer": "",
                  "sellerItemCatalogId": "",
                  "sellerItemId": "",
                  "commodityGroupCode": "",
                  "commodityClassificationCode": "",
                  "commodityDescription": ""
               },
               "locationId": "33857616",
               "buildingName": "",
               "lineDeliveryDetails": {
                  "deliveryLocationId": "33857616",
                  "deliveryLocationDescription": "",
                  "deliveryStartDate": null,
                  "deliveryEndDate": null,
                  "deliveryAttention": ""
               },
               "quantity": null,
               "quantityReceived": "",
               "quantityInvoiced": "",
               "quantityRemaining": "",
               "unitCost": "1000",
               "baseUnitCost": "",
               "receivedUnitCost": "",
               "lineItemAmount": "1000",
               "lineItemBaseAmount": "",
               "unitofMeasure": {
                  "code": "EACH",
                  "description": ""
               },
               "currencyCode": {
                  "code": "USD",
                  "description": ""
               },
               "lineItemDescription": "02 Site Utilities",
               "lineItemStatus": {
                  "code": "",
                  "description": ""
               },
               "termsConditions": "",
               "lineItemComments": null,
               "contractDetails": {
                  "contractNumber": "KPO000000003",
                  "contractScope": "",
                  "activity": "20_null_9999",
                  "billType": ""
               },
               "glAccountCode": {
                  "glAccountCode": null,
                  "bomaCode": null
               },
               "additionalCoding": {
                  "originatingProjectNumber": "KPO000000003",
                  "workOrderNumber": 89965,
                  "workOrderDetails": {
                     "workOrderClass": "",
                     "workOrderType": "",
                     "workOrderTypeDescr": "",
                     "workOrderDescription": "",
                     "woAgreementType": "",
                     "woContractNumber": "",
                     "woBillType": "",
                     "woClientReference": "",
                     "woCustomer": "",
                     "woCustomerParent": "",
                     "woCustomerAR": "",
                     "woLocation": "",
                     "woClientCostCenter": ""
                  }
               }
            }
         ]
      }
   }
}

Tried achieving same using mapper expression variables but still couldn't get to the final outcome. Urgently need any help here (so specifically am looking for a way to drill down to and use projectActivity-->chartCode object within items array, here for activity we have a logic in place to look for specific supplementalType value, fetching wherever we have it as 'BOMA'). Please let me know in case additional details are needed from my end.

Thanks,

Deepti

koryknick · ‎11-27-2023

HI @Deepti - update the second Mapper expression to the following:

$items
.map(x=> x.merge(
{ "additionalCoding" : { "workOrderNumber" : $project.projectId } 
,  "contractDetails" : { "workOrderNumber" : $project.projectId } }
)
)

So rather than an Object.extend(), we'll use Object.merge() to do a deep merge of the object, allowing us to also add the workOrderNumber into the existing contractDetails sub-element.

Hope this gets you closer to your goal!

View solution in original post

Deepti · ‎11-16-2023

Hi @bojanvelevski, I've read through a number of your insightful responses to other posts here, so I know you'll have something to offer me as well. Anticipating a response from you.TIA.

Deepti

koryknick · ‎11-20-2023

@Deepti - can you simplify the input/desired output? This is a lot to try to ingest and infer. If you could remove any extraneous fields that don't help explain the actual logic required, that would be helpful for someone to come up with a solution.

Deepti · ‎11-20-2023

Hi @koryknick ,

My bad, please find below a simplified version of same:

Input:

{
   "project": {
      "projectId": 2
   },
   "items": [
      {
         "description": "Architecture Fees",
         "unitPrice": "1000",
         "projectActivity": {
            "chartCodes": [
               {
                  "chartCode": {
                     "uniqueCode": "7845",
                     "supplementalType": "BOMA"
                  }
               }
            ]
         }
      },
      {
         "description": "02 Site Utilities",
         "unitPrice": "2000",
         "projectActivity": {
            "chartCodes": []
         }
      }
   ]
}

Desired output:

{
   "order": {
      "orderLineItems": {
         "orderLineItem": [
            {
               "lineItemName": "Architecture Fees",
               "unitCost": "1000",
               "contractDetails": {
                  "activity": "20_7845_9999"
               },
               "additionalCoding": {
                  "workOrderNumber": "2"
               }
            },
            {
               "lineItemName": "02 Site Utilities",
               "unitCost": "2000",
               "contractDetails": {
                  "activity": "20_null_9999"
               },
               "additionalCoding": {
                  "workOrderNumber": "2"
               }
            }
         ]
      }
   }
}

Mapping I tried inside mapper:

mapping expression I used:

$items.map(val=>{"lineItemName":val.description,"unitCost":val.unitPrice,"contractDetails":{"activity":'20_'+jsonPath($,"items[*].projectActivity.chartCodes[*].chartCode").find(x=>x.supplementalType=='BOMA').uniqueCode+'_9999'},"additionalCoding":{"workOrderNumber":JSON.stringify($project.projectId)}})

It gives me output as follows:

{
   "order": {
      "orderLineItems": {
         "orderLineItem": [
            {
               "lineItemName": "Architecture Fees",
               "unitCost": "1000",
               "contractDetails": {
                  "activity": "20_7845_9999"
               },
               "additionalCoding": {
                  "workOrderNumber": "2"
               }
            },
            {
               "lineItemName": "02 Site Utilities",
               "unitCost": "2000",
               "contractDetails": {
                  "activity": "20_7845_9999"
               },
               "additionalCoding": {
                  "workOrderNumber": "2"
               }
            }
         ]
      }
   }
}

So here, I tried many a ways to manipulate the BOMA value which should come as distinct, the way it shows up in input, like for item 1 we have value available but for item 2 we should get null (or any other value, as coming from source), but the final working mapping I could figure out is mentioned above and it's basically mapping first BOMA code encountered in all the list object values.

Appreciate any help here!

Please let me know in case additional details needed here.

Thanks,

Deepti

alchemiz · ‎11-21-2023

Hi @Deepti ,

Good day. Hope this helps

{'order':{'orderLineItems': {'orderLineItem': $items.map( i=> { 'lineItemName': i.get('description'), 'unitCost': i.get('unitPrice'), 'contractDetails': { 'activity': ''.concat('20_', (i['projectActivity'].get('chartCodes',[]).find((x,y,z)=> x.get('chartCode',{}).get('supplementalType','') == 'BOMA') || {}).get('chartCode',{}).get('uniqueCode','null'), '_9999')}, 'additionalCoding': {'workOrderNumber': $project.get('projectId')} })}}}

Thanks,

EmEm

SnapLogic - Integration Nation

Retrieve a list within another array list