dealing with time and schedules

(0) Share

Report

Posted on by nick9one1

353

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?

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Verified answer

v-xiaochen-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
nick9one1 353 on at

Like
a
(0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
nick9one1 353 on at

Like
a
(0)

Report
Copy link

Link copied!
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 } ) ) )

Was this reply helpful? Yes No
v-xiaochen-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
nick9one1 353 on at

Like
a
(0)

Report
Copy link

Link copied!
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})))

Was this reply helpful? Yes No