I have pulled some data from my CRM API. Invoice data to be exact. See the below Json code of the data.
I am trying to upsert the data into a Retool Database using the "Bulk upsert via a primary key" option. I have been able to do it with one item. See code at bottom. I believe this has to do with object vs array. However, I am not versed enough in coding to know how to get the data into the right format to go through every line item.
{{ [{item_id: ST.data.data['0'].items['0'].id, description: ST.data.data['0'].items['0'].description, quantity: ST.data.data['0'].items['0'].quantity, cost: ST.data.data['0'].items['0'].cost, total_cost: ST.data.data['0'].items['0'].totalCost, price: ST.data.data['0'].items['0'].price, sku_name: ST.data.data['0'].items['0'].skuName, total: ST.data.data['0'].items['0'].total, modified_on: ST.data.data['0'].modifiedOn, business_unit_name: ST.data.data['0'].businessUnit.name, job_number: ST.data.data['0'].job.number, customer_name: ST.data.data['0'].customer.name, invoice_date: ST.data.data['0'].invoiceDate, customer_id: ST.data.data['0'].customer.id, location_id: ST.data.data['0'].location.id, job_type: ST.data.data['0'].job.type, type: ST.data.data['0'].items['0'].type}]}}
Suggestions would be great.
({
"data": {
"page": 1,
"pageSize": 50,
"hasMore": false,
"totalCount": null,
"data": [{
"id": 44074191,
"invoiceDate": "2024-03-11T00:00:00Z",
"customer": {
"id": 43429110,
"name": "Rodney Jackson"
},
"location": {
"id": 43429115,
"name": "Rodney Jackson"
},
"businessUnit": {
"id": 61569,
"name": "Concrete Repair"
},
"modifiedOn": "2024-03-12T17:36:41.7845275Z",
"job": {
"id": 44074188,
"number": "44074188",
"type": "L1 POLY INSTALL"
},
"items": [{
"id": 44074208,
"description": "Install LEVELift polyurethane where needed to lift concrete panels",
"quantity": "96.0000000000000000000",
"cost": "0.0000000000",
"totalCost": "0.00",
"price": "0.00",
"type": "Service",
"skuName": "CR-LEVELIFT",
"skuId": 72193,
"total": "0.00",
}, {
"id": 44074209,
"description": "PolyLevel ISO component",
"quantity": "0.0000000000000000000",
"cost": "28.6700000000",
"totalCost": "0.00",
"price": "0.00",
"type": "Material",
"skuName": "CR-Polylevel-ISO",
"skuId": 9189335,
"total": "0.00",
}, {
"id": 44074212,
"description": "The PolyLevel® 2.5lb. Hydrophobic FAST is the commonly used PolyLevel® material and is the best option for nearly every residential application with a faster reaction time.",
"quantity": "0.0000000000000000000",
"cost": "28.6700000000",
"totalCost": "0.00",
"inventoryLocation": "Default Truck - Zachary Zaras",
"price": "0.00",
"type": "Material",
"skuName": "CR-PL250-B",
"skuId": 9190103,
"total": "0.00",
}, {
"id": 44082948,
"description": "Same Day & Military Discount combined 10% discount<div>NOT ELIGIBLE WITH FINANCING <br></div>",
"quantity": "1.0000000000000000000",
"cost": "0.0000000000",
"totalCost": "0.00",
"inventoryLocation": null,
"price": "0.00",
"type": "PriceModifier",
"skuName": "SDMD10",
"skuId": 43353959,
"total": "0.00",
}, {
"id": 55761226,
"description": "Forfeited Deposit",
"quantity": "1.0000000000000000000",
"cost": "0.0000000000",
"totalCost": "0.00",
"inventoryLocation": null,
"price": "262.44",
"type": "Service",
"skuName": "CR-MISC",
"skuId": 9003819,
"total": "262.44",
}],
"customFields": null
}, {
"id": 46056193,
"invoiceDate": "2024-03-05T00:00:00Z",
"customer": {
"id": 45879155,
"name": "Orane Love Jr"
},
"location": {
"id": 45879160,
"name": "Orane Love Jr"
},
"businessUnit": {
"id": 61569,
"name": "Concrete Repair"
},
"modifiedOn": "2024-03-12T15:51:31.7943158Z",
"job": {
"id": 46056190,
"number": "46056190",
"type": "L2 POLY INSTALL"
},
"reviewStatus": "Reviewed",
"items": [{
"id": 46056199,
"description": "Install LEVELift under concrete slabs and seal all joints using LevelSeal Joint Sealant. Protection system includes 5 year warranty on settlement and joint sealant.",
"quantity": "384.0000000000000000000",
"cost": "0.0000000000",
"totalCost": "0.00",
"inventoryLocation": null,
"price": "3.00",
"type": "Service",
"skuName": "CR-LPS",
"skuId": 43928650,
"total": "1152.00",
}, {
"id": 46056202,
"description": "Install LEVELift polyurethane where needed to lift concrete panels",
"quantity": "9.0000000000000000000",
"cost": "0.0000000000",
"totalCost": "0.00",
"inventoryLocation": null,
"price": "130.00",
"type": "Service",
"skuName": "CR-LEVELIFT",
"skuId": 72193,
"total": "1170.00",
}, {
"id": 46056205,
"description": "Customer is protected from any utility damage caused by contractor",
"quantity": "1.0000000000000000000",
"cost": "0.0000000000",
"totalCost": "0.00",
"inventoryLocation": null,
"price": "299.00",
"type": "Service",
"skuName": "UI-POOL",
"skuId": 9010369,
"total": "299.00",
}, {
"id": 46056209,
"description": "5% Same Day Savings<div>NOT ELIGIBLE WITH FINANCING <br></div>",
"quantity": "1.0000000000000000000",
"cost": "0.0000000000",
"totalCost": "0.00",
"inventoryLocation": null,
"price": "-116.10",
"type": "PriceModifier",
"skuName": "SDS5",
"skuId": 32643379,
"total": "-116.10",
}, {
"id": 46056213,
"description": "ESTIMATED HOURS TO COMPLETE",
"quantity": "4.0000000000000000000",
"cost": "0.0000000000",
"totalCost": "0.00",
"inventoryLocation": null,
"price": "0.00",
"type": "Service",
"skuName": "L2LABOR",
"skuId": 43608058,
"total": "0.00",
}, {
"id": 46056217,
"description": "PolyLevel ISO component",
"quantity": "3.0000000000000000000",
"cost": "28.6700000000",
"totalCost": "86.01",
"inventoryLocation": "307",
"price": "0.00",
"type": "Material",
"skuName": "CR-Polylevel-ISO",
"skuId": 9189335,
"total": "0.00",
}, {
"id": 46056221,
"description": "The PolyLevel® 2.5lb. Hydrophobic FAST is the commonly used PolyLevel® material and is the best option for nearly every residential application with a faster reaction time.",
"quantity": "3.0000000000000000000",
"cost": "28.6700000000",
"totalCost": "86.01",
"inventoryLocation": "307",
"price": "0.00",
"type": "Material",
"skuName": "CR-PL250-B",
"skuId": 9190103,
"total": "0.00",
}, {
"id": 55539825,
"description": "3/8IN DRILL BIT - SDS PLUS",
"quantity": "1.0000000000000000000",
"cost": "8.7300000000",
"totalCost": "8.73",
"inventoryLocation": "307",
"price": "0.00",
"type": "Material",
"skuName": "CR-38DRILLBIT",
"skuId": 32744166,
"total": "0.00",
}, {
"id": 55541813,
"description": "CONTRACTOR TRASH BAGS Each",
"quantity": "1.0000000000000000000",
"cost": "0.6000000000",
"totalCost": "0.60",
"inventoryLocation": "307",
"price": "0.00",
"type": "Material",
"skuName": "CR-TRASHBAG-EA",
"skuId": 45387853,
"total": "0.00",
}, {
"id": 55647375,
"description": "<div>Desert Sand Color</div>",
"quantity": "1.0000000000000000000",
"cost": "220.0000000000",
"totalCost": "220.00",
"inventoryLocation": "307",
"price": "0.00",
"type": "Material",
"skuName": "CR-NPJS-D",
"skuId": 9177310,
"total": "0.00",
}],
"customFields": null
}, {
"id": 52543182,
"syncStatus": "Posted",
"summary": null,
"referenceNumber": "52543179",
"invoiceDate": "2024-03-11T00:00:00Z",
"dueDate": "2024-03-11T00:00:00Z",
"subTotal": "398.40",
"salesTax": "0.00",
"salesTaxCode": null,
"total": "398.40",
"balance": "0.00",
"invoiceType": null,
"customer": {
"id": 45674342,
"name": "Mary Ann Dillehay"
},
"customerAddress": {
"street": "310 Stonehenge Lane",
"unit": null,
"city": "Friendswood",
"state": "TX",
"zip": "77546",
"country": "USA"
},
"location": {
"id": 45674347,
"name": "Mary Ann Dillehay"
},
"locationAddress": {
"street": "310 Stonehenge Lane",
"unit": null,
"city": "Friendswood",
"state": "TX",
"zip": "77546",
"country": "USA"
},
"businessUnit": {
"id": 61569,
"name": "Concrete Repair"
},
"termName": "Due On Receipt",
"createdBy": "amims726",
"batch": {
"id": 55773712,
"number": "1736",
"name": "Invoices (3/12/24)"
},
"depositedOn": "2024-03-12T17:36:41.8196962Z",
"createdOn": "2023-11-03T20:22:36.6811863Z",
"modifiedOn": "2024-03-12T17:36:41.7845275Z",
"adjustmentToId": null,
"job": {
"id": 52543179,
"number": "52543179",
"type": "L1 POLY INSTALL"
},
"projectId": 46375773,
"royalty": {
"status": "Pending",
"date": null,
"sentOn": null,
"memo": null
},
"employeeInfo": {
"id": 34119867,
"name": "amims726",
"modifiedOn": "2024-02-27T05:51:49.0204699Z"
},
"commissionEligibilityDate": null,
"sentStatus": "NotSent",
"reviewStatus": "Reviewed",
"assignedTo": null,
"items": [{
"id": 52543188,
"description": "Install LEVELift polyurethane where needed to lift concrete panels",
"quantity": "21.0000000000000000000",
"cost": "0.0000000000",
"totalCost": "0.00",
"inventoryLocation": null,
"price": "0.00",
"type": "Service",
"skuName": "CR-LEVELIFT",
"skuId": 72193,
"total": "0.00",
"inventory": false,
"taxable": false,
"generalLedgerAccount": {
"id": 1147,
"name": "Revenue Income",
"number": "4000",
"type": "Income",
"detailType": "Income"
},
"costOfSaleAccount": null,
"assetAccount": null,
"membershipTypeId": 0,
"itemGroup": null,
"displayName": "LEVELift Concrete Lifting",
"soldHours": 0,
"modifiedOn": "2024-03-11T15:38:26.3294439Z",
"serviceDate": "2023-12-18T00:00:00Z",
"order": 1,
"businessUnit": {
"id": 61569,
"name": "Concrete Repair"
}
}, {
"id": 52543191,
"description": "Customer is protected from any utility damage caused by contractor",
"quantity": "1.0000000000000000000",
"cost": "0.0000000000",
"totalCost": "0.00",
"inventoryLocation": null,
"price": "0.00",
"type": "Service",
"skuName": "UI-DWSW",
"skuId": 9010367,
"total": "0.00",
"inventory": false,
"taxable": false,
"generalLedgerAccount": {
"id": 54027547,
"name": "Utilities Protection",
"number": "4101",
"type": "Income",
"detailType": "Income"
},
"costOfSaleAccount": null,
"assetAccount": null,
"membershipTypeId": 0,
"itemGroup": null,
"displayName": "Utilities Protection Plan - Driveways and Sidewalks",
"soldHours": 0,
"modifiedOn": "2024-03-11T15:38:35.5669674Z",
"serviceDate": "2023-12-18T00:00:00Z",
"order": 2,
"businessUnit": {
"id": 61569,
"name": "Concrete Repair"
}
}, {
"id": 52543195,
"description": "Same Day & Military Discount combined 10% discount\nNOT ELIGIBLE WITH FINANCING",
"quantity": "1.0000000000000000000",
"cost": "0.0000000000",
"totalCost": "0.00",
"inventoryLocation": null,
"price": "0.00",
"type": "PriceModifier",
"skuName": "SDMD10",
"skuId": 43353959,
"total": "0.00",
"inventory": false,
"taxable": false,
"generalLedgerAccount": {
"id": 36490571,
"name": "Discounts",
"number": "4104",
"type": "Income",
"detailType": "Income"
},
"costOfSaleAccount": null,
"assetAccount": null,
"membershipTypeId": 0,
"itemGroup": null,
"displayName": "Same Day & Military Discount",
"soldHours": 0,
"modifiedOn": "2024-03-11T15:38:26.3196441Z",
"serviceDate": null,
"order": 3,
"businessUnit": {
"id": 61569,
"name": "Concrete Repair"
}
}, {
"id": 52543199,
"description": "ESTIMATED HOURS TO COMPLETE",
"quantity": "5.0000000000000000000",
"cost": "0.0000000000",
"totalCost": "0.00",
"inventoryLocation": null,
"price": "0.00",
"type": "Service",
"skuName": "L3LABOR",
"skuId": 43612795,
"total": "0.00",
"inventory": false,
"taxable": false,
"generalLedgerAccount": {
"id": 1147,
"name": "Revenue Income",
"number": "4000",
"type": "Income",
"detailType": "Income"
},
"costOfSaleAccount": null,
"assetAccount": null,
"membershipTypeId": 0,
"itemGroup": null,
"displayName": "ESTIMATED HOURS TO COMPLETE",
"soldHours": 1,
"modifiedOn": "2023-11-03T20:22:38.3432717Z",
"serviceDate": "2023-12-18T00:00:00Z",
"order": 4,
"businessUnit": {
"id": 61569,
"name": "Concrete Repair"
}
}, {
"id": 52543203,
"description": "PolyLevel ISO component",
"quantity": "10.5000000000000000000",
"cost": "28.6700000000",
"totalCost": "301.04",
"inventoryLocation": "On Hold",
"price": "0.00",
"type": "Material",
"skuName": "CR-Polylevel-ISO",
"skuId": 9189335,
"total": "0.00",
"inventory": true,
"taxable": false,
"generalLedgerAccount": {
"id": 1147,
"name": "Revenue Income",
"number": "4000",
"type": "Income",
"detailType": "Income"
},
"costOfSaleAccount": {
"id": 1155,
"name": "Cost of Materials",
"number": "5000",
"type": "Cost of Goods Sold",
"detailType": "Expense"
},
"assetAccount": {
"id": 1314963,
"name": "Inventory",
"number": "1202",
"type": "Other Current Asset",
"detailType": "Asset"
},
"membershipTypeId": 0,
"itemGroup": null,
"displayName": "PolyLevel - A Side",
"soldHours": 0,
"modifiedOn": "2024-03-11T15:43:52.9736494Z",
"serviceDate": "2024-03-11T00:00:00Z",
"order": 5,
"businessUnit": {
"id": 61569,
"name": "Concrete Repair"
}
}, {
"id": 52543206,
"description": "The PolyLevel® 2.5lb. Hydrophobic FAST is the commonly used PolyLevel® material and is the best option for nearly every residential application with a faster reaction time.",
"quantity": "10.5000000000000000000",
"cost": "28.6700000000",
"totalCost": "301.04",
"inventoryLocation": "On Hold",
"price": "0.00",
"type": "Material",
"skuName": "CR-PL250-B",
"skuId": 9190103,
"total": "0.00",
"inventory": true,
"taxable": false,
"generalLedgerAccount": {
"id": 1147,
"name": "Revenue Income",
"number": "4000",
"type": "Income",
"detailType": "Income"
},
"costOfSaleAccount": {
"id": 1155,
"name": "Cost of Materials",
"number": "5000",
"type": "Cost of Goods Sold",
"detailType": "Expense"
},
"assetAccount": {
"id": 1314963,
"name": "Inventory",
"number": "1202",
"type": "Other Current Asset",
"detailType": "Asset"
},
"membershipTypeId": 0,
"itemGroup": null,
"displayName": "PolyLevel 2.5lb Fast - B Side",
"soldHours": 0,
"modifiedOn": "2024-03-11T15:43:52.9736494Z",
"serviceDate": "2024-03-11T00:00:00Z",
"order": 6,
"businessUnit": {
"id": 61569,
"name": "Concrete Repair"
}
}, {
"id": 55767966,
"description": "Forfeited deposit",
"quantity": "1.0000000000000000000",
"cost": "0.0000000000",
"totalCost": "0.00",
"inventoryLocation": null,
"price": "398.40",
"type": "Service",
"skuName": "CR-MISC",
"skuId": 9003819,
"total": "398.40",
"inventory": false,
"taxable": false,
"generalLedgerAccount": {
"id": 1147,
"name": "Revenue Income",
"number": "4000",
"type": "Income",
"detailType": "Income"
},
"costOfSaleAccount": null,
"assetAccount": null,
"membershipTypeId": 0,
"itemGroup": null,
"displayName": "Misc - Concrete",
"soldHours": 0,
"modifiedOn": "2024-03-11T15:39:15.7523291Z",
"serviceDate": "2024-03-11T00:00:00Z",
"order": 7,
"businessUnit": {
"id": 61569,
"name": "Concrete Repair"
}
}],
"customFields": null
},
})
