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

3 ACCEPTED SOLUTIONS

Accepted Solutions
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!

View solution in original post

RandyHayes
Super User
Super User

@lolla70 

That is not a problem, that is your date in UTC.  If you write that to the list it will be the UTC time of the date.  When you pull the record back in to display, you can adjust the UTC with the following:

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(
                        With({_utc:First(_records).'Next Check-In Date'},
                           DateAdd(_utc, -TimeZoneOffSet(_utc), Minutes)
                        ),
                        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!

View solution in original post

RandyHayes
Super User
Super User

@lolla70 

I'm not entirely sure I understand where you want to add this.  

Are you stating the the Text would be either "Check In Required", "Next Check-In Date <dateAsDeterminedByFormula>", or "Next Check-In Date <dateAsDeterminedByWorkAtHomeLabel>"??

 

If that is the case, then your formula would be this:

With({_email: User().Email},
    If(!LookUp('Back to Work Eligibility', StartsWith(Employee.Email,_email),true),
        "Check in Required",

        "Next Check-In Date " & 
        
        If(lblResult_1.Text = "Work from Home", 
            DateAdd(Today(), 1, Days),
        
            With({_records: SortByColumns(Filter('Back to Work Eligibility',StartsWith(Employee.Email,_email)), "NextCheck_x002d_InDate", Descending)},
                Text(
                    With({_utc: First(_records).'Next Check-In Date'}, 
                        DateAdd(_utc, TimeZoneOffset(_utc), Minutes)
                    ), 
                    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!

View solution in original post

33 REPLIES 33
RandyHayes
Super User
Super User

@lolla70 

Please consider changing your Text Formula to the following:

If(
    !LookUp('Back to Work Eligibility', Employee.Email=VarUserEmail, true),
        "Check in Required",
        
        "Next Check-In Date " & 
            Text(
                First(
                    SortByColumns(
                        Filter('Back to Work Eligibility',
                            Employee.Email=VarUserEmail
                        )
                        'Next Check-In Date',
                        Descending
                     )
                ), 
                ShortDate
             )
)

 

I hope this is helpful 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!

It didn't work

RandyHayes
Super User
Super User

@lolla70 

Please scrub through the formulas I provide as I type them by hand without the aide of a design editor - there are sometimes syntax issues.

 

Updated formula is:

If(
    !LookUp('Back to Work Eligibility', Employee.Email=VarUserEmail, true),
        "Check in Required",
        
        "Next Check-In Date " & 
            Text(
                First(
                    SortByColumns(
                        Filter('Back to Work Eligibility',
                            Employee.Email=VarUserEmail
                        ),
                        '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!

Tried everything nothing working

RandyHayes
Super User
Super User

@lolla70 

Can you provide some more details on what you have tried and what is or is not working at this point?  It is very difficult to know your entire app and data source information without further details. 

 

Your original post had an image of your list (assuming that is the 'Back to Work Eligibility' list) with the Title, Check in and next check in.  You then reference an unseen column called Employee - I assume this is a Person column - yet you are not setting that column in your Patch formula.  I had assumed you were perhaps doing that someplace else, but if not, this will not give you any results for your formula.


My assumption was that you wanted to show the MOST recent Next Check In Date record value for the Employee.  So, again from above, if you are not setting the Employee column in your patch, then you will never be able to get that value.

 

Am I missing something with your scenario?

 

 

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

Here what I have, I have a record for Employee.

 

RandyHayes
Super User
Super User

@lolla70 

That all looks fine, but my question remains...where are you setting the Employee column?  It is not in your original Patch formula.

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

Do you mean this? 

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
),
Employee: {
'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
Claims: "i:0#.f|membership|" & Lower(Office365Users.MyProfile().Mail),
Department: Office365Users.MyProfile().Department,
DisplayName: Office365Users.MyProfile().Mail,
Email: Office365Users.MyProfile().Mail,
JobTitle: Office365Users.MyProfile().JobTitle,
Picture: ""
}
}
);

Hi

 

I cleared the Cache memory on my Edge Explorer, then changed the "First" to "Last" seems it is working now. Hopefully will keep it working. I will check tomorrow if it will change the date. Thank you so much. finger crossed

 

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

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.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

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
Users online (2,168)