Allocating payments to invoices questions

[meglome]

Need to allocate payments to invoices, helping a friend out (will I ever learn) and don't seem to be able to find the best way to do this.

So I have 3 tables...

Invoices, Payments Received and Payments Allocated (join table). I'm using Filemaker Pro Adv 11.

So let's say in...

Invoices. I have Job No, Invoice Date, Customer name and Total.

Payments received. Payment ID, Date, Customer and Amount.

Payments Allocated(join table). Allocated ID, Job No, Payment ID and Amount.

I've gone in and manually created all the invoices in the payment allocated(join table). Is there a way to automatically do this when an invoice is created?

Also I tried to add a portal for payments allocated(join table) in payments received. But it's creating a new allocation rather than allowing me to add to an existing one that I've added from the invoices.

I have it working in the grand scheme of things but I have to go into the join table and do everything from there.

What am I missing? Any help much appreciated.

[bcooney]

I don't understand the Payments Allocated table. Why can't you simply have Payments as a child of an Invoice? Also, since an Invoice has a CustomerID, you don't need one in Payments. Payments simply needs its parent InvoiceID.

Cust->Invoice->Payment

[meglome]

I don't understand the Payments Allocated table. Why can't you simply have Payments as a child of an Invoice? Also, since an Invoice has a CustomerID, you don't need one in Payments. Payments simply needs its parent InvoiceID.

Cust->Invoice->Payment

Thanks for the reply.

Well because they may get a payment for more than one job from a customer at one time. So doing it this way allows all the payment to be allocated against different jobs. The same would be the case with bank lodgements (when I add it), they could be from multiple jobs/payments.

Maybe some can point me to an example as I'm definitely missing something here.

[comment]

I've gone in and manually created all the invoices in the payment allocated(join table). Is there a way to automatically do this when an invoice is created?

For what purpose? There is no data in these records (other than JobID) and the time to create them is during the allocation - after a payment has been received.

Also I tried to add a portal for payments allocated(join table) in payments received. But it's creating a new allocation rather than allowing me to add to an existing one that I've added from the invoices.

You cannot "add to an existing one" because it's not related to the current payment (or any payment). The process of allocating a payment is creating new allocation records - and you need the select the job and the amount for each as you create them.

---

My own personal preference is to avoid the issue altogether and assign payments directly to customers. There is too much room for error in this method with customers under/overpaying and users having to allocate the payments manually.

[bcooney]

"My own personal preference is to avoid the issue altogether and assign payments directly to customers. There is too much room for error in this method with customers under/overpaying and users having to allocate the payments manually."

I second this approach! :thumbup:

[Vaughan]

Hi Barb and Comment... I'm working on an invoicing system right now, and am just getting up to the allocation of payments.

You propose "just" assigning a payment to a client, without splitting amounts between invoices if required?

[comment]

You propose "just" assigning a payment to a client, without splitting amounts between invoices if required?

No, I am merely questioning the "required" part. I should also add that there may be legal issues involved here, to make this even more complex. For example, there may be a requirement to allocate payments towards taxes first.

In any case, leaving the allocation process in the hands of the user is very problematic - as they can easily allocate more than the paid amount, or less. OTOH, they should allocate less if the client overpays, so...

[Vaughan]

Ok, so would the process be something like this:

Payment arrives. User navigates to Customer record in database. Adds payment as related record.

Would there be an interface that prompts the user to allocate this payment to an outstanding invoice? It might have to be smart enough to remember if the client had a previous credit balance and prompt to allocate that before the new payment, else the credit could get lost in the system easily.

Customers <-> Payments <-> Allocations <-> Invoices

Thanks.

[comment]

It might have to be smart enough to remember if the client had a previous credit balance and prompt to allocate that before the new payment

Yeah, well - that's essentially my point: in order to know that a client has a credit balance, you need to track their overall balance. Now if you already do that, then what's the point of micro-managing the allocations?

[Vaughan]

Now if you already do that, then what's the point of micro-managing the allocations?

Agreed... but how then do invoices know when they are paid? And if the customer wants to pay a later invoice while leaving another invoice unpaid (say the delivery is late) then how is that managed?

I cannot see a way of managing the payments WITHOUT allocating them by hand in some way. I'd be mighty glad to be shown otherwise though. ;)

[comment]

but how then do invoices know when they are paid?

They don't.

if the customer wants to pay a later invoice while leaving another invoice unpaid

I am afraid I have more questions than answers to this one: are they allowed (legally) to do this? If yes, do we track their wish in any way (in order to explain the irregular allocation)?

If you answered yes to both, then you have a business rule - that could be possibly incorporated in a script.

I cannot see a way of managing the payments WITHOUT allocating them by hand in some way.

Well, it depends on how many rules you have, and whether you're able to foresee all possible scenarios (and there can be an awful lot of those). That's why I'd rather stay away, unless it's absolutely required by the business.

[bcooney]

Vaughan, -full disclosure - I've always built the elaborate interface to allocate payments to invoices (dealing with partial payments, overpayments, etc.). I've never really stepped back and said, "is this required?" Think of how much simpler it is if it's not. So, I'm a being a bit disingenuous with my vote, because I've never actually gone this route. Next invoicing system, however, I certainly will propose the non-allocation method.

[RodSierra]

Vaughan, -full disclosure - I've always built the elaborate interface to allocate payments to invoices (dealing with partial payments, overpayments, etc.). I've never really stepped back and said, "is this required?" Think of how much simpler it is if it's not. So, I'm a being a bit disingenuous with my vote, because I've never actually gone this route. Next invoicing system, however, I certainly will propose the non-allocation method.

From another perspective, we include our financial module in all our solutions, but only turn it on at about the point the discussion above involves. We post transactions to financials if on, and simply close those same transactions if not. IMHO you will find yourself continually adding more and more to the partial integration if you bow to the clients desires in the beginning for partial implementation. It's a never ending trial to keep client and accountant happy by compromising. For us its all or nothing when you reach a certain point with a client with respect to financials.

[Vaughan]

Thanks for the help folks. Much appreciated.

[Fitch]

Adding my non-allocated $.02 here... I've been building a system over the past year that had the "requirement" that we had to know which invoices had been paid. I went down the road of allocating payments to invoices.

You run into some hairy problems when you have to deal with a lot of adjustments (bounced checks and invoice corrections). For example: a payment comes in advance, so there is nothing to apply it to. Another payment comes in. Now invoices are run, so you have to determine which payment to apply. There could be partial payments. An invoice that was paid has to get reversed, so you "unapply" the payment. On and on...

I'm now rebuilding the system to simply link payments to the account. Turns out that the real "requirement" is to be able to easily understand the invoicing and payment history of the account. The "payment applied" records were hard for the users to understand, so in the end not all that useful.

[Vaughan]

Tom, thanks for sharing your experience. I'll get the client to take a look at this thread.

[Dr. Evil]

Agreed... but how then do invoices know when they are paid? And if the customer wants to pay a later invoice while leaving another invoice unpaid (say the delivery is late) then how is that managed?

I cannot see a way of managing the payments WITHOUT allocating them by hand in some way. I'd be mighty glad to be shown otherwise though.

Wow! No allocation would be great... but I don't see how to get around it. I think it is important to track payment status on an invoice so you can track past due balances, 30+, 60+, 90+, etc... when payment is past terms, additional finance charges may be applied. Clients never write one check per invoice, they always write one check for total due.

I'm currently attempting to build a solid invoicing system as well and am very interested in this topic and what you all have to say.

[Vaughan]

I think it is important to track payment status on an invoice so you can track past due balances, 30+, 60+, 90+

In a system I have just completed, there are no invoices. Instead, there are accounts. Each account has "charges" and "payments" and the balance is the difference between the two. It may change each month. If an account has a balance outstanding then they get sent a statement. If a payment comes in it reduces the total owing.

It just occurred to me that "invoices" are really "requests for payment" that have a due date. Wow. Let me think about that a while.

[Dr. Evil]

In a system I have just completed, there are no invoices. Instead, there are accounts. Each account has "charges" and "payments" and the balance is the difference between the two. It may change each month. If an account has a balance outstanding then they get sent a statement. If a payment comes in it reduces the total owing.

It just occurred to me that "invoices" are really "requests for payment" that have a due date. Wow. Let me think about that a while.

I like where you're going on this, very interested in where it goes! A perfect invoicing/payment/account system that would work in any business model would be such a jewel. This topic is mega!

[Fitch]

Further accounting food for thought here:

www.martinfowler.com/eaaDev/ (see "Accounting Patterns")

... or the PDF version:

www.martinfowler.com/apsupp/accounting.pdf

[Vaughan]

Thanks Tom, I'm printing this out for bed-time reading tonight (yeah, I'm into quaint technologies).

[LaRetta]

I cannot see a way of managing the payments WITHOUT allocating them by hand in some way. I'd be mighty glad to be shown otherwise though.

How could I have missed this post?

Many businesses adhere to strict accounting rules (mostly forced upon them by the accounting industry, but there are good reasons). If a bookkeeper receives a check and doesn't have an invoice to apply it against, it is NOT their money and to deposit it is considered poor account practice.

If a customer pays a more current invoice and leaves an older one then it is the responsibility of the bookkeeper, owner and/or accountant in charge of past dues to find out why. If a customer over pays and it is not clear that the balance goes to another invoice, the customer must be called before depositing the check. I always design with applying to invoice. It is better to deal with oddities when the check is first received than to simply place it on their account. I've seen this happen. And pretty soon, business is just taking whatever funds remain in their 'accounts' to pay their past dues. They may be disputing one! Any time a payment doesn't match the invoice, the client should be contacted. I am firm (on my belief) on this one. It is good customer service.

I create a checks table (all incoming checks and cash) which is also used for the automatic deposit. Total amount of checks entered should equal total amount of double-checking using adding machine. Scripting splitting this confirmed check amount across different invoices isn't difficult. It is better to hold a check than to apply it to an incorrect invoice.

Some businesses can get away with customer accounts (contingency payments) but it is wise to check thoroughly on the rules of both and to discuss with your accounting firm before designing either way. I cannot tell you how I cringe to see developers (without a bit of accounting) designing accounting systems. And I cannot tell you how bad some of those systems run (with no proper accounting principle behind them). Nor can I tell you the horror stories I have heard from accountants - and not just about FM but about 'programmers who design accounting systems.'

Just pull the accounting world into accounting designs before you tackle it; at least spend three hours with their bookkeeper! And don't take the Owner's word for it; they usually know very little about their books. As I once read and still chuckle about, "Go to the library, people. Go to the library." :smile:

[Keith LaMarre]

So I have been forwarded this link on invoices/payments, etc. and I still think that payments should be applied to invoices if available and then to customers if there are no outstanding invoices. I have a system in place that checks for "unallocated" amounts - amounts that have been sent in with no corresponding invoice attached - that allows a user to apply this amount to an invoice at some future time. I also think that its dangerous simply to apply payments to accounts - as many accounts will have multiple departments - that may not want to have certain invoices paid with funds. I had an older system where law firms and insurance companies paid for medical records - but there was absolutely no way to simply apply the amount to the Law Firm - you wouldn't know which records to deliver...

[comment]

I think you are saying that allocating payments to invoices is required in some cases. That does not mean it's required in all cases.

[Keith LaMarre]

Yes - in the best situation you would have an invoice for the payment - but obviously that may not always be the case. In addition, there may be over and under pays involved - it can definitely get tricky.

However, the goal should always be to apply payments to open invoices (hopefully the correct one) - even if that means applying a payment to an account first and then moving it to an invoice at a later date.

[LaRetta]

I agree with you, Keith. Unless the business allows Retainer, a check should not be deposited unless it is clearly associated with an invoice. The more complex your business, the more important this becomes. Many business have it written right into their regulations.

Misappropriation of funds messes with aging and doubles the bookkeeping required to reverse it. I have been involved of clean-up where checks were applied incorrectly. And then the next check is applied incorrectly because that invoice isn't available and on it goes ... one audit took three months and four people to clean up the books. That business lost a lot of customers who no longer trusted their statements (and rightfully so). People get HOT when their money is mishandled. If you don't know where to apply a check - hold it and contact them. And if you make that the rule, you will always have happy customers and happy bookkeepers and owners.

And no, it is not required in all cases, Michael, and it can even be safe to run with scissors ... but I will never recommend it.

[LaRetta]

Hi Keith, I did not see your latest post.

even if that means applying a payment to an account first and then moving it to an invoice at a later date.

We disagree here then. AP really dislikes writing checks back to AR people because a check was improperly deposited which was not for AR at all - but rather a check to the owner's wife from her sister (who might also be a customer). It messes up both AR and AP. Addressing the issue later is too late. If unsure don't deposit. And I guess I've made my point. Can I assume that not one of you have contacted a CPA to find out how it should be handled?

[comment]

And no, it is not required in all cases, Michael, and it can even be safe to run with scissors ... but I will never recommend it.

I don't think we disagree on any facts, do we? Because I will always bow to your judgment when it comes to accounting. And I certainly agree with your advice to consult the client's bookkeeper or CPA.

However, I know what my CPA would say if I asked about my own business. After all, this is Filemaker: I would venture to guess that many, if not most businesses using it are run on cash basis and have no clue what AR or AP are; still others use it to shadow their "real" system that is run by a third-party accounting service, just to get an "executive overview" of how they are doing.

So my recommendation is still the same: don't do it unless you have to.

[LaRetta]

After all, this is Filemaker: I would venture to guess that many, if not most businesses using it are run on cash basis and have no clue what AR or AP are; still others use it to shadow their "real" system that is run by a third-party accounting service, just to get an "executive overview" of how they are doing.

Nice observation. I hadn't considered anything short of manic adherence, LOL (true). 'What is the context and purpose?' ... uttered by you several years ago and used by myself and others ever since, in almost every question relating to design ...

If you are a small business, only a few customers, you are cash business, you enter checks received yourself and you know your customers and their children, you let people have credit and then allow their accounts to slide without late fee or interest, you use Quicken or MAS90 but want some data in FileMaker for reports ... or any number of combinations which would fit you into the quote above, then put that money anywhere you want; after all, it is your business so just make the rule. :^)

But if the purpose is to use FileMaker as an integrated part of your business (as suggested by the title of this thread), handling at minimum your income Accounts Receivable (AR) and possibly your expenses Accounts Payable (AP) and you are having trouble tracking payments because you have so many incoming checks (blessed are you) and/or if payments can split accounts or pay multiple invoices, or if you suffer NSF fees (check return fees), product returns, carry payments 30, 60 or 90 days and track aging (and charge interest) then strict manic adherence is suggested for your own sanity. Heck, even if you are cash business you many times allow money orders and checks to be mailed in. That 80-year old grandmother didn't put a return address, her address isn't in FM, and her hand-writing is terrible. Are you going to deposit her check?

Forcing this rule (only deposit what can be clearly applied to an invoice) doesn’t make life more difficult, it saves headaches and problems trying to sort out the mess later. Unless you watch those 'on account' accounts, they can bloat and become a repository of unidentified debits, credits and fees, used to just 'make things balance.' I have seen 'on accounts' with 200+ misplaced credits, debits and fees for chain stores (in a < 10-employee business). Misplaced invoices can cause another problem ... if you place it on account to the wrong person (T. Brown instead of T.J. Brown) then T. Brown's outstanding debt disappears and his invoice is not aged properly - if aging is based upon debt not at the invoice level but at Customer level.

And sometimes a data-entry person can be pressed for time (or get lazy) so checks that take research or questions are placed on account because it is easier. The plan is to research them later when time but the time doesn't come. It goes beyond business and touches customers ... which would you prefer to hear from a company you sent money to:

You have submitted two payments for Invoice 342 so we are returning your second check.

... or ...

We received your check but didn't know where to put it so we just deposited it in our bank. So when your second check came in and you properly put your invoice number at its top, we applied it to the invoice and deposited your second check as well. Of course we didn't discover this until both checks had been deposited. You didn't need that money did you? So sorry."

If they pay too much, contact the customer or hold the check. If you were the customer, and you made a mistake on a payment and made the check for $990 instead of $90 would you want the company to place the 900 on your account and deposit your check even if the only invoice of yours was for 90?

So my recommendation is still the same: don't do it unless you have to.

My experience (using FM with AR) has been with small to mid-size businesses using it for full financials and it has been important to apply these rules. Corporations wouldn't use FM for it (at least less likely). But regardless the business size, my suggestion is disallow it through the software. The business can establish business rule allowing creation of Retainer Invoice only with prior authorization (of client or maybe Owner) or maybe business will just allow an invoice to be created freely but at least there are rules which CAN be established. If you are going to take their money then at least invoice them for it. Kidding of course, but if prior authorization is required, that check won't sit there long because of motivations to get it deposited.

So no, Michael, we don't disagree in this particular case (or in most cases). Each business and developer who chooses FileMaker to handle this functionality has their own purpose and context and I was saying too firmly that everyone should adhere and that simply isn't the case; my apology if it sounded that way. But if I were purchasing from a company, I would hope that their accounting was this tight.

I think I lived in that world far too long and it warped me a bit.

Thank you for standing up against the 'absolute' stance that I took ... where is Sòren when we need him?

added blue

Edited March 23, 2012 by LaRetta

[comment]

LOL, I cannot resist scribbling this on the margin: a corporation that bills close to 1M customers monthly has charged me for no good reason and subsequently issued a refund. The refund was slow in coming and after a few inquiries it turned out that they have "credited my account". Which account? My account with them, of course. They were very surprised to hear that I wanted them to actually return the money...

[LaRetta]

Folks in accounting (or those just at their computers assisting customers) and even software designers, can forget that there are people on the other end of their work and those aren't just numbers - it is people's money and if it were their money, they'd come unglued. Nice margin note. So ... why DID you want your money back? After all, there should be no place you would rather spend it than with them ...

As an aside, my anal and worrisome nature insists that I add this:

LaRetta said, "... but if prior authorization is required, that check won't sit there long because of motivations to get it deposited."

One reason folks can rush to make a deposit (other than wanting the bucks) is because they dislike leaving checks sitting around and rightfully so. If you hold checks, please take the proper precautions and lock them up. Here is a link with some guidelines:

http://amas.syr.edu/AMAS/display.cfm?content_ID=%23%28%28%25-%0A