Parse JSON Object into line items

Ask for Help
,
1

Below is a JSON object that was extracted from an invoice’s line items. I am trying to use this object to create line item records for each invoice.

What is the best way to parse this response into individual objects that I can create records from?

I am assuming that I am going to want to use JSON.parse in a Javascript column, but I am getting stumped on the config for that column.

Thanks in advance.

{
  "line-items": [
    {
      "date": "09/03/2024",
      "category": "travel",
      "quantity": 1,
      "unit type": "hours",
      "unit price": 125,
      "description": "Road Call Service Charge",
      "line item number": 1,
      "total line item cost": 125
    },
    {
      "date": "09/03/2024",
      "category": "service",
      "quantity": 2,
      "unit type": "hours",
      "unit price": 145,
      "description": "Traveled to troubleshoot generator power issue, discovered bad generator controller.",
      "line item number": 2,
      "total line item cost": 290
    },
    {
      "date": "09/05/2024",
      "category": "travel",
      "quantity": 1,
      "unit type": "hours",
      "unit price": 125,
      "description": "Road Call Service Charge",
      "line item number": 3,
      "total line item cost": 125
    },
    {
      "date": "09/05/2024",
      "category": "service",
      "quantity": 2.5,
      "unit type": "hours",
      "unit price": 145,
      "description": "Removed & replaced generator controller; calibrated new controller.",
      "line item number": 4,
      "total line item cost": 362.5
    },
    {
      "date": "09/05/2024",
      "category": "parts",
      "quantity": 1,
      "unit type": "units",
      "unit price": 1396.74,
      "description": "Onan Controller",
      "line item number": 5,
      "total line item cost": 1396.74
    },
    {
      "date": "09/05/2024",
      "category": "parts",
      "quantity": 1,
      "unit type": "units",
      "unit price": 62.62,
      "description": "Shipping",
      "line item number": 6,
      "total line item cost": 62.62
    },
    {
      "date": "09/05/2024",
      "category": "fees",
      "quantity": 1,
      "unit type": "units",
      "unit price": 75.55,
      "description": "Credit Card Convenience Fee",
      "line item number": 7,
      "total line item cost": 75.55
    }
  ]
}
2

You would need multiple query json column to make this working. But since you have arrays, you could potentially use webhook workflow to loop in the json array and add row for each element. What do you think?

3

Another use case that I can explain to you, is build a Glide API body with multiple Mutations to add multiple rows with a single API call.

4

I have done this in a lot of apps, I think it’s good to use, as long as you stay under something like 500 rows.

1 Like
5

How would get the workflow to identify the elements within the object? I was thinking if I could figure out a way to format this as individual objects then I would be able to loop a workflow that queries each variable and creates an item for each object.

6

Worst case scenario, I would be looking at running 50 line items for each invoice. Most of the time it is 2-10 items.

7

With the query JSON (if you send the full data in the body of your webhook call) or with a simple query to get the data from a specific location in your tables. Then be able to loop in that object. Does it make more sense ?

8

How often? You need to have this in mind for optimizing your updates usage.

9

I am not following on this one

10

Right now, it is a fairly low volume. A couple times a month, but I am anticipating that it will ramp up in the next 90 days, so I would rather pursue the most scalable options