Getting clients and appointments, then routing clients based on what appointment types they've had

:footprints: The steps I have taken
Currently my scenario:

  1. Lists Clients from Acuity
  2. Aggregates then iterates through the relevant fields from those clients
  3. Does the same for appointments
  4. Tries to route bundles to create ClickUp tasks in different lists based on the role of the client (student, parent, tutor, collaborator or leader).

The problem is, the first route filter after the router I actually built (student) is looking at a Notes field from the client record iterator, where if the field contains a manually applied tag “isStudent,” the bundle will pass:

The problem is it’s passing a bundle and creating a ClickUp record for every appointment bundle that comes from the appointment iterator. So even when I set the List Clients module to return only one client, Testy McTest, whose Notes field contains “isStudent,” it creating a ClickUp duplicate record for Testy for every one of his 40-some appointments (yes, I book a lot of test appointments with myself).

Now before we get into the duplication problem, some background is in order. The reason I set up the student filter this way is that most Client records in Acuity are parents, tutors, collaborators or school leaders, with only a few adult students who book under their own names. Since the parent clients and student clients book the same appointment types (Free Consult, Tutoring), I figured the easiest way to differentiate them would be to manually “tag” the student clients with “isStudent” in their Notes field (the only appropriate field that’s exposed to Make modules). So my thought was to identify the students this way, then weed out the tutors, collaborators and leaders based on their special appointment types (Tutor Interview, Collab Call, School Leader Consult, etc.), then pass all bundles that fail those filters to a fallback route the classifies them as parents (Each role has its own list in my ClickUp CRM, so what the different filtered routes are doing is creating ClickUp “tasks”–really contact records–in the appropriate lists.)

So even before I try to filter client bundles based on their associated appointment types that match them on First Last (since Acuity has no unique client IDs), we have this duplication issue, though it stems from the same root cause, I think, which is that the scenario doesn’t know how to associate clients with appointments. So far the only (clunky, operation-intensive) duplication solution I’ve thought of is to put a List Filtered Tasks ClickUp module after the router and before the Create Task module, with an aggregator and filter between them checking that the array of task names doesn’t contain the First Last of the appointment bundle being passed. Thus only one bundle for each client will pass, creating just one new ClickUp record. This just seems very operations-intensive since I have thousands of appointments in my Acuity (though I only have to run this once to ingest existing clients into ClickUp; after that another scenario will update the CRM when new appointments happen, so it shouldn’t run up against usage most months).

Also, while the above might solve the duplication problem with the manually tagged “isStudent” records, it won’t solve the problem of how to classify the other clients based on what appointment types they’ve had. And if we can solve this problem, it might also solve the duplication problem.

The scenario is passing a series of appointment bundles to the ClickUp modules, but it has no way of associating a client with the appointment. But wait, it doesn’t need to, as all the client info that needs to be passed is in the appointment bundle. So we just have to pass it only once; we don’t even need to list clients. We just have to limit the number of appointments for each client. We could limit the List Appointments module to 1! Client types don’t have any overlap in their appointment types, so there wouldn’t be a danger of misrouting the client if you only got one of their appointments with which to assign them a route. You would really be creating a client record for each appointment this way, and using the List Clients module only to limit the appointments to one per client. The only issue is this wouldn’t be able to tag the ClickUp client record with a number of appointments or which types that client has had.

How to solve this problem? Here’s one way I thought of that won’t work, so skip unless you want my whole thought process.

What if I don’t limit the List Appointments module, but put several different counters after it each of which increments only when a certain appointment type passes. Could I then put a Flow Control module after the Set Variable that would make it wait until all appointment bundles from that client had passed, then would pass just one bundle on. Imagining we can do this, the one bundle that passes would trigger a Set Variable module to grab the variable that has been set with the results , and create the ClickUp record based on that? You might not even need the router this way, but could just set the ClickUp task’s List ID with a function like IF variable contains “Free Consult” OR “Tutoring” NOT “isStudent.”

Why won’t this work? Because on further research I realized you can’t make an Increment module to count only a certain type of bundle. It just counts every bundle that passes in a “Cycle” or an “Execution” (not sure how it decides what a “Cycle” is). Also I don’t think there’s any way to make it wait until all bundles from one client have passed; all I see is Sleep for a number of seconds. Also no way to limit bundles passed after the wait to just one; can’t make a router filter that passes one bundle as far as I can find.

My next idea was to make a separate List Appointment module for each appointment type (you can tell it which type to list). But this creates a bigger operations problem because then you are listing every appointment of type 2 every time an appointment of type 1 passes through! You end up with thousands of operations as the totals of each appointment type are multiplied together.

I’m starting to despair of doing all this in one scenario. Do I need a separate scenario for each appointment type? Even then if I want to count appointments of each type, I will need to do the List Tasks at the end to block duplicates.

Okay, I’ve been working on this 2.5 hours just writing this post and thinking about it; maybe I should pass it off to smarter people who might be brave enough to read this whole thing…

Thanks a million in advance! If you solve this and need free tutoring you can totally have some.

:camera_flash: Relevant screenshots

Appointments filtered by First Last from Client Iterator module, pulling all appointments for, say, Testy McTest when his client bundle passes:

:link: Links

[ Code { "and": "JSON", "in" : "code block"} ]
:x: Exclude Personal Information.

@Robert_McKay :raised_hands:

Hi there! I’m an AI that’s still learning. I’m analyzing your message for typical information that might be missing for our community members to help. Can you provide more information about the scenario you have set up, such as relevant screenshots and links to the code? Also, please exclude any personal information. Thanks!

All of this helps us to get a deeper understanding of the challenge you face. :make:

1 Like

Still thinking in public. What if I imported all my clients into ClickUp first in one scenario, manually sorted them into lists, then dumped in all the appointments through another scenario to populate each client’s LTV, appointments counts, last appointment date, active status, etc?