Checking Multiple Apps for Duplicates, Avoiding Compounding Operations from Successive Iterators

[Robert_McKay]

I have a recurring issue with various scenarios and I wanted to generalize it and see if others with conceptually similar issues have found a solution.

The problem arises in cases where I want to search multiple apps for existing records relating to an incoming payload (say, to see if the client who just booked a new appointment already has a record in Google Sheets and/or ClickUp).

I list the existing records in app #1 (say, Search Rows in Sheets), aggregate a matching parameter (say, email) and then iterate through the results. I then run all those bundles through a router with filtered routes to see if they match the parameter (email) on the new payload that triggered the scenario. If they do, I update the existing record with data from the payload. If they don’t, I create a new record.

Now, in either case, I want to check for an existing record in app #2 (say, ClickUp). Since Make doesn’t have a converger module (the opposite of a router, though I think they used to), I have to duplicate all the following steps for each route. For instance, after creating a new Sheets row, I have to list, aggregate, iterate, route, filter, and update or create ClickUp task. Then I have to copy all that down to the other route so it also happens after updating a Sheets row.

Now here’s the problem: It would seem more elegant to search both apps’ existing records, then route the bundles through a four-way router that functions like an ELSIF: route 1 would pass bundles that matched in both app 1 and app 2, and subsequent modules would update both records; those that fail go on to route 2, which passes those that match app 1, which updates that record and creates a record for app 2; those that fail go to route 3, which passes those that match app 2, updates that record and creates a record for app 1; and those that fail go to route 4, the fallback route, which creates records in both apps.

The problem is that if you list the records in both apps on the same route, you get compounding numbers of operations, with each output bundle from app 1 module triggering a search operation on app 2 module, thus using ops equal to the product of the numbers of records in each app. With lots or records or more than two apps, it can run easily to five figures and blow your usage in one go, unless you force stop is, as here:

So I think the only way is to create duplicate routes. Less elegant, but should solve the problem. Any ideas? Will report back once I’ve built the ugly duplicative version.

[Loopz]

hi

Not sure if I completely understand the case, but:

have you tried using a router with multiple branches? In case a branch is conditional, add a filter to it. In case you will need the output/result from your branch, use the ‘set variable’ module at the end of the branch and use ‘get variable’ module in the next branch.

Setting variables and getting them afterwards can be a workaround for the missing converger module in some cases.

Let me know if I missed something

[Robert_McKay]

Yes, thank you, this does seem to be the accepted answer for the converger workaround.

But I’ve realized that I have another problem, which arises even if I’m checking for dupes in just one app. Namely, multiple bundles coming in to be checked against the list of existing records still creates compounding operations as every new bundle triggers an iteration of the list of existing records, so you end up with operations equaling the product of the incoming bundles and the existing records, which can be in the thousands. I’ve been opening various support tickets about this but let me know if you have any ideas. Do you think I could somehow use Switch module or function but pull in an array of all the emails, say, from existing records, as the “pattern” argument, and check each incoming email against that whole list? Regex? Increment on a match and route to update rather than create record if the counter module is >0?

Here’s an example:

[Loopz]

Hi @Robert_McKay

Not sure if I completely understand the context yet.

@Robert_McKay

Do you think I could somehow use Switch module or function but pull in an array of all the emails, say, from existing records, as the “pattern” argument, and check each incoming email against that whole list? Regex? Increment on a match and route to update rather than create record if the counter module is >0

That could work, but maybe I don’t have all details right yet.

Which modules’ consumptions would you like to get rid of? Not these two, because they consume only 2 operations, right?

Can you share a screenshot of a run result where we can see the operations consumed for a run, and can you highlight what part you want to simplify in terms of operations?

[Robert_McKay]

Current confused state of the scenario:

Originally the scenario looked like this:

Where for every Sheets record the ClickUp module would run another operation, listing all tasks and iterating through them. Here I’ve stopped it before it can do hundreds of operations.
Then I tried putting the ClickUp list tasks module first, so it would only run once:

But the problem is I don’t know how I can check each Sheets record against that whole list of ClickUp tasks, then route it to create a new task if a matching one doesn’t already exist.
Does that make more sense?

[Loopz]

Does the ‘list filtered tasks’ clickup module need any input values derived from the google sheets module?

If so, please explain.
If not, does the scenario work this way? → for every google sheets row, you need to know whether a corresponding clickup task exist. if no task exists you create one, else you update the task?
If this summary is correct, have you tried this? →

• start with listing all clickup tasks (like the first module in your last screenshot) but also aggregate using an array aggregator; aggregate one field that you later want to verify against and which also appears in the google sheet.
• then iterate over the google sheet (like the second module in your last screenshot)
• do not aggregate, just keep the bundles as ‘loops’. While you’re now looping over the google sheet rows, check if a corresponding clickup task exists by using the contains array function on the array of the first module. the second parameter to the contains function would be a value out of one of the columns for your google sheet row that matches the aggregated values from the first module.

Example:

scenario blueprint:
simulate-clickup-task-lookup.json (10.0 KB)

[Robert_McKay]

Thank you so much; this gives me a great place to start. Really appreciate the functions all being written in that blueprint! You are correct about what I’m trying to do. But don’t I need to aggregate the results of a Get Range Values Sheets module into an array to then iterate over them?

Also I need to aggregate ClickUp task IDs and Sheets row numbers in addition to the task name and “First Last” column to which I’ll match it. That way I can know which task ID to update and which row number to get additional values from:

The two identical Sheets modules after the router get all the relevant values from the row number from the original Sheets iterator.

There’s an additional issue, though: the update task module needs to know which task to update. So after the filtered route, I need to iterate through the task names and IDs from that first aggregator and pass the matching task name’s ID to the update task module through a filter:

The final issue, which I know is solvable with a different function, is to get the initial filtered route to catch matches based on First Last now that this value is one level down into an array:

Currently this is missing it. Some kind of nested function needed, or another aggregator to put just that name value into its own array?

Here’s the scenario as it now stands. Thanks again for all your help!
Sheets to ClickUp Dedupe.json (203.4 KB)

[Loopz]

But don’t I need to aggregate the results of a Get Range Values Sheets module into an array to then iterate over them?

If all you need is to process those rows individually, then there’s no need to aggregate and iterate over it again.

Also I need to aggregate ClickUp task IDs and Sheets row numbers in addition to the task name and “First Last” column to which I’ll match it. That way I can know which task ID to update and which row number to get additional values from:

In case the ‘Get Range Values’ module doesn’t return the rownumber by default, you can add a column in that range with a formula to calculate the rownumber. That rownumber you then access within make as one of the fields/column within the range.

In case you need from the clickup tasks both the id and name, you can have the 'list filtered tasks` module output both these properties.

Later on, when looking up a task by name, use the map function to filter the array with tasks to only tasks matching the name. https://www.make.com/en/help/functions/array-functions#map-complex-array--key--key-for-filtering---possible-values-for-filtering-separated-by-a-comma--
Example: map(arrayWithClickupTasks;id;name;nameOfTaskToLookup).
If the result has at least one item, then the task exists and you can access the task id by using get(map(arrayWithClickupTasks;id;name;nameOfTaskToLookup);1).

Does this help with the remaining challenges?

[Loopz]

Where is this second ‘Get Range Values’ module used for? Don’t you have all required data related to the row already available?