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 Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

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.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (6,886)