cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hayes3d
Level 8

Filtering Oracle data source using collection - No results?

I have a collection that is created from a user selection from a gallery using checkboxes. Check the box, it adds the item to the collection. Uncheck it removes it. Simple. Collection name: colSelectedMaterialsV2

 

Part of that collection contains a unique number that I want to filter the Oracle table with. The Material number. It could return a single result or multiple.

 

On the next screen, using the OnVisible, I am attempting to create another collection from a Oracle data source filtering with that unique number. If I use "=" and explicitly set the filter value, it works as expected. If I use the "In" filter, I get a delegation warning, but no results.

 

I have tried the following. Technically, both would be sending the same single value of 000000002624943000 for what I currently have in the collection:

No errors, 1 delegation warning highlighting "in", no results. Delegation warning states: The highlighted part of this formula might not work correctly on large data sets.

ClearCollect(colApprvBidVendors,Filter('OracleTable',MATL_NBR in colSelectedMaterialsV2[@MATL_NBR]));

Works, I get no warnings and I get the expected results.

ClearCollect(colApprvBidVendors,Filter('OracleTable',MATL_NBR = "000000002624943000"));


So does delegation using "in" work for Oracle as expected? I can't just get all of the results from the oracle table and then perform the filter as there are over 14,000. Options? Am I doing something incorrect?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Hayes3d
Level 8

Re: Filtering Oracle data source using collection - No results?

I have tried the filter call with both: colSelectedMaterialsV2[@MATL_NBR] and colSelectedMaterialsV2.MATL_NBR with the same results to Oracle: No results and no errors. Just a delegation warning. I have already set the limits to 2000 for delegable queries. Nothing works for Oracle, even though as @mdevaney posted, it shows that Oracle does support it in the documentation since 2017. If it doesn't, that documentation should be updated.

 

I did the same call to SQL using colSelectedMaterialsV2[@MATL_NBR] and it works just fine. So we had to duplicate data to SQL for our POC to work.

View solution in original post

6 REPLIES 6
Hayes3d
Level 8

Re: Filtering Oracle data source using collection - No results?

FYI - We duplicated a small subset of data from the table into SQL, ran the same statement and it returned data 3 items. Exactly what I expected. So it seems either Oracle doesn't support this delegation method or something is not configured correctly for the Oracle table? I'm not sure, not a database guy.

*Edit* - I just found out that the Oracle table is actually called a "Materialized View"? Maybe delegable actions are not supported for those?

Super User
Super User

Re: Filtering Oracle data source using collection - No results?

@Hayes3d 

I do not have the answer to your question, but the documentation for PowerApps definitively states delegation is supported for Oracle databases.

 

"List tables, and create, read, update and delete table rows in an Oracle database after you create a connection and build an app in PowerApps. The Oracle Database connection supports full delegation of filtering, sorting, and other functions but not triggers or stored procedures."

 

A materialized view (MV) is nothing more a snapshot of your database at a point in time containing columns from one or more tables.  MV is a table object, so I wouldn't expect the datasource to be the main issue.

Community Support Team
Community Support Team

Re: Filtering Oracle data source using collection - No results?

Hi @Hayes3d ,

Currently, the Oracle connector is not a delegable data source within PowerApps currently, so you could not delegate the data process from your app to your Oracle connector itself, instead, you could only process your data locally.

 

Do you want to filter records from your Orcale table based on the MATL_NBR column value in your colSelectedMaterialsV2? Based on the formula you provided, I think there is something wrong with it, please consider modify your formula as below:

ClearCollect(
colApprvBidVendors,
Filter('OracleTable',MATL_NBR in colSelectedMaterialsV2.MATL_NBR) /* <-- Please type colSelectedMaterialsV2.MATL_NBR rather than colSelectedMaterialsV2[@MATL_NBR] */
);

and please set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app, then try above formula again, check if the issue is solved.

 

In addition, because the Oracle connector is not a deleable data source within PowerApps, your filtering is done locally, only first 2000 records (maximum value) could be scanned from your Oracle table. If the scanned first 2000 records does not contains the Material number which is in your colSelectedMaterialsV2 collection, then your Filter formula would return empty result.

 

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

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview#non-delegable-limit...

 

In addition, if you want to get rid of this Delegation limits, please check and see if the following blog would help in your scenario:

https://blog.coeo.com/davidmorrison/2017/06/21/powerapps-500-record-limit-delegation-and-how-to-work...

 

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.
Super User
Super User

Re: Filtering Oracle data source using collection - No results?

@v-xida-msft 

 

It looks like I am missing something here...  you said the Oracle connector does not support delegation but the documentation I looked at says an Oracle database does support delegation.  Am I looking at the wrong webpage?  See image below.

 

img5.png

Hayes3d
Level 8

Re: Filtering Oracle data source using collection - No results?

I have tried the filter call with both: colSelectedMaterialsV2[@MATL_NBR] and colSelectedMaterialsV2.MATL_NBR with the same results to Oracle: No results and no errors. Just a delegation warning. I have already set the limits to 2000 for delegable queries. Nothing works for Oracle, even though as @mdevaney posted, it shows that Oracle does support it in the documentation since 2017. If it doesn't, that documentation should be updated.

 

I did the same call to SQL using colSelectedMaterialsV2[@MATL_NBR] and it works just fine. So we had to duplicate data to SQL for our POC to work.

View solution in original post

Super User
Super User

Re: Filtering Oracle data source using collection - No results?

@Hayes3d 

I'm glad that you were able to figure out an alternative.  But I still don't understand why the documentation says Oracle is delegable.  I hope @v-xida-msft can respond to this thread to clarify about the Oracle connector.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,013)