Get specific data from array based on date or highest id

App Building
,
1

Hi All,

I have a table column which is populated by a rest api. The data is an array with multiple records.
I would like to get only one value back and only for the record that is the newest, based on date or the highest id number

[{"id":173418693,"offer_data_id":2217344,"stock":7,"sold":0,"revenue":"0.00","price":7.95,"status":"success","created_at":"2022-12-17T15:25:24.000000Z","updated_at":"2022-12-17T15:25:24.000000Z"},{"id":174660695,"offer_data_id":2217344,"stock":7,"sold":0,"revenue":"0.00","price":7.95,"status":"success","created_at":"2022-12-18T16:22:58.000000Z","updated_at":"2022-12-18T16:22:58.000000Z"},{"id":175895890,"offer_data_id":2217344,"stock":7,"sold":0,"revenue":"0.00","price":7.95,"status":"success","created_at":"2022-12-19T03:19:03.000000Z","updated_at":"2022-12-19T03:19:03.000000Z"},{"id":177135277,"offer_data_id":2217344,"stock":7,"sold":0,"revenue":"0.00","price":7.95,"status":"success","created_at":"2022-12-20T15:36:03.000000Z","updated_at":"2022-12-20T15:36:03.000000Z"},{"id":178392787,"offer_data_id":2217344,"stock":27,"sold":0,"revenue":"0.00","price":7.95,"status":"success","created_at":"2022-12-21T03:09:37.000000Z","updated_at":"2022-12-21T03:09:37.000000Z"}]

If you look at this case I would need to return the value from the field stock, in this case it should return the number 27 because this is the "newest" record based on id and date.

I can get all the stock nicely formatted in a custom column with below code but not sure how to add the above logic to only show the newest record.

{{ean.data[i].tracked_products.map(d=>d.stock).toString()}}

image

2

you can try using a Query JSON with SQL (works just like querying a database except you are querying the table column... Check out this doc

3

Yes thank I know that I can use the query json with sql but I don't know how to write this query :slight_smile: Hope somebody can help me out

The end result would be to get the field stock (red) from the newest item based on created_at

image
image603×814 41.7 KB

4

Fun problem (i'm still learning JS). What about sort the array on reverse date order and take the first instance?

{{ean.data[i].tracked_products.sort(
     (p1, p2) => 
      {return p2.created_at - p1.created_at}
     )[0].stock
}}

hth

5

:slight_smile: thanks for that, it now shows the first created date but it's a start for me to look in to

1 minute later reply --> changed sort to reverse and it workssssssssss!

you are the best, thanks

1 Like