Entered Repeatedly

Can I have more then 1 key ( Column ) ? I tried but I couldn’t make it.

The reason is this:

I made 1 key for column [ MOBILE] and it cant be entered repeatedly, I want in column [EMAIL] to be the same ( Not entered repeatedly ).

You can have only one key column. Note that the key column value can never change once a row is saved, which could be a problem if a user wants to change their phone number. Better to choose a key value that can be guaranteed to never change. One common option is to generate a key value using the UNIQUEID() function.

To prevent other columns from having duplicate values, you need to use a Valid If expression similar to this:

ISBLANK(
  FILTER(
    "REGISTER",
    AND(
      ISNOTBLANK([EMAIL]),
      ([EMAIL] = [_THISROW].[EMAIL])
    )
  )
  - LIST([_THISROW])
)
  1. FILTER("REGISTER", ...) gathers the rows in the REGISTER table that meet the given criteria (...; see (2)). See also: FILTER().

  2. AND(..., ...) matches only rows that meet both criteria (see (3) & (4)). See also: AND().

  3. ISNOTBLANK([EMAIL]) matches only rows where the EMAIL column value is not blank. See also: ISNOTBLANK().

  4. ([EMAIL] = [_THISROW].[EMAIL]) matches only rows where the EMAIL column value ([EMAIL]) matches the same column value in the current form ([_THISROW].[EMAIL]).

  5. ... - LIST([_THISROW]) removes the row in the form ([_THISROW]) from the list of gathered rows (...; see (1)). See also: LIST().

  6. ISBLANK(...) asks whether the constructed list (...; see (5)) is empty. If so, the table contains no other rows with the same EMAIL column value. See also: ISBLANK().

5 Likes

Thank you a lot for the help, its working :slightly_smiling_face:

1 Like

Hello @Steve
How can I apply a validate to avoid repeated entries but allow some specific data?
Thanks for your help.

Do you want to allow the user to only select from a list of choices? Or can the user enter a value not in the list?

See also:

@Steve
Thanks for answering.
I would like to use this validation: NOT(IN([_THIS], SELECT(Users[User_Name], [UserID] <> [_ThisRow].[UserID])))
But to be able to repeat a data that I want, like an exception

Please describe in plain language what question you want Valid If to answer. For instance, “is this the only occurrence of this value in this column?”

Hello Steve,
Thanks for your answering again. I’ll try to explain my idea:

TABLE USER

ID CODE USER
001 01AB DAVID
002 01AC JOHN
003 NO DATA GEORGE

IF (the new code that I want to enter already exists in the database) {

*do not save this entry*

}
ELSE {

*allow saving the new user with the default code NO DATA*

}

That way I can find how many users don’t have a code.
Thanks for your help!

What should happen if the new code does not already exist in the database?

Where is the code coming from?

What should happen if the new code does not already exist in the database?

If the new code doesn’t already exist in the database, the App allows to save the information

Where is the code coming from?

I enter the code manually on the form, or the App itself reads it with the barcode reader

1 Like

Try this:

OR(
  ("NO CODE" = [CODE]),
  ISBLANK(
    FILTER(
      "USER",
      AND(
        ([CODE] = [_THISROW].[CODE],
        ([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER])
      )
    )
  )
)
1 Like

Steve,
Thanks a lot! it’s working perfectly. I just added a parenthesis.
I share the result:

OR(
  ("NO CODE" = [CODE]),
  ISBLANK(
    FILTER(
      "USER",
      AND(
        ([CODE] = [_THISROW].[CODE]),
        ([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER])
      )
    )
  )
)
2 Likes

NO_CODE_1