Combine Duplicates in array

Wappler General
, ,
1

I have an array where I have Ticket ID, Company Names and Time spent on a work order, however I need to be able to combine the Company1 Entries into one one object and add up the time by ticket id, or for any duplicated ticket id. so it looks like:
{"TimeArray":[{"arTicket":2562,"arName":"Company1","arTime":0.9666666666666667},{"arTicket":2564,"arName":"Company2","arTime":0.08333333333333333},{"arTicket":2562,"arName":"Company1","arTime":0.4166666666666667}"]

So far I haven’t been able to figure out how to use a while loop or repeat to go back through the results and create a new array with the arTime entries added together for a ticket Id.

This is generated from the following server side:

image
image814×499 18.1 KB

2

You could use the groupBy formatter from the data picker for the repeat expression, choosing the arTicket field to group the results by.

e.g. you should end up with

exec.monthlyWorkTime.where('billable','==',1).groupBy('ticket_id')

This will create a new array where the key is the ticket_id which you are repeating through. Inside each will be an array of the entries with that ticket id (which you can refer to with $value) which you can use the sum formatter on the times in a set value step

e.g.

$value.sum('end.dateDiff(\'minutes\',start)/60')
3

interesting result, the groupBy causes the same number of objects… they’re all empty and only have one variable in them, arTime.

image

4

Set the output filter option of the repeat to ‘Exclude’

You could also have a set value step for arTicket and arName with values:
$value[0].ticket_id and $value[0].name
which will take the ticket id and name from the first item in each grouped array

Can you share a screenshot of the new set value step for arTime?

5

it now has data again, after setting the output filter.
image

here’s the steps from the api:
image

6

It seems you can’t use a formatter inside the sum. You would possibly need a repeat inside to run through the tickets and calculate the time difference. Then you would use the sum after that repeat on its results.

An alternative, and probably easier method, would be to create a view in your DB that you would use instead of the table in your query:
something like this (you will need to adjust the table/column names accordingly - and also assuming MySQL)

SELECT *,
time_to_sec(timediff(end, start)) / 3600 AS ticket_hours 
FROM tickets
7

If you don’t want to go down the route of creating a view in the DB, this should work in your SC API:

image
image974×316 36 KB

8

i appreciate all your help with this. This project we moved away from wappler due to it’s limitations.