Incorrect JSON Output Using Concatenate and FormulaMap in Coda

1

Hello,

I’m encountering an issue while generating JSON using the Concatenate and FormulaMap functions in Coda. The resulting JSON does not correctly reflect the values from my tables. Below is the formula I’m using:
Concatenate(
“[”,
Join(
", ",
thisRow.ProductosDoliDB.FormulaMap(
Format(
‘{
“id”: {1},
“type”: “{2}”,
“price”: “{3}”,
“tva_tx”: “{4}”,
“qty”: {5},
“remise_percent”: {6}
}’,
CurrentValue.[Product id],
thisRow.AlbProvDetalleCodaDB.Type,
thisRow.AlbProvDetalleCodaDB.Precio,
thisRow.AlbProvDetalleCodaDB.Iva,
thisRow.AlbProvDetalleCodaDB.Cantidad,
thisRow.AlbProvDetalleCodaDB.Descuento
)
)
),
“]”
)

The result I’m getting is as follows:
image

However, the expected result should correctly reflect the actual values from my tables, like so:
[
{
“id”: 6298,
“type”: “0”,
“price”: “24”,
“tva_tx”: “21”,
“qty”: 1,
“remise_percent”: 40
},
{
“id”: 25784,
“type”: “0”,
“price”: “45”,
“tva_tx”: “21”,
“qty”: 2,
“remise_percent”: 40
}
]

Here’s a breakdown of the relevant tables and their data:

Name Price Product id Description Label AlbProvCabeceraCodaDB
ABBN22719 0.79 6298 BASTIDOR 1ELEM.2MÓD. ZENIT PLATA BASTIDOR 1ELEM.2MÓD. ZENIT PLATA 0/533
9302 0 25784 BRAZO TELÉFONO 2 0/533
IdPedido S/Ref Posición Articulo Referencia Articulo Denominación Cantidad Precio Descuento Importe Importe OK Iva Type Crear Producto Existe Ref para dolibar ProductosDoliDB
0/533 MIGUEL 5 288523 ABBN22719 BRAZO TELÉFONO MONITOR VEO 1 24 40 14,4 14.4 21 0 system true ABBN22719 6298
0/533 MIGUEL 5 288524 9302 BRAZO TELÉFONO 2 2 45 40 125,25 125.25 21 0 system true 9302 25784

Copy of AlbProvCabeceraCodaDB

IdPedido Fecha AlbProvDetalleCodaDB Total Crear pedido json Id Proveedor ProductosDoliDB ProductID S/Ref
0/533 10/07/2024 0/533,0/533 139.65 [{
“id”: 6298,
“type”: “0”,
“price”: “0”,
“tva_tx”: “24”,
“qty”: 45,
“remise_percent”: 21
}, {
“id”: 25784,
“type”: “0”,
“price”: “0”,
“tva_tx”: “24”,
“qty”: 45,
“remise_percent”: 21
}] 1131 BASTIDOR 1ELEM.2MÓD. ZENIT PLATA,BRAZO TELÉFONO 2 6298,25784 MIGUEL,MIGUEL

2

any chance you can share the doc (or copy it, remove any sensitive info and then share it)?

3

Here you go https://coda.io/d/_dOvlq8dd528/Incorrect-JSON-Output-Using-Concatenate-and-FormulaMap-in-Coda_su3pE

4

I see the issue.

  1. The Products.AlbProvCabeceraCodaDB field is point to the “Copy of AlbProvCabeceraCodaDB” table. Change that to point to the “AlbProvCabeceraCodaDB” table.

  2. The formula is using “thisRow” within a foreach. Within a foreach you want to use the “CurrentValue” variable. CurrentValue is the object for that iteration through the loop. Once you do that your formula will look like below. I also modified the formula to be a little cleaner.

image
image835×582 44.5 KB

  1. And you JSON will now be correct:
    image
    image434×501 7.68 KB

That should do it. Hope it helps.

1 Like
5

Yes and no. The adjustments have helped, but I’m still encountering an issue. The precio (price), cantidad (quantity), and descuento (discount) should correspond to each specific product. However, what’s happening is that these values are being taken from the same product, even though one of the products (BRAZO TELÉFONO 2) has different values.

For example, BASTIDOR 1ELEM.2MÓD. ZENIT PLATA and BRAZO TELÉFONO 2 should have distinct values for precio, cantidad, and descuento, but the JSON is showing the same values for both products.

6

That’s because there’s only 2 products (ABBN22719, 9302) and both products are pointing the same AlbProvCabeceraCodaDB. I added a AlbProId column on the Products table that displays the AlbProvCabeceraCodaDB ID and both are “1”.

If you change one of the products AlbProvCabeceraCodaDB values to a different AlbProvCabeceraCodaDB row the JSON changes accordingly. So there’s nothing wrong with the setup, it’s just the what AlbProvCabeceraCodaDB the products are pointed to.

A couple of tips:

  1. Since the AlbProvCabeceraCodaDB display column isn’t unique (ex: multiple rows display are “0/573”) I would suggest creating a unique column for that table and then set that column to the display so it’s easier to ensure the right AlbProvCabeceraCodaDB on the product is selected.

  2. I noticed the AlbProvCabeceraCodaDB column on products allows multiple AlbProvCabeceraCodaDB rows. Do you need that? If not disable that. If you do the “json” formula will not work if multiple AlbProvCabeceraCodaDB rows are selected and will need to be modified to handle multiple selected rows.

7

@exeinformatica_microsshop did that solve all your problems. Happy to jump in the doc if you need more help and tweak it if you need.

8

Hello @Ryan_Hubbard1 . Thank you very much for your help. I have managed to solve most of my problems with your solution.

9

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