cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sbgoh
Helper V
Helper V

Query is invalid

Hi,

I have this formula in my label text below. However during runtime the error attached occurs. I cannot figure out what went wrong. Can anyone help? 

sbgoh_0-1601364670402.png

 

 

Sum(ForAll(
Filter(
Filter(
    GP_REQUEST,
    (MFR = MR_cbMfr.Selected.mfr || If(
        IsBlank(MR_cbMfr.Selected.mfr),
        true,
        false
    )) 
	), Stage.Value="Pending")
	,1), Value)

 

9 REPLIES 9
VijayTailor
Resident Rockstar
Resident Rockstar

Hi @sbgoh ,

can you explain a little bit more about what you trying to achieve by this Code?.
more few more details about the problem statement.

 

Thanks,

Hi @VijayTailor,

 

I am trying to count the number of records with certain conditions in the sharepoint list "GP_REQUEST".

VijayTailor
Resident Rockstar
Resident Rockstar

Hi @sbgoh ,

You can go with the below approach.

ClearCollect(RetriveDataCollection,Filter(
Filter(
    GP_REQUEST,
    (MFR = MR_cbMfr.Selected.mfr || If(
        IsBlank(MR_cbMfr.Selected.mfr),
        true,
        false
    )) 
	), Stage.Value="Pending")
	,1), Value)

CountRows(RetriveDataCollection);



Thanks,
Hope help you to resolve your Issue

Is there any limit count for collection?

VijayTailor
Resident Rockstar
Resident Rockstar

No limit for collection, if you use directly with filter query then it will show the delegation warning.

But Power app has a retrieve limit is max 2000 rows. Initially, by default, it will set to 500 but you can change. if you have roes more then 2000 to retrieve then different approach required here.

Can I put this formula in the label.text as there is error?

sbgoh_0-1601368995732.png

 

VijayTailor
Resident Rockstar
Resident Rockstar

Yes
on Label Text. YOu can Use Like below.

Text(CountRows(colCntPending)).
then your Lable display the Count Of Rows.
Thanks

But my error seems to fail at ClearCollect. Do I need to initialize this variable somewhere first?

VijayTailor
Resident Rockstar
Resident Rockstar

No Need to Declare. But you can attach the error screenshot for reference. (Please just cross-check the Filter Query you are passing incorrect ways)

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

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
Top Kudoed Authors
Users online (4,376)