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?
Solved! Go to 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
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
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?
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
}
)
)
)
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
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})))
User | Count |
---|---|
258 | |
111 | |
95 | |
48 | |
41 |