Inserting records.

[johnG]

Aren't there days when you could murder a user?

One of my users has deleted records in the middle of a table, "I just thought I'd!" and the volume of work since the last complete back up is a lot.
The table forms a diary base ie one recocrd sequentially for each day.
The table displays in a browse within a form to show activity on that day.
Somehow 3 records have been deleted in the table say records 1120,1121, and 1122 or more particulalry 23/05/2010, 24/05/2010 and 25/05/2010.
Is it possible to insert records in the correct place. I appreciate the browse can be set up to show dates in order .
If I can reinsert blank records I can then manually enter dates etc.
I presume I could export to excel. add and sort records.. re-save an ordered file.. delete table contents and re-import but that seems a long winded way.
As ever any pointers welcome.

[Stan Mathews]

Re: Inserting records.

Why not undelete the records?

[johnG]

Re: Inserting records.

Stan thanks for the suggestion, I tried undelete in table utilities but for whatever reason it didn't work. Whether she nulled out the values first or whatever the records appear absent.
So the original question remains is there a simple way to insert records as per original query? I presume they cant be added to end of the table, table sorted on date-entry and saved in sorted order?

[Stan Mathews]

Re: Inserting records.

is there a simple way to insert records

Sorry, no. You could copy all records to a temporary table, add the missing record, empty the original table and copy them back in the desired order. Probably not any easier than using excel to do much the same.

It is probably also possible to add three blank records and create a script which moves the values in all fields 'downward' three records until the location was reached where you want to fill in the values. Tedious to construct and not worth the effort.

[Stan Mathews]

Re: Inserting records.

I think this will effectively insert the desired number of records starting at the desired record number. Give it a try with a test table. Insert your own table name.

rslt1 = val(ui_get_number("How many?","Enter the number of positions to move down.","1"))
IF rslt1 <1
	end
END IF
rslt2 = val(ui_get_number("Where?","Enter the record number of the starting record to move down.","1"))
IF rslt2 <1
	end
END IF

tbl = table.open("[COLOR="Red"]non_prov[/COLOR]")
FOR i = 1 TO rslt1
	tbl.enter_begin()
	tbl.enter_end(.t.)
NEXT i
tbl.order("recno()")
tbl.fetch_last()
last_rec = tbl.recno()
FOR r = (last_rec - rslt1) TO rslt2 step -1
	tbl.fetch_goto(r)
	record_data = tbl.record_data_get()
	tbl.fetch_goto(r+rslt1)
	tbl.change_begin()
	tbl.record_data_set(record_data)
	tbl.change_end(.t.)
NEXT r

FOR b = rslt2 TO rslt2+rslt1-1
	tbl.fetch_goto(b)
	tbl.change_begin()
	FOR t = 1 TO tbl.fields_get()
		fld = tbl.field_get(t)
		fld.blank_put()
	NEXT t
	tbl.change_end(.t.)
NEXT b
tbl.close()

[johnG]

Re: Inserting records.

Stan,
many thanks... just got to get something to eat.. then I'll give it a try.

[Stan Mathews]

Re: Inserting records.

Probably goes iwthout saying but

Backup First

[johnG]

Re: Inserting records.

Stan,
thanks for the back-up warning but I am already working of a backed up duplicate cony of the original. Belt and bracer!
OK ran the script... in large measure it works but rather than enter blank records it adds duplicates. Thats no problem as I can simply 'null' out the fields and save changes.
Again many thanks
John

[Stan Mathews]

Re: Inserting records.

You may have deleted records in your table that haven't been packed out. With a setting of 5 and 3 I get the results shown below.

[CALocklin]

Re: Inserting records.

Aren't there days when you could murder a user?

One of my users has deleted records in the middle of a table, "I just thought I'd!"....

Just had to say....

And people sometimes make fun of me for putting so many restrictions on what the user can do. This is especially true for deleting records. Even when I do allow deletion, I sometimes prompt 3 times before actually allowing a record in a critical table to finally be deleted. They may kid me about all the prompts but they almost never delete stuff that shouldn't be deleted. And, once I explain why I do it, nobody has ever asked me to reduce the number of prompts.

I also try to include at least one message that specifies what they are deleting. A question like, "Are you sure you want to delete the Starter Motor from this assembly?" is much better than a simple, "Are you sure you want to delete this record." I've seen this happen more than once - someone accidentally selects the wrong record to delete and doesn't realize it until too late.