Answered

Need a function to write vale from table to collection

(0) Share

Report

Posted on by Vasanthan

Hello Team,

We are trying to write the value from table to collection using add column. For example, we have 3 columns like name, score and date.

For same date, if the user name is twice that should get score sum-up as a single record.

For that i used, ClearCollect(NewCollection,AddColumns(GroupBy(Filter(MasterCollection,Organisation='Organisation Dropdown'.SelectedText.Value&&Date=CurrentDay),"cr73e_username","UserGroup"),"Submitted Pages",Sum(UserGroup,'Submitted Pages'), "Date", ShowColumns(UserGroup, "cr73e_selecteddate")));

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Vasanthan

Can you expand more on your scenario?

As I read your formula, you are grouping a second collection (MasterCollection) by the cr73e_username. Then you are summing the 'Submitted Pages' column (assumed to be numeric) into an additional column called "Submitted Pages". And finally, you are adding a Date column with a table of all the cre73e_selected date values.

What is it that is a problem for you?

Was this reply helpful? Yes No
Vasanthan 20 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Randy Hayes, Exactly what i need is a date column as a value not as a table. Master collection is the collection with all data. Creating a second collection based on groupby using username and summing the pages. Also i need the date as a value, not as a table. Regards, Vasanthan

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Vasanthan

So then the question is...which date do you want? If you end up with 10 rows in the "UserGroup" grouping...which date would you want? First record, last record, 4th record, etc?

Was this reply helpful? Yes No
Vasanthan 20 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

I have two scenarios here,

First Scenario: As far as Daily Basis, Usergroup will have same date for all record. Here we can have any rows.

Second Scenario: As far as monthly basis, Here i need to groupby using date and username. Should sum the pages if the date, username is same. Help me on formula for this scenario.

Hope you understood.

Regards,
Vasanthan

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@Vasanthan

For the first scenario, if the dates in the records are the same and you just need one, then take the first:

ClearCollect( NewCollection, AddColumns( GroupBy( Filter(MasterCollection, Organisation='Organisation Dropdown'.SelectedText.Value && Date=CurrentDay ), "cr73e_username", "UserGroup" ), "Submitted Pages", Sum(UserGroup,'Submitted Pages'), "Date", First(UserGroup).cr73e_selecteddate ) );

For the second scenario, you can just expand your GroupBy to include the date as well:

GroupBy(...<filter here>..., "cr73e_username", "cr73e_selecteddate", "UserGroup")

This will group by user and date and the resulting records will be in UserGroup

Was this reply helpful? Yes No
Vasanthan 20 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

Thank you for your response.
Got the date value in the collection but if we trying to show in the table it is showing in different format.

Regards,
Vasanthan

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@Vasanthan

For tables, you need to format your columns in the Items properly. If it is a formatted date that you want, then you will need to adjust your formula accordingly:

ClearCollect( NewCollection, AddColumns( GroupBy( Filter(MasterCollection, Organisation='Organisation Dropdown'.SelectedText.Value && Date=CurrentDay ), "cr73e_username", "UserGroup" ), "Submitted Pages", Sum(UserGroup,'Submitted Pages'), "Date", Text(First(UserGroup).cr73e_selecteddate, ShortDate) ) );

Was this reply helpful? Yes No