Help! Lookup? Relational DB? What?

[Karen1958]

I feel pretty stupid, but...right now I have one DB with multiple layouts, and a script which copies data from two fields of one layout into 2 fields of another, creating a new record in the latter.

The first layout ("master records") holds data that will never be deleted. The second has records that will be deleted after 3 months or so. I really need to create a relational DB (I guess) so that these two can be separated, but nothing I've read about lookups or relationships seems appropriate for this situation (which just cannot be right??).

I don't want to have ANY relationship between the two, but can't figure out how to use a lookup to achieve this, since lookup compares values and that has nothing to do with this.

I'm an idiot! Help!

[Breezer]

I think you need two tables. One to hold your historical records which you've named master. The second table will be your active records and can be deleted every three months.

What you need to do is have a relationship (or not depending on use of the data) that will post or copy all your current records to the master table just before you delete them. There are various ways of achiving this all depending on the estimated amount of data you will be working on.

One way could be import records to your master file before deleting the current records. This can be automated using a script step.

Another way could be to have a loop script that will loop through the current records and post them to the master file. Filemaker has improved the speed of loop script especially if you use a script variable denoted by $ for local variable and $$ for global variable. Although script variable is new in FM8, you can still use a loop script in FM7.

ps, you are NOT an idiot, there's many other ways of doing this, it all depends on the amount and nature of data that you are working with.

Edited October 16, 2005 by Guest

[Karen1958]

See below

[Karen1958]

Thanks Breezer for your help. I realized my question wasn't clearly described, so I'm gonna start over.

I currently have 1 DB with multiple layouts.

1 of those layouts should be a separate DB, but I don't know how to achieve a lookup between the two if I separate them.

The way it currently works (and the way I'd like it to continue to work after I make them 2 separate DBs):

1) From my "Main" layout, I run a script that changes to the "Master" layout and runs a find script in a title/artist field.

2) From the records returned I choose one, then run a script which copies the contents of that field, switches back to the "Main" layout and pastes in a corresponding field.

3) The script continues by returning to the "Master" record I'd already chosen, copying the contents of another field, returning to the "Main" layout, and pasting the contents of the second field into a second corresponding one.

There is NO relationship between the layouts. Changing the contents of either field in either layout does not change the contents in the other. Deleting a record in one layout does not delete a record in the other (well, it DOES, obviously, but the way the record looks in the other layout from whichever one I'm deleting records is nothing but blank fields - an empty record completely...which is why I need to separate these, because it's getting ridiculous. In each layout there are now many [what look like] blank records).

I can establish a relationship between the fields in 2 separate DBs, but then can't seem to rewrite the script so it runs; and in any event DON'T WANT ANY CHANGES in these fields to affect the corresponding fields in the other DB.

A lookup doesn't work, either, because it compares values and the exact value will never be identical: in order to find the record I want, I'm entering as little information as possible, and then choosing the best match from which to create a new record in the "Main" layout.

I am, once again, in murky waters.

Life preserver, anyone?

ADDED:

I've moved this topic to "relationships", where it seems more appropriate:

http://fmforums.com/forum/showtopic.php?tid/170756/

Edited October 17, 2005 by Guest