Troubles with new records

[skaff]

Hello all! I've been on the forums lurking around looking at peoples tips and feedback. Just wanted to first say "hi" and nice to meet everyone.

I got into filemaker a bit over a year or so ago in hopes that using it would solve some tedious issues for myself at work and at home. Making small databases to keep track of stuff and to eliminate some very tedious calculating. The few projects which I have done have really become more of an "elaborate" calculator which I can plug in a few values to and get my desired result and organize them for a quick reference if needed later.

Recently I've taken on a project for work which has required a bit more thought and planning. I've listed everything which I want to organize and have started sketching how I basically want everything to look if I were to represent the database from a distance in a visual manner. For the most part everything about it is fairly standard for a business... (Quotes, Jobs, Shipping, Contacts, Purchases, ect) I have decided to add an additional twist which has caused me some problems.... Making my quote table a bit more complex.

When laying everything out on paper I realized that a "quote" table was to general. I'm in a post press business and I can think of over 15 different operations we do that could each haver there own table. If I was to lump everything together I can see it being doable but that one table being massive filled with a bunch of fields that sit unused. (for example... operation_quantityA, operation_specsA, operation_quantityB, operation_quantityB... and so on)

The way I visioned laying this out would kind of be like a coat hanger which you would hang different "quote items" off of. All the main quote file would have is a couple fields that involve a quote_number, a text_ID (used to reference the quote type), a number_ID (used to reference the record of the quote type) and a few other various standard things like timestamps ect.

The other table fields which would be "attached" to this main table would have its own serial value (which allows the main table to point to that specific record within that table) and the value of its parent table (so it can point back to where it's attached to).

I'm having a heck of a time getting this to work properly the way I'm doing it right now. Currently I have both the "Quote" table and the "xxx_quotes" related by a global value within that table. The way I'm creating a new item within the quote is done though some buttons and scripts. I've attached a visual to hopefully give some help. It shows how I have the rough UI setup for creating an item, clearing something out, selecting quote types ect.

Then again I always know there is more the one way to skin a cat... but it's always better to skin it with a sharp and efficient object! From the image you can tell I do use some interface help/tools I've already got from these forums!

-Jimmy

[LaRetta]

Hi Jimmy! Even though you might not be NEW to FM Forums, this is your first post so welcome!

We truly would need to see your file or have more explanation of how it is structured. Why? Because any time I read something such as, "operation_quantityA, operation_specsA, operation_quantityB, operation_quantityB.." and then see something like what is listed down the left side of your layout (multiple value lists attached to multiple fields), I get concerned that you are using multiple fields within a table when you should be using related records.

I realise you said you were structured relationally but my mind sticks on 'that's not what I see'!!

Can you fill us in?

LaRetta

[skaff]

I've attached my file to hopefully help out. I know I have done something wrong either it be in relations or something else.

I've obviously had no type of classes or proper training on filemaker so I know I'm bound to make mistakes or develop unwanted habits I just try to make attempts to keep them at a minimum.

On a side note I have had one question in regards to the memory of tables and there fields. If say I have a field within a table that can have 50 repetitions... is the disk storage space pre allocated for all 50 fields, OR is that space only being used as I start to enter values into those fields and the small amount of memory used to declare those fields being used?

-Jimmy

OLB.zip

[Fenton]

First, this is a fairly complex project for a novice. I would advise that you proceed slowly, and take some time to learn some basic lessons, especially about relational design and its implications.

I would say that the first lesson regards the Relationship Graph and its structure. For complex solutions, of which yours is (moderately) one, the "anchor-buoy" method (or a variation) is the way to go. This means that each main "entity", that it is the primary object of each of the main tables, becomes an anchor table occurrence (TO) in its own table occurrence group (TOG).

Table Occurrence (TO) means those boxes on the graph, it is called an "occurrence" because each table (which you defined in Tables, duh) can occur multiple times on the graph. This is normal and essential.

Table Occurrence Group (TOG) is simply a group of TOs that are connected by relational lines. A separate TOG will not be, and should not be connected. This has numerous advantages, for avoiding circular references (which are not allowed), and for organizing the list of table occurrences offered in drop-down lists to Related and Non-related sections (which simplifies picking the right one).

Any given layout is connected to 1 TO. This means that the main data entry layout of each table must be assigned to its TOG anchor TO.

This is critical to understand right at the beginning. Because right now you've got almost all of them balled up into one TOG. Because they are need a connection to a TO of the Interface table, to connect to the "unstored" calculation fields of the Interface, you have a problem. The Interface file will likely exist once as a TO on its own. Then it will exist multiple times as a TO attached to one of the other anchors. In other words, there will be one attached to Quotes, one to Contacts, etc..

There are further questions, re: whether to Look Up Customer, or reference. I think you should NOT look up phones, fax, etc.. Whether there are multiple Contacts for a Customer, each specific to a Quote or Job, etc..

There is a further down the line question re: repeating fields? But that is a secondary concern, when the main entities did not even have serial IDs yet.

Attached is a somewhat hacked up file I modified.

OLB_fej.fp7.zip

[skaff]

Fenton,

I greatly appreciate your post. It took me a little bit of time to wrap my mind around it but it did trigger a light bulb in how the relationship map really works.

Once I was able to sort out everything I have been able to get my "desired" result as of now.

I think one of my upcoming challenges is going to be actually printing out a so called report for my "quote hanger". But I think I'll save any of those future questions till they actually come up!

I'll attach the file in its current state. I did kind of start fresh because I really was not that far in and I wanted to kick some things out.

-Jimmy

OLB_v1.0.zip

[Fenton]

I see you've stripped it down, and separated out some of main tables into separate TOGs. I think this will help, especially in the future as it gets more complex. It is like the foundation of a house.

A couple of points about naming TOs. The naming convention of Q__Quotes, for the main "anchor" TO of a TOG. The 2 underscores have a specific function. Which is to sort the main anchor TO to the top of the other TOs of that TOG. You'll see the advantage latter, when you've got a lot to choose from, and you know you want the main one of the Quotes TOG (which you often will). It's a pain if it's not at the top.

Also, I have to wonder why you'd suffix those TO names with _quotes. The Q_ at the front has already identified them as being in the Q__Quotes TOG, and, since there's not in between, that they come directly off the main Quotes anchor. I include abbreviated names of all tables in a relational line, so there's no question of where it comes from (sometimes drastically abbrev., but I know what they are.)

These seem like minor points, but I think you'll find that consistent (within reason) naming conventions will keep you sane; otherwise there's little hope -]. FileMaker has put together an excellent, though longish PDF on the subject (which is almost impossible to find at the oh-so-cute site)

http://www.filemaker.com/downloads/pdf/FMDev_ConvNov05.pdf

The other is that I personally would not split Laminating and Plasticoil off into separate tables. It really does not matter if a bunch of fields are different. That is far less important than whether or not you can do things like Purchase Orders and Inventory, or Jobs and Tasks, from products that are in the same table.

Yes, often you want to keep them apart, in the interface, from the users' point of view (which is not the developer's point of view).* You've begun to use a Tab Object to do that on the layout. You can also have a "Type" field in the table, which can keep them apart for portals and relationships. It usually easier to separate things which are slightly different, but basically the same thing, than it is to deal with things that are basically the same, but have been put in different tables, making value lists, reports, etc., a PITA.

Don't worry about "unused" fields. That is really not going to be a problem; unless you're talking about hundreds of fields, in which you may break the tables apart, not because you want to, but because you almost have to, accepting that it will cause you extra work everywhere you deal with them.

*A developer will almost never compromise structure for interface, as an interface can usually be made to look and work as it needs to, if the structure is sound. Whereas a poor structure can never be made to work properly, in the long run.

[Fenton]

Caveat: I'm not really saying you should put them in one table. I'm just saying you should consider it. I don't really know your business, nor have I scrutinized the fields in those tables.

Maybe they never belong together anywhere. Maybe you never want to do a report with both of them on it.

If you do put them in one table, you could prefix the fields that are different for each, like "l_something", "p_something". Anything else would be a common field. They would share fields like ID, Name, Qty (do not create separate quantity fields for each, or you've defeated one of the main purposes for using the same table).

[skaff]

I've thought about doing it that way as well... putting all of the quote information into one table. I may develop a version doing it that way along side the other way and compare the advantages/disadvantages of doing it the two ways in my system. I figure there can't be much harm in an exercise like that.

I would need to split up some items such as quantity's though. Like if I laminated covers for a book where the images occurred multiple times up on a sheet. (ie. a book that has 8.5 x 11 covers but the covers are printed on a sheet that is 18 x 24 4 up) Obviously in that example your quantity for laminating and binding would be different.

Thanks also for the reference for the naming... I've been searching for something similar to that recently. I know a BIG problem which I do watch out for is my naming of variables so they are spelled correctly I think on one of my first projects I ever did I spelled a variable three different ways! That was fun to fix.

-Jimmy

[skaff]

I played around a bit more with having my quotes broken apart into different tables and then brought them all together into one main table. My objective from there was to be able to generate a report from that information. Since one quote can have multiple things within it I decided to make a "brute force" type script which would scan though the quantity fields and then copy a proper report over to a field within that quote. I've been able to get a desired result but do have doubts that its really a proper way to do it. To give an analogy... It feels like I'm using hammer to get something done when I really should be using screwdriver.

Was going though some of the filemaker help files about the summery options and browsing though some of the forums but I think I have to work at it a bit. I also don't know if the way I have it set up currently will allow me to use the summery tools as well.

OLB.zip

Edited February 1, 2008 by Guest

[Fenton]

I'm at a bit of a loss here. In my opinion, you cannot have something like a Quotes table without a line items table, where the actual items are (quantity, item code/name, etc.). A Quotes table is much like a shell for the line items.

Before we were discussing whether you needed 2 (or more) line items tables (laminating, plastic, etc.). Or whether these would all work within 1 line items table, with some fields shared (quantity, item code/name), and some fields unique to a type of item. This, in my opinion, is the best way to start, only splitting the line item tables if the above becomes unworkable.

Now, we've got an overly flattened structure, no line items at all. That is just not going to work. We've gone from possibly too many tables to too few, which is even worse. We were not saying "no line items table," we were trying to decide on 1 or many.

When I see the items inside the parent table, and repeating fields, well, let's just say we are no longer looking at a robust system for a relational database.

[P.S. You can import all those line items repeating fields to a new table, to create the line items table. Or, if you have FileMaker Pro Advanced (but you don't seem to), copy/paste them.]

[Fenton]

Also, in my opinion, the whole point of putting all the line items in one table is to share the Quantity and code/name fields (whatever they're called). There is no need to use different fields for these, as there is 1 and only 1 item in a line items record. There would not be a laminating AND plastic (or whatever) line item. (And if there is, you need a "compound items" join table.)

Why? Because then Purchase Orders, Inventory, and reports of any kind do not have to go through all kinds of conniptions because there are so many different code/name and quantity fields.

The short rule of thumb is, break things into separate tables when necessary, but not when unnecessary. Vague advice, for sure.

I don't really know whether Books fields belong in Quotes or not (only 1 Book per Quote; then maybe so).

Anyway, here is your file with all the "items" fields move to a new line items table. I didn't delete anything because, 1. I don't know exactly which to delete, 2. It would remove them from the layouts, where they really need to be reassigned and put into portal(s), and 3. It's tedious -]

OLB_fej.fp7.zip