Data Validity Help

Ben_Rix · 12-10-2020 07:55 AM

Hi Everyone,

Im hoping someone can point me in the right direction.

I have a list of clients in a google sheet table, from that list I can assign them a session from another google sheet table, the clients are only authorised a certain number of sessions per case. I am tracking the number of attended sessions and also the number of authorised sessions. I have created a virtual column that tells me if they have maxed their session allowance or if they have available sessions.

If I want to stop the user from adding anymore sessions how would I go about this? Is it something under data validity, if anyone could point me in the right direction I would be really grateful.

Regards
Ben

Marc_Dillon

So in the “other table”, you have a column where you select a client. You’re going to use an expressions in that column’s valid_if.

You can set an expression in valid_if that returns either a Yes/No values, or a List value. See the following article for more info:

We’re going to use the List ability. We want to start with the entire list of all clients, and filter it down to only those that have available sessions left. This will create a nice drop-down of only valid clients that can be selected. Please modify the following basic expression to fit your specific app/data:

FILTER( Clients , [available sessions virtual column] <> 0 )

Ben_Rix

Thanks so much for that Marc, Ill have a look into that article you sent. I wouldnt be able to put the clients in a list as there are quite a few about 120. Im currently using the related sessions table to add new sessions for the clients related to the case. Was hoping to stop new rows from being added from the form option, maybe when i select the session type.