AR and AP Tables, Separate or Merge?

[Dr. Evil]

Should money coming in (AR) and money going out (AP) be in two different tables, or can the be merged?

I feel I should merge the two and just have one table called Transactions, then just label entries a deposit or withdrawal.

But maybe this is against standard accounting practices.

I could see where a merged table of AR and AP could get cloudy. e.g. deposit/payments would tie to Invoice(s) and withdrawals would not.

What to do, what to do?

THANKS for your help!

:)

[dsghs]

I think the tables would be:

Vendors

WorkOrders

SentPayments

Customers

Invoices

ReceivedPayments

Where Work Orders would be in a many:1 relationship with Vendors. SentPayments in a many:many with WorkOrder. This is your AP.

And Invoices would be in a many:1 relationship with Customer, ReceivedPayments in a many:many relationship with Invoices. This would be your AR.

This is how Intuit has quickbooks set up so it would be my guess. This is kind of a simple setup, though. Usually you also have expense accounts, deposits, etc.

[Dr. Evil]

Does the attached relationship/structure appear correct?

simple_accounting_demo.fp7.zip

[dsghs]

You need crosstables to provide the many-to-many relationships.

ie

invoicepaymentreceived

invoicepaymentreceivedid

invoiceid

paymentreceivedid

amount

read up:

http://help.filemake...n-filemaker-pro

[dsghs]

You need to match up your primary key names, but you should be able to get the gist.

The reason you need this many-to-many is because a received payment from a customer can be:

1. A full payment of one invoice

2. A single payment of multiple invoices

3. A partial payment of one or more invoices

The same thing goes with your work orders and sent payments. Also, the "amount" field in the crosstable is the amount to be applied to the invoice or work order. Sometimes it will be the full payment received/sent, sometimes it will only be a portion of the payment (like when one payment is to be applied to multiple invoices).

[Dr. Evil]

dsghs! thanks for the help... i will definitely read up on the linked post. Stay tuned.

[Dr. Evil]

So just to confirm we have two tables when it comes to payments? A separate table for payments received and a separate table for payments sent?

On the same note...

I believe there should be only one table for customers and vendors, maybe called companies. Differentiated by record field labeling.

My goal is not to convolute this topic, but to build a bare bones accounting structure to reference for not only myself but for other readers.

Thank you all for your help!

[eibcga]

I have taken a somewhat different approach to Dr. Evil's solution...

I have one table called "Contact" which includes customers and vendors. In this manner, if a customer and a vendor are the same company, it will only appear once in my database.

Over time, any particular contact can have many transactions ("Journal"). Each transaction, due to double-entry bookkeeping requirements, can have many line items ("Ledger"). Each account type (asset, liability, equity, revenue, expense) can have many account groups. Each account group can have many accounts. Each sub-account can have many transaction line items.

I enter all my transactions in the Journal table layout, which has a portal to the Ledger ledger table. I enter as many transaction line items in the portal as necessary to allocate my account increases or decreases. If an invoice has more than one payment, I just enter more line items for cash receipts, and indicate the invoice number in a separate field. The same holds true for vendor payments.

I run a sub-summary report on the Account table for a Trial Balance report. I also have detailed transaction line item report sorted by account, then by date, to get my General Ledger report.

To keep things simple, I have set up an 'amount' field in Ledger table whereby any positive amount represents a debit, and any negative amount represents a credit. Thus, when you add up all the amounts, the total should be zero and it will "balance". Whether a +/- amount increases or decreases a particular account will depend on the account type. (e.g., all asset type accounts are increased with positive debit amounts, and decreased by negative credit amounts, and so on.)

In this manner, I can track finances in a manner that Dr. Evil desires, plus follow proper accounting practices.

[Dr. Evil]

eibcga!

Thank you for sharing how you solve this matter, much appreciated.

Your model has reinforced the direction I was trying to go. I have laid out a new model (attached) for some feedback. Your thoughts, anyone..?

[TKnTexas]

One aspect regarding this question is do you have vendors who are also your customers? Or, do you have to do frequent refunds to customers such that you are making AP payments to them. If these are answered by YES, then the vendor table and the customer table could be combined.

[Dr. Evil]

TKnTexas... yes customers can also be vendors. is there a possibility of needing to refund a customer?.. also yes.

So I would say it is solid to have a table to called, say "companies" that would contain customers/vendors.

Thank you!