Relationships and Look-ups

[bright]

OK, I am so lost and don't know what to do but smash the computer (which I haven't, yet).

I'm making 3 simple databases - Purchase Order, Inventory, and Suppliers Account. I have created a relationship to Purchase Order and Inventory, and another one to Inventory and Suppliers Account.

I'd like to make the inventories as a main root, and contains both (Purchase Order and Suppliers Account) info (i.e. when it was purchased from what client, and when it was paid to what suppliers).

However, I noticed that some fields are related and some are not (doesn't show up on the inventory form), even I made sure those fields are from the other databases (Purchase Order + Suppliers Account).

Could you please someone help me? Please let me know if my question is hard to understand or something....

Thank you in advance.

[Reed]

A good place to start (a good example) is the Filemaker Business Tracker. You can download it at Filemaker's web site, and it's completely unlocked so you can see how it works.

If you post the files you're working on, it would be easier to understand what you're referring to.

Dana

[bright]

Thank you, Dana. I downloaded it and I'll play around a little. See how it goes.

I'm sure I'll post more ?s later (before I smash the computer)...

[Earthling]

Hi,

Maybe I can also help a little so that you understand more about relationships (sounds like on-line therapy!)

It is extremely good practice to have a totally independant field which will uniquely identify each record in a table. So for example in your inventory database (or table if you have decided to keep everything together) if you have a table called "Inventory Item" you make a field in the "Inventory Item" table which is unique (especially if it is going to be the "root" table as you have said). This unique value can be put into a field called "Inventory_Item_ID". Make this an auto-entered serial number and force it to be unique. That way every time you create a new record you will have a unique identifier for that record.

Similarly in "Purchase Order" don't use the purchase order number as the unique identifier, but rather create an ID tag in the same way (a new field which you might call "Purchase_Order_ID" and again make it an incrementally increasing number and unique). You should do this for every table that you have. You may not use all of this ID numbers but it is a great habit to get into if you want to start building further relationships later on. By the way these unique identifiers are called "keys" in the database trade.

These keys are then used to make the relationships between your databases.

If you want to link the purchase order table to your inventory database then you also need to create the "Inventory_Item_ID" as a new field in the Purchase Order table. This should not be a serial or unique number when it is in this table. When the field "Inventory_Item_ID" in the table "Purchase Order" has the same value as the "Inventory_Item_ID" in the Inventory table then the two records will be related.

Also it is possible that you may have set up a many to many relationship (which will not work) so you have to normalise your database. What does this mean and why do you have to do it?

There are 4 different types of relationships that any one table can have to any other table.

One to one: This is the simplest and it means for any record in a table it can have only one corresponding record in another related table. For example a State name has only one ((official) abbreviation. You can also say that An official state name abbreviation can only have one state.

So if we are in the situation where we have a table with a state name and this table is related to a table that has a state's official abbreviation in it, then (putting it in database-ese) for every instance of "StateName" there is one and only one corresponding value for "StateAbbreviation" and the reverse is also true.

The "many to one" relationship: A much simpler relationship to explain. An example of a many to one relationship is that of son to mother. A son can only have one mother, but a mother can have several sons.

The "one to many" relationship: the same as above but put the other way around. Any one mother can have many sons, but any one son can only have one mother.

The "many to many" relationship: a lock can have many pass keys and a pass key can have many lock (that it can open)

Why is this important? Because in general your database has to have a one to many or a many to one relationship between any instance of two tables.

Please feel free to contact me if you need anyfurther help.

If you know all this stuff already and I have been firing too low but you are still having problems you might be interested to know of a bug that I found in Filemaker 7. sometimes related records don't show up in portals etc even though they are properly related and everything should work OK. This happened to me. I wrote a script that "regenerates" my database. It goes to every key in every table, cuts the key and then pastes it back directly into the same field. and Lo and behold the relationships are repaired. This has happened time and again. That cut/paste command shoud effectively do nothing. I serve this database to the public and i get e-mails that they can't see one or two of their projects, I run the script and POW! the related records are back and can be seen again.

Hope this helps,

all the best,

Earthling.