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

[Becca51178]

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

[mdevaney]

@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]

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]))
);

[mdevaney]

@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]

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

[mdevaney]

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]

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

[v-xida-msft]

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,

[mdevaney]

@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]

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.