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.
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
11166:
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 !!
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
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
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.
1 Like
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.
Could you try the second option?
Suvrutt_Gurjar:
- 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.
I mean Valid_if of [VALIDCHECK] column?
