Schedule Creator

[BrendonBrooksP1]

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:

1. 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.

1. 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

1. 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

[wyotim · Accepted solution]

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*

[BrendonBrooksP1]

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

[mr-dang]

I like seeing commenting code and indented formulas 🙂

Kudos to you, @wyotim!

[wyotim]

@BrendonBrooksP1You are most welcome!

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