You’re offline. This is a read only version of the page.
345
Morning all,
I'm sure that I don't understanding GroupBy and AddColumns enough to be doing this, so to save myself running in circles and throwing stuff at the wall to see what sticks, I thought I would seek some guidance. I have a sharepoint list that I'm filtering into a collection to remove all blank rows. the resulting data looks like this:
Now what I need is create a list of distinct Items, where item selected to keep has the closest date to today. So far I've tried to group them by Item but i get [Object object] errors when i try to display them in a data table:
Share
ReportClearCollect(
calendarCol,
GroupBy(
AddColumns(
Filter(
calPre,
Not(IsBlank(FAQ_x0020_Item_x0020_Name))
),
"Itm",
Text('FAQ Item Name'.Value),
"Mon",
First(
Sort(
Filter(
calPre,
Month >= Today()
),
Month,
Ascending
)
).Month
),
"Itm",
"Mon"
)
)
I feel like I might be almost there but I can't figure out how to make GroupBy and AddColumns works.
Any help would be appreciated
Hi @EpicTriffid ,
Just checking if you got the result you were looking for on this thread. Happy to help further if not.
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
Thanks @EpicTriffid ,
I see what you are doing now - just had to get my mind around the logic. Firstly if you use a data table (rather than a gallery), you will need to delete the "Data" field as it will be a table. As well, what type of field is 'FAQ Item Name'?
If it is a Single line of Text, you can do this
All responses (
Answers (
ClearCollect(
calendarCol,
AddColumns(
GroupBy(
RenameColumns(
Filter(
calPre,
!IsBlank(FAQ_x0020_Item_x0020_Name)
),
"FAQ_x0020_Item_x0020_Name",
"Item"
),
"Item",
"Data"
),
"Mon",
First(
Sort(
Data,
Month
)
).Month
)
)If it is a Choice field, you will need this
ClearCollect(
calendarCol,
AddColumns(
GroupBy(
AddColumns(
Filter(
calPre,
!IsBlank(FAQ_x0020_Item_x0020_Name.Value)
),
"Item",
FAQ_x0020_Item_x0020_Name.Value
)
"Item",
"Data"
),
"Mon",
First(
Sort(
Data,
Month
)
).Month
)
)
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
Hi @WarrenBelz,
Thanks for the reply. The above makes sense in terms of it not having anything to take all the grouped fields. However, while it is now giving me a distinct list with dates, all the dates are set to tomorrow (13/04/2021), like below:
Thinking that my method of getting only those items with the soonest occurring date was incorrect, i set a variable to work out a month from now and added that into my filter (I also changed the fields name of Itm and Mon to make it easier to understand:
Set(nextMonth, DateAdd(Today(), 1, Months));
ClearCollect(
calendarCol,
GroupBy(
AddColumns(
Filter(
calPre,
!IsBlank(FAQ_x0020_Item_x0020_Name)
),
"FAQItem",
Text('FAQ Item Name'.Value),
"dateMonth",
First(
Sort(
Filter(
calPre,
And(Month >= Today(), Month <= nextMonth)
),
Month,
Ascending
)
).Month
),
"FAQItem",
"dateMonth",
"Data"
)
)
However, this gives the same thing of tomorrows date for all items. I would assume I've made an error in using First but I'm not how to get around it as i can't seem to return any date value without wrapping date in First.
Hi @EpicTriffid ,
You need to add a final column to the GroupBy() to take all the grouped fields (or it will assume Mon is it). Also Month is not a good name for a Field as it is a Reserved Word in Power Apps.
ClearCollect(
calendarCol,
GroupBy(
AddColumns(
Filter(
calPre,
!IsBlank(FAQ_x0020_Item_x0020_Name)
),
"Itm",
'FAQ Item Name',
"Mon",
First(
Sort(
Filter(
calPre,
Month >= Today()
),
Month,
Ascending
)
).Month
),
"Itm",
"Mon",
"Data"
)
)
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
#1
WarrenBelz
146,605
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,946
Most Valuable Professional
