I am 'playing' with FileMaker (18/19). This is to see what I know, or don't. I am creating a basic accounting solution. I have already created a theme for it.
I have created the tables for general ledger, payables and receivables. I have the fields I need for the day to day transactions.
My REPEATING FIELDs question has to do with storing "configuration" options. I have a table for XXConfig for each module.
My thought is ONE record in each. Some choices need more than one option. So I would use a REPEATING field for that option. I am thinking in terms of a USER (not me) wanting to decide what terms options provided.
Some of the fields would be used as values for DROP DOWN. Some would be used in calculations. Some would be customized labels for reports headers.
Then EndUser would not have access to Design features, no access to creating fields. This will never be a solution I market. I might use the techniques to build this in other solutions I do market.
dificult to compete with quickbooks and reinvent a accounting sheet.
but as a first thought. Do not create tables for entities that are payments.
Put them in one table and identify them with a boolean field.
It is not so much as I want to develop an accounting program. Sage is my go to NOT Quickbooks. But it is a product line I am most familiar with so doing it is a test if I can.
I have other ideas that I could transfer the 'features' to that might be marketable. Everything I have developed in FileMaker, from ver 3 to now ver 19.6 has been in-house solutions. Things I do, could not be done for a CUSTOMER.
So the reason for my question.
yes.. I just wrote, reading your post.. because I have in my life already developed an “ledger” for payments. And in the various iterations of this in-house payments database, I had to make the switch. I had, like you made lots of tables.. for payables, receivables etc.. which I then had to unite, because after all a payment is flow.. going in, or going out. If you have this in two tables is more coplicated. In our in house solution we also united Purcheases and Sales.. into “movimentos” movements.. we are in Brasil.. getting a much simpler solution.
In my experience, this is really use-case dependent (the data structure/table definitions part of the discussion). For example, depending on business type, one may need separate tables for purchases and sales if running a "mercantile" business which involves inventory (as apposed to a service business). And if a manufacturing company using raw material with varying replacement costs one faces accounting practices such as LIFO, FIFO, dollar cost averaging, etc. Plus, a business which has replaceable "SKUs" as opposed to one where every lot is unique and never exactly replaced (much of my work is in the latter group).
Handling the accounting side of an inventory-based business has some other things to think about as well. Are receivables always paid in full, and per invoice? Are partial payments of the total allowed? In one of my solutions (laboratory services in three different countries with differing business/revenue department rules) each lab submission is invoiced per record ID (one to several line items per invoice) and each item is paid exactly as invoiced – no exceptions.
That being said, if developing a general ledger, similar to a checkbook register or double-entry accounting, I agree with @pfro Pierre that using one table for "in or out" transactions makes sense.
Finally, regarding repeating fields: I'm not a fan of them, ever since FileMaker gave us more sophisticated tools/structure options. There are a few "hacks" like the Virtual List that use them, but that's a special use-case (and one I typically don't employ) –– oh, and for projects like calendars. Since around FM v3 or so, when FM became a relational database management system (RDBMS) repeating fields have lost much of their appeal/usefulness, but they are still used by some for certain "tricks". They're a tool in the toolbox, but I've never used them in any production solution.
I do deal with "terms" on invoices. Some clients just want a text field to enter verbose terms; some want date fields into which they set a due date field and an amount field. These terms are stored in a child table and can be "Net 10 Days", or 30 or whatever... or can be define over a year or more by date picker, etc. In this latter case, they're defined in a card window or similar so that many entries can be viewed, scrolled, etc. This can get messy as one tracks payments, partial payments, late fees, etc.
I found an old thread on the Claris community board that I remembered when it was active. It's from ~8 years ago. There are some relevant comments in the thread, and with so many new functions since that was posted, one has a lot of options to repeating fields.
One worthwhile thing to keep in mind (per Comment, AKA Michael):
I don't see why people are saying "I'd never use them for data". Why on earth not ?!
"Because repeating fields cannot be found/omitted and/or sorted/grouped. Or individually deleted (without leaving a gap)."
Maybe the read will be of interest to others.
And subsequent partial payments which neglect to include that late fee that was added when the first payment came in for the wrong amount...