Schedule Creator

(1) Share

Report

Posted on by BrendonBrooksP1

329

Summary

I'm developing an application that creates and manages a work schedule. There are two shifts, day and night shift. A manager or shift lead can create a schedule, which is saved to a database and emailed to the company. This schedule can be viewed by anyone in the company within PowerApps

Then, when a delivery is being made, a driver will create a log of which worker and their job role was on the delivery. Each delivery or job is then saved to databased, a report is emailed, and can be viewed within PowerApps.

There are 5 locations in which a delivery can originate.

For purposes of brevity, I will skip the email portion of the app and skip how to save data to a database, as these topics have been covered extensively in other posts.

Data

The data table contains the following columns:

 [Id] INT IDENTITY (1, 1) NOT NULL,
 [Date] DATETIME NULL,
 [CallSign] NVARCHAR (50) NULL,
 [Station] NVARCHAR (50) NULL,
 [VehicleNumber] NVARCHAR (50) NULL,
 [Driver] NVARCHAR (50) NULL,
 [Navigator] NVARCHAR (50) NULL,
 [Crew1] NVARCHAR (50) NULL,
 [Crew2] NVARCHAR (50) NULL,
 [Crew3] NVARCHAR (50) NULL,
 [ShiftType] NVARCHAR (50) NULL,

Steps

Here's the steps that I know I have to do to make this work:

Create a collection (either OnStart or after the Crew Schedule is updated)
- This collection will contain data from the "CrewSchedule" table.
- It will only collect the most recent changes to the schedule.
- There will be a separate row depending on if it Day or Night shift.
- Each row will be location specific.

Use Distinct
- When a driver selects a location, then either Day or Night Shift.
- The rest of the form auto-populates.
- I already know how to use the Distinct formula when using a dropdown menu.

Current Issues

Creating a collection with these specific parameters

This part is straight forward
1. ClearCollect(CachedSchedule, '[dbo].[CrewSchedule]')

My dilemma is this, how does one create a collection that updates based on the latest changes and creates a row that's specific on both location and shift? Since there are 5 locations and two different shifts per location, there should only be 10 rows total. Is there some sort of nested If statement that has to be used when creating this specific Collection?

I appreciate any input on how to solve this problem. I figured that an application like this can help as this can be used for all sorts of businesses that provide a delivery service or conduct on-site work. Thanks!

Brendon

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Verified answer

wyotim 2,545 on at

Like
a
(4)

Report
Copy link

Link copied!
So, essentially, you want the app to have a collection titled CachedSchedule that consistes of ten rows holding the most recent data for each location and shift, right? Just to help me understand: how do you know when the latest changes are made? Is that from the [Date] column or somewhere else? I think I am tracking with your question but I want to make sure.

If it is the [Date] column, you could sort the data in descending order by that column and then use LookUp and Filter to get the record the user is after (assuming Station and ShiftType are the location and shift categories and that the dropdowns used are called LocationDropdown and ShiftTypeDropDown). Something like this:

// Collect the sorted data ClearCollect( CachedSchedule, Sort( '[dbo].[CrewSchedule]', Date, Descending ) ) /* Lookup and collection of filtered row, placed in OnSelect of a button
to confirm the dropdown selections or the OnSelect of each dropdown */ ClearCollect( SelectedSchedule, Filter( CachedSchedule, ID = LookUp( CachedSchedule, Station = LocationDropdown.Selected.Value &&
ShiftType = ShiftTypeDropdown.Selected.Value, ID ) )

If the [Date] column isn't the criteria, you could set the app to refresh the data each time the dropdowns are used or when a confirmation button is pressed (assuming your users will always have a data connection) and then filter by the date (assuming today's date is the date they are after and that each location/shift will share a date).

// Refresh and collect the data, filtered by today's date Refresh('[dbo].[CrewSchedule]'); ClearCollect( CachedSchedule, Filter( '[dbo].[CrewSchedule]', Date = Today() ) )

If I am not fully understanding, just let me know and I will be glad to follow up.

*Edit: fixed a mistake in the sample code*

Was this reply helpful? Yes No
BrendonBrooksP1 329 on at

Like
a
(0)

Report
Copy link

Link copied!

So, essentially, you want the app to have a collection titled CachedSchedule that consistes of ten rows holding the most recent data for each location and shift, right? Just to help me understand: how do you know when the latest changes are made? Is that from the [Date] column or somewhere else? I think I am tracking with your question but I want to make sure.

-The [Date] column with the most recent changes will determine which schedule is the most recent.

The formula provided did work and it defineatley helped clear some things up in regards to Collections. Thank you so much!

Brendon

Was this reply helpful? Yes No
Brian Dang 3,976 on at

Like
a
(1)

Report
Copy link

Link copied!

I like seeing commenting code and indented formulas 🙂

Kudos to you, @wyotim!

Was this reply helpful? Yes No
wyotim 2,545 on at

Like
a
(1)

Report
Copy link

Link copied!

@BrendonBrooksP1You are most welcome!

@mr-dangThank you! The commenting feature is definitely one of my favorite recent additions.

Was this reply helpful? Yes No