List subtraction in a ref column not working

[CriMuggio]

Hello,
my app should manage the different services of a local Red Cross branch.
I am currently working with three tables:

- MEZZI (vehicles)
- TIPISER (kinds of services)  with the column [MezziAbilitati] that is a list of the vehicles that can be used for a specific service
- SERVIZI (services)

I'm using the following Valid if code for the REF column [Mezzo] of the table SERVIZI

SPLIT(
SELECT(
TIPISER[MezziAbilitati],
AND(
[Nome] = [_THISROW].[TipoServizio], [Richiedente] = [_THISROW].[Richiedente]
)
)," , "
)
-
SELECT(
SERVIZI[Mezzo],[Stato]="Aperto"
)

This code should subtract the ID of the vehicles currently being used from the list of all available vehicles for a specific service. Thus, in case you wanted to open a new service, you shouldn't be able to select one of the excluded vehicles.
I used SPLIT because [MezziAbilitati] is an EnumList of MEZZI[ID] stored in the DB as a string (i.e "201328 , 201332 , 201333 , 201339 , 201399")

The output is as follows 😞

Not only is the output a single option instead of multiple ones, but the code doesn't even exclude the vehicle with ID 201332 which is currently in use ([Stato]="Aperto").

Can anyone help me?

[Marc_Dillon · Accepted solution]

1. Put a TEXT() between the SPLIT and SELECT. I.e. SPLIT(TEXT(SELECT(......)) , " , " )

2. Make sure to remove the current record's value from the subtraction portion, otherwise this record would not pass validation on any future edits. Something like SELECT( table[col] , [key-col] <> [_THISROW].[key-col] )

[CriMuggio]

easy 🙂
thanks a lot, Marc!