Question on FM autoserial for relationships

[The Big Bear]

Hi All

Just receive my 8.5 advance last night and I have been reading on the Forum lately about letting FM auto enter the serial number that the relationship is base on between tables. I am trying to understand how would a unknown serial number benefit you in finding information once enter? I will be redoing my Welding Program from Version 6 to Version 8.5 and would like to understand this concept. At the moment I use a calculation field to create a unique number to base the relationship on.

Example (I use the following to create the unique field:

Welder clock number

Date tested

Size pipe tested on

Thickness on pipe

WPS number

these five fields creates a calculation field that look like:

90675 0987207 6.625 .876 1FC

Am I doing something incorrect and will pay for it in the future or is this acceptable. I would like to start correctly with 8.5 advance.

That for all the help in the past

Lionel

[LaRetta]

Hi Lionel,

With your current ID, what happens if the record is created and child records (which will need this ID) are also created. And then you realize it wasn't tested on the date you first entered or the date was typed incorrectly? If you change the test date, all your child records which rely on this ID will be orphaned. A unique ID (used to hold bits and pieces of data together from many tables) should be meaningless (so it will NEVER need to change). And, since it should be meaningless, a unique serial is perfect.

We see many times people creating unique IDs from people's names (for example). Then the person gets married and changes her name; or the name was spelled incorrectly when entered and must be changed. It just doesn't work well.

How do you search? You search whichever field contains the data you wish. If you want to find a pipe size, search the pipe size field. If you are searching on your ID of 90675 0987207 6.625 .876 1FC then you could get wrong results. As you solidify your solution and it grows, you may be running scripts to summarize via reports, bill clients etc. If you get wrong records in your search results, you will have problems. Search the field you wish for what you wish. Searching a concatenated field (many fields together in one line) can bite you.

LaRetta

[The Big Bear]

Hi LaRetta

I think am starting to understand a little. So you are saying that the relationship should be based on the serial number field in table A to the serial number field in the other table B. because this serial number with never change.

To search in the welder qualification now, I use a separate layout with only those five fields and the operator populates these fields for a value list that is attached to each field that shows only what that welder is qualify on. There is no typing involved so if a mistake was make enter the information the mistake would show in the value list as well.

Thanks for the help

Lionel

[LaRetta]

Hi Lionel,

"So you are saying that the relationship should be based on the serial number field in table A to the serial number field in the other table B. because this serial number with never change."

Not quite. Each table should have a unique serial. It identifies the entity. For instance, in your Customers table, you'd have a CustomerID (unique auto-enter serial). And in your Invoices table, you'd have InvoiceID (unique auto-enter serial) AND, because you need to identify which Customer the invoice is for, you would also have a standard field (in Invoices) called CustomerID. These two tables (one customer, many invoices) would be joined thus:

Customers::CustomerID = Invoices::CustomerID

The CustomerID in Customers is known as the Parent Key (because it exists in the table in which the entity it deplicts exists). It is also called the Primary Key. The CustomerID in Invoices is known as the Child Key (or Foreign Key).

In your original post, you had asked how you could search if you used auto-enter serials so I assumed you were searching on this concatenated key you created. Not good. As you see, your find could produce results NOT what you want (because they are all just numbers and if you search for 0908, you could find Date Tested or even a part number that BEGINS with your request. So I misunderstood your search process and you have it right.

Understanding the importance of using unique keys is critical to good design. If you do not properly structure your solution using them, your solution will require 20 times more work; will be 20 times the size; and will be IMPOSSIBLE to flex in all situations that may arise. Your base should be correct - then the rest is MUCH easier.

LaRetta :wink2:

[The Big Bear]

LaRetta

Thanks for the quick reply. I think I got it now.

Lionel