Check that it cannot be repeated, how should it be set?

Ask the teacher
If you want to add data to the attachment, please use [AREA] + [[NAME]
To check that it cannot be repeated, how should it be set?
Thank you, teacher

What are the current Valid If expressions for the AREA and NAME columns?

1 Like

Try this as the Valid If expression for NAME:

IFS(
  ISNOTBLANK([AREA]),
  (
    ORDERBY(
      FILTER(
        "personnel",
        ([_THISROW].[AREA] = [AREA])
      ),
      [INPUT SEQUENCE],
        FALSE
    )
    - SELECT(
      Participants[NAME],
      ([_THISROW].[AREA] = [AREA])
    )
  )
)

The - SELECT(...) portion attempts to remove the names in the area that have already selected, leaving only names in the area that haven’t been selected.

2 Likes

Thank you very much teacher

1 Like


Ask the teacher
If you want to add data to the attachment, please use [DATE]+[AREA] + [[NAME]
To check that it cannot be repeated, how should it be set?
Thank you, teacher

In the Valid If for the NAME column:

ISBLANK(
  FILTER(
    "Participants",
    AND(
      ([_THISROW].[DATE] = [DATE]),
      ([_THISROW].[AREA] = [AREA]),
      ([_THISROW].[NAME] = [NAME])
    )
  )
  - LIST([_THISROW])
)
1 Like
IFS(
  ISNOTBLANK([AREA]),
  (
    ORDERBY(
      FILTER(
        "personnel",
        ([_THISROW].[AREA] = [AREA])
      ),
      [INPUT SEQUENCE],
        FALSE
    )
    - SELECT(
      Participants[NAME],
      ([_THISROW].[AREA] = [AREA])
    )
  )
)

The - SELECT(...) portion attempts to remove the names in the area that have already selected, leaving only names in the area that haven’t been selected… …

In this way, when you enter the “Name” field when you open the window,
Not the same “Area” data will appear?
This is wrong!
I also want to keep the original function. When entering the “Name” field,
Only the same “Area” information can appear !!
Thank you

I’m afraid I do not understand. :frowning:

5

As shown in the example:

When I add the “AREA” field, if the input data is: AREA1, only three options NAME1 NAME2 NAME3 will appear in the “REF” window
Other information cannot appear

The last “SAVE” archive needs to be checked. DATE + AREA + NAME cannot be repeated

For example, if you enter DATE = 202005010 AREA = AREA8 NAME = NAME9 is OK

For example, if you enter DATE = 202005009 AREA = AREA8 NAME = NAME8 is OK

For example, if you enter DATE = 202005008 AREA = AREA8 NAME = NAME8 is not allowed to repeat

Don’t know if there is a way to solve it
Thank you

1 Like

If I have understood your requirement with the additional description in your latest post, please try following.

Please create a virtual column with an expression something like CONCATENATE([DATE],"-",[AREA],"-",[NAME]) . Suppose this column is called CHECKVALID

It sounds that out of three fields (DATE, AREA and NAME), [NAME] is filled in last by the user.

So in the Valid_if constraint of [NAME] column, please have an expression like,

NOT(IN([VALIDCHECK], SELECT(Participents[VALIDCHECK], NOT(IN([KEY], LIST([_THISROW].[KEY]))))))

The above expression is basically to avoid duplicates and will give error if any combination of [DATE], [AREA] and [NAME] is repeated. However , one caution to note is, this expression may fail in multiuser environment.

1 Like

*** Requirement 1:
When I newly input the [AREA] field, if the input data is: AREA1, the data displayed in the “REF” window in the [NAME] field can only appear NAME1, NAME2, NAME3
Three options, other information can not appear

*** Requirement 2:
Check the last “SAVE” when archiving DATE + AREA + NAME cannot be repeated

For example, if you enter DATE = 202005010 AREA = AREA8 NAME = NAME9 is OK

For example, if you enter DATE = 202005009 AREA = AREA8 NAME = NAME8 is OK

For example, if you enter DATE = 202005008 AREA = AREA8 NAME = NAME8 is not allowed to repeat

Can the above two points meet the requirements at the same time?

Thank you

1 Like

Thank you. I believe your Requirement 2 will be addressed by the solution shared in earlier post.

For requirement 1, could you please elaborate how NAME1, NAME2 and NAME3 are entered in the first place for AREA1. Do you mean ,if AREA1 is selected initially, when there were no records with AREA1, the NAME1 was added by the user using 'NEW" or “ADD” record option of the dropdown?

1 Like

1 Like

Thank you for more details.These are really useful and required for suggesting proper approach .

  1. Please move the expression

NOT(IN([VALIDCHECK], SELECT(Participents[VALIDCHECK], NOT(IN([KEY], LIST([_THISROW].[KEY]))))))

to valid_if of the [Area] column in the Participents table

This expression is for your Requirement 2 mentioned above.

  1. Please try an expression something like below in the valid_if of [Name] column in the Participents table

SELECT(personnel[NAME], [SEQUENCE]=[_THISROW].[SEQUENCE])

or the following expression.

SELECT(personnel[NAME], [AREA]=[_THISROW].[AREA])

This expression is for your Requirement 1 mentioned above.

1 Like

This entry is invalid ?

1 2 3

Could you please try any of the following

  1. Could you please modify the expression to

NOT(IN([_THISROW].[VALIDCHECK], SELECT(Participents[VALIDCHECK], NOT(IN([KEY], LIST([_THISROW].[KEY]))))))

or else

  1. Could you please try the following expression in [VALIDCHECK] column itself?

NOT(IN([_THIS], SELECT(Participents[VALIDCHECK], NOT(IN([KEY], LIST([_THISROW].[KEY]))))))

Please confirm [VALIDCHECK] is a virtual column.

mistaken?
Has it become repeatable?

Could you try the second option?

I mean Valid_if of [VALIDCHECK] column?