I'm running a report that is pulling two values from a table in a batch scheduler. I need just part of one of the values and all of the other one. When I pull both values in an NL filter report, I end up getting a lot of duplicates because the end of the one of the values changes but the front doesn't change as often. I need a way to like group or remove duplicates but also need to do it automatically because I'm using the batch scheduler
Here's what happens when I pull back two values from the NL filter
As shown above (not my company info, I just filled in some example of what it looks like) I only need the first 3 to 4 characters for first value to get the Job Number since it doesn't live in nav anywhere but on the beginning of a job. The building number is found in nav and I don't have to cut and split it. Since I'm just taking the first 3-4 characters for Job No. I'm left with a lot of repeats. I only need one combination of each Job no and building numbers.
This is what I need left when it removes duplicates and I also need it to do it automatically, no macros or having to manually filter stuff because it's in a batch scheduler.
Is there a way to get unique combos? It's hard because the Job No doesn't have its own value in nav, it's only part of an order number so you have to do some text manipulation to get it which makes this extra hard.