Hello! I'm hoping to get some assistance with this item in PowerApps that I am trying to do.
I have a SharePoint called Tracker that has columns for StartTime, EndTime, and Item (Item is Title). StartTime and EndTime are both "Date and Time" columns, while everything else is numbers.
There are multiple items (A, B, C) that each have start and end times.
I have a second SharePoint called Counts that has column DateTime that is also a "Date and Time" column, as well as columns for "Total", "Item A", "Item B", and "Item C".
I want to have three galleries that are nested within each other.
The first gallery is for the headers - it collects the dates and times, specifically from the EndTime column. It creates a gallery that looks like:
Date: 11/1/2023 | Time: 10:00
Date: 11/1/2023 | Time: 11:00
Date: 11/1/2023 | Time: 12:00
etc.
This gallery seems to work properly.
OnStart (App)
ClearCollect(
colTracker,AddColumns('Tracker',"Distinct_Time", Hour(EndTime),"Distinct_Date",DateValue(EndTime)))
ClearCollect(
colCombined,Counts,'Tracker')
Gallery 1 - Items
GroupBy(colTracker,"Distinct_Date","Distinct_Time","DATA")
Gallery 1 - Text Label
"Date: " &(ThisItem.Distinct_Date)&" | Time: " &(ThisItem.Distinct_Time) &":00"
The second gallery is for the counts of each item - it should collect the counts of how many rows have a title of that item that falls within those times. It should look something like this:
Date: 11/1/2023 | Time: 10:00All Items: 18Item A: 6Item B: 6Item C: 6
Date: 11/1/2023 | Time: 11:00All Items: 20Item A: 4Item B: 7Item C: 9
Date: 11/1/2023 | Time: 12:00All Items: 9Item A: 3Item B: 3Item C: 3
Gallery 2 - Items
ThisItem.DATA
Gallery 2 - Text Label (All)
CountRows(Filter(colTracker,Hour(ThisItem.EndTime)=Distinct_Time&&DateValue(ThisItem.EndTime)=Distinct_Date))
Gallery 2 - Text Label (Item A)
CountRows(Filter(colTracker,Hour(ThisItem.EndTime)=Distinct_Time&&DateValue(ThisItem.EndTime)=Distinct_Date&&Title="Item A"))
Gallery 2 - Text Label (Item B)
CountRows(Filter(colTracker,Hour(ThisItem.EndTime)=Distinct_Time&&DateValue(ThisItem.EndTime)=Distinct_Date&&Title="Item B"))
Gallery 2 - Text Label (Item C)
CountRows(Filter(colTracker,Hour(ThisItem.EndTime)=Distinct_Time&&DateValue(ThisItem.EndTime)=Distinct_Date&&Title="Item C"))
This gallery calculates correctly, but it repeats itself. For example, I get 18 repeating items for 11/1/23 at 10:00.
How can I make the data only appear once? I have tried using Distinct() but I get a series of errors when I try that.
Here are a few things I've tried:
CountRows(Filter(Distinct(colTracker,Hour(ThisItem.EndTime)=Distinct_Time&&DateValue(ThisItem.EndTime)=Distinct_Date)))
Invalid number of arguments: received 1, expected 2 or more.
CountRows(Distinct(Filter(colTracker,Hour(ThisItem.EndTime)=Distinct_Time&&DateValue(ThisItem.EndTime)=Distinct_Date)))
Invalid number of arguments: received 1, expected 2 or more.
Distinct(CountRows(Filter(colTracker,Hour(ThisItem.EndTime)=Distinct_Time&&DateValue(ThisItem.EndTime)=Distinct_Date)))
Invalid number of arguments: received 1, expected 2 or more.
Distinct(ThisItem.DATA)
Invalid number of arguments: received 1, expected 2.
Distinct(ThisItem.DATA,EndTime)
This causes the text label to show as: "Name isn't valid. EndTime isn't recognized"
The third gallery comes from SharePoint Counts and ideally would match the counts (not row counts, these are just numbers) to the date/time.
Gallery 3 - Items
colCombined
Gallery 3 - Text Label (Item A)
If(ThisItem.DateTime=ThisItem.EndTime,'Item A')
This one I am struggling to find the code for both the items and the text labels themselves. I think I need to be combining it back to the distinct time I used above, but I'm not sure. I know that the data is not connecting properly because I can't see them in the table.
Any suggestions on how to get the values I am looking for?
Thank you! Much appreciated for any information on both.
