Newbie design questions

[DeborahW]

I just started on my first project. It is to be a system for tracking fabrics used in making sample shirts. So far I have a Fabric table and a related table (FabricsOrdered). This arrangement keeps track of how much fabric is ordered for each location (most fabrics go to at least two separate destinations).

I've started another table called Samples. It will be related to the Fabric table. What I need is to be able to have the main fabric (for the body of the shirt) listed like this:

Fabric Mill

Fabric Name

Mill#

These fields are all also in the Fabric table and I could use a calculation (=) to fill in the Mill and Name fields. Is this the best way?

In addition (and this is the part that I can't begin to figure out) I need to list another fabric (used for trim) in a completely different part of the same layout (in other words, this is not like an invoice where the fabrics could be listed together in columns). This would look something like:

"Please use :FabricMill: :FabricName: :Mill#: for (selection from value list of options for fabric use)"

I would also like to be able to track the fabric usage. In the Sample table I will also have fields for entering the number of shirts ordered. I would then be able to calculate usage (#shirts X (constant meters/shirt) X number of times the fabric is used). Since the constant would be different for body fabric and trim fabric I would need to track the instances of the uses separately. Any fabric may be used multiple times for either or both uses (bodies & trims). Do I need a Line Items table? How would I differentiate between fabric used for the body of a shirt and one used for trim?

Any and all thoughts would be most appreciated.

Deborah

[Fenton]

Yes, you need a line items table. From Sample ("Order" is the name I'm using; it's more generic) I would use 2 portals, one for Body and one for Trim. In order to separate them you need a field "Type," which could be either.

The relationship to line items would be on BOTH the OrderID and a "fixed" type. So, to get the fixed types in Orders, you need 2 calculation fields, text result:

_cBody = "Body"

_cTrim = "Trim"

The 2 compound relationships to OrderLines would each point to both the OrderID and the "Type" field; but they would be mutually exclusive.

To create the new line items you would turn on "allow creation of related records." FileMaker would then enter BOTH the OrderID and the Type into each line (a version 7 wonder, which you spring chickens can hardly appreciate :-)

As far as showing names, in version 7 you no longer need to create calculation fields to "pipe" data through Table Occurrences (TO). You can create what I think of as "hanging" table occurrences of the desired "source" table off the end of the line items table; allowing you to display names from the original table, which is not directly related otherwise.

There is more than one way to do this however. This method adds more TOs to your graph, but eliminates extra ID and calculation fields in your tables. It's a trade-off. I call them "hanging" because they are at the end of the line, not used for anything else. I like to name which TO they're hanging off of abbreviated somehow, at the end of the name, in lower case; so I don't mix them up with other TOs.

The example file shows the bare bones setup. It does not "filter" fabrics by Mill (which would require another "Mill_Fabrics" table, unless a fabric can only come from 1 Mill, which I don't know), nor does it total anything, though all the data is there.

FabricOrders.zip

[DeborahW]

Dear Fenton,

Thank you. I will look at this tomorrow at work. I don't have FileMaker at home.

Deborah (no spring chicken, but appreciative nonetheless....)

[DeborahW]

Dear Fenton,

I just wanted to let you know that I've been working on my project off and on all week and progress is being made. Thank you so much for the example file. I'm not quite 100% sure how it works, but it does. I'm sure I'll be back with other questions as I continue.

Deborah