cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

SQL value in a label

Hi Folks, my DBA has created a custom view for me in a database. I want to use one of the values from that view as a Label in my app.

 

I have tried a number of different ways to display a value, unfortunately, nothing ever shows on the label. The value is a single number and in this case I am trying to get the annual value

 

'[dbo].[_ipvEmployeeLeaveTotals]'.Annual

LookUp('[dbo].[_ipvEmployeeLeaveTotals]'),Annual

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @Anonymous 

This might be a delegation issue. If that's the case, you can solve it by storing the user surname in a variable. To do this, you can add the following to the OnStart property of your app (or OnVisible property of your screen).

 

Set(mySurname, Office365Users.MyProfile().Surname)

You can then try to filter against the variable value.

LookUp('[dbo].[_ipvEmployeeLeaveTotals]',cSurname=mySurname).Annual

If that doesn't work, the first step I would suggest to diagnose the problem would be to add the following to the Text property of a label.

Office365Users.MyProfile().Surname

Does this actually return the surname that you expect?

View solution in original post

5 REPLIES 5
Highlighted
Super User III
Super User III

Hi @Anonymous 

The issue here is that a SQL view returns multiple records. Therefore, the formula we use must include some syntax to specify the row that we want to retrieve.

To return the annual value from the first row in the view, you can use the following formula:

First('[dbo].[_ipvEmployeeLeaveTotals]').Annual

If (for example) your view also returns an EmployeeID and you want to retrieve the Annual value for EmployeeID 8, you would use the formula:

LookUp('[dbo].[_ipvEmployeeLeaveTotals]',EmployeeID=8).Annual
Highlighted
Anonymous
Not applicable

That is awesome the second one lets my get a valid response if i change it to a manual surname

 

LookUp('[dbo].[_ipvEmployeeLeaveTotals]',cSurname="Test").Annual

 

If I try and get it to use the surname of the user it is not working though, I am sure there is just something small wrong with that code 

LookUp('[dbo].[_ipvEmployeeLeaveTotals]',cSurname=Office365Users.MyProfile().Surname).Annual

Any ideas?

 

Highlighted

Hi @Anonymous 

This might be a delegation issue. If that's the case, you can solve it by storing the user surname in a variable. To do this, you can add the following to the OnStart property of your app (or OnVisible property of your screen).

 

Set(mySurname, Office365Users.MyProfile().Surname)

You can then try to filter against the variable value.

LookUp('[dbo].[_ipvEmployeeLeaveTotals]',cSurname=mySurname).Annual

If that doesn't work, the first step I would suggest to diagnose the problem would be to add the following to the Text property of a label.

Office365Users.MyProfile().Surname

Does this actually return the surname that you expect?

View solution in original post

Highlighted
Anonymous
Not applicable

Yes if i create a new label and enter the string below it returns the surname of the user who is using the power app

Office365Users.MyProfile().Surname

 I will give some of the other suggestions a go as well and let you know

Highlighted
Anonymous
Not applicable

Setting the variable of mySurname has worked. It sucks that the extra step is required, i would have thought it could use that value straight from the office365user info but im glad it is working

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (7,307)