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

Retrieving data from SQL View based on values from SQL Table

Hi All,

I am new to PowerApps and would like some help.

I am trying to create an app that can insert new records and edit existing records from a SQL table.

Where I'm stuck is on an Add screen, there are some fields that have to be retrieved from a SQL View. These fields are non-editable and used for inserting into the record being added to the SQL table.

The record being added has 'Customer Name', 'Job Number' - these are selected from a previous screen, 'Date Created' - which defaults to Today's date (these are also non-editable). Then there are some editable text fields. The issue is trying return the fields from the SQL View which will then be stored in the record being added with the editable fields.

I currently have on the Add screen:

DataSource= '[dbo].[Dim_BI_MonthlyProjectReporting2]_3'

Item= LookUp('[dbo].[Dim_BI_MonthlyProjectReporting2]_3', 'Customer Name' = gvCustomer.Result && 'Job Number' = gvJob.Result && Text('Date Created',"[$-en-US]dd/mm/yyyy"))

On the DataCard for the fields from the view:

Default= LookUp('[dbo].[Vw_Powerapps_MonthlyCustomerJobDate]', 'Prospect No_' = gvCustomer.Result && 'Job No_' = gvJob.Result && Text('Date Entered',"[$-en-US]dd/mm/yyyy") = Text(gvDate, "[$-en-US]dd/mm/yyyy"), 'Time Remaining Charge Days')

The fields for the view are showing up as blank. I checked the view and there are values being returned from the view.

 

Any help would be appreciated.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Retrieving data from SQL View based on values from SQL Table

Hi @GRustean,

 

Can you share a bit more about your issue ?

Are  ‘ Customer Name , Job Number and Date Created from SQL View?

Are  ‘Prospect No and Job No from SQL table?

Do you want to display a field from the SQL View in the editable field from the SQL Table?

 

The fields from SQL View are read-only in PowerApps.  I think that Customer Name’ , Job Number and Date Created should be from SQL View .

Are they primary key columns within the basic tables?

 

Currently, there is a limit in looking up records from a SQL View in PowerApps. You could only LookUp records from a SQL View using a primary key column of your basic tables.

 

You can set Default property to :

LookUp('[dbo].[Vw_Powerapps_MonthlyCustomerJobDate]','primary key ID'=Value(DataCardValue9.Text),'Time Remaining Charge Days')

More details, please refer to :

https://powerapps.microsoft.com/en-us/blog/using-sql-server-views-in-powerapps/

 

Regards,

Eason

Community Support Team _ Eason YU
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
GRustean
Level: Powered On

Re: Retrieving data from SQL View based on values from SQL Table

Hi v-sheyu-msft ,

Thank you for your reply.

Here is the situation.

Screen 1 is a list of ‘Customer Names’ from SQL table ‘Dim_BI_Job’ – the user chooses 1.

Screen 2 is a list of ‘Job Numbers’ that belong to the ‘Customer Name’ from SQL table ‘Dim_BI_Job’ – the user chooses 1.

Screen 3 is a list of ‘Date Created’ that belong to the ‘Job Number’ selected - this is from SQL table ‘Dim_BI_MonthlyProjectReporting2’.

If there are no ‘Date Created’ records for the ‘Job Number’, a blank screen is shown except for the title line where the user can choose to Add a new ‘Date Created’ record for the ‘Job Number’. This will always be for the current date. There are no duplicate ‘Date Created’ records for the ‘Job Number’, ‘Customer Name’ selection.

There are some other fields ‘Time Remaining Charge Days’, ‘TimeRemainingNonChargeDays’ in the ‘Date Created’ record which are non-editable and derived from the View ‘Vw_Powerapps_MonthlyCustomerJobDate’.

So the Add screen has

  • ‘Customer Name’ passed through global variable – non-editable
  • ‘Job Number’ passed through global variable – non-editable
  • ‘Date Created’ passed through global variable – non-editable
  • ‘Time Remaining Charge Days’ – from View ‘Vw_Powerapps_MonthlyCustomerJobDate’ – non-editable
  • ‘TimeRemainingNonChargeDays’ – from View ‘Vw_Powerapps_MonthlyCustomerJobDate’ – non-editable
  • Other NTEXT fields – freeform editable

The issue is trying to display information from 2 data sources (SQL table and SQL view)

The ‘Item’ property is set to

LookUp('[dbo].[Dim_BI_MonthlyProjectReporting2]_3','Customer Name' = gvCustomer.Result && 'Job Number' = gvJob.Result && Text('Date Created',"[$-en-US]dd/mm/yyyy") = Text(gvDate,"[$-en-US]dd/mm/yyyy"))

and the ‘Default’ property for the field ‘Time Remaining Charge Days’ is set to

LookUp('[dbo].[Vw_Powerapps_MonthlyCustomerJobDate]', 'Prospect No_' = gvCustomer.Result && 'Job No_' = gvJob.Result && Text('Date Entered',"[$-en-US]dd/mm/yyyy") = Text(gvDate, "[$-en-US]dd/mm/yyyy"), 'Time Remaining Charge Days')

Nothing is coming through from the View fields.

I have checked the data by running the view in ssms and data is returned.

Any help is greatly appreciated.

 

Regards,

 

George

 

Helpful resources

Announcements
thirdimage

Power Apps 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

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,981)