Anyway to Loop thru records to update a field

[Carl Elmore]

Hi All,

I need some help/direction ... I am not proficient with XBASIC. Here is the problem: I have an equipment table for transformers etc. A record is created when the equipment is installed and when it is removed. There may be one (1) record or more than ten (10) records for each serial number. Each record contains a serial number (serialnum), date (eqpdate) etc. I have added a record type (rec_type) field. The idea is to flag each record for a serial number as "HISTORY" in the Rec_Type field except the latest record which will be flaged as "ACTIVE". This would allow for one to report on the history of a transformer or by using the "ACTIVE" record to know the current location of the equipment in question. This table has over 16000 records and I need to UPDATE THE rec_type field as "HISTORY" or "ACTIVE". Sure would hate to do this manually .....

The records are ordered by date (eqpdate). I think I would need to loop through the records grouped in serial number by date order. Then update (rec_type) as "HISTORY" except the last record for each serial number which would be updated as "ACTIVE". It might be easier to do an update to update all records as 'HISTORY" and then do a second update that would loop thru the records grouped in serial number in date order and somehow fetch the last record for each serial number and update it to "ACTIVE".

Any help would be greatly appreciated .....

Thanks,

Carl

[Mike Wilson]

Re: Anyway to Loop thru records to update a field

Carl,
Back up your table first. Then.....
I think this will do it. I can't test it so it's a best guess. It might be a tad slow with 16K records because it is not using indexed processes, but should not take that long. I assumed the table name. I also wrote the filter for character serial number. If is numeric, dim vSN as N and change the filter on line 18 to:

vfilter = "serialnum = "+var->vSN + " .and. rec_type = \"\""

dim t as P
dim tb as p
dim vSN as C
dim vmess as C

tb = table.open("equiptment")

t = table.open("equiptment")
query.filter = ""
query.order = "eqpdate"
xi = t.query_create()

t.fetch_first()
while .not. t.fetch_eof()
	vSN = t.serialnum

	' check to see if table has had the serial number already addressed
	vfilter = "serialnum = "+quote(vSN)+" .and. rec_type = \"\""
	if a5_get_records_in_query("equiptment",vfilter)<>0
#
		' start with most recent so it will be active
		vmess = "Active"

		' filter the second opening of the table
		query.filter = vfilter
		query.order = "invert(cdate(eqpdate)"		' this places the most recent (active) first
		xb = tb.query_create()
	
		tb.fetch_first()
		while .not. tb.fetch_eof()
		tb.change_begin()
			tb.rec_type = vmess
		tb.change_end(.t.)
			vmess = "History"
		tb.fetch_next()
		end while
		xb.drop()
	end if
t.fetch_next()
end while
xi.drop()
t.close()
tb.close()

[Carl Elmore]

Re: Anyway to Loop thru records to update a field

Hi Mike,

I greatly appreciate the reply.
I will check it out as soon as I can and let you know how it works out.

This will give me an opportunity to check out the Xbasic process, maybe I can learne something from this.

Again I appreciate the help from you and others on the forum.
Keep up the good work.

Carl

[Stan Mathews]

Re: Anyway to Loop thru records to update a field

A different take on the process borrowing heavily from Mike's script.

dim t as P
dim tb as p
dim vSN as C
dim vmess as C

tb = table.open("equiptment",FILE_RW_EXCLUSIVE)
' order the table
query.filter = ""
query.order = "serialnum + invert(cdate(eqpdate))	' this places the most recent (active) first
xb = tb.query_create()

tb.fetch_first()
WHILE .not. tb.fetch_eof()
	vSN = tb.serialnum
	vmess = "Active"
	tb.change_begin()
	tb.rec_type = vmess
	tb.change_end(.t.)
	tb.fetch_next()
	IF tb.serialnum = vSN
		WHILE tb.serialnum = vSN .and. (.not. tb.fetch_eof())
			vmess = "History"
			tb.change_begin()
			tb.rec_type = vmess
			tb.change_end(.t.)
			tb.fetch_next()
		END WHILE
	END IF
END WHILE
xb.drop()
tb.close()

[Mike Wilson]

Re: Anyway to Loop thru records to update a field

Stan,
Nice code. One table open, written to and evaluated for repeats. The query.order needs a " at the end but with that it works well. It process 16,000 records in 2:16 compared to mine which runs 6:28. I like it.

[Stan Mathews]

Re: Anyway to Loop thru records to update a field

Thanks, Ira could probably do this in two lines.

Try the modification below and see what you get.

[COLOR="Red"]tb.batch_begin()[/COLOR]
WHILE .not. tb.fetch_eof()
	vSN = tb.serialnum
	vmess = "Active"
	tb.change_begin()
	tb.rec_type = vmess
	tb.change_end(.t.)
	tb.fetch_next()
	IF tb.serialnum = vSN
		WHILE tb.serialnum = vSN .and. (.not. tb.fetch_eof())
			vmess = "History"
			tb.change_begin()
			tb.rec_type = vmess
			tb.change_end(.t.)
			tb.fetch_next()
		END WHILE
	END IF
END WHILE
[COLOR="red"]tb.batch_end()[/COLOR]

[Stan Mathews]

Re: Anyway to Loop thru records to update a field

Or one line, for the fun of it.

tb = table.open("equiptment",FILE_RW_EXCLUSIVE);query.filter = "";query.order = "serialnum + invert(cdate(eqpdate))";xb = tb.query_create();tb.fetch_first();WHILE .not. tb.fetch_eof();vSN = tb.serialnum;vmess = "Active";tb.change_begin();tb.rec_type = vmess;tb.change_end(.t.);tb.fetch_next();IF tb.serialnum = vSN;WHILE tb.serialnum = vSN .and. (.not. tb.fetch_eof());vmess = "History";tb.change_begin();tb.rec_type = vmess;tb.change_end(.t.);tb.fetch_next();END WHILE;END IF;END WHILE;xb.drop();tb.close()

[Mike Wilson]

Re: Anyway to Loop thru records to update a field

OK here are the results. Clearing and updating 16,000 records

............Stan's no batch()......Stan's with batch()......Clear w/ batch()
v8:1980-3136 .........3:16 .................. :46 ....................... :38

v9:2005-3252 ..........2:05 ..................2:05 .................... 1:18

Quite the example of the value of the batch method. I never experienced that difference before so don't tend to use it. I am pretty convinced now! Seems to be a big deal with and without batch() and between v8 and v9. What's with the latter????

BTW, I placed the tb.batch_begin() before the tb.fetch_first() command to get it to work.

[Stan Mathews]

Re: Anyway to Loop thru records to update a field

Mike,

Have any scripted operations or operations that perform a lookup? I converted a script last week to use a collection instead. Execution time went from 385.7 secs to 27.8.

Good process to use instead of an inlist() or inlist2() if the list is very large.

[Carl Elmore]

Re: Anyway to Loop thru records to update a field

Hi All,

Mike I used the code in your first reply. I got it to work ...... thanks !!!

I will look at all of the other examples provided.

I greatly appreciate all the help ........ I hope I can learn a little about Xbasic.

This is the best part about Alpha .... the help on the forum.

Again thanks to everyone .....

Carl Elmore

[Mike Wilson]

Re: Anyway to Loop thru records to update a field

Carl,
Great, I am glad you got it to work. I recommend you look at Stan's code. His is much more concise and efficient. The main differences are:

1. I opened two instances of the table, stepping through the first to direct filtering and changing values in the second. Stan used one table opening, got the records with the same serial number together with his concatenated query.order expression and stepped through the single open table making the changes the the group together.

2. I used a5_get_records_in_query() to try and bypass repeat serial numbers while stepping through the first table, where Stan's method also obviated this third opening of the table with his collection of the records together with the same serial numbers using the query.filter, evaluating the next record as having the same serial number as the previous whithin his second while .not. step.

The effieciency gained working through a single open table and removing the multiple open and opening of tables that my method generates makes for the substantial efficiency, and is far better coding.

Stan,
I don't recollect (pun intended) at the moment but I'll take a look. Thanks for the heads up!

[csda1]

Re: Anyway to Loop thru records to update a field

Hi Stan,

Or one line, for the fun of it.

Too Funny!:) ROTFL

Here's a more straightforward variation (I'll leave it to you to convert to 1 line!)

dim tb as p
dim vSN as C
dim vmess as C
dim xb as p

tb = table.open("equiptment",FILE_RW_EXCLUSIVE)
' order the table
query.filter = ""
query.order = "serialnum + invert(cdate(eqpdate))"    ' this places the most recent (active) first
xb = tb.query_create()

tb.batch_begin()
tb.fetch_first()
WHILE .not. tb.fetch_eof()
    vSN = tb.serialnum
    vmess = "Active"
    WHILE (tb.serialnum==vSN) .and. (.not. tb.fetch_eof())
        tb.change_begin()
        tb.rec_type = vmess
        tb.change_end(.t.)
        tb.fetch_next()
        vmess = "History"
    END WHILE
END WHILE
tb.batch_end()

xb.drop()
tb.close()

[Carl Elmore]

Re: Anyway to Loop thru records to update a field

To Everyone who responded ...

Thanks.

Mike,
I used your code .... It did take a while to process all the records. (Sure beats the manual method!)

When I first looked at your code I did see that you were opening the table several times. Not being experienced with Xbasic I was surprised that you could open a table multiple times and still be able to edit/change the data.

When I get a chance I may try Stan's code just to see the difference. Maybe I will learn a little something.

Thanks,

Carl

[Mike Wilson]

Re: Anyway to Loop thru records to update a field

Ira,
Your code times amending 16K records:

v8:1980-3136 0:52
v9:2005-3252 2:07

I have to share with you that I read your code and don't see how it could possibly work. I have studied this for quite a long time and I don't get it.
I can't figure out how it could possibly work, but I tried it and it does.

There is only one t.fetch_next() command and change command and these are in the region qualified by the 2nd WHILE loop. At some point, the expression of that 2nd WHILE statement will evaluate to false, and the code path I expect to go to the point after the nested END WHILE, and bypassing the change command and the t.fetch_next() command. So how is the table being changed when the expression evaluates to .f. and how is the next record fetched??

What am I missing? And why is v9 so much slower than v8?

[csda1]

Re: Anyway to Loop thru records to update a field

Ira,
Your code times amending 16K records:

v8:1980-3136 0:52
v9:2005-3252 2:07

I'm kind of surprised the v8 was slower than the batch version of Stan's. It should be almost identical (within milliseconds)

I have to share with you that I read your code and don't see how it could possibly work. I have studied this for quite a long time and I don't get it.
I can't figure out how it could possibly work, but I tried it and it does.

There is only one t.fetch_next() command and change command and these are in the region qualified by the 2nd WHILE loop. At some point, the expression of that 2nd WHILE statement will evaluate to false, and the code path I expect to go to the point after the nested END WHILE, and bypassing the change command and the t.fetch_next() command. So how is the table being changed when the expression evaluates to .f. and how is the next record fetched??

What am I missing?

Create a small table with maybe 6 records, 3 of one and 3 on another serial number. Then step through it with debug. Basically, on the 1st fetch of a new serial number (and the 1st pass), it sets the active state, then and subsequent ones are set to history. If it fetches past end of file, it leaves both loops. If it just changes serial number, it goes to the beginning of the outer loop, getting the new serial number, and setting it to active again.

And why is v9 so much slower than v8?

I suspect V9 is processing things associated with active link tables and is checking more stuff than V8 with this, but it is mere speculation. It probably hasn't been optimized to the degree it should be. Hopefully, Alpha will pick up on this thread and see what they can do.

But it is these little things that make a difference when processing many records, and is the reason why you constantly see me referencing the speed of items. For 10, 1000, or 10000 records, it's not a big deal. But start processing 100k plus, and time starts to be very relevant.