cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nick9one1
Helper III
Helper III

dealing with time and schedules

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. 

 

nick9one1_0-1669928151611.png

 

the data come from this dataverse table

nick9one1_1-1669928234063.png

 

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

 

e.g. 1st floor patrol

 

Start

End

9PM10pm
10pm11pm

11pm

12pm
12pm

1am

1am2am
  

 

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?

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xiaochen-msft
Community Support
Community Support

Hi @nick9one1 ,

 

I made a sample for you:

 

This is the data in Dataverse.

vxiaochenmsft_0-1669960923274.png

 

 

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))}))

vxiaochenmsft_1-1669960923277.png

 

 

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

vxiaochenmsft_2-1669960923279.png

 

Best Regards,

Wearsky

View solution in original post

5 REPLIES 5
v-xiaochen-msft
Community Support
Community Support

Hi @nick9one1 ,

 

I made a sample for you:

 

This is the data in Dataverse.

vxiaochenmsft_0-1669960923274.png

 

 

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))}))

vxiaochenmsft_1-1669960923277.png

 

 

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

vxiaochenmsft_2-1669960923279.png

 

Best Regards,

Wearsky

nick9one1
Helper III
Helper III

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_0-1669970594096.png

 



nick9one1
Helper III
Helper III

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
                
            }
        )
    )
)

 

 

 

 

 

nick9one1_1-1669971482413.png

 

v-xiaochen-msft
Community Support
Community Support

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}));

vxiaochenmsft_0-1669973379677.png

 

 

Click the button to create a collection.

vxiaochenmsft_1-1669973379681.png

 

Best Regards,

Wearsky

nick9one1
Helper III
Helper III

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})))

 

nick9one1_0-1669973642378.png

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,534)