List subtraction in a ref column not working

AppSheet AppSheet Q&A
1

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 :disappointed_face:

CriMuggio_0-1673980413620.png
CriMuggio_0-1673980413620.png334×598 7.94 KB

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?

2

  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] )

2 Likes
3

easy :slightly_smiling_face:
thanks a lot, Marc!

2 Likes