There's a good reason for the double-quotes - it is a way of containing commas that may appear in your data fields. A couple of suggestions: use a different export format, like Tab, XML, Excel - or, create a calculation field that includes your data fields combined together, separated by commas. Example:
substitute ( list ( field1, field2, field3 ), ¶, "," )
I'm a bit puzzled as to how the double quotes are preventing you from finding duplicates...
The historical data that is being compared does not have the double quotes. I deliberately had two duplicates in the test data as a control.
The eassiest way to export to comma-separated with quotes is export to .mer
This useually exports with semi-colon instead of comma, but that's easilly resolved bij replacing ";" with "," in any texteditor ;-)
"The historical data that is being compared does not have the double quotes. I deliberately had two duplicates in the test data as a control. "
Pardon my curiosity here, but what kind of duplicates? Since all the data is double-quoted, the duplicated values should also be identically quoted, or did the data duplicate a value already present in the other database?
The data represents invoices keyed that day into our AP accounting system. While the system will flag if the invoice number just keyed is a duplicate to one paid already, many of our keyers will pass through the message thus entering a duplicate. So before posting the data we verify that there are no duplicates.
Prior to June 1 this year, we were using a system that did what I am trying to recreate for the new one. The historical data that we are verifying against, back to 2008 is storied in an Access database, there are no double quotes around the data. I really know very little about Access.
Hmm, maybe there's a way to avoid the whole issue.
If it's just to check against existing invoice numbers, Why not import those numbers from Access into a filemaker table so you can check them in filemaker? Data in Access can easily be exported to an Excel file, simply open the table and use the Analyze with Excel option and save the resulting excel file. Now you can import the data into filemaker.
Or, since invoice numbers are usually sequential with no gaps in the series, you may be able to simply check the number against a range of values to verify that it's not a duplicate.
Also: "While the system will flag if the invoice number just keyed is a duplicate to one paid already, many of our keyers will pass through the message thus entering a duplicate."
That's a really dangerous way to have your system setup. Invoice numbers should be assigned automatically by the system instead of being keyed in. Here where I work, we have invoice numbers pre-printed on our NCR invoice forms and we still have the system auto-enter the invoice number by first entering a number in a table that tracks invoice number series for each invoice printer on our network.
I would love a Filemaker solution. However, it is not supported, only Access. IT has taken it upon themselves to decide what is to be secured and how so.
While invoice numbers might be sequential in an AR setting they are not in an AP setting. I have over 3000 vendors in my system. All formats of invoice numbering are used. Nearly all AP system will, when the invoice number is keyed, validate if the invoice number has been used with that vendor number. In the AP environment we are paying invoices that are numbered by our vendors' systems, not ours.
However, using AT&T as an example: I have an AT&T set up for land lines, cell phones, and long distance services as well as a couple minor one for specialty data lines (all told, 8 vendors). If a processor selects the wrong AT&T vendor to pay an invoice, the system does not know. AT&T says I didn't get such and such payment, I look at the correct vendor and say "yep, you are right, not there." I key the invoice. Now I have made two payments to AT&T for the same invoice.
What our Access app does it review on a different key: InvoNumber + InvoDate + InvoAmt as a compound key. We are not looking at the vendor.
The nice thing about the Access app is the speed it did the finds. I could load a 900 invoice file for the day to check for dupes within the 900 file, as well as the historical data of 25,000 records with the dupes printing out in a matter of minutes. I am NOT an Access fan. But this just works nicely. I just need to create the filter that loads the program for that day's work.
Phil, I really appreciate your help as well as everyone else. It has been very frustrating. It has also been very rewarding. While I have not licked the duplicate invoice aspect of the problem, I have resolved the creation of positive pay transmission files from our check registers. That has been a time saver.
I have to run the script in steps. I haven't figured out how to open to the correct folder on the J:\ drive to start the process. Nor how to export the positive pay file to the correct folder on G:\ drive. But I continue to read and pursue the goal.
I have augmented the file where I filter my dupes to provide some beneficial info that great. I am looking to come up with the calculations and scripts to derive metrics on the productivity of the AP keyers.
Yeah, I was thinking in terms of invoices you issue not your vendors...
Even if you can't do an all filemaker solution, (I've been there--that's how I came to learn how to design in Access in the first place!), you might be able to get the list of invoice numbers from Access in a single import of data that you then update from within filemaker from that point forward...
Phil, I am going to have to think of a FMP solution to this, because errors are slipping through. If you will let me think out loud on this:
I have the table of historical invoices. Each end of day it will get updated for the new day. I will have a table for the new invoices not yet posted, but to be reviewed for duplicates, both in the same batch and against history. There are 10s of thousands of historical invoices. There could be as many as a 1000 in a given day.
My thought is to use a third table to hold the dupe pairs of duplicated invoices. My first pass would be those dupes that are wholly in the daily file. I would be using the ! to capture those.
For the next comparison I would use a looping script to get the current record of the Daily table to find in the History table? If found, then copy that record from History and the one from Daily to table 3 for reporting.
I would print the report for investigation from table 3 (reporting only). After resolving the issues in AP, I would move the daily records to the history file. Then I would post AP. Hopefully this will not be too long a search.
Does this read as if I am on the correct path?
I assume a "duplicate invoice" would mean identical Invoice numbers and vendor IDs?
Two different vendors could conceivably issue Invoice numbers of "54321", but that shouldn't be an indication of an error since the vendors are different.
What I don't get is why you would allow the duplication in the first place. Wouldn't you want to trap for this during initial data entry and throw up an error message if a data entry person tries to log a combination of invoice number and vendor ID that isn't unique?
While two different do (and have done) issued the same number, we use a compound key for the search: invoice.num +invoice.date+invoice.amount. The point is to catch the error, paying the invoice to the wrong vendor. The program was developed while we were using the DOS version of our accounting system. It was a batch processing system.
I might enter 275 invoices in a shift. It was possible the first invoice could have a copy farther down in the batch. Since none of the invoices had been posted, merely entered, the system couldn't flag the duplication. It did not do a duplicate check during the posting. So the Access application was created to do a pre-check before posting. The AP system could only say an invoice was a duplicate if the first was already posted in a prior day.
The other error, of posting to the wrong vendor was harder to catch before the Access app. Keyers would think they have the vendor numbers memorized. So they key A541 when it should be A451. With head down, they do not see the wrong vendor name pop up. The next month vendor A451 is looking for payment of the invoice. When queried, it did not show in the system for that vendor. Keying it, it did not dupe since it had been keyed to a different vendor number.
Prior to ACH payment types, vendors would have multiple remit-to-addresses. A vendor might have received payment, but to the wrong lock box. Maybe they would share it when discovered, or maybe not. In those cases where two vendors legitimately issued the same invoice number (coincidently) it was the rare occurence.
In the last two days, I have had two errors come to light. I keyed the same invoice twice. I have a good rapport with the vendors I process. They brought it to my attention quickly and I took a credit for the duplicate item. This morning in our check run was a check for $23,300, paid to two different vendors. I did this with a limited test version of what I am trying to do.
I voided the wrong payment before it went to the vendor. There are a number of reasons the error happens, mostly carelessness on the part of the keyer not paying attention. Sometimes we get to keying so fast that the data connection to our servers can't keep up, the warning message gets lost.
Given the $23,300 error this morning I need to step this up. We use a mega-application that is a vertical application to the cylinder gas industry. I really liked the DOS version. The limitations were common to the time it was written.
The Win-version is very poorly written by comparison. My director is tired of me "bitching" about it. We really do not have another option. Overall, it will be a good option once we work through the wrinkles.
Sorry for the long narrative. One of the positives I am foreseeing in this project of mine is that I can do analytical work with the data: productivity metrics: invoices keyed per person, number of lines of distribution keyed, etc. This will be invaluable (or at least I hope my director think so). Part of the benefits to having a new systems is to be able to roll our company acquisitions into the system. Currently 5 different accounting systems are in use. By the end of the year, there will be ours and one acquisition. If anyone reading this has any tips for me they are more than welcome.
I understood you were trying to set up an "all filemaker" solution for this.
If so, I'd still be trapping for these at the data entry point. You can use a script trigger to identify the possible error, sound a beep, pop up a modal floating window and halt the data entry until the issue is resolved. This would keep a "head down" data entry clerk from running over the warning message.
I'm not opposed to doing the same and more error checking in a batch process, it's just much easier to correct errors by either making them impossible or by catching and correcting them at the time they occur as your data-entry person should at that point have all the correct relvant data at their finger tips where fixing them after the fact often requires a paper search to locate the correct data that should have been entered.
The data-entry is in the Tims4 accounting program we do, not a Filemaker Solution. The Access or Filemaker solution is to double check the massive amount of data keyed by varying individuals from points outside the office. I can't change that aspect. Tims4 is the accounting system. Anything else is supplemental to the process; i.e. an augmentation.