Date formula is not working

[lolla70]

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

[RandyHayes · Accepted solution]

@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
                 )
    )
)

[lolla70]

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]

@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.

[lolla70]

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]

@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
                    )
                )
    )
)

[RandyHayes]

@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!

[lolla70]

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]

@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: ""
             }
        }
    )
)

[lolla70]

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

[RandyHayes]

@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: ""
             }
        }
    )
)