Using Temporary Tables in Scripts and Reports

[Graham Wickens]

Attached is a script that works just fine with a normal table. I'm trying to fathom how to replace the normal table with a temporary one.
How do I initailly define the table and its fields, so I can use the temp table in a report?

[Stan Mathews]

In my limited experience I usually create a temp table by duplicating an existing table and removing the data and field rules as necessary. This duplicate has the structure needed to hold the data for reports, etc.

If you want a temp table for a summarize or crosstab operation I would run that operation and perform the above on the result.

If you examine the xbasic for an import to an existing table I believe you will find code like the following which actually imports to a temp table and then code (not included here) which appends to the existing table.

[B]temporary_import_table[/B] = file.temp_path_get()+ "awgnash_temp_table.dbf"
import_destination_table = "awgnash"

import_filename = filename_decode("awgfiles\rtlinva.txt")
if file.exists(import_filename) = .f. then 
	ui_msg_box("Error","File to import not found: '"+import_filename+"'.",ui_stop_symbol)
	end	
end if 

import.type = 0
import.names = "use"
import.file = import_filename
import.db = [B]temporary_import_table[/B]
import.skip_lines = 0
import.skip_bytes = 0
import.field_sep = ","
import.record_sep = "<CR><LF>"
import.text_qualifier = ""
import.record_len = 522
import.rem_blanks = .F.
import.escape_newlines = .F.
import.fields = 78
import.field1 = "type,c,0,1,0"
import.field2 = "vendor,c,1,7,0"
import.field3 = "facility,c,2,1,0"
import.field4 = "whse,c,3,2,0"
import.field5 = "awgnum,c,4,8,0"
import.field6 = "com,c,5,3,0"
import.field7 = "subcom,c,6,3,0"
import.field8 = "mpack,c,7,7,0"
import.field9 = "stpack,c,8,7,0"
import.field10 = "size,c,9,6,0"
import.field11 = "blank1,c,10,9,0"
import.field12 = "uom,c,11,2,0"
import.field13 = "unitwt,c,12,5,0"
import.field14 = "uwtdesc,c,13,2,0"
import.field15 = "desc,c,14,30,0"
import.field16 = "cost,c,15,9,0"
import.field17 = "rwtflag,c,16,1,0"
import.field18 = "refl,c,17,1,0"
import.field19 = "casewt,c,18,5,0"
import.field20 = "availflg,c,19,1,0"
import.field21 = "rcode,c,20,1,0"
import.field22 = "availdte,c,21,8,0"
import.field23 = "caseupc,c,22,5,0"
import.field24 = "zsuppress,c,23,1,0"
import.field25 = "upc,c,24,11,0"
import.field26 = "chkdig,c,25,1,0"
import.field27 = "supupc,c,26,7,0"
import.field28 = "drugupc,c,27,1,0"
import.field29 = "posdesc,c,28,12,0"
import.field30 = "deltype,c,29,1,0"
import.field31 = "orderflg,c,30,1,0"
import.field32 = "invtype,c,31,1,0"
import.field33 = "pl,c,32,1,0"
import.field34 = "mcode,c,33,2,0"
import.field35 = "actdate,c,34,8,0"
import.field36 = "podate1,c,35,8,0"
import.field37 = "podate2,c,36,8,0"
import.field38 = "podate3,c,37,8,0"
import.field39 = "blank2,c,38,1,0"
import.field40 = "disrsn,c,39,1,0"
import.field41 = "subitem,c,40,7,0"
import.field42 = "seq,c,41,5,0"
import.field43 = "ptype,c,42,1,0"
import.field44 = "plltct,c,43,4,0"
import.field45 = "pcost,c,44,9,0"
import.field46 = "blank3,c,45,1,0"
import.field47 = "substcd,c,46,1,0"
import.field48 = "allocitm,c,47,1,0"
import.field49 = "itmstat,c,48,1,0"
import.field50 = "isnew,c,49,1,0"
import.field51 = "isrein,c,50,1,0"
import.field52 = "outseas,c,51,1,0"
import.field53 = "disc,c,52,1,0"
import.field54 = "tempout,c,53,1,0"
import.field55 = "dwhnout,c,54,1,0"
import.field56 = "major,c,55,3,0"
import.field57 = "state1,c,56,2,0"
import.field58 = "tax1,c,57,4,0"
import.field59 = "state2,c,58,2,0"
import.field60 = "tax2,c,59,4,0"
import.field61 = "state3,c,60,2,0"
import.field62 = "tax3,c,61,4,0"
import.field63 = "state4,c,62,2,0"
import.field64 = "tax4,c,63,4,0"
import.field65 = "state5,c,64,2,0"
import.field66 = "tax5,c,65,4,0"
import.field67 = "state6,c,66,2,0"
import.field68 = "tax6,c,67,4,0"
import.field69 = "state7,c,68,2,0"
import.field70 = "tax7,c,69,4,0"
import.field71 = "state8,c,70,2,0"
import.field72 = "tax8,c,71,4,0"
import.field73 = "state9,c,72,2,0"
import.field74 = "tax9,c,73,4,0"
import.field75 = "state10,c,74,2,0"
import.field76 = "tax10,c,75,4,0"
import.field77 = "pltflag,c,76,1,0"
import.field78 = "srp,c,77,5,0"
import()

[Graham Wickens]

I have got it to work [rather clumsily] of a sorts. I cannot work out how to get the pre-defined report to work on the temporary table [it being defined for the normal version of the table]. Is there a way of doing this?? I know how to copy a report from one table to another manually, but how do I do it using Xbasic? or is there an easier way?

here is the script:

'Date Created: 30-Sep-2005 01:16:42 PM
'Last Updated: 14-Oct-2005 03:54:12 PM
'Created By  : Graham Wickens
'Updated By  : Graham Wickens
DIM id_count AS N
DIM idx AS P
DIM tbl_one AS P
DIM tbl_two AS P
DIM target AS C

[COLOR="Red"]''  define temporary table[/COLOR]

[COLOR="Blue"]temp_table = file.temp_path_get()+"totals.dbf"[/COLOR]

[COLOR="Red"]'' create fields in temporary Table[/COLOR]

[COLOR="Blue"]table.create_begin("serial","c",12)
table.field_add("indiv_total","n",6)
tbl = table.create_end(temp_table)
tbl.close()[/COLOR]

tbl_one = table.open("movements")
tbl_two = table.open(temp_table)
query.filter = "isnotblank('serial')"
query.order  = "SERIAL"
query.description = "Get Individual"
query.options = "M"
idx=tbl_one.query_create()
id_count = 1
tbl_one.fetch_first()
target = tbl_one.serial
while .not. tbl_one.fetch_eof()
	statusbar.robot()
	IF tbl_one.serial = target
		id_count = id_count + 1
	END IF
	IF tbl_one.serial <> target
		tbl_two.enter_begin()
		tbl_two.serial = target
		tbl_two.indiv_total = id_count
		tbl_two.enter_end(.t.)
		target = tbl_one.serial
		id_count = 1
	END IF
tbl_one.fetch_next()
END WHILE
statusbar.clear()
tbl_two.close()
tbl_one.close()
[COLOR="Red"][B]:Report.Preview("Individual Totals")[/B][/COLOR]
done:
[COLOR="blue"]table.erase(temp_table, .t.)[/COLOR]
end

[Daniel Weiss]

You can also duplicate an existing table if you want the temp should have the same fields as an existing one

new_table = "temp_tbl"
if file.filename_parse(new_table,"dp") = "" then 
	new_table = a5.Get_Path() + chr(92) + new_table 
end if 
new_table = file.filename_parse(new_table,"dpn") + ".dbf"
if file.exists(new_table) then 
	a5_table_delete(new_table)
end if 
dim table_name as c 
table_name = a5.Get_Master_Path()+"\old_tbl.dbf"
dim tbl as p 
tbl = table.open(table_name)
tbl.duplicate(new_table,9)
tbl.close()
file_add_to_db(new_table)

Or you could create a new table using x-basic

path = iif(a5.Get_Master_Path() = "",a5.Get_Path(),a5.Get_Master_Path())
f = filefind.get(path+chr(92)+"temp_tbl.dbf",FILE_FIND_NORMAL, "PN")
fields = <<%a%
Name,C,20,0
Address,C,20,0
Age,N,3,0
Dob,D,8,0
Active,L,1,0
%a%
if f = ""
	create_table(path+chr(92)+"temp_tbl.dbf",fields)
	file_add_to_db(a5.Get_Path()+chr(92)+"temp_tbl.dbf")
end if

To delete and drop it from the DB use

path = iif(a5.Get_Master_Path() = "",a5.Get_Path(),a5.Get_Master_Path())
f = path+chr(92)+"temp_tbl.dbf"
if file.exists(f)
	table.erase(f, .T.)
	file_drop_from_db(f)
end if

[Daniel Weiss]

I guess I noticed your last post to late but I would suggest you add the �file_drop_from_db� if not table stays attached to the db and could create errors down the road

[Graham Wickens]

Thanks Daniel,
I will add the "file_drop" to my script.
This just leaves the question of how do I import a pre-defined report to the temp table and run it?

[Stan Mathews]

I've not tried it but if you

remove the table.erase() from your script
don't use the file_drop_...

use file.remove() to delete the temp.dbf (and just the .dbf)

then any reports created for the temp.dbf should be available from use to use.

[CALocklin]

If you want to print a predefined report, I suggest you use the Temp table as just that and put the report in a permanent 'Reports' table. In other words, do whatever is needed to get the appropriate data into the Temp table then run an append routine to get the Temp data into the 'Reports' table. Obviously, in this case the 'Reports' table would be 'permanent' to hold the report definition but the data would be zapped before each new group of report data was entered. (Sometimes you may want the ability to print various reports from the same data. In that case, it's faster for the user if you give them the option to use the previous data.)

I've done this a number of times and it works quite well when you run into a situation where a simple filter on existing tables just won't do the job.

Sometimes (in fact, in most cases) the intermediate 'Temp' table isn't needed. I'm stretching my memory here but I believe that as long as you can get the data into the 'Report' table without having to create intermediate sets to get additional data, the one table is enough.

[Graham Wickens]

I tried Stan's suggestion about only deleting the temp.dbf, but the TABLE_CREATE overwrites the files and the Report definition is lost.

[Finian Lennon]

Graham:

I use this method all the time. To keep it simple requires a certain amount of housekeeping but for about a hundred reasons it's worthwhile.

I do all of our bill and statement printing from these temp tables. These are the most heavily used transaction tables in our application. I don't see how - in a multi-user environment - you could attempt to create the structure of these temp tables on the fly.

Here's the process: make a duplicate of the key tables (Copy/paste from the table editor is easy) then add a field for the current user to each table. Without a field for the current user you have no way of allocating the records in the temp tables among multiple users.

The duplicate tables generally shouldn't need any field rules and usually need only one index. It is essential that the field order be the same as the source table up through the temp table to any additional fields you might add.

For this method, you need to redesign any sets on which reports are based to use these temp tables. It's a little tedious to get all the table references correct, but it's usually a one-time job.

To print the report:

1. Query the transaction table or set to get the records you need
2. You must first delete any records that user currently owns in each temp table. Then fetch through the records and populate each of the temp tables. I use record_data_get() -> record_data_set() for this, writing the current user name into that field of each temp table before committing the record.
3. You can then generally print the report and the only filter needed is the User name.

The process is necessarily more complex for something like monthly statements which may need to examine and calculate a much larger set of records. However it still works very well.

For our bill and insurance form printing it is extremely fast and runs the same regardless of whether the data base has hundreds or many hundreds of thousands of composite records in a set to be queried. Remember, at the end, the tables involved in printing have a relatively tiny number of records and, as a result, prinitng is just about instantaneous.

Finian

[CALocklin]

Graham, note that in both my suggestion and Finian's you do NOT overwrite the table that has the report definition in it. Finian's method apparently does not even require building a true intermediate table. His reports are printed from a table that holds data temporarily. This is similar to my comment that you don't really need a true temp (perhaps should have said intermediate) table in most cases.

I have had a couple situations where I had to do something like the following which did require an intermediate table (I'm sure this isn't exactly the situation since it's been a few years but it should give you the concept): create an 'intermediate' table, fill it with some basic data, link it to another table to get some additional info, filter it based on the new data and copy that to yet another 'intermediate' table, link it to yet another table to update more fields then append the result into my report table.

You can do just about anything you want - it all depends on your imagination and perseverence. In fact, the previous process could have been done with one 'intermediate' table by adding all the necessary fields, updating them based on the first linked table, deleting the records that weren't needed, then linking and updating the final list. In another situation my customer essentially wanted two reports printed as one. In order to get him the results he wanted, I built a 'report' table (temporary data only) then ran a rather complex xbasic script to fill in each line (record) with the necessary text. It's just takes some study to understand exactly what the reports can do then some imagination (i.e., thinking 'outside the box') to mix and match the appropriate routines to get the result you need. In most cases, it isn't as difficult to actually build the routines as it is to figure out what process is required.

[Tom Cone Jr]

Finian, thanks for this:

It is essential that the field order be the same as the source table up through the temp table to any additional fields you might add.

I thought the table structures must be identical to use <tbl>.record_data_get() and <tbl>.record_data_set(). Your tip that these functions will work as long as any additional fields are placed at the end of the target table's structure is a valuable insight.

thanks.

-- tom

[Finian Lennon]

I've been bitten by this a few times, so that's the voice of experience!

It reinforces the notion that always adding fields at the end of a table is a "good" practice if not for the reasons usually given.

The problems occur when adding a new field to one of the transaction tables without updating the field structure of the temp table. There, of course, the new field is NOT placed at the end but rather in the position that will match the structure of the transaction table. If you don't do this, the data will NOT be properly transferred to the temp table.

OK, there's a housekeeping issue, but being able to add additional fields to the temp table for specific purposes opens up a whole range of extremely useful options.

The only real downside is that you have to kludge some sort of method to periodically (on every startup for example) pack the temp tables which can get huge with deleted records.

Finian