how to fetch only required data to Power APP using oracle Database Connection

[LohithaSannathi]

Hi

I have oracle  On-Premise database, so i have installed gateway and able to connect to oracle database.

Now i wanted to pass SQL query to fetch the required data based on the selection value in power app  drop down fields from oracle database. 

how to achieve this any idea.

Quick responses are highly appreciated.

Thanks

Lohitha

[yashag2255]

Hi @LohithaSannathi 

You can use Filter() expression to achieve this:

Filter(DataSourceName, ColumnName = DropdownName.Selected.ColumnName)

For more info, you can refer: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[LohithaSannathi]

@yashag2255 

Hi 

Filter(DataSourceName, ColumnName = DropdownName.Selected.ColumnName) 

above mentioned filter is possible with single table right?

This is not my Question

Below is my Question

In PowerAPP , i click on connectors - oracle database - select the choice of database for oracle( dev or test)-  then it populates all the tables - here i need to select tables ( is it mandatory to select only one table?) , i wanted to select multiple tables as per my requirement , i wanted to pass sql query with joins and where conditions for the tables i have selected and fetch the data into couple of fields

For Example 

Suppose i wanted to select tables 'Suppliers_all' and 'Supplier_Sites_all'

I have field names (Dropdown fields) - Supplier name,address,site code  (when ever i select particular supplier name ,address and site against each supplier name should be automatically populated / internally filtered to address and Site code dropdown fields

( as we have large chunk of data wanted to filter them by Oracle SQL Queries)

how do i achieve this , Im newbie to powerapps , so trying to figure out things.

quick responses are highly appreciated.

Thanks

Lohitha

[yashag2255]

Hi @LohithaSannathi 

In PowerAPP , i click on connectors - oracle database - select the choice of database for oracle( dev or test)- then it populates all the tables - here i need to select tables ( is it mandatory to select only one table?) , i wanted to select multiple tables as per my requirement , i wanted to pass sql query with joins and where conditions for the tables i have selected and fetch the data into couple of fields
--Yes you can select multiple tables and use them in the filter() function.

You can use Filter on multiple tables with combination of AddColumns() function to join the tables based on related data.

If you can share more information about the schema of these two tables, we might be able to help you better.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[LohithaSannathi]

@yashag2255 

I have 2 tables

op_unit and Supplier_sites . I have attached schema in excel sheet.

Thanks

Lohitha

[yashag2255]

Hi @LohithaSannathi 

In case you want to select short_name from op_unit from the dropdown and based on that you want to filter out records from the supplier_sites, you can use the expression as:

Filter(supplier_sites, org_id = LookUp(op_unit, short_name = Dropdown1.Selected.ColumnName).Org_id)

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[LohithaSannathi]

Hi 

@yashag2255 

I have thousands of records based on the selection , in this case filter is giving me performance issues. Is there any other to achieve the same.

Thanks

Lohitha 

[yashag2255]

Hi @LohithaSannathi 

You can either create Views in the Oracle table or can have multiple filters applied in the formula to achieve your needs. If you could share more about the solution that you need, we would be able to help you better.

Hope this helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[LohithaSannathi]

Hi  @yashag2255 

As suggested by you , i have created oracle view GETS_SUPPLIER_DETAILS

I have attached the query of View.

now my requirement is to fetch data based on the Org name choosen in the power apps 

Please see pic 1 (i have created this app based on sharepoint list).

i have org Name (lov) - GEINIME, GEINIMT

when i ever i choose GEINIME in org name based on that Supplier GSL Number( shown in pic 2) should be fetched into "Supplier GSL Number"  (of course it will be more than one " Supplier GSL Number " from view GETS_SUPPLIER_DETAILS

Similary with GEINIMT, As i have thousands of records request you to please suggest the best possible way to achieve this

Can you please let me know your thoughts and how to achieve this. Quick responses are highly appreciated.

Thanks

Lohitha