Check that it cannot be repeated, how should it be...

11166 · 05-22-2020 06:42 PM

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

Steve

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

11166

Steve

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.

11166

Thank you very much teacher

11166

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

Steve

In the Valid If for the NAME column:

ISBLANK(
  FILTER(
    "Participants",
    AND(
      ([_THISROW].[DATE] = [DATE]),
      ([_THISROW].[AREA] = [AREA]),
      ([_THISROW].[NAME] = [NAME])
    )
  )
  - LIST([_THISROW])
)

11166

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

Steve

I’m afraid I do not understand.

11166

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

Suvrutt_Gurjar

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.

11166

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

Suvrutt_Gurjar

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?

11166

Suvrutt_Gurjar

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

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.

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.

11166

This entry is invalid ?

Suvrutt_Gurjar

Could you please try any of the following

Could you please modify the expression to

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

or else

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.

11166

mistaken?
Has it become repeatable?

Suvrutt_Gurjar

Could you try the second option?

I mean Valid_if of [VALIDCHECK] column?

11166

Suvrutt_Gurjar

Please put in valid_if of [VALIDCHECK] as mentioned earlier

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

Edit: and [VALIDCHECK] 's App formula should be as before only

CONCATENATE([DATE],"-",[AREA],"-",[NAME])

It needs to be a TEXT type column.

11166

Suvrutt_Gurjar

Checkvalid should be a TEXT type column as mentioned earlier. Please make it TEXT type.

11166

Suvrutt_Gurjar

Great. Thank you. Is it working now?

11166

Ok
Thank you very much teacher

11166

Suvrutt_Gurjar

Is it working now? Please try adding new entries in [AREA] and [NAME]

11166

May I ask what to add?

Suvrutt_Gurjar

Is it working as you wish? I requested to add more records and check that they are not repeated.

11166

The current function is correct
Thank you, teacher
Bye Bye

11166

NOT(IN([_THIS], SELECT(Participants[CHECKVALID], NOT(IN( [_THIS].[KEY] , LIST( Participants[KEY]) ))) ))

Ask the teacher
Is this correct?
Thank you

Suvrutt_Gurjar

Yes, if used in Valid_if constraint of [VALIDCHECK] column to avoid duplicates of [VALIDCHECK] , it is correct.

The general construct of the expression to i=use in any column is

NOT(IN([_THIS], SELECT(Participants[Column Name Where duplicates are to be avoided], NOT(IN( [_THIS].[Key column Name of the table] , LIST( Participants[Key Column Name of the table]) ))) ))

The expression needs to be used in Valid_Check constraint of the column [Column Name Where duplicates are to be avoided]
Point to note is this approach to avoid duplicates can fail in multi user app environment at times.

11166

Is there any other way to ensure that it is correct and error-free when used by multiple people?

Suvrutt_Gurjar

I am unaware of another more reliable way. I suggest that you may start a new post thread to get an answer from the community. This post thread has become very long as well.

11166

If it is in the Valid_if constraint of the [VALIDCHECK] column
Join UNIQUEID () can
Solve it
Thank you

Suvrutt_Gurjar

Yes, you are correct.

11166

NOT(IN([_THIS], SELECT(Participants[VALIDCHECK], NOT(IN( [_THIS].[KEY] , LIST( Participants[KEY]) ))))) + UNIQUEID()

Ask the teacher
Is that right?
Will there be an error message?
Thank you

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