We have a project that we want to do, and want to find out if anyone has done this or something similar, of if a package from someone already exists. From our bank, we get a csv file that contains the customer name, their ABA routing number, the check number, and the amount of the check. What we would like to do is import this text file, then have Navision take over, and provide an interface to automatically post these entries. We would use the routing number as the match to find the customer. So, in the simplest form, what it would do is find the customer ledger entries for Customer A. And let us say that he has an invoice that is unpaid for $200, and he sent in a check for $200. Navision would then present a user a form that would basically have a check box asking whether to post this entry, as well as all of the other entries. The code so far would be a little difficult, but would not be the problem. Where it gets tricky is to efficiently find out which invoices to post to when the check is for several invoices. So now lets say we have customer B, and they sent a check in for $1000. And this customer has the following entries open: Inv1 $500 * Inv2 $250 * Inv3 $250 * Inv4 $200 In this example, I put a * next to the entries that should be posted to make up the $1000. If we expand this out even further, we can see where the code used to generate these entries needs to be efficient enough to recognize this, and not take forever when the amounts are not as easy to discern, and when the open ledger entries may number 10, 20, 30, etc. Anyone see a solution like this, or have any ideas of how we should proceed? Running Nav 2.6, but we are anxious to upgrade within the next year to 4.0.
Torolf, your approach is exactly what needs to be done, but instead of presenting the user with a form and checking the invoices to apply I would rather import the bank payment info directly into the payment journal, mark those lines where the system had problems allocating the amount and the user manually adjusts the “Apply to ID” / “Apply to Doc. No” fields. By default, I would first filter for the exact amount (perhaps include a small tolerance) and afterwards apply to amounts to the oldest invoices… Actually, we are using the same approach, though forcing the customer/bank to provide the detailed invoice nos, therefore making the application much simpler. Saludos Nils
Basically what you explain is what I want to do. Where we have been reluctant to go further is a couple of little sticky points that make this project more difficult. First, our bank charges by the field per every check, such as if they were to manually key in the invoice numbers on their end, we would get charged for every invoice # that they key in, for every check. I want to avoid that problem. And secondly, and this is where we have lots of problems in house, is that our customers are usually not very specific as to the invoices that they are paying. Most just write a check out, and that is it, and don’t specify which invoices that they want to pay.
most our customer use electronic banking. we have special unique numbers that are printed on invoices. and when customer pays they enter in their banking system this unique number. finding corresponding invoice is then piece of cake. if customer don’t speciay unique id, then we use “smart” serach methods like the ones mentioned before (amount, date, etc)