JSON duplicates rows

LXARGENTINA_S_A · 11-25-2021 04:29 AM

Hi!
We have a problem…
In our aplication there is a bot that executes a JSON to create rows based on the number of days between two dates.
The problem is that it duplicates the rows
Ex:
Sick leave 11/1/2021 to 11/5/2021 → Total: 5 days = 5 rows (one for each day)

Rows Result:
11/1/2021
11/2/2021
11/3/2021
11/4/2021
11/5/2021
11/1/2021
11/2/2021
11/3/2021
11/4/2021
11/5/2021

This is the JSON:
{
“Action”: “Add”,
“Properties”: {
“Locale”: “es-AR”,
“Location”: “47.623098, -122.330184”,
“Timezone”: “Pacific Standard Time”,
“RunAsUserEmail”: “email@company.com”
}
,
“Rows”: [
<START:UNIQUE(SELECT(dbo.Fechas[Fecha];AND([Fecha]=[_THISROW-1].[Fecha_desde];[Fecha]<=[_THISROW-1].[Fecha_hasta]);TRUE))>>
{
“Fechahora”: “<<CONCATENATE(TEXT(TODAY(),“dd/mm/yyyy”);” “;TEXT(TIMENOW(),“hh:mm:ss”);”.000")>>" ,
“ID”: “<<UNIQUEID()>>” ,
“ID_Nov”: “<<[_THISROW-1].[ID]>>” ,
“Usuario_valid”: “<<USEREMAIL()>>” ,
“Empresa”: “<<[_THISROW-1].[Empresa]>>” ,
“Persona”: “<<[_THISROW-1].[Persona]>>” ,
“CUIL”: “<<[_THISROW-1].[CUIL]>>” ,
“Legajo”: “<<[_THISROW-1].[Legajo]>>” ,
“Novedad”: “<<[_THISROW-1].[Novedad]>>” ,
“Concepto”: “<<[_THISROW-1].[Concepto_Liq]>>” ,
“Codigo_Con”: “<<[_THISROW-1].[Codigo]>>” ,
“Liquida”: “<<[_THISROW-1].[Aplica_Liq]>>” ,
“Fecha_ref”: “<<TEXT([Fecha],“dd/mm/yyyy”)>>”,
“Fecha_d”: “<<TEXT([_THISROW-1].[Fecha_desde],“dd/mm/yyyy”)>>” ,
“Fecha_h”: “<<TEXT([_THISROW-1].[Fecha_hasta],“dd/mm/yyyy”)>>” ,
“Cantidad”: “<<[_THISROW-1].[Canti]>>” ,
“Medida”: “<<[_THISROW-1].[Mide]>>” ,
“Cantidad_2”: “<<[_THISROW-1].[Canti_2]>>”
}
<>
]
}

Why does that happen?

Thanks

Suvrutt_Gurjar

It sounds that you are trying to add rows to a table based on “from and to dates” mentioned in the parent table and dates list as per this date range being drawn from a date list table (dbo.Fechas) that has a list of every day’s dates.

If so, in general, your START: expression seems to be missing a greater than equality operator and also a “<” before START . Both are highlighted below.

<< START:UNIQUE(SELECT(dbo.Fechas[Fecha];AND([Fecha] >= [_THISROW-1].[Fecha_desde];[Fecha]<=[_THISROW-1].[Fecha_hasta]);TRUE))>>

Please refer to the below post that shows a similar approach as yours for adding multiple records to another table.