cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
biancadevera
Helper III
Helper III

Prevent duplicate entries in Excel

My data source is an Excel file. Although I have set some validation rules in my file (wherein cells fill with red when duplicate was detected within the column), I want my app not to allow the SubmitForm function or error notification once duplicate entry was found.

 

I know this is possible with SharePoint list through the enforce unique values. Is this possible with Excel, too?

1 ACCEPTED SOLUTION

Accepted Solutions
StalinPonnusamy
Super User
Super User

Hi @biancadevera 

 

We need to check Ticket numbers exist when adding a new record.

 

If(
   Len(txtTicketNumber.Text)=0 || Len(drpStatus.Selected.Value)=0 || Len(drpPriority.Selected.Value)=0 || Len(txtIssueDesc.Text)=0,
   Notify(
      "Please fill out all fields marked with an asterisk (*)", 
      NotificationType.Error
   ),

If(TicketInfo.Mode<>New || (TicketInfo.Mode=New && IsBlank(LookUp([@Table1],Ticket Number = txtTicketNumber.Text))),
Patch(Table1,
If(TicketInfo.Mode=New,Defaults(Table1),galleryDashboard.Selected),
TicketInfo.Updates,
DetailsInfo.Updates,
   );
   Navigate(SuccessScreen),
Notify("Data exist already",NotificationType.Error)   
)
)

 

View solution in original post

8 REPLIES 8
biancadevera
Helper III
Helper III

Hi @biancadevera

 

We need to Lookup to validate for the same data exists or not.

 

 

If(
    IsBlank(
        LookUp(
            [@ExcelTable],
            Condition
        )
    ),
    SubmitForm(EditForm1),
    Notify("Data exist already")
)

 

 

Sample using my Datasource

If(
    IsBlank(
        LookUp(
            [@ProjectsExcel],
            Phase = DataCardValue4.Text
        )
    ),
    SubmitForm(EditForm1),
    Notify("Data exist already",NotificationType.Warning)
)


Thanks,
Stalin - Learn To Illuminate

HI @StalinPonnusamy 

 

Currently, I have this code:

 

If(
   Len(txtValue.Text)=0 || Len(drpValue.Selected.Value)=0,
   Notify(
      "Please fill out all fields marked with asterisk (*)", 
      NotificationType.Error
   ),
   Patch(
      {update_here}
   );
   Navigate(SuccessScreen)
)

 

How can I include the Lookup validation on my code? 

StalinPonnusamy
Super User
Super User

Hi @biancadevera 

 

Please try this

 

If(
   Len(txtValue.Text)=0 || Len(drpValue.Selected.Value)=0,
   Notify(
      "Please fill out all fields marked with an asterisk (*)", 
      NotificationType.Error
   ),
If(
    IsBlank(
        LookUp(
            [@ProjectsExcel],
            Phase = DataCardValue4.Text
        )
    ),
       Patch(
      {update_here}
   );
   Navigate(SuccessScreen),
   Notify("Data exist already",NotificationType.Error)
)
)

 


Thanks,
Stalin - Learn To Illuminate

Hi @StalinPonnusamy 

 

I had this error:

biancadevera_0-1636483001418.png

Please check this out:

 

biancadevera_1-1636483046193.png

 

Hi @biancadevera 

 

Can you make sure the Lookup condition is correct as per your table and column-like

    IsBlank(
        LookUp(
            [@Tablename],
            ColumnName = DataCardValue1.Text
        )
    ),

 

Post your code, so that we can validate your entire code.

 

Hi @StalinPonnusamy 

 

Please check my code:

 

If(
   Len(txtTicketNumber.Text)=0 || Len(drpStatus.Selected.Value)=0 || Len(drpPriority.Selected.Value)=0 || Len(txtIssueDesc.Text)=0,
   Notify(
      "Please fill out all fields marked with an asterisk (*)", 
      NotificationType.Error
   ),
If(
    IsBlank(
        LookUp(
            [@Table1],
            Ticket Number = txtTicketNumber.Text
        )
    ),
Patch(Table1,
If(TicketInfo.Mode=New,Defaults(Table1),galleryDashboard.Selected),
TicketInfo.Updates,
DetailsInfo.Updates,
   );
   Navigate(SuccessScreen),
   Notify("Data exist already",NotificationType.Error)
)
)

 

Table1 - name of table

Ticket Number - name of column

StalinPonnusamy
Super User
Super User

Hi @biancadevera 

 

We need to check Ticket numbers exist when adding a new record.

 

If(
   Len(txtTicketNumber.Text)=0 || Len(drpStatus.Selected.Value)=0 || Len(drpPriority.Selected.Value)=0 || Len(txtIssueDesc.Text)=0,
   Notify(
      "Please fill out all fields marked with an asterisk (*)", 
      NotificationType.Error
   ),

If(TicketInfo.Mode<>New || (TicketInfo.Mode=New && IsBlank(LookUp([@Table1],Ticket Number = txtTicketNumber.Text))),
Patch(Table1,
If(TicketInfo.Mode=New,Defaults(Table1),galleryDashboard.Selected),
TicketInfo.Updates,
DetailsInfo.Updates,
   );
   Navigate(SuccessScreen),
Notify("Data exist already",NotificationType.Error)   
)
)

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

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