Stopping Iterative Macro

[trevorwightman]

Hello,

I know when it comes to an iterative macro, in order to make it stop, the "Iterative" output must produce 0 records, however, for my use case I am not able to return 0 records. Here is my scenario:

I have two files, one with postal records, and one with postal and email records. The goal is to append emails where I can to the postal only file. In both files I can have multiple records with the same information so when I join the two it produces many matches (due to the duplicates). After the join I have to use a unique tool on the recordID from my postal file to remove duplicates there. Then I use a unique tool on the recordID of my email file so I remove duplicates there. I take the records sent to the "duplicate" portions of the unique tools and iterate those records again which produces a few more matches and put this on repeat. Unfortunately the way my database is setup is that sometimes there are a LOT of records such that I could loop 500 times if I wanted to. But I do not, I want to limit my iterations based on how many more matches is produced on each successive loop. For example, if I produce 500 matches then continue, but if I produce only 9 matches, stop iterating.

Please let me know if I need to elaborate on anything.

[CharlieS · Accepted solution]

What I typically do in these situations is if I can determine when I need to stop the iterations, create a field like "Loop" and append it to all records. Then you have a Filter tool send all records to Loop or Done. You'll need a Select tool before each output to manage the field schema.

In your case, use the Unique tools to determine which records need to loop, but if you use a field to flag those records, then you can add additional criteria like "record is unique AND no more records worth matching" kind of logic. That way this new field is used to control the loop instead of just the records. Does that make sense?

[trevorwightman]

I am not exactly sure I follow. Here are some more details:

In stage 1, I join on First, Last, and Address and I loop until I have gotten enough matches.

In stage 2, I join on Last, and Address and I loop until I have gotten enough matches

In stage 3, I join on Address only, and I loop until I have gotten enough matches.

So, in lieu of making the situation simpler, I actually lied a small amount. I take all non matches, PLUS the duplicates and iterate all of those records. So I might have Start with 200k records (100k of each), I match 1,000 records and I sent those to the Matches output. This removes 1k email records and 1k postal records and I am left with 198k. On iteration 2 I might match 100 records. Now I am left with 197,800. And so on. My stopping function would be f(How many records did I send through iteration last round, minus how many records am I going to iterate this round. If the different is less than 20, meaning if I have 10 matches or less, we can stop).

It seems like to make this work I need to use a summary tool to count the number of records are coming from my Input tool. Use a summary to count how many records I have at the "Unmatched Plus Duplicates" stage, and subtract the two using a formula tool. Then I will take this number and append it to all records. Finally my filter tool will say, if this number is great than 10, Loop, else Done. I will have an output to on the Done section such that I will be able to pick up all of these records for my Stage 2, and so on.

Do I have that right?

EDIT: It worked and you are brilliant. Thank you, I almost owe you my life! 😄

[CharlieS]

Nice work! That's my tried and true method for iterative macros; I'm glad it helped.