dealing with time and schedules

[nick9one1]

I've built an app to help with security guards doing routine patrols on a night shift (or not doing them more like).

The first page shows the list of patrols. 

the data come from this dataverse table

I need to create collection for each of the rows, indexing when each patrol should start.  

e.g. 1st floor patrol

Start	End
9PM	10pm
10pm	11pm
11pm	12pm
12pm	1am
1am	2am

this would ideally be done in a forall loop, but that doesn't seem to be possible.

Are there any other solutions to this problem?

[v-xiaochen-msft · Accepted solution]

Hi @nick9one1 ,

I made a sample for you:

This is the data in Dataverse.

Add a button to the app and set 'OnSelect' as follows.

Clear(Col1st);ForAll(Sequence(Hour(LookUp(SJT9S,Name="1st floor").'end time')+24-Hour(LookUp(SJT9S,Name="1st floor").'start time'),0),Collect(Col1st,{Start:(Text(DateAdd(LookUp(SJT9S,Name="1st floor").'start time',ThisRecord.Value,Hours),ShortTime)),End:(Text(DateAdd(LookUp(SJT9S,Name="1st floor").'start time',ThisRecord.Value+1,Hours),ShortTime))}))

Add a table to view all the data in the collection.

Best Regards,

Wearsky

[nick9one1]

Thank you, thats almost exactly what I need. 

The list of patrols is dynamic, so I really need the formula to handle all of them. 

Would it be possible to have the formula output something like this?

[nick9one1]

this almost works, but only returns the first patrol

Clear(col_schedule);
ForAll(
    NFC_Sequence_Mains As OuterLoop,
    
    ForAll(
        Sequence(
            Hour(
                LookUp(
                    NFC_Sequence_Mains,
                    Name = OuterLoop.Name
                ).'end time'
            ) + 24 - Hour(
                LookUp(
                    NFC_Sequence_Mains,
                    Name = OuterLoop.Name
                ).'start time'
            ),
            0
        ),
        Collect(
            col_schedule,
            {
                Start: (Text(
                    DateAdd(
                        LookUp(
                            NFC_Sequence_Mains,
                            Name = OuterLoop.Name
                        ).'start time',
                        ThisRecord.Value,
                        Hours
                    ),
                    ShortTime
                )),
                End: (Text(
                    DateAdd(
                        LookUp(
                            NFC_Sequence_Mains,
                            Name = OuterLoop.Name
                        ).'start time',
                        ThisRecord.Value + 1,
                        Hours
                    ),
                    ShortTime
                )),
                Patrol: OuterLoop.Name
                
            }
        )
    )
)

[v-xiaochen-msft]

Hi @nick9one1 ,

If you only have three rows of data, you can use ForAll() three times in the formula.

I made a sample for you.

Clear(Col1st);

ForAll(Sequence(Hour(LookUp(SJT9S,Name="1st floor").'end time')+24-Hour(LookUp(SJT9S,Name="1st floor").'start time'),0),Collect(Col1st,{Start:(Text(DateAdd(LookUp(SJT9S,Name="1st floor").'start time',ThisRecord.Value,Hours),ShortTime)),End:(Text(DateAdd(LookUp(SJT9S,Name="1st floor").'start time',ThisRecord.Value+1,Hours),ShortTime)),Patrol:LookUp(SJT9S,Name="1st floor").Name}));

ForAll(Sequence(Hour(LookUp(SJT9S,Name="Car Park").'end time')+24-Hour(LookUp(SJT9S,Name="Car Park").'start time'),0),Collect(Col1st,{Start:(Text(DateAdd(LookUp(SJT9S,Name="Car Park").'start time',ThisRecord.Value,Hours),ShortTime)),End:(Text(DateAdd(LookUp(SJT9S,Name="Car Park").'start time',ThisRecord.Value+1,Hours),ShortTime)),Patrol:LookUp(SJT9S,Name="Car Park").Name}));

ForAll(Sequence(Hour(LookUp(SJT9S,Name="Ground Floor").'end time')+24-Hour(LookUp(SJT9S,Name="Ground Floor").'start time'),0),Collect(Col1st,{Start:(Text(DateAdd(LookUp(SJT9S,Name="Ground Floor").'start time',ThisRecord.Value,Hours),ShortTime)),End:(Text(DateAdd(LookUp(SJT9S,Name="Ground Floor").'start time',ThisRecord.Value+1,Hours),ShortTime)),Patrol:LookUp(SJT9S,Name="Ground Floor").Name}));

Click the button to create a collection.

Best Regards,

Wearsky

[nick9one1]

Sorry my mistake. the code I posted does work 🙄

I was looking at the collection preview which only shows 5 rows 

Clear(col_schedule);ForAll(NFC_Sequence_Mains As OuterLoop,ForAll(Sequence(Hour(LookUp(NFC_Sequence_Mains,Name=OuterLoop.Name).'end time')+24-Hour(LookUp(NFC_Sequence_Mains,Name=OuterLoop.Name).'start time'),0),Collect(col_schedule,{Start:(Text(DateAdd(LookUp(NFC_Sequence_Mains,Name=OuterLoop.Name).'start time',ThisRecord.Value,Hours),ShortTime)),End:(Text(DateAdd(LookUp(NFC_Sequence_Mains,Name=OuterLoop.Name).'start time',ThisRecord.Value+1,Hours),ShortTime)),Patrol:OuterLoop.Name})))