For our file folders at the end of the year we would run a Crystal report that would find all the vendors who were issued payments over the year. These labels would only consist of the vendor name and number (and the fiscal year, but that was handled with a text and parameter field). However, after a large upgrade our Mas90 install (to version ~4.something), the report would duplicate some vendors, and not print others at all. Digging into it I discovered that the report was set to use a table that nominally tracked AP vendor payments per period and that in the past this table kept a cumulative total for each vendor over the course of a year.
In the new version though, the new periods were broken out by months, essentially making the table a dupe of the AP check history table in Mas90. Now when the report was run, a label would be printed for every check issued to a vendor, and as a filter the report left payments out that were less than $100 (which was fine for a whole year, but bad for each month!).
I poked around and after consulting a couple different websites I came up with the following fix:
- I put the vendor number field into a group
- I suppressed the group header and footer sections (right click->suppress (no drill down))
- Went to Report->Selection Formulas->Group
- Added the following formula : {AP_CheckHistoryHeader.CheckDate}=Minimum ({AP_CheckHistoryHeader.CheckDate},{AP_CheckHistoryHeader.VendorNo})
What the last line does is it only picks out the first date that the vendor appears. This can still lead to duplicates if the vendor was issued more than one check on that same day, but it’s much better than getting twelve labels for many of the vendors!
(By the way, there might be a better way to do this as I don’t know too much about Mas90, and even less when it comes to Crystal Reports; but I was given the evil eye for mentioning that I could do this up in .Net or, even worse, with an Excel/Word mail merge where the ‘remove duplicates’ function had been used in Excel).