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
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,281)