Creating a macro to remove unwanted records

[jmarcuzb]

Hi guys. I have a problem I just cant seem to figure out. I thought I had a solution but it doesn't work for some reason. If someone could either identify why my macro doesn't work or offer a better solution, that would be great.

Here is the problem.

1. I have a set of records, and each record has a unique id. 

ID	FirstName	LastName	Title
1	TIm	Jones	VP
2	Jerry	Smith	SVP
3	Tom	Williams	AVP
4	Jon	Davids	EVP
5	Mark	Davis	CFO

2. I have identified the ids I need removed and have them in a list

ID

2

4

5

3. Macro Settings

     a. I have created a batch macro containing a filter that filters by default ID=2

     b. There is a control parameter

     c. There is an UpdateValue Action with "action type" = "UpdateValue(Default)"  with Expression - value = "[ID] = 2", Mode - value="Custom",   Replace a specific string set to 2

4. The macro is being passed the entire data set from the initial workflow in step 1 and also the "list" of IDs I want to be removed.

Here is the issue. Even though I am passing it a list of the 3 ids shown above, it is only filtering out the first ID listed (ID=2). The others are not removed.

I thought the macro would run 3 times and each time it would grab the next id in the list but it is not working.

This cannot be a simple filter because these IDs are dynamic and I have no way of knowing what the IDs will be on every run.

Can anyone help me with this? 

Thanks guys.

[JamesCharnley · Accepted solution]

@jmarcuzb  I'll let someone better at macros comment on that, but if you already know the ideas you can achieve the result you want in an outer output anchor of a join. In my example, the IDs coming out of the L output anchor are all the IDs that don't match any IDs in the table of IDs you don't want.

[ChrisTX]

Can you post your macro and a small sample macro input file?  in the Action tool, make sure the right line is "selected" / highlighted.  

Chris

[jmarcuzb]

@ChrisTX I have modified the workflow and macros with sample data and attached here. Another issue with this is that the batch macro creates duplicate records (which drives me nuts) and I have to end up doing a summarize tool with a group by after the macro runs to eliminate the duplicates. I'm pretty sure that is wrong but I'm not sure of how to correctly do this.

[JamesCharnley]

Is there any particular reason that you need this to be a macro? The fact you can filter that list of IDs that you don't want means that the join logic should be completely dynamic and remove the need for a macro at all.

[jmarcuzb]

Ah man I think I can see part of the issue. The macro runs 3 times but each time through it removes only the id at that iteration but leaves the others untouched. It removes the 2 the first iteration, then the 4 and then the 5.  Now I can see the logic, but how can I get what I want using this? I know it has to be easier than I am making it.

[jmarcuzb]

Good question. No it doesn't have to be a macro. I'm open to any solution that resolves the issue. That was just my initial line of thought

[jmarcuzb]

This could work. Let me try it. However, the macro problem will help me in other areas. I've run across that issue several times when passing things to a batch macro so I can utilize both these methods. I am going to try your approach on my original data set.  I will update this post shortly.

[JamesCharnley]

In the example workflow you posted you've filtered to find the IDs you don't want using the [Status] field. If this is how you're choosing which IDs you want might you also just be able to take the F anchor of the filter as rows you want to keep or does the real data have additional complications? Curious to see your update!

[jmarcuzb]

Thanks @JamesCharnley . Your initial solution with the outer join was the thing needed for this solution. I did have to modify slightly because of the way the data is constructed but your solution gave me just the thing I needed to figure out the rest.  I knew it had to be easier than I was making it. Thanks for that!  Because I am also an Enterprise Architect/Developer that work on large scale projects, I think that I overthink it sometimes and make things way more complicated than they need to be. 

That macro issue is still a thing though. I need to figure out how to pass a macro data and do operations on it without it duplicating all the records every iteration. 5 iterations of 100 rows gives me 500 results. This has been a pain in my side for some time now but I guess I can post that question with a specific scenario at a later time. Thanks everyone!!