Filtering Oracle data source using collection - No results?

[Hayes3d]

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?

[Hayes3d]

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?

[mdevaney]

@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.

[v-xida-msft]

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,

[mdevaney]

@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.

[Hayes3d · Accepted solution]

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.

[mdevaney]

@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.