lookup through date and title

[Anonymous]

So lets say I create a new share point list
Title
Start Date
End Date
I go to power apps -> customize forums
In the on Save function -> I want to prevent a user from entering a duplicate date and title.

So lets say the title is red,start date 1/11/20,end date is 1/20/20.
And I want to insert data. If a user is submitting a title red, start date 1/19/20, end date 1/25/20. I do not want them to be able to insert it because the title is red and its conflicting with the previous date. vs if they change it to 1/21/20 as their start date they can submit it. 

[Anonymous]

If( DataCardValue1.Text exactin Filter(neiltestlist3, DataCardValue3.SelectedDate <= sharepointcolumnnameENDDATE).Title,

Notify("Date must be outside the range", NotificationType.Warning),
SubmitForm(SharePointForm1)

^^^^^^^^^^
DataCardValue3.SelectedDate = start date

DataCardValue2.SelectedDate = END DATE

That is not what I am wanting but it is close
Lets say theres a date range 11/20/2021 to 11/22/21 already inserted
When a user is create a new row I don't want him to be able to select 20th 21th 22th

Lets say He enters a date range 11/25/2021 to 11/28/21 already inserted
When a user is create a new row I don't want him to be able to select 25th 26th 27th 28th when creating a new start/end date. if that makes sense

[v-siky-msft]

Hi @Anonymous ,

Please try with this code in OnSave property.

If(
IsEmpty(Filter('SP list', Title=TextInput.Text, 'Start Date'>DataCardValue2.SelectedDate || 'End Date' < DataCardValue3.SelectedDate)) && DataCardValue3.SelectedDate<DataCardValue2.SelectedDate,
SubmitForm(FormName),
Notify("Date conflict!",NotificationType.Error)
)

Hope this helps.

Sik

[Anonymous]

If( IsEmpty(Filter(neiltestlist3, Title=DataCardValue1.Text, 'StartDate'>DataCardValue2.SelectedDate || 'EndDate' < DataCardValue3.SelectedDate)) && DataCardValue3.SelectedDate<DataCardValue2.SelectedDate, SubmitForm(SharePointForm1), Notify("Date conflict!",NotificationType.Error) ) 

Didn't work. Regardless of what I am submitting its saying date conflict

[Anonymous]

If(
IsEmpty(Filter(neiltestlist3, Title = DataCardValue1.Text && StartDate = DataCardValue3.SelectedDate)),
SubmitForm(SharePointForm1),
Notify("Date conflict!",NotificationType.Error)
)

The above code works if I wanted to filter out a title and a start date
I want to check if start date and end date insertion doesn't fall in any date range between startdate and end date already in the table

[v-siky-msft · Accepted solution]

Hi @Anonymous ,

Yes, I made some adjust, please try this.

If( IsEmpty(Filter(neiltestlist3, Title = DataCardValue1.Text, (StartDate<DatePicker2.SelectedDate&&StartDate>DatePicker3.SelectedDate)||( EndDate > DatePicker3.SelectedDate&&EndDate < DatePicker2.SelectedDate)||(StartDate<DatePicker3.SelectedDate&& EndDate > DatePicker2.SelectedDate))) && DatePicker3.SelectedDate<DatePicker2.SelectedDate,
SubmitForm(SharePointForm1), Notify("Date conflict!",NotificationType.Error) )

Hope this helps.

Sik

[Anonymous]

If( IsEmpty(Filter(neiltestlist3, Title = DataCardValue1.Text, (StartDate<DataCardValue3.SelectedDate&&StartDate>DataCardValue2.SelectedDate)||( EndDate > DataCardValue2.SelectedDate&&EndDate < DataCardValue3.SelectedDate)||(StartDate<DataCardValue2.SelectedDate&& EndDate > DataCardValue3.SelectedDate))) && DataCardValue2.SelectedDate<DataCardValue3.SelectedDate,
SubmitForm(SharePointForm1), Notify("Date conflict!",NotificationType.Error) )

didn't work
DataCardValue3.SelectedDate = startdate
DataCardValue2.SelectedDate = enddate

[Anonymous]

If( IsEmpty(Filter(neiltestlist3, Title = DataCardValue1.Text, (StartDate < DataCardValue3.SelectedDate) || (EndDate > DataCardValue2.SelectedDate))), SubmitForm(SharePointForm1), Notify("Date conflict!",NotificationType.Error) )

I was playing around with your code.
Wouldn't it be better to do StartDate <= since that will prevent duplicate enteries.  Same with end date.
Even now still struggling getting this all working. 

[Anonymous]

if start to end date is 27th to 30th, I don't want anyone creating a date around that time
So no one can create 26th to 29th (27-30th should already be blocked off)

[v-siky-msft]

Hi @Anonymous ,

I found the usage of DataCardValue3 and DataCardValue2 in your code is different from what I post?

Why is that? The code I post work well on my side,  the filter formula can get all conflict records if the selected date range is occupied.

Sik

[Anonymous]