cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

ForAll, Collection and Filter

Hi,

 

I have a collection named WellDataCollection, which holds certain records.

There is another table named Runs which has some data which I need to extract using WellId which exist in WellDataCollection.

Since WellDataCollection.WellId did not work, I copied this collection into a gallery named WellDataCollectionGallery.

This is the piece of code that I wrote:

 

If (
!IsEmpty(WellDataCollection),
ForAll(
WellDataCollectionGallery.AllItems,
Notify(
GUID(WellDataCollectionGallery.Selected.WellID),
NotificationType.Information
);
Collect(
ExistingRunsCollection,
Filter(
'[dbo].[DHTRRunsDetails]',
WellId = GUID(WellDataCollectionGallery.Selected.WellID)
)
)
)
);

 

The problem is that even within the ForAll loop, WellDataCollectionGallery.Selected.WellID always returns with first item.

Unsure on how do I go to the next item in the list.

Also, is there any way in which I can do it without copying the collection in a gallery?

 

Any inputs will be greatly appreciated.

 

Thanks

--Ash

6 REPLIES 6
RandyHayes
Super User III
Super User III

@Anonymous 

The issue is that you are referring to the selected item of a Gallery, which will not change in a ForAll function.

Your formula can use the values of each record in the ForAll.  In other words, your ForAll implements the formula you have for each record of the datasource that you reference with your ForAll.

So, if your WellDataCollection Gallery has a column in it named WellID, then you can simply reference that in your ForAll.

Also, because you are referencing the WellID in your source and you are also using it to compare for your Filter function, you will (easiest way) need to rename the column so there is no ambiguity in the reference to WellID.

 

Your formula would be as follows:

If (
    !IsEmpty(WellDataCollection),
         ForAll(
             RenameColumns(
                     WellDataCollectionGallery.AllItems,
                     "WellID", "loopWellID"),
                  Notify(
                       GUID(loopWellID),
                       NotificationType.Information
                   );
                  Collect(
                      ExistingRunsCollection,
                      Filter(
                           '[dbo].[DHTRRunsDetails]',
                          WellId = GUID(loopWellID)
                        )
                  )
           )
  );

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
v-xida-msft
Community Support
Community Support

Hi @Anonymous ,

Could you please share a bit more about your scenario?

Do you want to extract the records from your SQL Table ('[dbo].[DHTRRunsDetails]') based on the WellId column value in your WellDataCollection?

 

Based on the formula that you mentioned, I think there is something wrong with it. Please consider modify your formula as below (set the OnSelect property of a button control to following😞

If (
    !IsEmpty(WellDataCollection),
    ForAll(
            RenameColumns(WellDataCollectionGallery.AllItems, "WellID", "WellID1"),  /* <-- Modify formula here */
            Notify(
                   GUID(WellID1),
                   NotificationType.Information
            );Collect(
                      ExistingRunsCollection,
                      Filter(
                             '[dbo].[DHTRRunsDetails]',
                              WellId = GUID(WellID1)
                      )
           )
    )
)

 

Actually, I think it is not necessary to add a Gallery control to list all records of your WellDataCollection, you could extract the records from your SQL Table based on the WellID column value in your WellDataCollection directly.

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

ForAll(
       WellDataCollection,
Notify(
GUID(WellDataCollection[@WellID]),
NotificationType.Information
);Collect(
ExistingRunsCollection,
Filter('[dbo].[DHTRRunsDetails]', WellId = GUID(WellDataCollection[@WellID]))
) )

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.
Anonymous
Not applicable

Thank you so much for your reply.

The piece of code does work, however, it throws a delegation warning at WellId = GUID(loopWellID). I am indeed working with large data sets, so this can be a potential problem.

Is there any other way to go about this?

 

Thanks in advance

--AShep

Hi @Anonymous ,

Is the WellID column a GUID type column in your SQL Table?

How many records existed in your SQL Table? More than 2000 records?

 

If the amount of your SQL Table records is not more than 2000, you could ignore this Delegation warning issue. Based on the issue that you mentioned, I think this issue may be related to the GUID() function.

 

If you have faced a Delegation warning issue with your formula, it means that you could not delegate the data process to your SQL Table, instead, you could only process data locally within your app. In default, you could only process 500 records locally at most. You could change the "Data row limit for Non-delegable queries" option to maximum vlaue -- 2000.

More details about the Delegation in PowerApps, please check the following article:

Delegation

 

If the amount of your SQL Table records is more than 2000, please check and see if the following solution would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M71518

On your side, please consider take a try with the following workaround (I assume that the amount of your SQL Table records is not more than 10000, and you add a Index column (may be a IDENTITY(1,1) column) in your SQL Table to store the row index, 1, 2, 3, 4, 5, 6, 7, ... , 9999, 10000😞

Set the OnStart property of the App control to following:

Concurrent(
           ClearCollect(col1, Filter('[dbo].[DHTRRunsDetails]', recordID >= 1 && recordID <= 2000)),
           ClearCollect(col2, Filter('[dbo].[DHTRRunsDetails]', recordID >= 2001 && recordID <= 4000)),
           ClearCollect(col3, Filter('[dbo].[DHTRRunsDetails]', recordID >= 4001 && recordID <= 6000)),
           ClearCollect(col4, Filter('[dbo].[DHTRRunsDetails]', recordID >= 6001 && recordID <= 8000)),
           ClearCollect(col5, Filter('[dbo].[DHTRRunsDetails]', recordID >= 8001 && recordID <= 10000))
);
ClearCollect(
MergedCollection, col1, col2, col3, col4, col5 )

Then modify your formula as below:

ForAll(
       WellDataCollection,
Notify(
GUID(WellDataCollection[@WellID]),
NotificationType.Information
);Collect(
ExistingRunsCollection,
Filter(MergedCollection, WellId = GUID(WellDataCollection[@WellID]))
) )

Note: Please make sure that you have changed the "Data row limit for Non-delegable queries" option to maximum value -- 2000.

 

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

 

If you have solved your problem, please go ahead to click "Accept as Solution" to identify this thread has been solved.

 

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.
Anonymous
Not applicable

Hi 

 

 

 

Hi @Anonymous ,

Please take a try with the following workaround (set the OnSelect property of a button control to following😞

ForAll(
       WellDataCollection,
Notify(
GUID(WellDataCollection[@WellID]),
NotificationType.Information
);Collect(
ExistingRunsCollection,
Filter('[dbo].[DHTRRunsDetails]', WellDataCollection[@WellID] in Text(WellId))
) )

 

Please consider take a try with above formula, then check if the Delegation warning issue is fixed.

 

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.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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

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.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,876)