cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

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
timl
Super User
Super User

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
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?

 

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

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

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
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (981)