GroupBy

[TB05]

Hi,  I have a collection and it is Grouped based on the EmployeeID.  And I'm summing up the pay for the week.

But within that Grouping, there is a comments section.  How do I get all the comments to combine into one field and be distinct?

[gabibalaban]

@TB05 ,

Based on your description, I supose that you grouped by the datasource and sum pay for the week using something like:

AddColumns(
      GroupBy(YourDataSource,"EmployeeID","Result"),
      "PayForTheWeek",
      Sum(Result,PayForTheWeekColumn)
)

 Why don' t you add a new column to concat the comments column from each group by like this:

AddColumns(
      GroupBy(YourDataSource,"EmployeeID","Result"),
      "PayForTheWeek",
      Sum(Result,PayForTheWeekColumn),
      "AllCommentsForEmployeeID",
      Concat(Result,Comments & Char(10))
)

I used Char(10) to put comments each onnew line.

Hope it helps !

[TB05]

That worked great, one other question...so now in the text box, I have the following:

So, is there a way to count the DR and the Tier 1, Tier 2 and have them display like this:

4 DR, 2 Tier 1, 1 Tier 2, 1 Tier 3, 1 MSK or something like that??

Thanks for your quick response.

[gabibalaban · Accepted solution]

@TB05 ,

To obtain that, there is small change in my previous formula. Put "," instead of char(10). I supposed that the delimatator is ",".

And on text property of the label use (you may need to adjust the formula):

With(
    {
        CommentsCollection: Split(
            AllCommentsForEmployeeID,
            ","
        )
    },
    Concat(
        ForAll(
            Distinct(
                CommentsCollection,
                Result
            ) As temp,
            CountIf(
                CommentsCollection,
                Result = temp.Result
            ) & " " & temp.Result
        ),
        Value & ", "
    )
)

[TB05]

Thank you, that worked great!  Only had to change Split(ThisItem.AllComments......

[TB05]

Sorry to bother you, what if I just want to see in the text field, one word.  I have another text field that has 

multiple RPI, RPI, RPI and I only want to show one.  I tried to work your code to show one RPI but not successful.

[gabibalaban · Accepted solution]

So, in other word, you don't need the number of appearence for each word? Check this out...

With(
{
CommentsCollection: Split( 
ThisItem.AllCommentsForEmployeeID,
","
)
},
Concat(
Distinct(
CommentsCollection,
Result
),
Result & ", "
)
)

[TB05]

Thanks Again!  That worked great.  I have applied it to multiple text fields.

Have a great weekend!!