Schema for payments vs expenses

[blissland]

I have a FM solution for my school that operates smoothly on the income/student side, and now I'm trying to add functionality to the expenses and payments side.  

I have an expenses table that would allow me to list every purchase.

I have a vendors table where I store entities such as instructors (independent contractors.  i have no employees)  and individuals I rent from.  

I have an accounts payable table where I store the things that I owe instructors or rent for (e.g. teaching hours, lodging, rent for separate months).  This keeps track of how much I still owe.  I can manually check off payments I've made so it stays up to date.  

But it would be better if I didn't manually check it but rather had a Payments table that would track the payments to each instructor or landlord and update the accounts payable table automatically.  

But if I put that in a payments table that lives separately from an expenses table, this seems odd.  It also seems odd to track my instructor payments in the expenses table and figure out a way for the instructor payments to affect the accounts payable table.  This functionality has sat undone for years because I don't know how to handle this.  Should I have a payments table and an expenses table or just one of those?  Should I have an invoices table (though technically no one sends me invoices even though they are supposed to)?

I imagine that this problem has been solved millions of times already....

[bcooney]

You could follow the quickbooks data model. Payments can be applied to many entities and so there is a join table paymentLines. Of note, invoices differ from bills. Bills are invoices that you receive from a vendor. 
 

we sell a FileMaker solution that is completely unlocked that acts as an intermediary between FileMaker and quickbooks online. even if you don’t intend to sync to QBO, you can check it out just to see the data model and how payments are handled. Just skip the initial setup wizard. 

https://www.ledgerlink.co

 

[blissland]

Thank you.  I've never thought about bills vs invoices before.  I suppose I'm working with bills, not invoices.  

The payment lines join table.... I do not understand by what means this solves the issue of payments vs expenses.  Is my payment to my instructor joe in one record and my payment for a stapler at walmart in another record, in the same table?  Or are they in separate tables?  It seems odd to have them in separate tables since the payments go to individuals who have their own (vendor) table while the rest of the records wouldn't have that extra detail.... I'd have to relate those particular records to the vendor table, which seems wonky.   But it also seems wonky to have expenses divided into two separate tables.  

[bcooney]

You might receive a payment from a customer and apply it to more than one invoice. You might write a check to cover more than one bill. In both cases it’s a many to many. That is why there is a join table between payments and entities. 
 

all expenses should be in one table with a type of expense field.

why build this in FileMaker? Why not use QBO? 

[blissland]

The logic of the join table is clear to me.   

Note that the payments I'm talking about are outgoing payments to vendors, not incoming payments from students.  

So if all in one table, am I going to have a field that is empty for most records but filled in with a vendor ID for a few records, and use that to register how much of the bills for that vendor has been paid?  Is that proper schema design?  I've considered this for years but not implemented it because that design seems wonky.

[bcooney]

Imho, building an accounting system in fm is not advisable. 
 

i reread the QBO api docs and see now that they use a separate table for outgoing payments to vendors (billpayments)  with a similar model as incoming payments. It’s probably the same table under the hood with a type field. 
 See https://developer.intuit.com/app/developer/qbo/docs/workflows/manage-linked-transactions

 

the linked transaction is the join table. 

[bcooney]

Overall, this gets complex. Refunds, credit memos, etc. 

[blissland]

Good to know that expenses and outgoing  payments are in separate tables.  I wonder how they get all the outgoing costs on one report if the outgoing payments are split between two tables. 

I don't know what QBO is, but I'm surprised by your comment that things like this shouldn't be done in filmmaker, because a) we're in an FM forum where I would think FM is used for everything and b) I'm not knowledgeable about how the structure of financial data like this is not well suited for FM--I thought all things could be represented in a relational db form.  I'd love to understand more about the nature of the limitation that you're referring to.  

[bcooney]

Before integrations were possible, we created everything we could in FileMaker. Our approach now is to integrate with another system offering the functionality we need. Qbo is just one accounting solution that offers a rest api. Why build an accounting system if you can integrate? You’ll never build what has taken years of dev to accomplish. I wouldn’t build an sms system, I’d integrate with twilio.  

What’s in FileMaker is what is unique to your business.