cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Becca51178
Level: Powered On

I need help pulling data from two sharepoint lists into 1 collection

Someone please help me figure this out

 

I have a primary events list with the name of an upcoming event people can signup for. I have a signup list in Sharepoint that generates everytime a person signs up for an event.

 

List1 = Primary List

List 2= Signup sheet

 

I have a collection

 

ClearCollect(ChartData, {EventName: List1.EventName, VolunteersNeeded: List1.VolunteersNeeded});

 

I just want to add a column to the collection that counts the number of times someone has signed up for an event in List2

 

I want the ChartData Collection to look like this

Column1 =List1.EventName

Column2= List1.VolunteersNeeded

Column3= List2.Count Of Volunteers for each event

 

Please help. I have been working on this all day

 

Becca

10 REPLIES 10
Super User
Super User

Re: I need help pulling data from two sharepoint lists into 1 collection

@Becca51178 

I'd like to help you solve your issue.  This is what you current collection called ChartData looks like

 

ClearCollect(
   ChartData, 
   {
       EventName: List1.EventName, 
       VolunteersNeeded: List1.VolunteersNeeded
   }
);

 

We can use the ADDCOLUMN function to add a new column to ChartData.  The new column will be called CountOfVolunteers.  To find the number of volunteers we will FILTER List2 and wrap the result within the COUNTROWS function.

 

ClearCollect(
   ChartData, 
   {
       EventName: List1.EventName, 
       VolunteersNeeded: List1.VolunteersNeeded
   }
);
AddColumns(
    ChartData,
    "CountOfVolunteers",
    CountRows(Filter(List2,EventName=ChartData[@EventName]))
);

 

...

 

One more thing! In an ideal world all Events would have a unique name.  In real life there are no such guarantees.  I would suggest including the unique event ID in your original collection.  Of course you would also need a column for tracking the EventID in List1 and List2.

 

ClearCollect(
   ChartData, 
   {
       EventID: List1.EventID,
       EventName: List1.EventName, 
       VolunteersNeeded: List1.VolunteersNeeded
   }
);

 

Then we would use the EventID to count the number of volunteers instead of EventName.

 

ClearCollect(
   ChartData, 
   {
       EventID: List1.EventID,
       EventName: List1.EventName, 
       VolunteersNeeded: List1.VolunteersNeeded
   }
);
AddColumns(
    ChartData,
    "CountOfVolunteers",
    CountRows(Filter(List2,EventID=ChartData[@EventID]))
);

 

Best of luck.  Let me know what questions you have!

 

---
Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."

 

Becca51178
Level: Powered On

Re: I need help pulling data from two sharepoint lists into 1 collection

I don't know what I am doing wrong. It keeps giving me the error message attached

 

ClearCollect(
ChartData,
{
    EventName: 'Governance Events Primary List'.EventName,
    VolunteersNeeded: 'Governance Events Primary List'.VolunteersNeeded
}
);
AddColumns(
    ChartData,
   "CountofVolunteers",
    CountRows(Filter('Governance Signup Form_Powerapps',EventName=ChartData[@EventName]))
);

Super User
Super User

Re: I need help pulling data from two sharepoint lists into 1 collection

@Becca51178
This is a delegation warning. It displays to let you know the CountRows function will stop counting after 500 rows (500 volunteers). You can increase the delegation limit to 2000 rows via the PowerApps Advanced Settings. I believe this is under the experimental settings group.

If the event will have fewer than 2000 volunteers you should be fine and can safely ignore the warning. Let me know!
Becca51178
Level: Powered On

Re: I need help pulling data from two sharepoint lists into 1 collection

Still not working, but I think I know what to do. I am heading home, but will pick it up from there

Super User
Super User

Re: I need help pulling data from two sharepoint lists into 1 collection

On closer inspection I also see a 2nd error in your code (Screenshot2). See the small red underline near EventName=ChartData[@EventName]? There should be a second error message. If you can share it we’ll fix the problem together.

I’ll be on later tonight too. Let me know.
Becca51178
Level: Powered On

Re: I need help pulling data from two sharepoint lists into 1 collection

It said I was using the wrong variable. That an equal sign wasn't supposed to be there
Community Support Team
Community Support Team

Re: I need help pulling data from two sharepoint lists into 1 collection

Hi @Becca51178 ,

Do you want to add a additional column in your ChartData collection to record the times for signing up for a specific event?

 

Based on the formula you provided, I think there is something wrong with it. The EventName column and VolunteersNeeded column in your ChartData is a Table value rather than a Text value, so you could not compare the EventName from your ChartData collection with the EventName column in your 'Governance Signup Form_Powerapps' List.

 

I have made a test on my side, please take a try with the following workaround (set OnSelect property of the a button to following😞

Clear(ChartData);
ForAll(
'Governance Events Primary List',
Collect(
ChartData,
{
EventName: 'Governance Events Primary List'[@EventName],
VolunteersNeeded: 'Governance Events Primary List'[@VolunteersNeeded],
CountofVolunteers: CountRows(Filter('Governance Signup Form_Powerapps', EventName = 'Governance Events Primary List[@EventName]))
}
)
)

Please consider take a try with above solution, then check if the issue is solved.

 

More details about the ForAll function, please check the following article:

ForAll function

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: I need help pulling data from two sharepoint lists into 1 collection

@Becca51178 

 

The reason for your error is because this code puts the entire column of data for List1 into the collection.

 

{
    EventName: List1.EventName, 
    VolunteersNeeded: List1.VolunteersNeeded
}

To get only a single field you'd need to do this instead

 

ForAll(
    List1,
    {
        EventName: EventName, 
        VolunteersNeeded: VolunteersNeeded
    }
)

 

So your code should look like this in the end...

 

ClearCollect(
   ChartData, 
       ForAll(
           List1,
           {
               EventName: EventName, 
               VolunteersNeeded: VolunteersNeeded
           }
);
AddColumns(
    ChartData,
    "CountOfVolunteers",
    CountRows(Filter(List2,EventName=ChartData[@EventName]))
);

 

---
Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."

Becca51178
Level: Powered On

Re: I need help pulling data from two sharepoint lists into 1 collection

Ok this part of the code works

 

ClearCollect(ChartData,

ForAll('Governance Events Primary List',

{EventName: 'Governance Events Primary List'.EventName,

VolunteersNeeded: 'Governance Events Primary List'.VolunteersNeeded}));

 

This part is saying that the "=" is an invalid argument type

 

AddColumns(ChartData,"CountofVolunteers",

CountRows(Filter('Governance Signup Form_Powerapps', EventName = ChartData[@EventName])));

 

I am new to powerapps so I am not sure the text vs table conflicts. 

 

 

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (6,081)