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 Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

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.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (2,128)