cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marpisa
Frequent Visitor

Collection CountRows With "In" Condition

Hi everybody,

I have a collection called AuditItems

In This Collection I store data in a column called RefElement

 

All The possible RefElements values are stored in a table called dbo.Elements

The dbo.Element Table has this structure:

[...]

[KeyElement] (PK)
[RefScope]   (FK)

[...]

 

I want to count all the items in the AuditItems collection that belongs to same dbo.Elements.RefScope.

 

In SQL I will write a query like that:

 

"SELECT COUNT(RefElement) FROM AuditItems  WHERE RefElement IN (Select KeyElement FROM dbo.Elements WHERE RefScope = [MyRefScope])"

 

In powerapps I've tried to do this:

 

CountRows(AuditItems;Filter(AuditItems.RefElement in Search('[dbo].[Elements]';Filter(RefScope = ThisItem.KeyScope))))

 

but it returns me many errors.

 

Where am I wrong?

 

Thanks

Marco

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
PowerAddict
Super User
Super User

To simulate your use case, I created 2 collections: 

ClearCollect(
    AuditItems,
    {
        RefElements1: "A",
        Column: 1
    },
    {
        RefElements1: "B",
        Column: 2
    },
    {
        RefElements1: "C",
        Column: 3
    },
    {
        RefElements1: "A",
        Column: 4
    }
);
ClearCollect(
    Elements,
    {RefElements2: "A"},
    {RefElements2: "B"},
    {RefElements2: "C"}
);

 

I then tried to capture the results in another collection using the following expression: 

ForAll(
    Elements,
    Collect(
        CountColl,
        {
            RefElements3: RefElements2,
            CountRefElements: CountIf(
                AuditItems,
                RefElements1 = RefElements2
            )
        }
    )
)

 

This is how my collection looks like: 

CountColl.JPG

 

Let me know if this helps. 

 

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

 

Thanks!

Hardit Bhatia

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

View solution in original post

v-xida-msft
Community Support
Community Support

Hi @marpisa ,

Based on the formula that you mentioned, I think there is something wrong with it.

 

I have made a test on my side, please consider modify your formula as below:

CountRows(
   Filter(
       AuditItems;
       RefElement in Filter(
                             '[dbo].[Elements]',
                              RefScope = ThisItem.KeyScope
                     ).KeyElement
   )
)

 

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

 

Best reagrds,

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.

View solution in original post

3 REPLIES 3
PowerAddict
Super User
Super User

To simulate your use case, I created 2 collections: 

ClearCollect(
    AuditItems,
    {
        RefElements1: "A",
        Column: 1
    },
    {
        RefElements1: "B",
        Column: 2
    },
    {
        RefElements1: "C",
        Column: 3
    },
    {
        RefElements1: "A",
        Column: 4
    }
);
ClearCollect(
    Elements,
    {RefElements2: "A"},
    {RefElements2: "B"},
    {RefElements2: "C"}
);

 

I then tried to capture the results in another collection using the following expression: 

ForAll(
    Elements,
    Collect(
        CountColl,
        {
            RefElements3: RefElements2,
            CountRefElements: CountIf(
                AuditItems,
                RefElements1 = RefElements2
            )
        }
    )
)

 

This is how my collection looks like: 

CountColl.JPG

 

Let me know if this helps. 

 

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

 

Thanks!

Hardit Bhatia

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

View solution in original post

v-xida-msft
Community Support
Community Support

Hi @marpisa ,

Based on the formula that you mentioned, I think there is something wrong with it.

 

I have made a test on my side, please consider modify your formula as below:

CountRows(
   Filter(
       AuditItems;
       RefElement in Filter(
                             '[dbo].[Elements]',
                              RefScope = ThisItem.KeyScope
                     ).KeyElement
   )
)

 

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

 

Best reagrds,

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.

View solution in original post

@marpisa

There are 2 slightly diff approaches. Feel free to pick the one that helped you get to your solution.

Thanks,
Hardit Bhatia

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,699)