Revisions and Primary Keys: Help Wanted

[scolesm]

Hello,

I have a database made up of products. Each of these products has a unique ID number. Information on them also undergoes multiple revisions. I want to save these revisions.

Right now all tables are related by the ID number alone, but I forsee that when a revision takes place, because of this it will change for all revisions with the same ID (which I don't want).

Would the simplest way to keep everything sorted be to create a field that is common to all IDs with the same revision and use that as the relationship key? The thought in my head now is it would be a concatenation like IDNumber.RevisionNumber

Sorry if the question sounds basic, I'm thinking aloud and looking for advice.

Thanks a lot,

Michael

[scolesm]

Hello fellow FileMakers,

Alright, I whipped up a primary key to link them all, as I suggested previously.

My great problem now is that although the proper records are preserved, the new one created is empty.

I have about 10 different tables, each of which need to be copied and assigned the new key.

How do I go about doing this?

Help at this point would be greatly appreciated, as I feel I am so close.

I am posting my file. The way it should work is as follows:

Data is entered into Data Entry

From Data Entry, a Draft Spec is created

Once the Draft Spec is good, the first revision of the Standard Spec and Certificate of Analysis are created

Also, since the revision number is now incremented to one, a new record of data entry is created.

My problem lies in the fact that the new record has a new primary key value and thus no longer links to the correct information.

What scripting process can I use to copy all related information and give them new primary key values?

Thank you greatly for any help,

Michael

Edit:

For the sake of simplicity, guest has full access

PMFv0.11.zip

Edited July 12, 2006 by Guest
User name needed

[Ender]

It sounds like you need to retain most of the data (and related data) from the previous revision for historical purposes. I would propose to do this by introducing a Product Revision table, related to Product (your "Data Entry" table) by Item Number. The records in the Product table would then represent the product, and the revision-specific details can be stored in the Product Revision table.

With this logical structure in place, the Product Revision table could have it's own set of auto-entered IDs that are used to link all those other child tables (instead of using the Item Number), or you could use two keys, the Item Number and the Revision Number.

To actually populate the new revision's child records, you would use a self-join of each table (either using the previous Product Revision ID as the parent key or the combination of the Item Number and Revision Number - 1. Have a script loop through them creating a new record for each or perform an import of the relevant related record set.

There are a couple advantages to using a Product Revision table for this:

1. If there is information that is always shared among all revisions of the products, that can be kept in the Product table.

2. If you Item Numbers are auto-enter serial numbers, this design simplifies their creation and avoids creating gaps that you might have with your current structure.

[scolesm]

Ender,

Thank you for responding!

Please bear with me, as this is my first time doing anything this complicated. I have just a few questions which I pray will get me back on my feet.

What would this Product Revision Table hold as fields? I play on going the Item Number & Revision Number way and currently only have those two, which are linking directly to my Product (Data Entry). I hope I haven't gone wrong yet

My understanding of the 'child records' is that they are the * per products. Are you suggesting that I add a Revision Number field to all of them?

How would I self join each of these tables, Item Number to Revision Number?

Thanks for bearing with me, I'm only inches away from getting the hang of this,

Michael

[Ender]

What would this Product Revision Table hold as fields?

As I said, whatever is common to all revisions of a Product goes in the Product table, whatever is revision-specific goes in the Product Revision table. Only you know the business rules, so you'll have to figure this out.

I play on going the Item Number & Revision Number way and currently only have those two, which are linking directly to my Product (Data Entry)

The primary link between Product and Product Revision would be by Item Number only. Remember, there are many Product Revisions for each Product, so the primary key in Product is the Item Number, and the foriegn key in Product Revision linking to Product is then Item Number too. The logical structure would then have each of the child tables relating to Product Revision by Item Number and Revision Number (using compound relationships). This necessarily requires a Revision Number be stored in each child table.

How would I self join each of these tables, Item Number to Revision Number?

The relationships would look something like:

table <=> table selfjoin =

table::Item Number = table selfjoin::Item Number

AND table::Revision Number Less One = table selfjoin::Revision Number

This means that if you're on Item Number 1223, Revision Number 2, and you want to create Rev 3, you would

(1. Store the current Item Number (1223) and Revision Number (2) in globals.

(2. Create a new record.

(3. Set the Item Number from what was stored (1223).

(4. Set the Revision Number from what was stored, adding 1 (gives us Revision Number 3).

(5. Utilize the selfjoin relationship to get the data from the previous revision into any fields you wish to populate.

[hillborne]

The relationships would look something like:

table <=> table selfjoin =

table::Item Number = table selfjoin::Item Number

AND table::Revision Number Less One = table selfjoin::Revision Number

This means that if you're on Item Number 1223, Revision Number 2, and you want to create Rev 3, you would

(1. Store the current Item Number (1223) and Revision Number (2) in globals.

(2. Create a new record.

(3. Set the Item Number from what was stored (1223).

(4. Set the Revision Number from what was stored, adding 1 (gives us Revision Number 3).

(5. Utilize the selfjoin relationship to get the data from the previous revision into any fields you wish to populate.

Hi Ender,

I just happened to realize that I am in the same situation and stumbled upon your elegant solution. I have a few questions for you, though, if you'd be so kind...

#1. I think I understand all the steps you've outlined except #5. Can you elaborate a bit more on how you'd best populate the new record? I'm kinda fuzzy on selfjoins, unless they involve superglue.

#2. Would it be better just to duplicate the record, set the ID, and increment the Rev#?

#3. If yes to #2, then would you just have a script that goes to related records, duplicate those, and increment their Rev#'s?

Thanks in advance, I'm really excited about this!

[Ender]

Sure. There are lots of ways to skin this cat.

#1. I think I understand all the steps you've outlined except #5. Can you elaborate a bit more on how you'd best populate the new record? I'm kinda fuzzy on selfjoins, unless they involve superglue.

The self-join is just a relationship. Use Set Field [] to populate the fields.

#2. Would it be better just to duplicate the record, set the ID, and increment the Rev#?

That may work. It kind of depends on what all should be retained from the previous Revision record. If it should mostly start out blank, then it's easier to create a new one, if it retains a lot of fields (for historical purposes,) then it's easier to duplicate and change the last record.

#3. If yes to #2, then would you just have a script that goes to related records, duplicate those, and increment their Rev#'s?

That's about right, but I was thinking this would be done on only one record at a time (the last Product Revision for the selected Product). So the algorithm would be:

Go to Related Records [ Show only Related (current) ; Product Revision ]

Go to Record/Request/Page [ Last ]

Duplicate Record

Set Field [ Revision Number ; Revision Number + 1 ]

...

[hillborne]

Excellent!

Yes, I wanted to archive it for record keeping purposes so I'll try the duplicate route.

Thanks for the input!

[scolesm]

Yes, sorry Ender, I forgot to give thanks for your help, your solution was most useful to me.