Solved: Entered Repeatedly

Izzat_Safrah · 07-04-2019 03:43 AM

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

Steve

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

FILTER("REGISTER", ...) gathers the rows in the REGISTER table that meet the given criteria (...; see (2)). See also: FILTER().
AND(..., ...) matches only rows that meet both criteria (see (3) & (4)). See also: AND().
ISNOTBLANK([EMAIL]) matches only rows where the EMAIL column value is not blank. See also: ISNOTBLANK().
([EMAIL] = [_THISROW].[EMAIL]) matches only rows where the EMAIL column value ([EMAIL]) matches the same column value in the current form ([_THISROW].[EMAIL]).
... - LIST([_THISROW]) removes the row in the form ([_THISROW]) from the list of gathered rows (...; see (1)). See also: LIST().
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().

View solution in original post

Steve

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

FILTER("REGISTER", ...) gathers the rows in the REGISTER table that meet the given criteria (...; see (2)). See also: FILTER().
AND(..., ...) matches only rows that meet both criteria (see (3) & (4)). See also: AND().
ISNOTBLANK([EMAIL]) matches only rows where the EMAIL column value is not blank. See also: ISNOTBLANK().
([EMAIL] = [_THISROW].[EMAIL]) matches only rows where the EMAIL column value ([EMAIL]) matches the same column value in the current form ([_THISROW].[EMAIL]).
... - LIST([_THISROW]) removes the row in the form ([_THISROW]) from the list of gathered rows (...; see (1)). See also: LIST().
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().

Izzat_Safrah

Thank you a lot for the help, its working

daniel5905

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

Steve

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?

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!

Steve

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

Where is the code coming from?

daniel5905

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

Steve

Try this:

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

daniel5905

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

Izzat_Safrah

daniel5905

Hi Steve,

You helped me with this topic a while ago.
Now how can I do the same with another table? That it be possible to do the two validations at the same column.

That is, it is not allowed to enter a code that exists in another table

Thanks.

Steve

Definitely possible.