<table>mark, fetch, change confusion

[Tom Cone Jr]

RE: &lt;table&gt;mark, fetch, change confusion

Don,

Here's the approach I'd take...

tblchoice = ui_get_radio("Select Table to Index in Ad order",1,"chrontemp","chronads","chrontest")
IF tblchoice = ""  'If user presses Cancel...
	END   ' end the script.
END IF
AdTable = table.open(tblchoice, File_RW_Exclusive)
AdTable.index_create_begin("Tmp_idx","Substr(AdInfo,1,100)+cdate(AdDate)")
Adx = AdTable.index_create_end()

xbasic_wait_for_idle()

AdTable.index_primary_put(Adx)
AdTable.Fetch_First()
vAdInfo = Substr(AdTable.AdInfo,1,100)  'set initial var before loop
AdTable.fetch_next() 'move to first comparison
while .not. AdTable.fetch_eof()
	if vAdInfo = Substr(AdTable.Adinfo,1,100) then
		AdTable.fetch_prev()
		AdTable.change_begin()
		AdTable.mark()
		AdTable.change_end(.t.)
		xbasic_wait_for_idle()
		AdTable.fetch_next()
	end if  
	vAdInfo = Substr(AdTable.AdInfo,1,100)
	AdTable.fetch_next()
end while
AdTable.close()
END

[Peter.Wayne]

RE: &lt;table&gt;mark, fetch, change confusion

Don,
This script should do what you want -- delete duplicate records, deleting the oldest ones:

tblchoice = ui_get_radio("Select Table to Index in Ad order",1,"chrontemp","chronads","chrontest")
IF tblchoice = ""  'If user presses Cancel...
	END   ' end the script.
END IF
AdTable = table.open(tblchoice)
query.filter=".t."
' notice the INVERT of the AdDate -- simplifies later processing
query.order="Substr(AdInfo,1,100)+invert(AdDate)"
query.options=""
ix=AdTable.query_create()
AdTable.Fetch_First()
vAdInfo=""

while .not. AdTable.fetch_eof()
	if Substr(AdTable.AdInfo,1,100) == vAdInfo		'duplicate Ad
		AdTable.change_begin()
		AdTable.delete()		
		AdTable.change_end()
	else
		vAdInfo = Substr(AdTable.AdInfo,1,100)
		AdTable.fetch_next()
	end if
end while
AdTable.close()
END

What you may see here is that the delete() method does an automatic fetch_next() -- the table pointer has to go somewhere and it can't stay on the deleted record, so it bounces up to the next record in order.
By indexing in inverse order by date you can dispense with all the back-and-forth shuffling you were doing with fetch_prev() and fetch_next(). It also simplifies matters by incorporating the deletion into the first loop.

[Tom Cone Jr]

RE: &lt;table&gt;mark, fetch, change confusion

Dr. Wayne,

Don't you mean:

...

while .not. AdTable.fetch_eof()
	if Substr(AdTable.AdInfo,1,100) == vAdInfo		'duplicate Ad
		AdTable.change_begin()
		AdTable.delete()		
		AdTable.change_end()
	else
		vAdInfo = Substr(AdTable.AdInfo,1,100)
		'AdTable.fetch_next()
	end if
        AdTable.fetch_next()  
end while

--
-- tom

[Peter.Wayne]

RE: &lt;table&gt;mark, fetch, change confusion

No, Tom, I meant what I said and I said what I meant:

The fetch_next() occurs automatically if a deletion takes place. If you do another fetch_next() after the deletion the script will skip over records.

Try it and see. I can always be wrong.

- Peter

[Tom Cone Jr]

RE: &lt;table&gt;mark, fetch, change confusion

Hah!

I was so intently concentrating on the logic of the loop I overlooked that you're deleting records, while I'm just marking them. Sorry to have intruded!

-- tom

[JohnZaleski]

RE: &lt;table&gt;mark, fetch, change confusion

Don,
Peter's script looks like it will keep the latest ad date and delete all others, but it sounded like you wanted to keep the earliest ad date and the latest ad date.
If that's the case it's a little more complicated than Peter's script - there could be 3 situations:
1) exactly two ads - two dates delete none
2) one ad - one date delete none
3) more than two ads - save earliest and latest - delete all others
If that's what you want to do, Peter's script will get you in the right direction but it will need more work.
John

[Don Lipke]

RE: &lt;table&gt;mark, fetch, change confusion

I still get the same type of trace discrepancy on my machine with your code. Does your machine show the same trace difference between disabling and enabling the mark command as I've listed here?

I added the following TRACE line just ahead of "end while":
trace.writeln(" Next "+str(AdTable.recno()))

With AdTable.mark() commented out:
Next 3
Next 5
Next 2
Next 4
Next 4

With AdTable.mark() enabled:
Next 3
Next 3
Next 5
Next 2
Next 4
Next 3
Next 5
Next 2
Next 4
Next 4

For each run I checked that the table had six unmarked records and I packed it to ensure there are no deleted records in the table. The shorter trace above makes sense to me as the index would put record numbers in 1,6,3,5,2,4 order. I cannot conceive of how the second trace sequence is produced. The jump from 4 to 3 midway is curious...then it follows the correct sequence for the next four fetches.

How can marking a record affect the sequence like this???

[Don Lipke]

RE: &lt;table&gt;mark, fetch, change confusion

I haven't tried your code yet, Peter, but I do have more data manipulation in mind. I want to pass the oldest AdDate to a field FirstDate and keep the newest AdDate in that field for all records with the same ad text. I will need some back and forth stepping through the data.

My original code and a simple database with a 6-record table (with some parts that I need commented out for testing) is attached to my Message ID 51779. I'm not asking for help with the code methods but rather what is wrong with the fetch sequence.

Why should the act of marking records affect the sequence that fetches follow? See my Message ID 51791.

I'm really curious as to whether I have a problem with my installation of Alpha5V5 and would like to know if the same trace results are obtained on another machine. (I only have a single full version license.)

Thanks,
Don

[Don Lipke]

RE: &lt;table&gt;mark, fetch, change confusion

John,

Please see my response (Message ID 51794) to Peter.

Basically I am comfortable with working out the code to do what I need except when I find a descrepancy between what the code should do and what evidence shows that it does!

What I've experienced is not logical and is either a bug in my system or with the A5V5 program itself. I would first suspect my installation.

What I'd appreciate most is for someone to determine whether or not they can duplicate my trace results on my submitted database.

Thanks,
Don

[Tom Cone Jr]

RE: &lt;table&gt;mark, fetch, change confusion

Don,

Here's what I'm seeing with the code you'll find in the attached zip file:

Curr          1 Next          6
Curr          6 Next          3
Curr          3 Next          5
Curr          5 Next          2
Curr          2 Next          4
Curr          4 Next          4

this is what you are shooting for, I think.
I'm using Win98SE and Build 1498-1051.

Unzip the attachment to an empty folder.
Run the script. Do you get the same
results?

-- tom

[Peter.Wayne]

RE: &lt;table&gt;mark, fetch, change confusion

Don,
I'm not sure that indexes are allowed on memo fields, or even portions of memo fields, and I think that may be the cause of your problem.
Here's what I did:
1) I changed the name of the AdInfo memo field to "AdInfoM".
2) I created a new field, AdInfo, C, 100, and ran an update in which I inserted left(AdInfoM,100) into the new field.
3) I modified the script to do the updates into the FirstDate field and phone fields:

tblchoice = ui_get_radio("Select Table to Index in Ad order",1,"chrontemp","chronads","chrontest")
IF tblchoice = ""  'If user presses Cancel...
	END   ' end the script.
END IF
AdTable = table.open(tblchoice)
query.filter=".t."
query.order="Substr(AdInfo,1,100)+invert(cdate(AdDate))"
query.options=""
ix=AdTable.query_create()
AdTable.Fetch_First()
vAdInfo=""
'debug(1)
while .not. AdTable.fetch_eof()
	if Substr(AdTable.AdInfo,1,100) == vAdInfo		'duplicate Ad
		vFirstDate=AdTable.AdDate
		vStatus = AdTable.Status 
		vPhone1 = AdTable.Phone1 
		vPhone2 = AdTable.Phone2 
		AdTable.change_begin()
		AdTable.delete()		
		AdTable.change_end() 
		' now the record pointer is autoadvanced, after the deletion
		AdTable.fetch_prev() ' go back to original, skipping over deleted record
		AdTable.change_begin()
			AdTable.FirstDate=vFirstDate
			AdTable.Phone1 = vPhone1 'keep older phones in case they were... 
			AdTable.Phone2 = vPhone2 ' changed from AdInfo in older data 
		AdTable.change_end(.t.)
	end if 
	vAdInfo = Substr(AdTable.AdInfo,1,100)
	AdTable.fetch_next()
end while
AdTable.close()
END

Try this script and see if it does what you want on your full dataset.

[Peter.Wayne]

RE: &lt;table&gt;mark, fetch, change confusion

Also, once you have created the new field and filled it with an update, you can use the default field values rules under "Data Entry" to make sure that the field is always filled in when a new entry is made. See the attached picture. Note that this will only fill in the field properly when a new entry is made. If you want to make sure that it is kept up to date whenever a change is made to the AdInfoM field, you could need to add a short script to the OnSaveRecord event of the table, e.g.
t=table.get("chrontest")
if t.AdInfoleft(t.AdInfoM,100) then
t.change_begin()
t.AdInfo=left(t.AdInfoM,100)
t.change_end()
end if

which should do it.

[JohnZaleski]

RE: &lt;table&gt;mark, fetch, change confusion

peter,
Why not make the new field a calculated field that is the first 100 bytes of the ememo field
John

[Peter.Wayne]

RE: &lt;table&gt;mark, fetch, change confusion

John is right. I wasn't sure whether a query would work on a calculated field, but apparently it does.
The script I posted earlier also fails if the duplicate record is at the end_of_file -- then the autoadvance can't take place. Here is a revised version of the script:

tblchoice = ui_get_radio("Select Table to Index in Ad order",1,"chrontemp","chronads","chrontest")
IF tblchoice = ""  'If user presses Cancel...
	END   ' end the script.
END IF
AdTable = table.open(tblchoice)
query.filter=".t."
query.order="AdInfo+invert(cdate(AdDate))"
query.options=""
ix=AdTable.query_create()
AdTable.Fetch_First()
vAdInfo=""

dim eof_flag as l

while .not. AdTable.fetch_eof()
	if Substr(AdTable.AdInfo,1,100) == vAdInfo		'duplicate Ad
		'debug(1)
		vFirstDate=AdTable.AdDate
		vStatus = AdTable.Status 
		vPhone1 = AdTable.Phone1 
		vPhone2 = AdTable.Phone2
		eof_flag=AdTable.eof() 
		AdTable.change_begin()
		AdTable.delete()		
		AdTable.change_end() 

		' now the record pointer is autoadvanced, after the deletion
		if eof_flag=.f. then ' exception to autoadvance if we were at eof
			AdTable.fetch_prev() ' go back to original, skipping over deleted record
		else
			AdTable.fetch_last() ' make sure you're at the last record
		end if
		AdTable.change_begin()
			AdTable.FirstDate=vFirstDate
			AdTable.Phone1 = vPhone1 'keep older phones in case they were... 
			AdTable.Phone2 = vPhone2 ' changed from AdInfo in older data 
		AdTable.change_end(.t.)
	end if 
	vAdInfo = Substr(AdTable.AdInfo,1,100)
	AdTable.fetch_next()
end while
AdTable.close()
END

[JohnZaleski]

RE: &lt;table&gt;mark, fetch, change confusion

If you follow Peter's steps, Don's original code will fetch exactly as dsired ( even with the syntax errors using the index_create). It's obviously indexing on the memo field that is the problem and not Alpha's record fetching.
It's been my understanding that you can do some full text indexing on memo fields for queries, but this type of index isn't helpful when fetching - something like:
KEYWORDSI(ADINFO_M,VAR->EXCLUDED_WORDS)