Lookup Function For Date and Time is not working correctly.

[laurenc]

Hello,

I have two separate forms, that connect to the same SharePoint List. I was not having this problem, until the addition of the second form.

Both forms ask for a date and time for the submission of the form. When you click submit, the app is supposed to check the SharePoint list for if a date already exists. 

I had renamed the List that the forms were referencing, but I used the search function to make sure I did not miss any references. 

One form is for Adobe installations, called "Adobe", and the other is for laptop deployments, called "Laptop Deployment Form". The difference between the two forms is a hidden text block that distinguishes which form was filled out. The screens for reach respective form are linked to a button on the main menu. 

This is the code for the submit buttons:

If(!IsBlank(LookUp('Adobe Install and Laptop Deployment','Date and Time'=DateValue4.SelectedDate+Time(Hour(HourValue4.Selected.Value),Minute(MinuteValue4.Selected.Value),0))),Notify("The date and time you selected are unavailable. Please choose a different date or time.",NotificationType.Error,3000);ResetForm(Adobe),Set(MyVar,1);SubmitForm(Adobe); Navigate('Main Menu',ScreenTransition.Fade))

If(!IsBlank(LookUp('Adobe Install and Laptop Deployment','Date and Time'=DateValue5.SelectedDate+Time(Hour(HourValue5.Selected.Value),Minute(MinuteValue5.Selected.Value),0))),Notify("The date and time you selected are unavailable. Please choose a different date or time.",NotificationType.Error,3000);ResetForm('Laptop Deployment Form'),Set(MyVar,1);SubmitForm('Laptop Deployment Form'); Navigate('Main Menu',ScreenTransition.Fade))

The problem that is happening is the submit is not accurately searching the list. It tells me a date and time already exist when one does not, and in some cases, submits a date despite the reservation already existing in the List. I have also noticed that it does not work when attempting to submit times past 12 PM. 13,14,15, etc. (hour times) do not allow me through.

Editing to add that I am now seeing this as well:

There's never been a problem with the time pickers like this before, how is 14 not an hour value?

Switching the order of the syntax did not help this issue, nor has adjusting data row limits (which is set to 500)

I could really use the help, thank you!

[WarrenBelz]

Hi @laurenc ,

Your logic seems sound except maybe trying to match on an precise data/time value - try this first

With(
   {
      _DateTime:
      DateValue4.SelectedDate + 
         Time(
            Hour(HourValue4.Selected.Value),
            Minute(MinuteValue4.Selected.Value),
            0
         )
   },
   If(
      !IsBlank(
         LookUp(
            'Adobe Install and Laptop Deployment',
            'Date and Time' = _DateTime
         )
      ),
      Notify(
         "The date and time you selected are unavailable. Please choose a different date or time.",
         NotificationType.Error,
         3000
      );
      ResetForm(Adobe),
      Set(MyVar,1);
      SubmitForm(Adobe); 
      Navigate(
         'Main Menu',
         ScreenTransition.Fade
      )
   )
)

but the best option may be to make the date a date field and the time text.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[laurenc]

Hi!

Thanks for your suggestion. Unfortunately, I seem to be getting the same results. I weirdly don't have this issue when trying to match strings for names, though. How would you suggest I do the time, if not the drop downs? I only want the end user to be able to select "00" or "30" for the minutes.

Thank you,

Lauren

[WarrenBelz]

Hi @laurenc ,

My suggestion is to store the date in a date field and the time in a text field.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[laurenc]

I have figured it out! I changed the syntax a bit, and this was the resolution:

If(!IsBlank(LookUp('Adobe Install and Laptop Deployment','Date and Time'= DateValue(DateValue4.SelectedDate)+Time(HourValue4.Selected.Value,MinuteValue4.Selected.Value,0))),Notify("The date and time you selected are unavailable. Please choose a different date or time.",NotificationType.Error,3000);ResetForm(Adobe),Set(MyVar,1);SubmitForm(Adobe); Navigate('Main Menu',ScreenTransition.Fade))

I removed the Hour() and Minute() functions. Seems they weren't needed!