cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lolla70
Helper IV
Helper IV

Date formula is not working

Hi All

 

I created a Sharepoint List that include "Check In Date" and "Next Check In Date"

 

I have a button in the App on "TEXT" I wrote 

If(IsBlank(LookUp('Back to Work Eligibility', Employee.Email=VarUserEmail)),
"Check in Required",
"Next Check-In Date " & Text(DateTimeValue(Text(First('Back to Work Eligibility'). 'Next Check-In Date' )), "[$-en-US]mm/dd/yyyy"))

 

On other button the Patch

Patch(
'Back to Work Eligibility',
Defaults('Back to Work Eligibility'),
{
Title: "Status from " & User().FullName & " on " & Now(),
'Check-In Date': Now(),
'Eligibility Status': EmployeeEmail,
Result: lblResult.Text,
'POC Name': ThisItem.'Employee Name'.DisplayName,
'Next Check-In Date': DateAdd(
Now(),
Value(varCheckInDays),
Days
),

 

The button is not updated the Next Date Button on the APP

When I change the formula to "Last" instead of First it works but then all my colors change to black. Cause I need to get the last Next Check - In Date. In the SharePoint it works but it is not updating in the App????

If(IsBlank(LookUp('Back to Work Eligibility', Employee.Email=VarUserEmail)),
"Check in Required",
"Next Check-In Date " & Text(DateTimeValue(Text(Last('Back to Work Eligibility'). 'Next Check-In Date' )), "[$-en-US]mm/dd/yyyy"))

 

 

Any idea??

On other button the Patch

33 REPLIES 33
RandyHayes
Super User
Super User

@lolla70 

Yes, that was not complete in the first post.  I wanted to make sure you were actually setting the Employee column.

One slight change to your formula would be this below.  It sets the "expensive" office 365 functions (which are not needed) out of the formula and puts the User in a scoped With variable (sometimes directly in the formula this causes issues).  When you set a Person column, you don't need the odata bit any longer and when you set the SharePoint person column, you only need to specify the claims, email and displayname to be the email.  SharePoint ignores all the rest as it will be filling in those values from its own User management system.

With({_user: User()},

    Patch(
        'Back to Work Eligibility',
        Defaults('Back to Work Eligibility'),
        {
          Title: "Status from " & _user.FullName & " on " & Text(Now(), ShortDate),
          'Check-In Date': Now(),
          'Eligibility Status': EmployeeEmail,
          Result: lblResult.Text,
          'POC Name': ThisItem.'Employee Name'.DisplayName,
          'Next Check-In Date': DateAdd(Now(), Value(varCheckInDays), Days),
          Employee: {
              Claims: "i:0#.f|membership|" & Lower(_user.Email),
              Department: "",
              DisplayName: _user.Email,
              Email: _user.Email,
              JobTitle: "",
              Picture: ""
             }
        }
    )
)

 

Now, with this in place, your Text formula should be the following:

With({_email: User().Email},

    If(
        !LookUp('Back to Work Eligibility', StartsWith(Employee.Email, _email), true),
            "Check in Required",
        
            "Next Check-In Date " & 
                Text(
                    First(
                        SortByColumns(
                            Filter('Back to Work Eligibility',
                                StartsWith(Employee.Email, _email)
                            ),
                            'Next Check-In Date',
                            Descending
                         )
                    ).'Next Check-In Date', 
                    ShortDate
                 )
    )
)
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

I changed based on your formula and it give me the date for next date, however I have blue lines. Is this will be an issue later on?

 

RandyHayes
Super User
Super User

@lolla70 

That's all fine, but it is certainly not the formula I provided - and it does not take into account the employee.  Your formula will give you the Last (which will give you delegation issues) of ANY record in your list.

So...see how far that goes for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Thank you so much @RandyHayes Randy, this was a panic for me. It is hard when you a in learning process and Employer wants everything so fast lol :). I am using your solution right now. Your help much appreciated.

RandyHayes
Super User
Super User

@lolla70 

Sorry, responded out of sequence....

So, the thing is, your formula is giving you the delegation warning because of the First function.  However, you are feeding the table of reverse sorted records (which is delegable) to the First function, so technically this should cause no issues in the future.

 

However, to get rid of it (as I know it is bothering)_, change the formula to this:

With({_email: User().Email},

    If(
        !LookUp('Back to Work Eligibility', StartsWith(Employee.Email, _email), true),
            "Check in Required",
        
            "Next Check-In Date " & 
                With({_records:
                         SortByColumns(
                            Filter('Back to Work Eligibility',
                                StartsWith(Employee.Email, _email)
                            ),
                            'Next Check-In Date',
                            Descending
                         )},
                    Text(
                        First(_records).'Next Check-In Date', 
                        ShortDate
                    )
                )
    )
)

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@lolla70 

Absolutely - happy to help.  I tell people not to panic...just come over to the forum and someone will have some advice to help!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hi @RandyHayes 

 

Quick question, If I want the "Check-in Date" as Now and want the "Next Date Check-In" to start next day but at 12:00am. How I change that? I put Today but didn't work

RandyHayes
Super User
Super User

@lolla70 

If you are stating that you want the Next date to be 12am the next day, then this would be in your button OnSelect:

With({_user: User()},

    Patch(
        'Back to Work Eligibility',
        Defaults('Back to Work Eligibility'),
        {
          Title: "Status from " & _user.FullName & " on " & Text(Now(), ShortDate),
          'Check-In Date': Now(),
          'Eligibility Status': EmployeeEmail,
          Result: lblResult.Text,
          'POC Name': ThisItem.'Employee Name'.DisplayName,
          'Next Check-In Date': DateAdd(Today(), 1, Days),
          Employee: {
              Claims: "i:0#.f|membership|" & Lower(_user.Email),
              Department: "",
              DisplayName: _user.Email,
              Email: _user.Email,
              JobTitle: "",
              Picture: ""
             }
        }
    )
)
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

When i did that the date is showing 27 instead of 28

RandyHayes
Super User
Super User

@lolla70 

It's actually correct!  My guess is that you are 3 hours behind in your timezone and the regional settings on your SharePoint site have not been set properly.

You can set your time to UTC as well:

With({_user: User()},

    Patch(
        'Back to Work Eligibility',
        Defaults('Back to Work Eligibility'),
        {
          Title: "Status from " & _user.FullName & " on " & Text(Now(), ShortDate),
          'Check-In Date': Now(),
          'Eligibility Status': EmployeeEmail,
          Result: lblResult.Text,
          'POC Name': ThisItem.'Employee Name'.DisplayName,
          'Next Check-In Date': DateAdd(DateAdd(Today(), 1, Days), TimeZoneOffset(), Minutes)
          Employee: {
              Claims: "i:0#.f|membership|" & Lower(_user.Email),
              Department: "",
              DisplayName: _user.Email,
              Email: _user.Email,
              JobTitle: "",
              Picture: ""
             }
        }
    )
)
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (2,644)