Multiple records come in for each row through 3rd party data collection form; how do I separate these as 1 record per row?

1

Hello there,

I have a 3rd party data collection tool which is linked to my Coda doc, but the data that comes through the data collection tool includes several records per row. Is there a way to get these as 1 record per row in a separate table?

image
image1740×1600 229 KB

Thank you,
Tzviatko

2

Hey there!

Two ways:

  1. Wherever you catch your webhook, prior to running your add row call, run a script over the json to parse it, and add the appropriate rows to your table. You can do some light json manipulation with coda formulas and for a forEach() over the object but if the object has any sort of complexity and or nested structure I’d recommend catching your webhook somewhere else (google apps script is my choice) then using Codas api to add all your rows.
  2. Use a two table approach like your screenshot. The first table where it catches one row per submission you put some buttons on it. The buttons then take the relevant information and add add the appropriate number of rows to the second table. And you push those buttons via the same automation that caught the webhook
3

Thanks for the lightning fast response @Scott_Collier-Weir !
Making the adjustments before the data comes into the Coda doc is a much more elegant solution :slight_smile: thank you!

Here is my AddRow formula and this now works fine, just a couple of questions to optimize it:

  1. Is there a way to ignore blank entries from the form. For example if ID_03 and Status_03 are empty, with the current formula a blank row will be added to the Coda table, which is not ideal.

  2. If I have a large number of entries in the form (they go up to ID_20 and Status_20), is there a more efficient formula I can use instead of repeating the AddRow formula 20 times.

Thanks!
Tzviatko

image
image1584×674 110 KB

4

Yes! There is a more efficient way. It will be by creating an index for a forEach() loop through use of Sequence(1, totalItemCount) and looping through that

A bit more complex of a solve, but if the keys In your JSON are consistent like that it could work. Better shown than typed.

Can you share a doc with an example JSON payload?

5

Thanks Scott, I’m happy to hear there is a better way!
Here is an embed that describes the source data and shows the table that I would like populated with this data:

I’ve looked up forEach() loops a bit but I still can’t figure it out.

Thanks!
Tzviatko

6

Thanks! Does that doc have an actual json payload in it?

EDIT: Actually, I got it! Busy for the next couple hours but should be able to get to it tonight hopefully!

7

Alright here you go - This is what I would do:

image
image1708×1448 88.6 KB

And here’s the doc link that you can see the button in it working.

Couple things to note:

  1. Yours will be different, because you will be doing it in an automation, and therefore where Im accessing JSON you are going to be accessing Step1Result
  2. In order to find the number of keys in your JSON object, Im splitting on a : character. This will only work in situations where your incoming object is NOT nested and doesn’t contain a : anywhere but to separate keys and values.
  3. You can’t simply divide by 2 in the sequence formula IF the actual object has other keys that are unrelated to your status/id keys that you are after.

Again - coda is not great at working with Objects, it’s great at working with lists. If you need more complex object manipulation, best to pass the data first through a script outside of Coda and then use the API to add the rows.

Personally, I’d do it through a google apps script (Catch your webhooks in the apps script, parse the payload there, and then use Coda’s API to add the rows.

8

@Scott_Collier-Weir you are a legend! Thank you so much - this works perfectly!
And I appreciate the detailed, step by step explanation. Thanks!

9

It looks like I may have to change the field naming convention in the source so it doesn’t have the trailing 0 in front of single digits (ID_1 instead of ID_01), and slightly modify the formula you shared to better accommodate for the double digit index values so the loop looks for ID_11 instead of ID_011, etc.

ParseJSON(
  Format("ID_{1}", CurrentValue) //change this from "ID_0{1}"
)

Thank you!

10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.