How to check if a record exists in a sharepoint list with powerapps

[shar27]

Hi, 

I have a power apps form that allows me to add a new record to our SharePoint list.

The submit button uses this logic

If (EditScreen_1_Form.Valid,Navigate(BrowseScreen_1,ScreenTransition.None));
SubmitForm(EditScreen_1_Form);
Refresh(xxxxxx_V2);

The record adds the following: name, project, from(dates) & to(dates). 

 The problem we have is, currently I can submit the same record with dates that can overlap. so for example I can submit a record name, project, and with dates between January 31, 2022 - July 10th, 2023. This is not what I want. I want the form to present a pop-up alert saying, this record already has these dates, please choose different dates. 

I'm not sure how to go about this. I would be grateful for some support on this

[shar27]

Would this catch overlap in dates rather than specific dates? and what happens to my submit button logic. Where i am putting the filter logic?

EXAMPLE

sharepoint list dates = 04/04/23 - 06/08/23

form submit = 06/05/23 -   06/07/23

should throw an error as the record(person) is already engaged within that time period. 

[TimBarnes97]

Guys, these searches only check to see if there are other records with the same from date, but what she is actually saying is she wants the If Statement to return an error if any of the date RANGES in the list overlap with another. For example you could have one event date range which is 07/12/2023-15/12/2023 and another which is 09/12/2023-12/12/2023. In this case even though the from date is not the same the statement still needs to return an error. 

[Talha_Dar]

Hi @shar27,

If you wish to verify whether an item with the same start and end date, along with the same project name, already exists in the list before adding new data, you can use the following code:

If(
    First(
        Filter(
            xxxxxx_V2,
            From = FromDataCardName.SelectedDate,
            To = ToDataCardName.SelectedDate,
            Project = ProjectDataCardName.Text
        )
    ).Project = Blank(),
    If (EditScreen_1_Form.Valid,Navigate(BrowseScreen_1,ScreenTransition.None));
    SubmitForm(EditScreen_1_Form);
    Refresh(xxxxxx_V2);
    Notify("Data Added Successfully");,
    Notify("This record already has these dates, please choose different dates.")
);

The updated code will allow you to check for any duplicate project names with matching start and end dates, and it will perform appropriate actions based on the result. Simply replace "xxxxxx_V2" with the data source name "FromDataCardName" with the form input containing the from date, and "ToDataCardName" with the form input containing the to date. Also, replace "ProjectDataCardName" with the form input containing the project name.

If you found my answer helpful, please consider giving it a thumbs-up or a like. Your feedback is greatly appreciated!

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

[shar27]

I don't see this @"

= DatePicker1.SelectedDate

"as an option, is there a reason why?" also the '=' gets an underline and says the countrows is not supported by the connector?

when i use one of these i get an error message 

[Talha_Dar]

Hi @shar27,

You can check the name of your field by clicking on it, and the name will be highlighted in the side pane. This will help you identify the specific field you are working with and make it easier to reference it in your code or settings.

Also if your data has less than 500 rows, using the CountRows function will work without any issues. However, if your data exceeds 500 rows, the CountRows function may encounter a delegation error.

If you found my answer helpful, please consider giving it a thumbs-up or a like. Your feedback is greatly appreciated!

[shar27]

so it would be this ?

 EditScreen_1_Form_From_Card_Value?

[shar27]

He* Yes, exactly. I wonder if instead blocking the dates in the calendar control would be easier than having to run a pop-up alert. 

I.e you select the user, project and when you click calendar control you can see what dates are disabled?

[Talha_Dar]

I don't believe it is supported in the date picker control.

[Talha_Dar]

If your issue has been resolved, please consider closing this thread to inform others that the problem has been resolved. This will help other users find relevant information more efficiently.

[shar27]

adding this for others 

https://powerusers.microsoft.com/t5/Building-Power-Apps/Filtering-date-range-in-SharePoint-List-gall...