Should I import or should I LookUp?

[Hoib]

tbl_Session_Ops is my daily transaction table. I collect transactions over the course of the day here. tbl_Session_Ops_History is a related table (used Duplicate Table) into which I intend to deposit all today's transactions so that I can have history. I have _kp_clientID in tbl_Session_Ops relation"ed" to _kf_ClientID in tbl_Session_Ops_History. I don't want to dynamically copy each transaction during the day because I often have to return to a previous transaction to change something.

To do this "copy" at the end of the day, what should I use? A script that imports into Session_Ops_History or a script that uses LookUp from tbl_Session_Ops to tbl_Session_Ops_History. The Help file indicates using LookUps for this but I can't see how I would copy all of the data from one to the other.

Advice, please?

Hoib

[Ocean West]

several ways -

export & import or import directly in some cases can be faster

use an SQL plugin and insert the known data into the target table.

looping script.

any time you transverse the related records it is a performance hit -

[doughemi]

Why have a second table at all? Add a number field Archive and add to your startup script:

ReplaceField Contents[No Dialog; tbl_Session_Ops::Archive; If(Get(CurrentDate) = YourDateField; 0; 1)]

Enter Find Mode

Set Field[tbl_Session_Ops::Archive; 0]

Perform Find

to hide all the archived (not today's) records.

[Ocean West]

the problem with replace is you do have to account for record locking (in hosted environment) and properly error capture

plus too when you edit all records in this manner it will also change the modification time or date or timestamp fields. So if these

are used for a particular purpose, they may need to be modified not to update when performing a replace or a looping script, just to set a flag.

[Hoib]

Gee - I sort of like doughemi's approach (which of course I never dreamed of). I'm' still studying this piece ---Set Field[tbl_Session_Ops::Archive; 0]real carefully---. It's got me scratching my head. When you "Set Field", what precise field are you addressing? Archive has already received it's value as either 0 or 1. Let me do some more research in the Help files...

Originally , I was leaning toward an export/import process. Now I'm seeing the problem with this. Such a simple thing...

Still, it doesn't appear that FM 11 Pro Adv has a direct "built in" capability to deposit the contents of one table into another, which is fine. You must program this; loop through each record one at a time. Is that what I'm getting here?

H

[doughemi]

The Set Field step is in Find mode; I am using it to search for all records where Archive = 0 (i.e. today's records only) and omitting (or hiding) all the archived records. Set Field in Find mode just enters your search parameters. At the beginning of the day, there are no found records, of course. But if you have to close the database and reopen it during the day, you will see only the records entered earlier that day.

You could do basically a similar procedure by finding today's date in the startup script. This would address OceanWest's point about destroying modification date/time/timestamp fields.

It is difficult to copy data from table to table because that procedure violates one of the basic tenets of relational databases: DRY (Don't Repeat Yourself). It is just poor practice to store the same data in more than one place. The major exception to that rule is for backups that are only accessed in an emergency, not for data that must be used frequently for reports or other purposes.

[Hoib]

Terrific advice Doughemi. I'll just have to see what "Set Field" does in this context and play with it. I've been reading about "normalization" and I think what you've pointed out is a big part of that. I'm going to try this. I'll post back with results. It looks "easy"... (har).

Thank you, again.

H

[Hoib]

OK, I'm giving this a good try and I'm having trouble getting this syntax sorted out.

Should it be: Set Field[tbl_Session_Ops::Archive; 0]

or

should it be: Set Field[tbl_Session_Ops::Archive = "0"]

or

Should it be: Set Field[tbl_Session_Ops::Archive = 0]

If it's really the semi-colon, there's no way I see to specify semi-colon in the calc dialog in FM.

H

[doughemi]

The semicolon is not a operator, so it doesn't show up on the operator list in the specify calculation dialog. if you use the Specify buttons to pick the field and enter the 0 in the calculated result, FM will drop the semicolon in there itself.

[Vaughan]

Still, it doesn't appear that FM 11 Pro Adv has a direct "built in" capability to deposit the contents of one table into another, which is fine. You must program this; loop through each record one at a time.

That's because good relational design does not require anything like this. If something like this is required then it's an sign of BAD relational design.

It's like complaining that your car battery goes flat every night and the manufacturer should have made it easier to charge the battery by having, say, an external power connection near the driver door to it's easy to plug and unplug, and a reminder somewhere so you don't accidentally drive off with the power cord connected. No. Fix the problem that is causing the battery to go flat. :D

[Hoib]

OK, I've gotten it down now. Both Specify Buttons need to be touched; one for the target field and the other for the match value. If it doesn't hit me square in the face, I don't notice it!

Vaughn - I appreciate what you've pointed out. It's so true as I'm slowly finding out. Many of us coming from the world of Access or mighty SQL are sort of stuck in that mindset - give me a tool that is short and sweet (Select this-n-that/CopyTo), not one that does things correctly in the more academic sense.

Everything here with FM is new, at least to the likes of yours truly. I probabaly could have gotten away without my editorializing... Mon' apolgere'...

Now let's go test some data and see how this actually works....

H