I have created a list in SharePoint, called Six Week Forward Plan. This is to be used by my colleagues to 'book' reports that they would like to be presented at our weekly management meeting over a rolling six week period. Each week, there are 14 available slots that can be 'booked'.
As the list works over a rolling six week period, i have built a flow that each week, deletes all items for the meeting which has just passed, then adds 14 available slots for the new sixth week.
I have built an app to be used by my colleagues to 'book' their reports into this forward plan and it works great. However i have thought of a new idea to develop it further and wonder whether it is possible.
I would like a front screen with a button for each of the six weeks. I would then like to use a traffic light system to show the user whether there are available slots within that week. For example, green would indicate available, amber would indicate limited slots and red would indicate fully booked.
Any suggestions for how i could make this work?
Hi @ell1990 ,
Could you please share a bit more about the "available slots" that you mentioned? Do you mean that the available Meeting Rooms in your Outlook?
Where do you store the "available slots" that you mentioned?
Based on the needs that you mentioned, I think the CountRows function could achieve your needs. You could consider use the CountRows function to calculate the left available rooms/slots in current week. If the amount of available rooms/slots is more than 10, color the button in Green, if the amount of available rooms/slots is greater than 0 but less than 5, color the button in amber, if the amount of available rooms/slots is 0, color the button in red.
Within your app, please take a try with the following workaround:
Set the Fill property of the Button in your front screen to following:
If( CountRows('Type your formula to retrieve the available slots left in current week') > 10, RGBA( 0, 128, 0, 1 ), CountRows('Type your formula to retrieve the available slots left in current week') > 0 && CountRows('Type your formula to retrieve the available slots left in current week') < 5, RGBA(255, 255, 255, 1), CountRows('Type your formula to retrieve the available slots left in current week') = 0, RGBA( 255, 0, 0, 1 ) )
More details about checking available rooms in your Outlook, I think the formula within the OnVisible property of RoomSelectScreen in the "Book a Room (New)" template app would help in your scenario:
Thank you for your reply.
I have a gallery in my app connected to a SharePoint document library. Within this document library i have six folders which are named after the date of the meeting.
Within each of these six folders, i have fourteen subfolders, all are titled with 'Available' in their name.
Within the app, i want the six folders named after the date of the meeting, to be colored either green, red or amber, based upon the number of "Available" subfolders within each folder.
So for example, if the folder named "CMB - 17 March 2020" has fourteen subfolders and ten of these still have "Available" in their names, i would want the "CMB - 17 March 2020" item in PowerApps to be coloured green.
Hope that makes sense!
Hi @ell1990 ,
Do you want to achieve your needs in PowerApps canvas app or within SP Library itself?
If you want to achieve your needs in SP Library itself, I afraid that PowerApps could not achieve your needs.
If you list your SP Library folders in your canvas app, and want to color the parent folders (six folders) based on the "Available" sub-folder, please take a try with the following workaround:
Add a Gallery in your app, set the Items property to following:
Filter(MyLibrary1, IsFolder = true, 'Folder path'="MyLibrary1/")
Note: The 'MyLibrary1' represents the SP Library name on my side, you should replace it with your actual SP Library Name.
Filter(MyLibrary1, IsFolder = true, 'Folder path'="YourSPLibraryName/")
Set the TemplateFill property of the above Gallery to following:
If( CountRows( Filter(YourSPLibrary, IsFolder=true, 'Folder path' = "YourSPLibraryName/" & ThisItem.'File name with extension' & "/", "Available" in 'File name with extension') )>=10, RGBA( 0, 128, 0, 1 ), // Color the Gallery item in Green color RGBA(0, 0, 0, 0) )
Please take a try with above solution, then check if the issue solved.
The formula has worked perfectly!
I wonder whether i can incorporate a third colour into it.
So, if there are more than 10 'Available' items, the template fill changes to green. If there are less than 3 'Available' items, the template fill changes to red. Anything else should be amber.
Here is my formula. 'Six Week Forward Plan Booking System' is the name of the SP Document Library.
If(CountRows(Filter('Six Week Forward Plan Booking System', IsFolder = true, 'Folder path' = "Six Week Forward Plan Booking System/" & ThisItem.'File name with extension' & "/", "Available" in 'File name with extension')) >=10, RGBA(146, 208, 80, 0.5), RGBA(255, 204, 0, 0.5))
Read the announcement for more information!
Features releasing from October 2019 through March 2020