issue moving records

[DVSOL005]

I have created attached script "TST01" that should move selected records to an archive table.
In a first test I commented out the line "inv.move_record_to.....".
The result is displayed in the Trace Window on the first line of the other attachment, (Selected 14 out of 43 records). Those are the correct figures.
In a next test run I have included the line "inv.move_record_to.....".
The result is displayed in the second line of the trace window.
Selected 8 out of 35 records! I can see that 8+35 = the total amount of 43 records.
I see a couple of issues;
1. not all eligeable records are selected to be moved.
I can imagine this has to do with a change in relative position because records are moved.
2. The counter "hvh" is incremented for each record so that should give the amount of 43 records after the first run. It looks like records are skipped.

As can be seen in the trace window, after a couple of runs all records are processed.

Anybody have an idea how to solve this problem?

Thanks,

[Stan Mathews]

Re: issue moving records

The issue is probably with your placement of inv.fetch_next(). If you do move a record the table pointer no longer remains on the record "spot" of the record that was moved, it has to move to the next record - much like deleting a record. Then when the script executes inv.fetch_next() you have skipped the record that followed the moved record.

Maybe

if lmt >31 then
	sel=sel + 1
	inv.move_record_to("hist_afspraken")
      else
        inv.fetch_next()
end if

and eliminate the later instance of that line.

[MoGrace]

Re: issue moving records

Why not mark the records to move as you fetch thru the queried table, move the marked records, verify they exist in the archive table THEN remove them? An unmark operation on the table before you apply the query will insure that no other records get selected.

[DVSOL005]

Re: issue moving records

Stan,

Your suggested change didn't work. I think the script came in a loop and A5 had to be restarted. Tried it a couple of times.

Now I am working on a query approach which might be a better approach. I have tested a filter and that seems to work as required.
query.filter="Inv_Done=" + S_QUOTE(TF)+ " .and. Sched_date < " + s_quote(ct)
With this filter the records are selected and instead of fetching through those records I am considering the use of a for next loop to process the command inv.move_record_to("hist_afspraken") n-times.

Once it works I will show the script.

Robin,

Thanks for your suggestions, it helped me on the way to use a query.

Thanks,

[Stan Mathews]

Re: issue moving records

With this filter the records are selected and instead of fetching through those records I am considering the use of a for next loop to process the command [/COLOR]inv.move_record_to("hist_afspraken") n-times.

I would suggest

<TBL>.MOVE_RECORDS_TO( Table_Name as C [, Filter as C ] )

Instead of looping just use your query filter

[DVSOL005]

Re: issue moving records

Stan,

I've got it working now.
This is the script:
dim inv as P
dim TF as L = .T.
dim DT as N = 31
dim CT as D = Date()-dt
inv = table.open("afspraken")
result =inv.move_records_to("hist_afspraken","Inv_Done=" + S_QUOTE(TF)+ " .and. Sched_date < " + s_quote(ct))
ui_msg_box("Result", word(result, 1) + " Records moved. " + word(result, 2) + " Records not moved (see Violated table).")
inv.close()
end

Thanks for you support.

[MoGrace]

Re: issue moving records

Sometimes I don't read well :) My comment may have been worthless but I learned about a new function nonetheless!

[DVSOL005]

Re: issue moving records

Robin,

The fact that you gave the issue some attention, like Stan did, is more important than being right or wrong in approach or comments. In the end it has contributed towards a solution that I have now found. After all, I mentioned before that your comment triggered me towards a query-solution.

Thanks,

[MoGrace]

Re: issue moving records

Thanks Jo, if I had seen that function in the script I would not have commented. It was back in v5 that I tried archiving records - and that function wasn't around then. But now that you have posted your solution, we have a great place to find this answer again - which is the beauty of this forum!