cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Iamglt
Helper V
Helper V

Checking to see if email already exists in list before saving a record

Hi,

I have been trying to get some code working so that my data table which is attached to a form will check before it allows a user to be added to the datatable.   The rule needs to be that if the user already exists, a message like the following will be displayed:

Iamglt_0-1612408771687.png

So the screen looks like the following:

Iamglt_1-1612409011323.png

So basically, if someone tries to add user Test Account30 again, it should display the red message above.  The code that I have tried so far can detect zero instances of the record, but I also want it to be able to detect 1 instance of the record as well (in other words, the record can be a new record or an existing record) before it throws the error above.

The code I have used so far is:

 

Set(
    varRecordDuplicate,
    LookUp(
        NewEmployeeAccess,
        EmailAddress = ComboBox11.Selected.Mail
    )
);
If(
    IsBlank(varRecordDuplicate.ID),
    Set(
        varBlah,
        false
    ),
    Set(
        varBlah,
        true
    )
);
If(
    varBlah = false,
    If(
        varSecMode = "Edit",
        Patch(
            NewEmployeeAccess,
            {ID: DataTable1.Selected.ID},
            {FullName: ComboBox6.Selected.DisplayName},
            {EmailAddress: ComboBox11.Selected.Mail},
            {
                HRAccess: If(
                    Checkbox14.Value = true,
                    "Yes",
                    "No"
                )
            },
            {
                SecurityAccess: If(
                    Checkbox15.Value = true,
                    "Yes",
                    "No"
                )
            },
            {
                AdminAccess: If(
                    Checkbox16.Value = true,
                    "Yes",
                    "No"
                )
            }
        );
        ,
        Patch(
            NewEmployeeAccess,
            Defaults(NewEmployeeAccess),
            {FullName: ComboBox6.Selected.DisplayName},
            {EmailAddress: ComboBox11.Selected.Mail},
            {
                HRAccess: If(
                    Checkbox14.Value = true,
                    "Yes",
                    "No"
                )
            },
            {
                SecurityAccess: If(
                    Checkbox15.Value = true,
                    "Yes",
                    "No"
                )
            },
            {
                AdminAccess: If(
                    Checkbox16.Value = true,
                    "Yes",
                    "No"
                )
            }
        )
    )
);
Refresh(NewEmployeeAccess);
ResetForm(Form1);
RequestHide();
NewForm(Form1)

 

Any assistance is always greatly appreciated.

Cheers,

Geoff.

1 ACCEPTED SOLUTION

Accepted Solutions

Hi Wearsky,

I solved this by going about it in a different way.  I performed a record count of any new email addresses added, and it only adds them if the record count is 0, otherwise it will overwrite any record that already exists if the count is 1.

That way it will only ever allow 1 email entry - either it will be added or overwritten (therefor removing the need for any error messages).  I used the Switch statement to achieve this, and if anyone else out there wants more options for something similar you can just add more options to the switch statement.  I used Warrens blog to handle the delegation warnings:

Power Apps Delegation – SharePoint – Practical Power Apps

And here is my code:

ClearCollect(
    colCheckRecordCount,
    Sort(
        NewEmployeeAccess,
        ID,
        Descending
    )
);
Set(
    varRecordDuplicate,
    (CountRows(
        Filter(
            colCheckRecordCount,
            EmailAddress = ComboBox11.Selected.Mail
        )
    ))
);
Switch(
    varRecordDuplicate,
    0,//Email Address does not exist, so add new record
    Patch(
        NewEmployeeAccess,
        Defaults(NewEmployeeAccess),
        {FullName: ComboBox6.Selected.DisplayName},
        {EmailAddress: ComboBox11.Selected.Mail},
        {
            HRAccess: If(
                Checkbox14.Value = true,
                "Yes",
                "No"
            )
        },
        {
            SecurityAccess: If(
                Checkbox15.Value = true,
                "Yes",
                "No"
            )
        },
        {
            AdminAccess: If(
                Checkbox16.Value = true,
                "Yes",
                "No"
            )
        }
    ),
    1,//1 Copy of email address found - allow to edit record
    Patch(
        NewEmployeeAccess,
        {ID: DataTable1.Selected.ID},
        {FullName: ComboBox6.Selected.DisplayName},
        {EmailAddress: ComboBox11.Selected.Mail},
        {
            HRAccess: If(
                Checkbox14.Value = true,
                "Yes",
                "No"
            )
        },
        {
            SecurityAccess: If(
                Checkbox15.Value = true,
                "Yes",
                "No"
            )
        },
        {
            AdminAccess: If(
                Checkbox16.Value = true,
                "Yes",
                "No"
            )
        }
    )
);
Refresh(NewEmployeeAccess);
ResetForm(Form1);
RequestHide();
NewForm(Form1)

 

 

View solution in original post

5 REPLIES 5
v-xiaochen-msft
Community Support
Community Support

Hi @Iamglt ,

 

Could you tell me:

  • Does the value of varRecordDuplicate.ID being blank means that there is no such record in the datatable control?At this point, do you need to create a new record?
  • If the value of varRecordDuplicate.ID is not blank, do you want to edit this record? And do you want to show an  message?

 

If so, please try the formula:

Set(
    varRecordDuplicate,
    LookUp(
        NewEmployeeAccess,
        EmailAddress = ComboBox11.Selected.Mail
    )
);
If(
    IsBlank(varRecordDuplicate.ID),
    Set(
        varBlah,
        false
    ),
    Set(
        varBlah,
        true
    )
);
If(
    varBlah = true,       // If the record in the table already exists, remind the user that the record already exists, and modify this record

  		 Notify("This record already exists",NotificationType.Warning);	
		 Patch(
            NewEmployeeAccess,
            {ID: DataTable1.Selected.ID},
            {FullName: ComboBox6.Selected.DisplayName},
            {EmailAddress: ComboBox11.Selected.Mail},
            {
                HRAccess: If(
                    Checkbox14.Value = true,
                    "Yes",
                    "No"
                )
            },
            {
                SecurityAccess: If(
                    Checkbox15.Value = true,
                    "Yes",
                    "No"
                )
            },
            {
                AdminAccess: If(
                    Checkbox16.Value = true,
                    "Yes",
                    "No"
                )
            }
        ),    

    varBlah = false,      // If the record does not exist, create a new record
        Patch(
            NewEmployeeAccess,
            Defaults(NewEmployeeAccess),
            {FullName: ComboBox6.Selected.DisplayName},
            {EmailAddress: ComboBox11.Selected.Mail},
            {
                HRAccess: If(
                    Checkbox14.Value = true,
                    "Yes",
                    "No"
                )
            },
            {
                SecurityAccess: If(
                    Checkbox15.Value = true,
                    "Yes",
                    "No"
                )
            },
            {
                AdminAccess: If(
                    Checkbox16.Value = true,
                    "Yes",
                    "No"
                )
            }
        )
    
);
Refresh(NewEmployeeAccess);
ResetForm(Form1);
RequestHide();
NewForm(Form1)

 

If I understand something wrong, please let me know.Thanks.

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

Hi Wearsky,

 

Thanks for your response.  If the varRecordDuplicate.ID value =

0 - then allow to create a new record

1 - then allow to edit or delete record

If adding the new record causes varRecordDuplicate.ID > 1, then red error message is displayed.

Cheers,

Geoff.

v-xiaochen-msft
Community Support
Community Support

Hi @Iamglt ,

 

According to your description, please try this formula:

1\ Add a save button control and set its onselect property to:

Set(

    varRecordDuplicate,

    LookUp(

        NewEmployeeAccess,

        EmailAddress = ComboBox11.Selected.Mail

    )

);

 

If(

    varRecordDuplicate.ID value =1,       // varRecordDuplicate.ID value =1, user could modify this record.

 

               Patch(

            NewEmployeeAccess,

            ID= DataTable1.Selected.ID,

            {FullName: ComboBox6.Selected.DisplayName,

            EmailAddress: ComboBox11.Selected.Mail,

           

                HRAccess: If(

                    Checkbox14.Value = true,

                    "Yes",

                    "No"

                )

            ,

           

                SecurityAccess: If(

                    Checkbox15.Value = true,

                    "Yes",

                    "No"

                )

            ,

           

                AdminAccess: If(

                    Checkbox16.Value = true,

                    "Yes",

                    "No"

                )

            }

        ),   

 

    varRecordDuplicate.ID value =0,      // If the record does not exist, create a new record

        Patch(

            NewEmployeeAccess,

            Defaults(NewEmployeeAccess),

            {FullName: ComboBox6.Selected.DisplayName,

            EmailAddress: ComboBox11.Selected.Mail,

           

                HRAccess: If(

                    Checkbox14.Value = true,

                    "Yes",

                    "No"

                )

            }

           

                SecurityAccess: If(

                    Checkbox15.Value = true,

                    "Yes",

                    "No"

                )

            ,

           

                AdminAccess: If(

                    Checkbox16.Value = true,

                    "Yes",

                    "No"

                )

            }

        )

varRecordDuplicate.ID value>1,    // If varRecordDuplicate.ID value>1 ,it will show the warning message.

Notify("This record already exists",NotificationType.Warning)

 

);

Refresh(NewEmployeeAccess);

ResetForm(Form1);

RequestHide();

NewForm(Form1)

 

2\ Add a delete button control and set its onselect property to:

If(

       varRecordDuplicate.ID value =1,   // If varRecordDuplicate.ID value =1, user can remove this record.

       RemoveIf(NewEmployeeAccess, ID= DataTable1.Selected.ID)

)

 

Please tell me if this is what you want. Thanks.

 

Best Regards,

Wearsky

If my post helps, then please consider Accept it as the solution to help others. Thanks.

Hi Wearsky,

 

Thanks for your reply - just going through the code now - I don't think PowerApps likes the 'value':

 

Are you sure that the 'value' exists in PowerApps like you have programmed it?

 

Iamglt_0-1612475874888.png

 

Cheers,

Geoff.

 

Hi Wearsky,

I solved this by going about it in a different way.  I performed a record count of any new email addresses added, and it only adds them if the record count is 0, otherwise it will overwrite any record that already exists if the count is 1.

That way it will only ever allow 1 email entry - either it will be added or overwritten (therefor removing the need for any error messages).  I used the Switch statement to achieve this, and if anyone else out there wants more options for something similar you can just add more options to the switch statement.  I used Warrens blog to handle the delegation warnings:

Power Apps Delegation – SharePoint – Practical Power Apps

And here is my code:

ClearCollect(
    colCheckRecordCount,
    Sort(
        NewEmployeeAccess,
        ID,
        Descending
    )
);
Set(
    varRecordDuplicate,
    (CountRows(
        Filter(
            colCheckRecordCount,
            EmailAddress = ComboBox11.Selected.Mail
        )
    ))
);
Switch(
    varRecordDuplicate,
    0,//Email Address does not exist, so add new record
    Patch(
        NewEmployeeAccess,
        Defaults(NewEmployeeAccess),
        {FullName: ComboBox6.Selected.DisplayName},
        {EmailAddress: ComboBox11.Selected.Mail},
        {
            HRAccess: If(
                Checkbox14.Value = true,
                "Yes",
                "No"
            )
        },
        {
            SecurityAccess: If(
                Checkbox15.Value = true,
                "Yes",
                "No"
            )
        },
        {
            AdminAccess: If(
                Checkbox16.Value = true,
                "Yes",
                "No"
            )
        }
    ),
    1,//1 Copy of email address found - allow to edit record
    Patch(
        NewEmployeeAccess,
        {ID: DataTable1.Selected.ID},
        {FullName: ComboBox6.Selected.DisplayName},
        {EmailAddress: ComboBox11.Selected.Mail},
        {
            HRAccess: If(
                Checkbox14.Value = true,
                "Yes",
                "No"
            )
        },
        {
            SecurityAccess: If(
                Checkbox15.Value = true,
                "Yes",
                "No"
            )
        },
        {
            AdminAccess: If(
                Checkbox16.Value = true,
                "Yes",
                "No"
            )
        }
    )
);
Refresh(NewEmployeeAccess);
ResetForm(Form1);
RequestHide();
NewForm(Form1)

 

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,536)