cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dathuberr
New Member

Error on Lookup in Powerapps data table

Hello,

 

I am trying to create an app to track employee attendance, but an getting an error when using a lookup in a column of a data table.

 

Background: I have two excel tables: 1 - (Name,Shift,Department), 2 - (Employee,Date,Status). Table 1 is general employee info, table 2 is supposed to be a log for each day for each employee (present, sick, vacation, etc). A pic of table 2 is attached.

 

I am creating an app so that employees can request time off and also so supervisors can do bulk attendance at start of the shift. The main problem is in the bulk attendance screen. I had considered a gallery with a dropdown for each employee, but I do not think there is a way to pre-populate the dropdowns for employees that have already submitted prior requests for the day off. Based on that, I am using a data table (see attached). Left column shows employees on the shift and right column I need to lookup if the employee has already requested time off for the day. Supervisors will also be able to double click each row in the table to open a menu to select whether that employee is present or not.

 

Problem: I am using a formula to lookup if each employee has already requested the day off, but I am getting an error - "this formula uses scope, which is not presently supported for evaluation". Is there something wrong with my syntax, or some other way that you would suggest doing this? It is important that the supervisors see the previous requests on this screen so they are not holding out for someone who might be a few minutes late when really they are on vacation.

 

Thanks in advance for any ideas you have

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @dathuberr ,

Based on the AddColumns formula that you mentioned, I think there is something wrong with it. I have made a test on my side, please consider modify your formula as below (set the Items property of the Data Table to following) :

AddColumns(
          tblEmployees,
          "Is Request for Selected Day Off",
          LookUp(tblPts, Employee = tblEmployees[@Name]).Status
)

or

AddColumns(
          tblEmployees,
          "Is Request for Selected Day Off",
          LookUp(tblPts, Employee = tblEmployees[@Name], Status)
)

Then enable the "Is Request for Selected Day Off" field in your Data Table, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-xida-msft
Community Support
Community Support

Hi @dathuberr ,

Could you please share more details about the LookUp formula you used in your app?

Do you mean that the "This formula uses scope, which is not presently supported for evaluation" is an error message?

 

Actually, the "This formula uses scope, which is not presently supported for evaluation" is not an error message, it is just a prompt message from the formula bar intelligence.

 

Based on the needs that you mentioned, I think you are in a right direction, the LookUp function or Filter function could achieve your needs. I have made a test on my side, please consider take a try with the following workaround:

Set the Items property of the Data Table to following:

AddColumns(
           Table1,
           "Is Request For Selected Day Off",
           If(
              IsEmpty(
                      Filter(Table2, Employee = Table1[@Name], Text(Date, "yyyymmdd") = Text(DatePicker1.SelectedDate, "yyyymmdd"))
              ),
              "No",
              "Yes"
           )
)

then enable "Name" column and "Is Request For Selected Day Off" field within the Data Table, then you would see if each employee has already requested time off for the selected date.

 

If you only want to display employees who have requested time off for the selected date in your Data Table, please take a try with the following workaround:

Set the Items property of the Data Table to following:

Filter(
       Table1,
       Name in Filter(Table2, Status = "V - 8hr", Text(Date, "yyyymmdd") = Text(DatePicker1.SelectedDate)).Employee
)

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Kris,

 

Sorry for not posting the formula - I had to login via SSO when I hit submit and there was an error - my text disappeared and I had to retype it.

 

Formula is LookUp(tblPts,Employee = ThisItem.Name,Status). Where tblPts is the second Table in excel with the requests. ThisItem.Name does not seem to work as I expected it in this instance to grab the name for each row in the data table. Is there some other way I should be getting the name from the data table for each row to compare against?

 

I also tried your formula - it works as is to say yes/no if there is a request, but I need to know what type of request it was (Vacation, late arrival, etc.) I tried to modify your formula to:

 

AddColumns(
tblEmployees,
"Is Request for Selected Day Off",
Filter(tblPts, Employee = tblEmployees[@Name]).Status
)

 

The problem is that the output in the column is [object Object] for the people that have a request. I am guessing that is because the filter function returns an array of rows, so the .Status at the end does not work as I had hoped if there is only one request, but I am not sure

 

Note that I took out the date filter just to reduce complexity as I was troubleshooting. I will add back in once the formula works to pull in actual request type.

Hi @dathuberr ,

Based on the AddColumns formula that you mentioned, I think there is something wrong with it. I have made a test on my side, please consider modify your formula as below (set the Items property of the Data Table to following) :

AddColumns(
          tblEmployees,
          "Is Request for Selected Day Off",
          LookUp(tblPts, Employee = tblEmployees[@Name]).Status
)

or

AddColumns(
          tblEmployees,
          "Is Request for Selected Day Off",
          LookUp(tblPts, Employee = tblEmployees[@Name], Status)
)

Then enable the "Is Request for Selected Day Off" field in your Data Table, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,310)