Retrieving data from SQL View based on values from SQL Table

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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.

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (0)

Sort by

v-sheyu-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous,

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No