Comparing Dataverse DateTime Column and DatePicker Selection

[kent-culpepper]

I am patching a record- whilst populating a Lookup column (which is related to a Calendar table). And the comparison between a dataverse Datetime type column and a DataPicker selection is not working as expected.

The following is the syntax;

Calendar: LookUp(Calendars,Date=dtDate_Daily.SelectedDate)

How might this be accomplished?

[Amik]

@kent-culpepper , try wrapping the Date field with a DateValue function (non-delegable):

LookUp(
    Calendars,
    DateValue(Date) = dtDate_Daily.SelectedDate
)

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan

[poweractivate]

@kent-culpepper 

To avoid delegation issues, try defining two context variables for the current day (midnight) and the next day (midnight) as DateTime based off of your Date only currently SelectedDate, then you can perform a LookUp to find the record that falls within that date range.

This should accurately match the Dataverse DateTime column with the DatePicker selection.

Here's how you can do this:

1. Set Context Variables for Current Day and Next Day as DateTime:

(do this right before the LookUp)

UpdateContext({
  varCurrentDay: DateTimeValue(Text(dtDate_Daily.SelectedDate, "[$-en-US]yyyy-MM-dd") & "T00:00:00"),
  varNextDay: DateTimeValue(Text(DateAdd(dtDate_Daily.SelectedDate, 1, TimeUnit.Days), "[$-en-US]yyyy-MM-dd") & "T00:00:00")
})

2. Perform the LookUp Using the Context Variables:

Calendar: LookUp(Calendars, Date >= varCurrentDay && Date < varNextDay)

This approach makes sure that the record's DateTime value falls within the specified range for the selected date.

It should only match the records that have a date component essentially equal to the selected date from the DatePicker control, as if ignoring the time component.

By using the greater than or equal (>=) and less than (<) operators, you can accurately identify the correct record without having to manipulate the DateTime column in Dataverse or resort to complex solutions like to create an additional DateOnly column in Dataverse Table that has the Date only component of the DateTime you already have and updating all the records you already have to match, and creating new records with a Date only and a DateTime.

Note that if you have multiple records that may match the same date range within the same day, you may have to use Filter instead of LookUp, and use other criteria to determine what would happen. Even if you resorted to creating a new DateValue only column in Dataverse, you still have to use Filter instead of LookUp if you need to process multiple potential same-date matches. The above idea may allow you to avoid having to create that additional DateOnly Dataverse column and still use LookUp or Filter without DateValue and without delegation issue.

See if it helps @kent-culpepper 

[kent-culpepper]

That doesn't work.

[kent-culpepper]

The DatePicker is a gallery control. This solution would work in this scenario?

[poweractivate]

@kent-culpepper 

By Gallery Control you mean that you have the Date Picker nested within the Gallery and it repeats for each Record you have in Gallery?

Even if so it may still work actually, but if the date picker is not related at all to the use of ThisItem of Gallery in any way from anywhere in the Record scope of Gallery, and it just does LookUp / Filter on some potentially other matching record(s) not related to ThisItem, then this Date Picker Control should ideally be moved outside of the Gallery so as not to cause confusion and unnecessarily degrade performance by being unused for current record. Although if that Date picker does have to be used for the current record, you may have to leave it there and my suggestion should still work.

[kent-culpepper]

End-user can edit the date of an entry and then save their changes.

[kent-culpepper]

That returned an error on the Save (Patch).

"Internal error in the ForAll function: TypeError: Cannot convert undefined or null to object."

[poweractivate]

@kent-culpepper 

If so you should not be performing the LookUp at all by Date if it's from within a property of an item that is nested within the Gallery.

Just apply the change directly to ThisItem.
if you have to do a LookUp the most you should do is LookUp(Calendar, CalendarPrimaryKeyColumn = ThisItem.CalendarPrimaryKeyColumn)
However its best patch directly to the current Item of DataSource the changes without using a LookUp function actually, like this:

Patch(Calendar,{CalendarPrimaryKeyColumnNameInDataverse:ThisItem.CalendarPrimaryKeyColumnNameInDataverse,newDateTime:yourNewDateTime}

If the table name is Accounts, the primary key column name in Dataverse is usually something like the singular version, or Account.

Not sure what it is for you for Calendar, you can try and check it on your end.

See if it helps @kent-culpepper 

[poweractivate]

@kent-culpepper 
If you already have the ThisItem scope on using a nested control, you should actually not even look it up, try this instead:

Patch(Calendar,{CalendarPrimaryKeyColumnNameInDataverse:ThisItem.CalendarPrimaryKeyColumnNameInDataverse,newDateTime:yourNewDateTime}

//If the Dataverse table name is Accounts for examples, the primary key column name is usually Account. Such as:
//Patch(Accounts,{Account:ThisItem.Account,newDateTime:yourNewDateTime}
//Not sure what it is for you for Calendar table, you can try and check it on your end.