Find the duplicate records in a child table based on the details entered in parent table

[KUMAR_NULU]

Hi, 

I have created two tables in dataverse with the table structure as below.. Need help in identifying the duplicate leaves in the child table with the query details provided in leave input form such as EmpID, Leave From and Leave To. Have also shared the input query parameters in 4 scenarios along with the expected outcome for each.

To give more context in in terms of data storage, for every leave application in parent table, am creating multiple child records with each day as a seperate record with the same leave request ID as parent one. (sample data of parent and child tables are provided for reference)  

Would appreciate any help in accomplishing this request. Thanks in advance.

Parent Table : Leave Request	Child Table: Leave Request Details
EmpID
Leave request unique ID (PK)	leave request Id (FK - lookup column)
leave from	leave date
leave to
leave status

Parent Table : Leave Request
EmpID	Leave request unique ID (PK)	leave from (dd-mm-yy)	leave to (dd-mm-yy)	Leave status
1	LR-001	10-06-23	13-06-23	Approved
2	LR-002	08-06-23	10-06-23	Approved
Child Table: Leave Request Details
leave request Id (FK - lookup column)	leave date (dd-mm-yy)
LR-001	10-06-23
LR-001	11-06-23
LR-001	12-06-23
LR-001	13-06-23
LR-002	08-06-23
LR-002	09-06-23
LR-002	10-06-23
	Emp ID	Leave From (dd-mm-yy)	Leave To (dd-mm-yy)	Expected result	Addl. Info
Case 1 :	1	11-06-23	13-06-23	Don't allow to apply leave	Both from and To dates are within the existing leaves
Case 2:	1	08-06-23	12-06-23	Don't allow	To date is with in the existing leaves
Case 3:	1	12-06-23	15-06-23	Don't allow	from date is with in the existing leaves
Case 4:	1	08-06-23	09-06-23	Allow to apply leave	No existing leaves in between from and to dates

[ShaheerAhmad]

In Power Apps, you can achieve this by using formulas and functions in the app's screens and controls. Here's a step-by-step guide to implementing the duplicate leave check:

1. Open your Power App in edit mode and navigate to the screen where you have the leave input form.
2. Identify the controls on the form that capture the EmpID, Leave From, and Leave To values. Make sure to assign suitable names to these controls (e.g., EmpIDInput, LeaveFromInput, LeaveToInput).
3. Add a button control to trigger the duplicate check and assign it an appropriate name (e.g., CheckDuplicateButton).
4. In the OnSelect property of the CheckDuplicateButton, add the following formula:

ClearCollect(
    DuplicateLeaves,
    Filter(
        'Child Table: Leave Request Details',
        leave_date >= DatePicker1.SelectedDate,
        leave_date <= DatePicker2.SelectedDate,
        leave_request_Id = LookUp('Parent Table: Leave Request', leave request unique ID = Dropdown1.Selected.Value).leave request Id
    )
);
If(
    CountRows(DuplicateLeaves) > 0,
    Notify("Duplicate leaves found!", NotificationType.Error),
    Notify("No duplicate leaves found.", NotificationType.Success)
)

Make sure to replace 'Child Table: Leave Request Details' with the actual name of your child table and 'Parent Table: Leave Request' with the actual name of your parent table. Also, adjust the control names (DatePicker1, DatePicker2, Dropdown1) based on your app's controls.

1. Optionally, you can display the details of the duplicate leaves by adding a Label control to your screen and setting its Text property to Concat(DuplicateLeaves, leave_date & ", "). This will concatenate and display the leave dates of the duplicate leaves in the label.

When the CheckDuplicateButton is clicked, the formula executes the following steps:

• It uses the Filter function to retrieve the child table records that fall within the selected Leave From and Leave To dates and have the same leave request ID as the selected value in the parent table.
• The ClearCollect function collects the filtered records into a collection named DuplicateLeaves.
• It checks the CountRows of the DuplicateLeaves collection. If the count is greater than 0, it displays a notification indicating the presence of duplicate leaves; otherwise, it shows a success notification.
• If you added the Label control, it will display the leave dates of the duplicate leaves.

By following these steps, you can implement the duplicate leave check in your Power App and provide appropriate notifications to the user.

[KUMAR_NULU]

Hi @ShaheerAhmad - Thanks for your quick revert.. I also need to pass empID as a query parameter, which is not an attribute in the child table. Leave request ID is having an 1-->n relationship. 

[ShaheerAhmad]

To pass the empID as a query parameter and filter the child table based on it, you can modify the formula in the OnSelect property of the CheckDuplicateButton as follows:

ClearCollect(
    DuplicateLeaves,
    Filter(
        'Child Table: Leave Request Details',
        leave_date >= DatePicker1.SelectedDate,
        leave_date <= DatePicker2.SelectedDate,
        leave_request_Id = LookUp('Parent Table: Leave Request', leave request unique ID = Dropdown1.Selected.Value).leave request Id &&
        empID = QueryParam("empID")
    )
);
If(
    CountRows(DuplicateLeaves) > 0,
    Notify("Duplicate leaves found!", NotificationType.Error),
    Notify("No duplicate leaves found.", NotificationType.Success)
)

Replace 'Child Table: Leave Request Details' and 'Parent Table: Leave Request' with the actual names of your child and parent tables, respectively. Also, adjust the control names (DatePicker1, DatePicker2, Dropdown1, TextInput1) based on your app's controls.

In this modified formula, we added an additional condition to filter the child table records based on the empID value. Assuming you have a TextInput control named TextInput1 on your form where the user enters the empID, we use the TextInput1.Text property to retrieve the entered value and compare it with the empID attribute in the child table.

By including this condition, the Filter function will only include the child table records that match both the leave request ID and empID, ensuring that you're checking for duplicate leaves specific to the selected empID.

Make sure to adjust the formula and control names based on your app's configuration.

[KUMAR_NULU]

Hi @ShaheerAhmad - Sorry for the confusion. in the above formula you have mentioned to provide the leave request ID in the look up function. Ideally this leave request ID (1 or more) should be fetched from the parent table based on the emp id , followed by fetching records of all those leave request ids in child table.  The only search parameters passed to the entire query is empID, Leave From and Leave To inputs.