Hide Dynamic Select Option if Exists in Second Table

[brad]

I have two tables/queries .... 'Available Programs' and 'Enrolled Programs'. On my form I have a Dynamic select that pulls the options from a 'available programs' query. And when the form is submitted it adds a record to the 'enrolled programs' table.

What I need to do is hide or disable the select option if it already exists in the enrolled programs query so a client can't be added to the same program more than once.

I don't even know where to start with this. Any help would be appreciated. Thanks.

[brad]

I think I need to create an array of enrolled program IDs and filter my available programs list where program id is not in enrolled programs.

I can't find any documentation for creating arrays from a query on either server side or front end.

[franse]

Hello @brad
What about

Select from a table that is not in other table

How can I select values form a Mysql table that are not already stored in other table? Server connect.

[brad]

Thanks @franse

I saw that one, unfortunately joins won't work as I have to also filter the second table only by client ID.

[Apple]

@brad

I can't find any documentation for creating arrays from a query on either server side or front end.

I believe it's a formatter called flatten, try checking in the Collections category (Server Action)

query_enrolled.flatten('id') // Will build an array of IDs, from column "id"

[brad]

Thanks @Apple, that appears to be what I need but I can't find the flatten formatter anywhere. Where do I add that?

[Apple]

@brad

Where do I add that?

In a Set Value, manually insert the .flatten formatter in the code view

query_enrolled.flatten('id')

The Wappler editor sometimes hides formatters from the UI depending on the data type of the object being manipulated - it doesn't always guess right

[brad]

I think I got it! I created an array on the server side and I was able to use the 'NOT IN' on the second query to get my list of programs available to that client.

Works perfectly on a test page. Tomorrow I will try it on my live project. Thank you guys for your hints and input and time.

I love Wappler.

Putting this here for my future reference.

[Teodor]

You can directly use the results of the profiles query to filter the programs query even without using an array.

[brad]

I changed the solution to @Apple' s suggestion of using the flatten formatter. That was WAY easier and works like a charm. Thanks @Teodor for the screenshot in another thread. That got me in the right direction. Man, that was so easy.

Note to self: I had to add a condition to the programs query. If the profiles query was empty it wasn't showing any programs. Adding a condition to the filter solved that.