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?
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.
Thank you very much teacher
In the Valid If for the NAME column:
ISBLANK(
FILTER(
"Participants",
AND(
([_THISROW].[DATE] = [DATE]),
([_THISROW].[AREA] = [AREA]),
([_THISROW].[NAME] = [NAME])
)
)
- LIST([_THISROW])
)
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.
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
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.
*** 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
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?
Thank you for more details.These are really useful and required for suggesting proper approach .
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.
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.
This entry is invalid ?
Could you please try any of the following
NOT(IN([_THISROW].[VALIDCHECK], SELECT(Participents[VALIDCHECK], NOT(IN([KEY], LIST([_THISROW].[KEY]))))))
or else
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?
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.
Checkvalid should be a TEXT type column as mentioned earlier. Please make it TEXT type.
Great. Thank you. Is it working now?
Ok
Thank you very much teacher
Is it working now? Please try adding new entries in [AREA] and [NAME]
May I ask what to add?
Is it working as you wish? I requested to add more records and check that they are not repeated.
The current function is correct
Thank you, teacher
Bye Bye
NOT(IN([_THIS], SELECT(Participants[CHECKVALID], NOT(IN( [_THIS].[KEY] , LIST( Participants[KEY]) ))) ))
Ask the teacher
Is this correct?
Thank you
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.
Is there any other way to ensure that it is correct and error-free when used by multiple people?
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.
If it is in the Valid_if constraint of the [VALIDCHECK] column
Join UNIQUEID () can
Solve it
Thank you
Yes, you are correct.
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
User | Count |
---|---|
46 | |
29 | |
24 | |
22 | |
13 |