Deleting All Invoice Records

[[email protected]]

Hi,

I'm using a routine to delete all current Invoice records, but it is deleting all records in the invoice items files: Here's the code, as I'm sure I'm doing something stupid:

'Date Created: 14-Sep-2009 09:49:00 PM
'Last Updated: 14-Sep-2009 09:57:25 PM
'Created By : TRACS
'Updated By : TRACS
dim my_id as c

dim tbl as p
tbl = table.current()
my_id = tbl.invoice_number 'get current invoice number

tbl=table.open("invoice_items")
tbl.index_primary_put("invoice_number") ' assumes index
tbl.fetch_find(my_id) ' go to first of line items

while tbl.invoice_number = my_id .AND. .not. tbl.fetch_eof()
tbl.change_begin()
tbl.delete()
tbl.change_end(.t.)
tbl.fetch_next()
end while

tbl.close()
topparent.resynch()

Thanks Ryland

[Stan Mathews]

Re: Deleting All Invoice Records

You have a table pointer reuse issue. Better practice is

[COLOR="Blue"]parentform.commit() 'make sure we are not in change or enter mode[/COLOR]
dim my_id as c

dim tbl as p 
tbl = table.current()
my_id = tbl.invoice_number 'get current invoice number

[COLOR="Red"]itms[/COLOR]tbl=table.open("invoice_items") 
[COLOR="red"]itms[/COLOR]tbl.index_primary_put("invoice_number") ' assumes index
[COLOR="red"]itms[/COLOR]tbl.fetch_find(my_id) ' go to first of line items

while [COLOR="red"]itms[/COLOR]tbl.invoice_number = my_id .AND. .not. [COLOR="red"]itms[/COLOR]tbl.fetch_eof()
[COLOR="red"]itms[/COLOR]tbl.change_begin() 
[COLOR="red"]itms[/COLOR]tbl.delete() 
[COLOR="red"]itms[/COLOR]tbl.change_end(.t.) 
[COLOR="red"]itms[/COLOR]tbl.fetch_next()
end while

itmstbl.close() 
topparent.resynch()

[[email protected]]

Re: Deleting All Invoice Records

Thanks Stan,

It appears that the routine is deleting the header as well. Actually, I want to retain the Invoice header as an audir train, but just remove the "invoice Items" themselves. The routine alos seems only to delet the last line item, and not all of them.

Ryland

[Stan Mathews]

Re: Deleting All Invoice Records

It appears that the routine is deleting the header as well.

The suggested change shouldn't delete any records in the header table so long as the header table is not invoice_items.

To ensure that all line items are processed we should probably go to the top of the table first.

parentform.commit() 'make sure we are not in change or enter mode
dim my_id as c

dim tbl as p 
tbl = table.current()
my_id = tbl.invoice_number 'get current invoice number

itmstbl=table.open("invoice_items") 
itmstbl.index_primary_put("invoice_number") ' assumes index
[COLOR="Red"]itmstbl.fetch_first()[/COLOR]
itmstbl.fetch_find(my_id) 
while itmstbl.invoice_number = my_id .AND. .not. itmstbl.fetch_eof()
itmstbl.change_begin() 
itmstbl.delete() 
itmstbl.change_end(.t.) 
itmstbl.fetch_next()
end while

itmstbl.close() 
topparent.resynch()

[Tim Kiebert]

Re: Deleting All Invoice Records

When deleting records in a loop you don't need to use itmstbl.fetch_next().
If you had enough records to see, you would probably notice that every other record is being deleted. When the active record gets deleted the table pointer is moved to the next record.

[[email protected]]

Re: Deleting All Invoice Records

Hi Stan / Tim

I copied the code into an inline basic script, but it will only delete one record when run, then no others. I'm not in enter mode, and the Header record is intact which is good. I also commented out the fetch next, but that did not work either. Could I be missing something else ??

Thanks,

Ryland

[Tom Cone Jr]

Re: Deleting All Invoice Records

Yes. Time to show us your revised script. :)

[[email protected]]

Re: Deleting All Invoice Records

OK will zip some files up and send to ya'll. On the train commuting and keep losing signal, Thanks

[Tim Kiebert]

Re: Deleting All Invoice Records

The index you are using wouldn't by any chance be set to 'unique only' instead of 'all' would it.

Failing that we may need to see a sample.

[Tim Kiebert]

Re: Deleting All Invoice Records

Failing that we may need to see a sample.

Woops. A bit slow I see. :)

[[email protected]]

Re: Deleting All Invoice Records

Hi,

Attached is the zip file. Go to Forms in the control panel, and select the TRACS Management. From there, select the Tool Issue, enter 123456 as the login and hit enter .

A simular screen appears like AlphaSports. You will see a Delete Button in the middle of the screen that I'm testing with for the Delete all line items. There should be plenty of stock in the dropdown for the product numbers.

[Stan Mathews]

Re: Deleting All Invoice Records

Your app is misbehaving under V8 and I haven't installed v9 yet. Can't close/save the invoice_issue_new form without Alpha closing and corrupting the form.

Someone else will help, I'm sure.

[[email protected]]

Re: Deleting All Invoice Records

Thanks Stan,

Sometimes I just add the system menu to the properties so I can individually close windows that I'm working on. I this way it should not kick you out of the application,,,Ryland

[Tom Cone Jr]

Re: Deleting All Invoice Records

Ryland, your instructions are incomplete and lead to error messages. Can't log in using vers 9.

[[email protected]]

Re: Deleting All Invoice Records

Humm, I zipped the file, but what I did was disable the Autoexec that ran the Main Menu.

From the form tab, If you run the Main Menu, that is where all the variables also get set. So from the main menu:

1 - enter the scan id "rsw123" and hit enter.
2 - that will bring up the second menu "TRACS Management"
3 - select the "tool issue" and use "123456" and hit enter, and that should take you into the tool issue screen.

The only errors that should be there in the version 9 is just the picture files. Other than that, then I'll have to cut a full release. Sorry that ya'll are having probllem.Ryland