cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LohithaSannathi
Level: Powered On

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

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

8 REPLIES 8
Highlighted
Dual Super User
Dual Super User

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

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
Level: Powered On

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

@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

Dual Super User
Dual Super User

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

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
Level: Powered On

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

@yashag2255 

 

I have 2 tables

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

 

Thanks

Lohitha

Dual Super User
Dual Super User

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

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
Level: Powered On

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

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 

Dual Super User
Dual Super User

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

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
Level: Powered On

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

 

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

 

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (8,534)