Combining Filter, group by and Concatenate in Gallery

[vaika160]

It seems like it should be simple but I cannot figure this out.
Filter RequestDate >= today()

I have a table. this table has other fields.

RequestDate   Name    Office     Field 1   Field2 ...

2021/04/22    Name1   Office1
2021/04/23      Name2   Office2

2021/04/22      Name3   Office1

2021/04/23      Name4  Office1

What I need to accomplish:

RequestDate    Name                       Office
2021/04/22     Name1 , Name3        Office1
2021/04/23     Name4                     Office1
2021/04/23     Name2                   Office2

Not sure how to do it for these fields only?
Thanks!

[WarrenBelz]

Hi @vaika160 ,

I cannot test this presently, but something like this should do it.

AddColumns(
   GroupBy(
      YourTableName,
      "RequestDate",
      "OtherData"
   ),
   "Name1",
   Concat(
      OtherData,
      Name & ","
   ),
   "Office1",
   First(OtherData).Office
)

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.

[WarrenBelz]

Hi @vaika160 ,

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.

[vaika160]

Hi WarrenBelz,

I really do not understand how to use your suggestion, sorry.

Like in my table name1 and Office1 is the field value.

What other data means? 

Can you, please, explain in more details?

Thank you.

[WarrenBelz]

@vaika160 ,

It will be the Items of your gallery and is using the GroupBy function. Substitute your Table name where I have that and your field name where I have Name.

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.

[WarrenBelz]

Hi @vaika160 ,

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.

[vaika160]

I am still cannot make it work, sorry

[vaika160]

I really thank you for your help and follow up with me on this issue but I can not put 2 & 2 together. 

My SP list has these columns: Employee, DateRequested, Office, ApprovedBy, ApproveDate, CheckSubmitted, SubmittedDate, Passed

I have a collection based on this list : 

ClearCollect(colReservations, Filter(OfficeBooking,  DateRequested >= Today() ) )

 
for this grouping I assume I have to create a new collection based on already existing one as I need the same filter?

So I was trying to follow what you suggested. 

ClearCollect(  colReservationDay,

     AddColumns( 

            GroupBy( colReservations, "DateRequested", "Office" ),

        "EmployeeName", Concat( Employee.DisplayName & "," ),

        "Office",

First(Office)

        )

)

I still do not get what is this and why it has to be used? 

First(OtherData).Office

Thanks a lot.

Appreciate your help.

 Also in Concat function I do not think I need Office added. It should be a separate column.

Please, correct me if I am wrong 

[WarrenBelz]

Hi @vaika160 ,

Firstly, it is very useful to understand the GroupBy() function. The key is that you need a "final" column to hold a table where the "rest of the fields" matching the group are stored. You can then utilize that table for an AddColumns function and do things like totals and counts, or in your case, a Concat list. If you want to group by DateRequested AND Office (all unique combinations of these), you would do this

ClearCollect(  
   colReservationDay,
   AddColumns( 
      GroupBy( 
         colReservations, 
         "DateRequested", 
         "Office",
         "OtherData"
      ),
      "EmployeeName", 
      Concat( 
         OtherData,
         Employee.DisplayName & "," 
      )
   )
)

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.

[vaika160]

Hi @WarrenBelz ,

I am still getting an error that Group By function has some invalid arguments.
I have checked all brackets - they look fine to me.

Thank you for your explanation re: Other Data - that it is kind of a table. It was very helpful.

Thank you.