cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Paolo750
Frequent Visitor

Checking for duplicates when submitting data from a collection

Hi,

I've been asked to create a simple app for staff to book which days they are in the office and to allow colleagues to view when colleagues will be in to allow them to meet up in person (post COVID restrictions).

 

So far I have been able to create a collection for the dates (maximum of a week/5 days each booking) and on hitting the submit button it creates a collection and then submits each date as a separate list item in SharePoint along with the name of the logged on user.

What I need to do next is for each collection item is to check if the user has has already booked the date in the system.  If they HAVE already booked it, move to the next item. If they have NOT already booked it, to record it in SharePoint.

The OnSelect formula I'm using for the submit button is below:

 

ClearCollect(
DateRange,
AddColumns(
FirstN(
[
0,
1,
2,
3,
4
],
DateDiff(
DatePickerStartDate.SelectedDate,
DatePickerEndDate.SelectedDate,
Days
) + 1
),
"Day",
"Day " & (Value + 1),
"Date",
DateAdd(
DatePickerStartDate.SelectedDate,
Value,
Days
)
)
);ForAll(DateRange, Patch('In Office DEV List', Defaults('In Office DEV List'), {Date: Date, Requestor: RequestorDataCardValue.Selected, RequestedBy: RequestedByDataCardValue.Text}))

 

I have an earlier version of the app that allows users to book a single date and checks to see if the date has already been booked by the user. If the date has been booked, a popup is presented otherwise the date is recorded in SharePoint.  The formula for this is below:

 

If(
!IsBlank(
LookUp(
'In Office DEV List',
Date = DateDataCardValue.SelectedDate && RequestedBy = RequestedByDataCardValue.Text
)
),
UpdateContext({PopUp: true}),
SubmitForm(DateForm)
);
ResetForm(DateForm)

 

What I can't seem to do (without errors I can't resolve) is to incorporate the 2nd formula in to the 1st to do the duplicate checking.  In the final formula I do NOT need the popup, it just needs to skip to the next Collection Item.

 

Can anyone advise on how I can either amend the 1st formula to achieve the duplicate checking OR offer an alternative solution to what I am trying to achieve?

 

Please let me know if you need any further info.

Thanks,

Paolo

 

1 ACCEPTED SOLUTION

Accepted Solutions
Paolo750
Frequent Visitor

I found a way to do this and thought I'd share it in case it helps anyone else trying to do something similar.  Rather than try and do it all with 1 long formula I broke the task in to small chunks that run sequentially.

After selecting the 'from date' and 'to date' pickers to set the date range, below is what runs 'OnSelect' for the 'submit' button:

 

//Create new collection to store the dates being submitted and remove any dates on Saturday or Sunday
ClearCollect(
DaysToStore,
Filter(
AddColumns(
[
0,
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
18,
19,
20,
21,
22,
23,
24,
25,
26,
27,
28,
29
],
"Day",
DateAdd(
DatePickerStartDate.SelectedDate,
Value,
Days
)
),
Day <= DatePickerEndDate.SelectedDate,
Weekday(
Day,
StartOfWeek.Saturday
) > 2
)
);

//create new collection of existing booked dates for logged on user.
ClearCollect(
BookedDays,
Sort(
Filter(
ShowColumns(
'In Office DEV List',
"Date",
"RequestedBy",
"Requestor"
),
Date > Today() - 1 && RequestedBy in RequestedByDataCardValue
),
Date,
Ascending
)
);
//Compare requested days with existing dates booked and remove duplicates from the collection
RemoveIf(
DaysToStore,
Day in BookedDays.Date
);
//for the remaining items, submit them to SharePoint and add the Requestors details
ForAll(
DaysToStore,
Patch(
'In Office DEV List',
Defaults('In Office DEV List'),
{
Date: Day,
Requestor: RequestorDataCardValue.Selected,
RequestedBy: RequestedByDataCardValue.Text
}
)
);

View solution in original post

1 REPLY 1
Paolo750
Frequent Visitor

I found a way to do this and thought I'd share it in case it helps anyone else trying to do something similar.  Rather than try and do it all with 1 long formula I broke the task in to small chunks that run sequentially.

After selecting the 'from date' and 'to date' pickers to set the date range, below is what runs 'OnSelect' for the 'submit' button:

 

//Create new collection to store the dates being submitted and remove any dates on Saturday or Sunday
ClearCollect(
DaysToStore,
Filter(
AddColumns(
[
0,
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
18,
19,
20,
21,
22,
23,
24,
25,
26,
27,
28,
29
],
"Day",
DateAdd(
DatePickerStartDate.SelectedDate,
Value,
Days
)
),
Day <= DatePickerEndDate.SelectedDate,
Weekday(
Day,
StartOfWeek.Saturday
) > 2
)
);

//create new collection of existing booked dates for logged on user.
ClearCollect(
BookedDays,
Sort(
Filter(
ShowColumns(
'In Office DEV List',
"Date",
"RequestedBy",
"Requestor"
),
Date > Today() - 1 && RequestedBy in RequestedByDataCardValue
),
Date,
Ascending
)
);
//Compare requested days with existing dates booked and remove duplicates from the collection
RemoveIf(
DaysToStore,
Day in BookedDays.Date
);
//for the remaining items, submit them to SharePoint and add the Requestors details
ForAll(
DaysToStore,
Patch(
'In Office DEV List',
Defaults('In Office DEV List'),
{
Date: Day,
Requestor: RequestorDataCardValue.Selected,
RequestedBy: RequestedByDataCardValue.Text
}
)
);

Helpful resources

Announcements
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.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (3,297)