Sub-records?

[zenmom]

Dear Collective Wisdom,

Hi, semi-novice here! I might have asked this question a couple years ago, but if I did I couldn't find it in the archives. 

I'm using FM Pro 12, on an iMac OS v10.12.2 aka Sierra. (I know I'm behind the times, am planning to catch up to FM Pro 15 soon. But for now, v12 it is.) 

So. I'm setting up a database to track expenses. My first bash at fields is shown in the attached screenshot. (I may prune out a couple of the fields near the bottom of the list.) The db currently has 3 records, to wit: "banjo maintenance", "Revere Ware copper-bottom pot", and "FileMaker Pro upgrade", each with appropriate info in some of the other fields. 

The problem is, "banjo maintenance" actually consists of several different things, for example "new banjo head", "strings", and "banjo case". I'd like each of these to have their own sub-records (or whatever structure /format /thing that would consist of). Each of the sub-items might have different dates associated with them (and of course different prices). And if it's set up so that "banjo maintenance" has sub-entries, then it would make sense that "Revere Ware copper-bottom pot" would have to be structured with a sub-entry too, even it if there were only one. 

I'm comfortable with simple relational db's that involve more than one table, and with joining those tables, sometimes through an intermediary join table, but I'm at a loss for how to build the sort of thing I'm asking about here. I suppose it'd be something like an invoice, with a separate line for each item purchased? One of my passing thoughts was a separate table for each record that requires sub-entries, but that doesn't make any sense at all to me, especially once you start scaling it up. 

I tend to charge off into a thicket of possibilities, but I'd like to keep it as simple as possible. This is just for my private use, not for a large business. 

Thank you in advance for any suggestions you might have.

Edited January 15, 2017 by zenmom
Hit the send key too soon, wasn't finished!

[comment]

This part is not clear:

54 minutes ago, zenmom said:

Each of the sub-items might have different dates associated with them

If they have different dates. then why wouldn't they be separate records, entered individually as they occur, and share a common category or subcategory? What ties them together as single item?

[zenmom]

I suppose there could be different categories or subcategories, but in that case there would be many, many categories, to the point of being unwieldy. 

But let us sidestep that by saying they would not have different dates, just different costs, as in an invoice with n+1 line items. How would that be handled?

[comment]

I don't know, because I don't understand the situation. There are numerous options here, depending on what exactly you're tracking and - no less importantly - for what purposes. These questions cannot be sidestepped.

[Fitch]

Sounds like you'd want to start with a table for items, and a related table for expenses. The Category field could go in either table, or both -- presumably with different values, e.g. banjo category might be "twangy things" and its expenses might have categories like, "parts," "lessons," "accessories," etc.

[zenmom]

Okay, I'm back! 

On 1/18/2017 at 0:52 PM, comment said:

[...] There are numerous options here, depending on what exactly you're tracking and - no less importantly - for what purposes. These questions cannot be sidestepped.

Let me give two different examples. 

First, tracking expenses. I'd like to be able to enter data with the basic "building block" being a receipt. If at the end of the day I've got 3 receipts, I'd like to enter the data with each receipt as a separate thing that I can track by vendor and date. Within each receipt the items might fall into various categories, as in the following three receipt examples:

On a hardware store receipt, the line items might include a personal expense (table lamp for me), a "house" expense (LED lightbulbs, to be reimbursed from our co-op house account), and something that one of my housemates asked me to pick up for them (window cleaner, for which I will be reimbursed by that particular housemate). 

On a grocery store receipt, items might include personal groceries, supplies for a co-op house party (to be reimbursed from the co-op house account), and something I'm planning to give as a present (a personal expense, but one I'd like to track separately from food expenses). 

On a statement from Amazon.com, items might include clothing (personal expense for me), wrist braces (no kidding! I was just diagnosed with carpal tunnel syndrome; to be tracked as a medical expense), and a book my son asked me to order for him (for which he will reimburse me). 

Second, tracking vitamins & supplements. I take a number of different supplements. Many of them are combinations of different ingredients, and I'd like to be able to calculate the total amount of "x" ingredient I'm getting from all of them combined. In this case, I want to the basic building block to be each named supplement, that is, the name on the package. Here are three supplement examples:

Vitamin D-3, 1000 IU. One ingredient, nothing else. 

Caltrate Calcium & Vitamin D3 Supplement. The supplement facts panel on the back of the package says that this contains vitamin D3, calcium, magnesium, zinc, copper & manganese.

Theragran-M Premier 50 Plus High Potency Multivitamin /Multimineral Supplement. A more-or-less standard multivitamin, which contains 31 different named ingredients, including vitamin D, calcium, magnesium, zinc, copper & manganese.

I want the basic unit to be the product, so (for example) if I stop taking one brand of multivitamins and start taking another one, I can add the new one (and it's component ingreds) and just mark the first product as "not current".

I want to eventually be able to print out a report that lists each *product* I'm taking, and then a report by *ingredient*, so I can tell how much total calcium, vitamin D, zinc etc per day I'm getting. 

I assume that if I've got a receipt with one item -- or a product with one ingredient -- it would be the same structure as something with multiple parts, but would have just one part. 

Is there a way (that makes sense) to set up the kind of tracking that I want, given my descriptions above?

Looking forward with great interest to any replies.

P.S. Fitch, I love the category "twangy things". Here's a picture of my banjo:

Edited February 4, 2017 by zenmom

[comment]

The proper solution requires two tables. In the case of expenses, these could be Receipts and ReceiptItems, related by ReceiptID. You would enter the common details such as date and vendor into fields of the Receipts table, and the individual item data into a portal to the ReceiptItems table.

Alternatively, you could use a single table - but then you would have to enter the common details separately and repeatedly for each entered item.

Edited February 4, 2017 by comment

[zenmom]

Dear comment,

Ah, that makes sense. I will try the two table solution. 

Thanks!

M.