Lookup on a date field not working

[___Pool___]

Do you know why the following formula does not return any record from dataverse? Or do you have any working alternative?

Example:

Set(selectedUserShift, LookUp(Shifts, 'Shift User'.domainname = User().Email && Date = Today()));
Set(selectedUserShift, LookUp(Shifts, 'Shift User'.domainname = User().Email && Date = Date(2021,3,22))));

**Update: the following is working but not what I need

Set(selectedUserShift, LookUp(Shifts, 'Shift User'.domainname = User().Email && Date >= Date(2021,3,22))));

Set(selectedUserShift, LookUp(Shifts, 'Shift User'.domainname = User().Email && Date = Text(Date(2021,3,22))))); // using this method it complains about different types but it is working'' 

Set(selectedUserShift, LookUp(Shifts, 'Shift User'.domainname = User().Email && Date = Text(Date(2021,3,22),"yyyy-mm-dd")))); // using this method it complains about different types but it is working, for now this is the solution I have applied but I am not confident of the type error I am receiving I am sure in the future it will have some catastrofic side effect

I have a table called in the dataverse Shift with a date only field called Date that I want to uuse to filter my shifts, the time in my local machine is in the format mm/dd/yyyy, I did not change the format and untill today the LookUp formula was working good...

**Update: take a closer look to this test I did 

Set(
    selectedUserShift,
    LookUp(
        Shifts,
        'Shift User'.domainname = currentSystemUserEmail && Date = Text(selectedDate,"[$-en-US]yyyy-mm-dd")
    ) 
);
//selectedUserShift DOES contain Data

Set(
    selectedUserShift1,
    LookUp(
        Shifts,
        'Shift User'.domainname = currentSystemUserEmail && Date = selectedUserShift.Date
    ) 
);
//selectedUserShift1 DOES NOT contain Data

Thanks

[___Pool___]

ad

[v-jefferni]

Hi @___Pool___ ,

Could you please share more details about your scenario? What do you mean by not working when using your original formula?

I made a test, with a table AAAA which includes a LookUp column from User table and a Date only column. The result shows it could work with the formula below:

Set(selectedUserShift, LookUp(AAAA, User.domainname = varUser.Email && Date = Today()))

 However, it would return a blank variable with any test formula of yours.

Please consider test with below formula to remove the incompatible type error:

Set(selectedUserShift, LookUp(Shifts, 'Shift User'.domainname = User().Email && Date = DateTimeValue(Text(Today(),"[$-en-US]yyyy-mm-dd"),"en-US")))

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[___Pool___]

_Jeffer Ni tx for your answer, the scenario is trivial: in dataverse there is a Shift table with a Date only field. I want to retriveve the single record for MyUser ('Shift User'.domainname = User().Email) and a specific date (i.e. Date=Date(2021,3,22)) but the result of the lookup formula:

Set(selectedUserShift, LookUp(Shifts, 'Shift User'.domainname = User().Email && Date = Today()));

is null\empty even if the record does exist. Due that it works for you I guess it could be related to the local machine time format, or another hint is that the function Today() returns a date and time field while the column in dataverse is DateOnly, this will explain why using >= it is working.

Instead when I use this rough workaround it works:

Set(selectedUserShift, LookUp(Shifts, 'Shift User'.domainname = User().Email && Date = Text(Date(2021,3,22),"yyyy-mm-dd"))));

when I use this formula it does not work

Set(selectedUserShift, LookUp(Shifts, 'Shift User'.domainname = User().Email && Date = DateTimeValue(Text(Today(),"[$-en-US]yyyy-mm-dd"),"en-US")))

[v-jefferni]

Hi @___Pool___ ,

Please test with only the Date condition first. It looks like the Date column behaves like a Text column. Please show us the detail setup of that Date only column, like below:

And the error message of different types like below:

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[___Pool___]

Hi, using the date only filter the result of the lookup is empty:

Set( selectedUserShift,
            LookUp(
                Shifts,
                Date = Date(2021,3,24)
            )
        );

while when I use the text conversion:

Set(
    selectedUserShift,
    LookUp(
        Shifts,
        'Shift User'.domainname = currentSystemUserEmail && Date = Text(selectedDate,"[$-en-US]yyyy-mm-dd")
    )
);

here is the warning I receive:

even if while running the app everything works fine and the lookup formula returns the expected record:

following the definition of the date field:

**Update: using the date converstion to string when I run the app from mobile or desktop the following notification error is displayed (but the app works fine):

The requested operation is invalid. Server Response: Shifts failed: A binary operator with incompatible types was detected. Found operand types 'Edm.DateTimeOffset' and 'Edm.String' for operator kind 'Equal'.

As you can see the error I am receiving at runtime is the same you receive on Power App dev tool, instead in power apps dev tool I receive a different error then yours. Is it possible that you have an upgrade that fixes this issue that I have not installed??

[v-jefferni]

Hi @___Pool___ ,

Error message difference is because of settings:

If turn on the formula level error management in experimental features, you will receive more detailed error messages.

Something more, please try to remove the table from the connections list in Power Apps studio and add it again. Sometimes connections would cause issues when tables were modified. This reminds me, have you modified the table, such as the behavior of the Date column after Date column has already been populated with data?

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[v-jefferni]

Hi @___Pool___ ,

Have you resolved the issue?

If the issue persists, I would suggest you raise a support ticket for further research:

https://powerapps.microsoft.com/en-us/support/

If above post helps, then please consider Accept it as the solution to help the other members find it.

Best regards,
Community Support Team _ Jeffer Ni

[___Pool___]

Dear @v-jefferni thanks for your help, unfortunately the issue with date only field in Dataverse still is affecting my Power App even after removing and adding back the connector. 

That is why I currently developed a workaround:

- Defined a new Field 'Date String' in my table of type string that store the field 'Date' as type string

- Power App uses the 'Date String' to filter the records

The workaround works good!