Prevent duplicate entries in Excel

[biancadevera]

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?

[biancadevera]

Please help @WarrenBelz @RandyHayes @poweractivate @StalinPonnusamy 

[StalinPonnusamy]

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

[biancadevera]

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]

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

[biancadevera]

Hi @StalinPonnusamy 

I had this error:

Please check this out:

[StalinPonnusamy]

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.

[biancadevera]

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 · Accepted solution]

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