DELETE_RANGE() in a multi-user environment? What should I use instead?

[Jeff@Listbrokers]

I'm trying to implement a "Cancel" button on a form. When clicked I want this button to delete records from five different tables based on a unique index called countid. The countid can belong to many different records.

I was thinking about using the following:

tbl.delete_range("countid = form.countid")

The Alpha 5 documentation states:

Note: Do not use <TBL>.DELETE_RANGE() in a multi-user environment where other users may lock records within the range.

My question is: How do I implement this without using delete_range() or, is there a way to test to see if anyone is in the records/table I want to edit?

[Jeff@Listbrokers]

Re: DELETE_RANGE() in a multi-user environment? What should I use instead?

No takers, huh?!?

How about this question:

What's the best way to find and delete one record in a table of unique records using a unique key value? i.e., deleting the record that matches the primary key value...


I was think of something like this:

tbl.delete_range("primary key=1234")

Does anyone know if I'm only deleting this one record and it's guarenteed to be unique, will the delete_range() function still cause problems in a multi-user environment?

[Stan Mathews]

Re: DELETE_RANGE() in a multi-user environment? What should I use instead?

No takers, huh?!?

How about this question:

What's the best way to find and delete one record in a table of unique records using a unique key value? i.e., deleting the record that matches the primary key value...


I was think of something like this:



Does anyone know if I'm only deleting this one record and it's guarenteed to be unique, will the delete_range() function still cause problems in a multi-user environment?

You could, given that you have a valid pointer, tbl, and you know the index tag name

tbl.index_primary_put("key tag name")
rec - tbl.fetch_find(key_value)
if (rec > 0) then
tbl.change_begin()
    tbl.delete()
tbl.change_end(.t.)
end if

[G Gabriel]

Re: DELETE_RANGE() in a multi-user environment? What should I use instead?

When clicked I want this button to delete records from five different tables based on a unique index called countid

Is that form based on a set?

[Jeff@Listbrokers]

Re: DELETE_RANGE() in a multi-user environment? What should I use instead?

G Garbiel,

no it's not a set. There are 5 tables that can have data entered into them. The user has a dropdown box with 5 choices. When the user makes a selection in the DD box the form uses a conditional object to display fields corresponding to each table. The user could possibly enter info into one table, then change the DD box and enter info into another table. This is the desired behavior. Should it be a set?

[Jeff@Listbrokers]

Re: DELETE_RANGE() in a multi-user environment? What should I use instead?

You could, given that you have a valid pointer, tbl, and you know the index tag name

tbl.index_primary_put("key tag name")
rec - tbl.fetch_find(key_value)
if (rec > 0) then
tbl.change_begin()
    tbl.delete()
tbl.change_end(.t.)
end if

Hey Stan, could you elaborate on your code example please?
-What is the "key tag name"? Is that the name of an index?
-Is that supposed to be rec = tbl.fetch_find(key_value)?

Thanks!

[G Gabriel]

Re: DELETE_RANGE() in a multi-user environment? What should I use instead?

Sounds to me, you'd be better off with a set.

[Stan Mathews]

Re: DELETE_RANGE() in a multi-user environment? What should I use instead?

Hey Stan, could you elaborate on your code example please?
-What is the "key tag name"? Is that the name of an index?
-Is that supposed to be rec = tbl.fetch_find(key_value)?

Thanks!

tbl.index_primary_put("key tag name")
you would substitute the tag name of the index that you wanted to control the order of the table
if you have an index on countid and the tag name is countid, you would substitute countid for "key tag name" like
tbl.index_primary_put("countid")

rec - tbl.fetch_find(key_value)
once the controlling index is set you would find the desired record by substituting the countid to find like
rec - tbl.fetch_find(1234)
if countid is numeric

This is only for one table but could be used for all your tables by changing the table that the pointer (tbl) references. Requires that all the tables are indexed on countdi.

[Jeff@Listbrokers]

Re: DELETE_RANGE() in a multi-user environment? What should I use instead?

Thank you both for your input.

- G Gabriel, what would be the advantage of using a set in this instance?


-Stan, where does rec come from? What's stored in it?


I am currently using this script to delete a record:

'Date Created: 13-Oct-2006 10:23:48 AM
'Last Updated: 13-Oct-2006 01:23:47 PM
'Created By  : lab
'Updated By  : lab

[COLOR="Red"]DIM current_tbl as P[/COLOR]      ' Pointer to current table.
[COLOR="red"]DIM vDeleteFields as C[/COLOR]    ' Holds a comma delimited list of field names.
[COLOR="red"]DIM vFieldValues as C[/COLOR]	  ' Holds a comma delimited list of field values.
[COLOR="red"]DIM tblname as C[/COLOR]		  ' The name of the table to perform the delete in.

debug(1)

[COLOR="red"]current_tbl = table.current()[/COLOR]    'get pointer to current table.
[COLOR="red"]tblname = "selected_counties"[/COLOR]    'set the name of the table to be opened by RecordDeleted()
[COLOR="red"]vDeleteFields = "ContactID, CountID, County, State"[/COLOR]     'create the string of fieldnames to be passed to RecordDeleted
[COLOR="red"]vFieldValues = alltrim(current_tbl.contactid) +", "+ alltrim(current_tbl.countid) +", "+ alltrim(VAR->varPickedCity) +", "+ alltrim(cstate_abrevlookup(VAR->varTempState)) [/COLOR]     'create the string of values to be passed to RecordDeleted
[COLOR="red"]RecordDeleted(tblname, vDeleteFields, vFieldValues)[/COLOR]     'call RecordDeleted

Below is the function used in the above script. I'm getting an error saying, "Table in use by another table or user." when I try to pack the table using t.pack(). (See below). Do you guys have any idea why this might be?
This is kind of an ignorant question but, isn't A5 supposed to support muliple users? I've tried closing alpha, and/or deleting the offending table. I don't really understand this because I use the same script above/function below on the four other (identical) tables. The only difference in the tables is their names, and the name of one column in the table. i.e., one table has a column named city, another has county, etc... I'm confused!!!

FUNCTION RecordDeleted as L(tblname as C, vDeleteFields as C,vFieldValues as C)

[COLOR="Blue"]'tblname - is the "name" of the table to be opened.
vDeleteFields - is a comma seperated string containing the fieldnames of the table that I want to use in my filter expression.
vFieldValues - is a comman seperated string containing the values corresponding to the fieldnames that I want to use in my filter expression.[/COLOR]


[COLOR="Red"]DIM	vString as C [/COLOR]  	' modified version of vDeleteFields.  this is parsed to create query filter
[COLOR="red"]DIM vString2 as C[/COLOR]	' modified version of vFieldValues.	  this is parsed to create query filter in conjunction with vString
[COLOR="red"]DIM	vFilter as C  [/COLOR] 	' filter constructed from vDeleteFields.
[COLOR="red"]DIM	vRecs as N [/COLOR]    	' number of duplicate records found.
[COLOR="red"]DIM	vName as C [/COLOR]    	' the currently parsed field name.
[COLOR="red"]DIM  vName2 as C[/COLOR]     ' the currently parsed field value.
'DIM	vNameArray[20] as C	' fields to display. used when building the display string for duplicates.
[COLOR="red"]DIM	n as N [/COLOR]        	' counter
[COLOR="red"]DIM	m as N  [/COLOR]       	' counter

[COLOR="red"]CONSTANT CRLF = chr(13) +chr(10)
	
RecordDeleted = .f.[/COLOR]		

'	***** Generate filter *****
'	---------------------------
'	produce something like "First_Name='" +t.First_Name +"' .AND. Last_Name='" +a_field_value +"'"
'	which translates to "First_Name='abc'.AND.Last_Name='def'"
'	make sure delimeter is at beginning and end of string

[COLOR="red"]vString = ALLTRIM(vDeleteFields)
vString2 = ALLTRIM(vFieldValues)[/COLOR]

' Add a comma to the beginning of vString
[COLOR="red"]IF SUBSTR(vString,1,1)<>","
	vString = "," +vString
END IF[/COLOR]
' Add a comma to the beginning of vString2
[COLOR="red"]IF SUBSTR(vString2,1,1)<>","
	vString2 = "," +vString2
END IF[/COLOR]

' Add a comma to the end of vString
[COLOR="red"]IF SUBSTR(vString,LEN(vString),1)<>","
	vString = vString +","
END IF[/COLOR]
' Add a comma to the end of vString2
[COLOR="red"]IF SUBSTR(vString2,LEN(vString2),1)<>","
	vString2 = vString2 +","
END IF[/COLOR]

'	loop through string of field names to create filter
[COLOR="red"]vFilter = ""
FOR n=1 to 20
	vName = ALLTRIM(piece(vString, n, ","))
	vName2 = ALLTRIM(piece(vString2, n, ","))
	
	IF vName="" .or. vName2=""
		n = 20
	ELSE
		IF n>1
			vFilter = vFilter +".AND."
		END IF
		IF vName<>"".and. vName2<>""
			vFilter = vFilter +vName +"='" +vName2 +"'"
		END IF
	END IF
NEXT n

t = table.open(tblname,FILE_RW_SHARED)
t.delete_range(vFilter)
t.pack() [COLOR="Blue"]<--------- I'm getting an error here that says "Table is in use by another table or user." [/COLOR]
i = t.query_create()
vRecs = i.records_get()
t.query_detach()
t.close()

IF vRecs>0
	RecordDeleted = .t.   ' Dup found. Return true.
END IF[/COLOR]
'	***** Wrapup *****
'	------------------


[COLOR="red"]END FUNCTION[/COLOR]'	*****************************************************************************************
[COLOR="red"]FUNCTION piece as C(vString as C,n as N,vDelim as C)[/COLOR]
'	Return a value from vString, based on the Nth occurance of vDelim
'	if string is " a=~1~ b=~2~ c=~3~ ", specify n=5 and vDelim="~" to return "3"
'	if string is " a=~1~ b=~2~ c=~3~ ", specify n=3 and vDelim=" " to return "c=~3~"
'	don't use this with records such as field rules. that format requires different parsing.
'	based on a script by Peter Wayne
[COLOR="red"]DIM	vBeg as N[/COLOR]		' beginning position
[COLOR="red"]DIM	vEnd as N[/COLOR]		' ending position
[COLOR="red"]vBeg = ATC(vDelim, vString, n)
vEnd = ATC(vDelim, vString, n+1)
piece = SUBSTR(vString, vBeg+1, vEnd-vBeg-1)
END FUNCTION[/COLOR]

[Stan Mathews]

Re: DELETE_RANGE() in a multi-user environment? What should I use instead?

-Stan, where does rec come from? What's stored in it?

tbl.fetch_find(), per the documentation

If <TBL>.FETCH_FIND() is successful, the found record is retrieved and its Record_Number is returned. If no exact match is found, a negative number corresponding to the record number of the closest matching record is returned.

so the script stores the return value of the function rec variable and tests that against zero. If > 0, we found the record, delete it
if not, do nothing.

[Jeff@Listbrokers]

Re: DELETE_RANGE() in a multi-user environment? What should I use instead?

Stan,

tbl.index_primary_put("key tag name")
rec - tbl.fetch_find(key_value)  [COLOR="Red"]<--- Is that supposed to be [/COLOR]rec [COLOR="Red"]=[/COLOR] tbl.fetch_find(key_value)
if (rec > 0) then
tbl.change_begin()
    tbl.delete()
tbl.change_end(.t.)
end if

[/QUOTE]

[Stan Mathews]

Re: DELETE_RANGE() in a multi-user environment? What should I use instead?

Stan,

tbl.index_primary_put("key tag name")
rec - tbl.fetch_find(key_value)  [COLOR="Red"]<--- Is that supposed to be [/COLOR]rec [COLOR="Red"]=[/COLOR] tbl.fetch_find(key_value)
if (rec > 0) then
tbl.change_begin()
    tbl.delete()
tbl.change_end(.t.)
end if

[/QUOTE]

Yep, my eyesight isn't what it one was. And this darn keyboard has a mind of its own.

[Jeff@Listbrokers]

Re: DELETE_RANGE() in a multi-user environment? What should I use instead?

ahh! It all makes sense now. :) Thanks for your help.

[G Gabriel]

Re: DELETE_RANGE() in a multi-user environment? What should I use instead?

what would be the advantage of using a set in this instance?

A set uses a very little overhead.
With a set, all fields from all tables are available for data entry or delete.
Better, if you choose cascade delete/changes, you could with one stroke delete all these records.

[Jeff@Listbrokers]

Re: DELETE_RANGE() in a multi-user environment? What should I use instead?

I see your point. The only problem is that the tables all have compound keys. alpha doesn't support cascading deletes with using compound keys does it?