cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Super User III
Super User III

Re: ForAll, Collection and Filter

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Community Support
Community Support

Re: ForAll, Collection and Filter

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

Re: ForAll, Collection and Filter

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

Highlighted
Community Support
Community Support

Re: ForAll, Collection and Filter

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

Re: ForAll, Collection and Filter

Hi 

 

 

 

Highlighted
Community Support
Community Support

Re: ForAll, Collection and Filter

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

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