cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GlennM
Frequent Visitor

Label displays number different than formula calculates

Greetings - hope everyone is having a great holiday season. I'm trying to create an employee performance review application, and it is using two galleries for displaying employees from Office365Users connected to a SharePoint library that contains employee files, including performance reviews. I'm trying to construct an indicator at the employee level if the review for the current year, so I created a collection of the reviews that adds the "Review Year" from the completion date of each performance review. I added a text label with a lookup formula to get the selected employees MAX Review Year from the new collection, but for some employees, the year displayed is one year off. When I look at results in the formula editor the lookup of the MAX Review Year is 2022, but what is displayed in the label on the canvas is 2021.  It appears to only happen to some employees, even though they all are in the same document library and use the same Completion Date column. I have refreshed the connection to the library without impact.  I'm hoping someone can help me solve this mystery.  Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @GlennM ,

What "level" folder are your trying to find ? If for instance you wanted the third level, you just need this

Index(
   Split(
      'Folder path',
      "/"
   ).Result,
   3
).Result

but back to your code - you can make this collection - assuming 'Document Type' is a single select

With(
   {
      wPR:
      Filter(
         'Employee Folder',
         'Document Type'.Value = "Performance Review"
      )
   },
   ClearCollect(
      colLatestReview,
      AddColumns(
         wPR,
         "GalleryName",
         Index(
            Split(
               'Folder path',
               "/"
            ).Result,
            3
         ).Result,
         "ReviewYear",
         Year('Date Completed')
      )
   )
);

then your Label

LookUp(
   Sort(
      colLatestReview,
      ReviewYear,
      Descending
   ),
   GalleryName = galleryKey.Text
).ReviewYear

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

 

 

View solution in original post

4 REPLIES 4
WarrenBelz
Super User
Super User

Hi @GlennM ,

Can you please share your formula in Text and also how many documents are in your Library and your Delegation limit.

GlennM
Frequent Visitor

Here is the label formula: LookUp(colLatestReview,galleryKey.Text = GalleryName, Max(ReviewYear)) where galleryKey.Text is employee name organized by last name, first name, GalleryName is Last Name First Name extracted from library folder paths.

The basic collection for the Review Library is: ClearCollect(colReviews,Filter('Employee Folder',"Performance Review" in 'Document Type'.Value));

The collection that adds review year to colReviews is:  ClearCollect(colLatestReview,AddColumns(colReviews,"GalleryName",Mid('Folder path',Find("/",'Folder path')+1,Find("/",'Folder path',Find("/",'Folder path')+1)-Find("/",'Folder path',Find("/",'Folder path'))-1),"ReviewYear",Year('Date Completed')));

The Employee Folder library itself has a significant number of documents, but the collection filters to only performance reviews, which currently total 302.  I have the data row limit set to 2000.  Hope that helps. 

Hi @GlennM ,

What "level" folder are your trying to find ? If for instance you wanted the third level, you just need this

Index(
   Split(
      'Folder path',
      "/"
   ).Result,
   3
).Result

but back to your code - you can make this collection - assuming 'Document Type' is a single select

With(
   {
      wPR:
      Filter(
         'Employee Folder',
         'Document Type'.Value = "Performance Review"
      )
   },
   ClearCollect(
      colLatestReview,
      AddColumns(
         wPR,
         "GalleryName",
         Index(
            Split(
               'Folder path',
               "/"
            ).Result,
            3
         ).Result,
         "ReviewYear",
         Year('Date Completed')
      )
   )
);

then your Label

LookUp(
   Sort(
      colLatestReview,
      ReviewYear,
      Descending
   ),
   GalleryName = galleryKey.Text
).ReviewYear

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

 

 

GlennM
Frequent Visitor

So you really are a super user, that did the trick. I still have a lot to learn about formulas. Thanks for the help!

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,407)