Filter previously selected articles from a REF list

[JuanCruzCookdat]

Today I am faced with this challenge: I am building a form to audit different sectors within a company, the objective is that the person who loads the form, gives a score to the different areas of a business premises, the inconvenience or challenge What I find is that I cannot filter the sectors that were previously selected from the REF list, thus achieving that the person cannot load the same sector twice in the same form load, I have used this formula:

NOT(
IN(
[_THIS],
SELECT(
DETALLE AUDITORIAS[SECCION AUDITORIA],
AND(
[ID AUDITORIA] = [_THISROW].[ID AUDITORIA],
[SECCION AUDITORIA] = [_THISROW].[SECCION AUDITORIA]
)
)
)
)

Processing: screen-capture.webm…

but this only invalidates the sectors that have already been loaded, I want them to STOP appearing in that list, so the list is cleaner of excluded values

[Suvrutt_Gurjar · Accepted solution]

Maybe something like below in the Valid_if of the [SECCION AUDITORIA] column of the DETALLE AUDITORIAS table

SECCIONES AUDITORIA[Key Column of SECCIONES AUDITORIA] -

SELECT( DETALLE AUDITORIAS[SECCION AUDITORIA], [ID AUDITORIA] = [_THISROW].[ID AUDITORIA] )

[JuanCruzCookdat]

THANKS,
The truth is, you helped me solve a challenge that I had been needing for many other applications for a long time, and I couldn’t find a way to do it, so thank you very much, you made my day

[Suvrutt_Gurjar]

Thank you for the update. Good to know it works the way you wish. Just to add the details for any future reader , the logic behind the expression is

A) The part SECCIONES AUDITORIA[Key Column of SECCIONES AUDITORIA] creates a list of all the keys from the referenced parent table SECCIONES AUDITORIA

B) The part SELECT( DETALLE AUDITORIAS[SECCION AUDITORIA], [ID AUDITORIA] = [_THISROW].[ID AUDITORIA] ) subtracts the list of parent keys that have been used earlier in the child table, thereby creating a net list of remaining keys from the parent table.

[Jose_Narva]

Hola, hice lo que recomendaron, pero no me aparece ningún item en la lista, me podrian ayudar?