How to create a filtered list of 'clients' that have >1 items related to another table?


I have a table called “clients” (different from the users table) and a table for “schedules” on my database.

We’ve come to an issue with user creating more than one schedule for each client, which should not be possible. I fixed the creation problem that caused it, but now I need to clean the “schedules” table.

I’m trying to list on a test page the clients that have more than one schedule. How can I list them? I have created a repeating group but I’m having problems conecting the two tables.

On the “schedules” table I have a client_list column (that for now contains only one client, so i can use :first item if needed) , but I don’t have anything related to the “schedules”.

So my main question is: how can i create a filter that would allow me to search for clients that have more than 1 schedule?

Because this is an admin/test/temporary functionality, the efficiency and performance of the query is not important.

You could create a recursive workflow which iterates through all the Cleints and runs a search for Schedules where Client=This Client. You can then mark the Clients where the search returned more than one item, or do your required adjustments directly from the backend workflow.

You could also achieve this on a frontend page rather than the backend, however if you have many clients in your database, it might be more stable to run it on the backend.
You could either use looping/ping-pong custom events to iterate through the users, or use Advanced Filters to filter clients whose search for schedules returned more than one Schedule. The latter probably might crash your browser with a large enough database, and I generally wouldn’t recommend doing this on a page, as there is no real benefit. For these types of operations, its best to get comfortable with using backend workflows.

1 Like

Hi, Nico, thanks for the answer!

or use Advanced Filters to filter clients whose search for schedules returned more than one Schedule.

Can you tell me how would I do this filter? This is what I tryed to do but couldn’t. I thought i could Do a search for Client and then choose “:filtered” + advanced, but I don’t know what to put on the advanced filter to make it work.

Forget I mentioned advanced filters… they work in theory but absolutely not in practice. Inside the advanced filter you would have to run a search for Scedules where client=This client and check whether the count of the search is greater than 1.
You would have to download the entire Client table onto your computer, and then run an additional search for every client in the list.This is not how advanced filters are supposed to be used, and it will almost definitely crash your browser.

Looking at your question again, I realised there is a much simpler solution, which you can run from the frontend.
Use the :group by operator.
Run a search for Schedules without any constraints and group them by their Client, aggregating by count.
You can then filter the grouping for counts greater than 1 to identify the clients with more than 1 schedule.
This method will not identify the actual duplicate schedules, but it will help you identify the clients you need to run fixes on.

1 Like

Thanks for the reply once again! You helped me a lot!

We used your suggestion and it helped us to have a list. The list was enourmous so we couldn’t process it even in the backend workflow. But we were able to export a csv that we then filtered on excel.

Once again, thanks for taking your time commenting.