Db design...

[Dan D Lyon]

I work for a company that makes road/flight cases. Almost 20 years ago I wrote some software using Macintosh HyperCard running on Mac OS 8.6. You enter the 3 dimensions in inches plus the cover height and it calculates a cut sheet, materials and labor pricing.

Time is long past to remake this application run on our new computers (before our old macs become extinct out from under us), and FileMaker seems to be the platform to retool to.

My question is how to adapt the Hypercard concept into a FileMaker world. In Hypercard there is a stack for each of 8 different case types we make. Would it make sense to have 8 tables in Filemaker for each case type layout, or lump them all in one table and use 8 different layouts and some fancy find/sorting to keep them in their respective categories. We would never need to see the data from one type of case in any other than its own type layout; that could potentially be a real problem.

Also, we'd like to keep an electronic archival linked with our client db (move away from the old paper filing cabinets) and maintain our vendor contacts all in one place.

Thanks for helping me see how to manage this addaptation.

[comment]

Would it make sense to have 8 tables in Filemaker for each case type layout

I don't think so. You should get by with one table for CaseTypes, populated by 8 records. But it depends on how different these types are in terms of their attributes.

NOTE:

You are asking a question about data structure, yet you speak of layouts. Layouts are merely a data presentation device, and as such are largely irrelevant in this aspect.

Edited May 21, 2009 by Guest

[Dan D Lyon]

This makes sense to me, I'm just learning the table concept and trying to adapt from HyperCard, which is quite different. The reason for this question was to try and emulate the HyperCard archival aspect of having a "card", like a snapshot, for each job.

I'll go back to studying FileMaker and tables and not try to emulate HyperCard functionality. :

Thanks much.

[comment]

The reason for this question was to try and emulate the HyperCard archival aspect of having a "card", like a snapshot, for each job.

There is no reason why you couldn't do that, by having another table of Jobs where each Job would be record, linked to the relevant record in CaseTypes.

Or, more likely, there would be table of Orders and a table of OrderItems (cases ordered) - so one could order multiple cases at the same time.

[Dan D Lyon]

I neglected to say that each job would be a record, and the records would be grouped by case type. So are you saying under these circumstances, having each case type group, could be separate tables?

[comment]

I am saying that if you have 2 tables, Jobs and Types, then each Job can be of one type only - so it's limited to one case per job. Whereas if you have three tables, Jobs, Cases and CaseTypes, then one job can have many cases, and each case can have its own type.

[Dan D Lyon]

Okay, I'm getting it. Use tables as sub categories, not as types, right?

[comment]

I am not sure what you mean by sub-categories or types, so I don't know.

I'd suggest you take a look at a basic invoicing sample here:

http://fmforums.com/forum/showpost.php?post/309136/

Think of Invoices as jobs, LineItems as individual cases, and Products as case types.

[Dan D Lyon]

I appreciate your help, thanks for bearing with me. Hopefully I'll get my needs across more clearly and understand your recommendations better...

The most important part of this database is to be able to enter the dimensions for a specific type of case, plus the cover height; for instance: 24" high x 30" wide x 16" deep and the cover 4" high.

Then with many calculation fields create a cut sheet layout with the many size parts needed... and also calculate materials costs with labor for pricing each custom case.

As I said, there are 8 different types of cases (and which I wasn't so clear) with variable sizes for each job. The software we are currently using virtually has a file for each type and each job has its own record within each file. To adapt to Filemaker it seems there are three options:

1. Create 8 separate files with duplicate fields, though edited for each type.

2. Lump all 8 types into 1 file and table with all the fields named separately and use find/sort/different layout reports to separate and work with any one case type and job at a time... or

3. Have one file with each case type being a table, with duplicate, but altered sets of fields and isolated sets of records for each type.

Option 3 makes sense to me, unless I have the concept of tables all wrong. Many of the fields are similar for each case type, so once creating all the calculation fields for one type table, it would be nice to be able to duplicate that table and all of its fields and then edit each tables set of fields for each case types parameters and have all the case types records grouped separately. The problem with implementing this is that fm7 doesn't have a duplicate option for creating new tables.

Am I wrong that the purpose of tables is to virtually group a number of files (called tables) into one database file. This is my understanding of what I've read about tables so far...

[comment]

Am I wrong that the purpose of tables is to virtually group a number of files (called tables) into one database file.

I am afraid you are very wrong about that. A table is a collection of records - basically a shoebox with cards. A file is a collection of one or more tables. A solution is a collection of one or more files.

I still don't understand the main issue here: suppose you have a table of Cases, with fields for:

• Height

• Width

• Depth

• CoverHeight

• CaseType (one of 8 possible types)

What additional attributes (i.e. fields) are necessary to describe an individual case?

[Dan D Lyon]

"A table is a collection of records - basically a shoebox with cards. A file is a collection of one or more tables. A solution is a collection of one or more files."

This sounds like option 1. -- A file for each of 8 case types.

"I still don't understand the main issue here: suppose you have a table of Cases, with fields for:

• Height

• Width

• Depth

• CoverHeight

• CaseType (one of 8 possible types)

What additional attributes (i.e. fields) are necessary to describe an individual case?"

All the calculation fields that are unique to each case type that contain its precise parts measurements. These fields are presented in a unique layout for each case type.

At the top of the Define Database window under Tables it says:

"A table is a unique set of records and fields. A file can contain more than one table."

This is exactly what I want. It would be very nice if I could duplicate a table with all my intricate field calculations that I could then edit instead of having to cut and paste each field and its calculation formula. I googled an application that says it can do this, called FMfobot, for $199, think I'll pass.

Otherwise I can do this by option 1 -- by duplicating my original file 7 times.

[Fenton]

4. A method known as the David Graham method (after the creator); though others began using the general idea earlier, and it is well-known in general database theory (though I can't remember the name).

In this structure there would be a central table (Cabinets) for the fields common to all the different types. It would have its own unique serial ID. Each of the 8 other tables would have only the unique fields for their particular type, and a foreign key linking them in a one-to-one relationship with the central table. In David's method this key is populated into both tables at the same time, simply by entered data into one of the central table's common fields (on a layout of the outer table).

Each of the "outer" table has its own layouts and records, but all of them can also be seen as the same thing in the central table, when necessary. Whether this method would be suitable depends on how many different fields you have (a lot), and whether you really need to see these as separate sometimes.

But remember, it is not a big problem to have many fields which are not used in any particular record, nor is it really a problem to have different layouts or tabs with mostly different fields. So this separation may not be required.

P.S. I would recommend designing a new database especially for FileMaker 10, if you have the hardware to support it. The Script Triggers may turn out to be very useful.

P.P.S. FileMaker Pro Advanced can copy/paste fields selectively between tables. Any serious developer should use it. Regular FileMaker Pro can Import a set of fields from another FileMaker file's table and create a new table using its fields.

Edited May 26, 2009 by Guest

[comment]

"A table is a collection of records - basically a shoebox with cards. A file is a collection of one or more tables. A solution is a collection of one or more files."

This sounds like option 1. -- A file for each of 8 case types.

I don't see what leads you to this conclusion. To begin with, you should have a SINGLE file containing all the tables that are necessary for this solution.

"…What additional attributes (i.e. fields) are necessary to describe an individual case?"

All the calculation fields that are unique to each case type that contain its precise parts measurements. These fields are presented in a unique layout for each case type.

Could you provide some practical examples of these calculations? Are there calculations that need to be performed for some types only, or is the difference merely in the formula used to produce the result?

While you COULD have a table for each type, I am not at all convinced that this would be the best approach. It is certainly not an easy one to implement or to maintain.

[Dan D Lyon]

I should mention that tables are new to me. The prior version of Filemaker I had was 3, no tables then.

Could you provide some practical examples of these calculations? Are there calculations that need to be performed for some types only, or is the difference merely in the formula used to produce the result?

While you COULD have a table for each type, I am not at all convinced that this would be the best approach. It is certainly not an easy one to implement or to maintain.

An example of one of the many calculation fields that are subtly different for each case type:

(text result)

Side Panels =

Width & " x *" & Let([

integer=Int(Evaluate ( Substitute ( Height ; " " ; "+" ) ) - .3125) ;

numerator=Mod(Evaluate ( Substitute ( Height ; " " ; "+" ) ) - .3125 ;1)];

Case(integer;integer &" ";"")&

Case(Int(numerator*64) < 1;"";

Mod(Int(numerator*64);32) = 0; "1/2";

Mod(Int(numerator*64);16) = 0;Div(Int(numerator*64);16) & "/4";

Mod(Int(numerator*64);8) = 0; Div(Int(numerator*64);8) & "/8";

Mod(Int(numerator*64);4) = 0; Div(Int(numerator*64);4) & "/16";

Mod(Int(numerator*64);2) = 0; Div(Int(numerator*64);2) & "/32";

Int(numerator*64) & "/64"))

Most of this calculation is for decimal and fraction conversions back and forth. Measurements can be entered as either decimal or fractional, but the results are always fractional; to be implemented in the woodworking world of tape measures. The formula above I cut and paste from searches in decimal/fractions conversions in this forum and then adapted them together. I did not write them nor fully understand them, but they work.

The sole reason for isolating the records of each case type is that it is imperative that the figures for one type of case are never able to be overlaid on any other case type. So that what is called a "cut sheet," the print out with all the correct parts to be cut, when handed to the builder, is such that there is no possibility that they inadvertently build the wrong type of case.

All case types have 3 dimensions and cover thickness, but they have different size and type parts and have specifically different attributes (for instance strength and weight).

When using the format of option 2, where all the records for all case types are combined in one table, and there are different layouts for each case type cut sheet, making sure that when you flip through the records that the correct layout is selected for each record is the trick.

It just seems prudent to keep them separate.

The only link they have is by client, job number and materials expenses, but these are of secondary importance at this point of development.

[Fenton]

FileMaker 10 can solve the problem of which layout or tab is shown, via a Script Trigger, OnRecordLoad. For me, if the number of fields which were different were a lot, and if I wanted to absolutely sure they different types were always separate in all cases (pun), I'd use the Graham method of separation. It is a little more difficult on the Relationship Graph, but not that much. And it still allows seeing them all as one, for expenses, etc..

Do a search for posts about "David Graham", or filtered to my posts about it. I would not recommend it straight off to a beginner. But anyone with Hypercard programing experience is a bit beyond that.

You should also look into FileMaker Pro Advanced and Custom Functions, as that is what I used when I had to build a "panel cutting" module (not fun, for me anyway). This is what I used, and I couldn't find it at:

http://www.briandunning.com/filemaker-custom-functions/list.php

//RectCount ( PieceA ; PieceB ; MaterialA; MaterialB )

//Returns the number of rectangles (with dimensions PieceA x PieceB,) that

//can be cut from an area of materials (with dimensions MaterialA x MaterialB.)

//10-13-2005

//By Mike Hackett

Let(

[

PieceLongSide = Case(PieceA

PieceShortSide = Case(PieceA≥PieceB;PieceB;PieceA);

MaterialLongSide = Case(MaterialA

MaterialShortSide = Case(MaterialA≥MaterialB;MaterialB;MaterialA);

/*Check the count with first cutting hozontal pieces from horizontal material.*/

CountRows = Case(MaterialLongSide≥PieceLongSide and Div(MaterialShortSide;PieceShortSide)>0;

Div(MaterialShortSide;PieceShortSide) +

RectCount(PieceA;PieceB;MaterialShortSide;MaterialLongSide-PieceLongSide));

/*Check the count with first cutting vertical pieces from horizontal material.*/

CountColumns = Case(MaterialShortSide≥PieceLongSide and Div(MaterialLongSide;PieceShortSide)>0;

Div(MaterialLongSide;PieceShortSide) +

RectCount(PieceA;PieceB;MaterialLongSide;MaterialShortSide-PieceLongSide))

];

/*Return the greater count*/

Max(countrows;countcolumns)

)

[Dan D Lyon]

Thanks for these interesting ideas.... more to digest....... I'll be back...

[comment]

I see ONE calculation. I asked for an example where there are different calculations for each type.

The sole reason for isolating the records of each case type is that it is imperative that the figures for one type of case are never able to be overlaid on any other case type.

I am afraid I don't understand what that means.

[Dan D Lyon]

I see ONE calculation. I asked for an example where there are different calculations for each type.

Each case type has this Side Panel calculation field, but with a different equation to determine its required size.

I am afraid I don't understand what that means.

My apologies for not being more precise, I'll try again...

All type cases have the same 4 input number fields (height, width, depth and cover thickness). The specific calculation fields for each case type cut sheet are are in a specific layout for each case type. When stepping through the records while in a 1/2 inch cover type case layout and the next record is intended as a 1/4 inch rack type case, this displays erroneous results. Each record must only be displayed in its corresponding layout with its intended part size calculation fields.

Here are some pictures of a few flight cases...

http://www.calcases.com/

[comment]

Each case type has this Side Panel calculation field, but with a different equation to determine its required size.

So what would happen if the calculations were built along the lines of =

Case (

CaseTypeID = 1 ;

Width * Height ;



CaseTypeID = 2 ;

( Width + 0.5 ) * Height ;



CaseTypeID = 3 ;

Width * ( Height + 0.5 ) + Depth ;



...

)

or even:

Evaluate ( CaseTypes::SomeFormula )

Each record must only be displayed in its corresponding layout with its intended part size calculation fields.

That would be true only if the calculation fields were indeed allowed to display erroneous results.

Anyway, I believe Fenton already addressed this issue: it's a matter of scripting the users navigation. You could either restrict the found set to cases of a selected type, or jump to the appropriate layout upon loading a record.

[Dan D Lyon]

Anyway, I believe Fenton already addressed this issue: it's a matter of scripting the users navigation. You could either restrict the found set to cases of a selected type, or jump to the appropriate layout upon loading a record.

Thank you gentlemen. I downloaded a trial version of fm10 and the script trigger (onRecordLoad) feature solves my issue nicely. Upgrading soon.

I wish the new UI could toggle to the old style side menu ... now I have to redesign my layouts to fit the window so as not to have to scroll so much... oh well.

Thanks again also for all the other ideas and info to study.

Edited May 29, 2009 by Guest