Split a Time value that belongs to a Collection

[Anonymous]

So I have a Gallery with some items, and one of the columns is Time (minutes, seconds, milliseconds "mm:ss:fff") and I need to sum the times to make a Total Value of all the times in that list, to do that I need to first convert it to milliseconds and sum those.

This is what I use to split and sum it:

Sum(
      Value(First(Split(example.;":")).Result)*60000;
      Value(Last(FirstN(Split(example;":");2)).Result)*1000;
      Value(Last(FirstN(Split(example;":");3)).Result)
))

What I've been told and think is more correct is to use a Collection and put the times there then use that collection to sum all values, but it doesn't work (Collect(somatempos;{OP: GaleriaModOP.AllItems.Operação};{Tempo: GaleriaModOP.AllItems.Tempo})).

I also tried to use  the Gallery.AllItems.Time (or the Label itself) to sum the values but It doesn't work too.

What do I need to put instead of example? Any help?

[Mink21 · Accepted solution]

Hi @Anonymous ,

You can try it like this:

Clear(colMinutes);;

ForAll(YourTable;

Collect(colMinutes; First(Split(ThisRecord.Time;":")).Result)

);;

Clear(colSeconds);;

ForAll(YourTable;

Collect(colSeconds; Last(FirstN(Split(ThisRecord.Time;":");2)).Result)

);;

Clear(colMiliseconds);;

ForAll(YourTable;

Collect(colMiliseconds; Last(FirstN(Split(ThisRecord.Time;":");3)).Result)

);;
Sum(Sum(colMinutes;Value)*60000;Sum(colSeconds;Value)*1000;Sum(colMiliseconds;Value))

I hope this helps!

[rubin_boer]

hi @Mink21 in addition to @Anonymous 

You can do the following providing the time in your column is a string

colTime

ClearCollect(colTime,{time: "01:02:15"})

Below colTime is your datasource and time is the column

With(
    {
        _data:Split(First(colTime).time,":").Result
    },
    First(colTime).time & " converted: "&
    Sum(
        Index(_data,1).Result*60000,
        Index(_data,2).Result*1000,
        Index(_data,3).Result
    )
)

result

Adding it all together in a collection:

//original datasource
ClearCollect(colTime,{time: "01:02:15"}, {time: "03:17:18"});
//add total column
ClearCollect(
    colTimeWithMs,
        AddColumns(colTime,"total (ms)", With(
        {
            _data: Split(time,":").Result
        },
            Sum(
                Index(_data,1).Result *  60000,
                Index(_data,2).Result* 1000,
                Index(_data,3).Result
            )
        )
    )
)

Result

Hope it helps,

R

[Anonymous]

That's it!

Thank You So Much @Mink21!

Now there is only one thing left.

That List is a Related list so what it means is that I have another list called Models, and in each Model it has its operations and Times. What I need to do is to show the time in the Models Gallery, not in the Operations And Times Gallery/Screen. So for each Model, I need to do that formula so I can see how much time took each one to be made.

Any help?

[Mink21]

Hi @Anonymous ,

I'm not sure how everything is working in your app, but if I understood correctly, maybe you could call the operations and times table from the Models gallery using a filter inside the ForAlls:

ForAll(Filter('Operations and Times'; Your condition);

Collect(colMinutes; First(Split(ThisRecord.Time;":")).Result)

)

I hope this helps!

[Anonymous]

It's ALMOST there!

Just need to find the right Filter to do this. I'm Using the Gallery.Selected.ID formula but that makes it appear all the same, and when I select another one, leave, and come back, it all changes to that one, since my sum formula is in the OnVisible property of the screen.

Here are some Images for you to understand.

That side arrow takes me to the gallery that has all the times, but maybe doing the sum in the Models Screen is easier?

[Mink21]

The problem is that you have the code inside the on visible, so it will always get the data for only one register.

In this case I'd try to modify the collection. Instead of filtering the data, I'd add columns with the information you need to identify each operation. 

Imagine you have an ID of the Model in your operations table.

ForAll('Operations and Times';

Collect(colMinutes; {Value:First(Split(ThisRecord.Time;":")).Result; ID: ThisRecord.ID})

)

You can still write the code above in your On Visible propiety, but I'd put the sum in the label in your gallery adding a filter:
Sum(
 Sum(Filter(colMinutes; ID = ThisItem.ID);Value)*60000;

 Sum(Filter(colSeconds; ID = ThisItem.ID);Value)*1000;

 Sum(Filter(colMiliseconds; ID = ThisItem.ID; Value)

)

I hope this helps!

[Anonymous]

Hi Again!

Well, I gave up on it, but I found a workaround!

So I have a variable which it records what is the gallery item that is selected (ID), so instead, I Filtered it to the Gallery Selected ID so when I select an Item, the Total Time will change, and then I made it up so the label only shows on the selected Item. I wanted it to show on all Items at once, but also works this way!