Returning unique values in a column based on a specific condition

[Sankalp]

I have the following scenario.. a database in retool using google sheets for workshop database and it has "Client ID" as its primary key. Now what i have done is suppose there is a client with client id "THE1" and the next client as "THE2" and so on.. now the client "THE1" is a recurring one so the first time he comes for workshop i have saved his id as "THE1" and the next time he comes i saved his id as "THE1a" where that small "a" signifies that its his 2nd visit and for the third visit i will save it as "THE1b" and so on.. now i want to return the value of the total number of clients in the "Client ID" column but only the unique ones.. that is I don't want the IDs which have an alphabet in its end to be counted.. i have a query named "Workshop" which contains the whole therapy table.. so what should i write in the "value" parameter to get this data fetched.. or even a javascript query or transformer will do.

I tried searching different places but i cant find it. and this query also doesnt run as "endsWith" is not recognizable by retool.
i am new to this platform so help is appreciated. Thank you

[lenti]

Hello @Sankalp,

It looks like endsWith doesn't accept regular express which is why it isn't working. You can try this instead.
#### Total Clients : {{Workshop.data.filter(client=>!(new RegExp(/[a-z]$/).test(client))).length}}

[Sankalp]

Its still giving output as 4

it should show output as 3 as i have a recurring client with id "TRA3a" so it shouldnt count TRA3a and give me 3.
i have tried different functions (like endsWith, match, replace) but they all give error saying that retool cant read the properties of

[Sankalp]

[lenti]

I see where the issue is. Your Workshop data are objects array, and it need to specify which column to filter by. I'm using 'Client ID' here, update the key if it is different.

#### Total Clients : {{Workshop.data.filter(client=>!(new RegExp(/[a-z]$/).test(client['Client ID']))).length}}