Need help duplicating invoice

[Ronald Anusiewicz]

I'm having a problem trying to duplicate an invoice. I found some code written by Dr. Wayne, but it doesn't work properly for me. I've tried changing it with my limited knowledge, but with no success.

The problem is that when the code copies the "Msquawk table" records, it copies them at the "Minv_hdr set" level instead of at the "Msquawk" table level.

What I'm trying to say is that if you look at the "Msquawk table" records that belong to that invoice number, there are 6 records. However, when viewing the "Msquawk table records at the "Minv_hdr" set level, there are 26 records.
I've tried changing the code to many different variants with no success. The following code copies only 6 records, but at the set level. Because the set level has 26 records, the first 6 records are all the same.

Does anyone have any suggestions as to how I can make it copy only the linked records at the "Msquawk" table level.

The goal is to copy the entire invoice, but for now, I'm just trying to copy the first child and I can't make it work.

'Duplicate the current invoice

dim hdr as p
dim squawk as p
dim old_hdr as b 'a blob variable to hold the old header record
dim count_squawks as n 'array of blobs to hold squawk Records; start with a size of 1 record
dim i as n

hdr = table.get("minv_hdr")
swk = table.get("msquawk")

'The folowing line counts the proper amount of records
count_squawks = count(msquawk->transno,GRP->minv_hdr)
if count_squawks > 0 then
dim squawkrecs[count_squawks] as b
i = 0
swk.fetch_first()
while i 0
parent.resynch()
end

[Tom Cone Jr]

RE: Need help duplicating invoice

Ron,

Your script mystifies me!

On its face it seems to read records from a table and then copy them (enter them) back to the same table. This gets complicated cause you're working with a set. It becomes more problematic when you use the count() function, which was designed for reports, not scripts or form layouts.

If you're trying to archive copies of records that were included in a particular invoice I suggest you rearrange things like this:

(pseudo code)
Get pointer to the source header table
Set index
Fetch_find desired record

Open the target_hdr table
begin new record there
Get the source record
Set the source record
end the new record
close target_hdr table
close source header table

Get pointer to the source items table
Run query to find all line items in the invoice
Open the target_items table
Use while...end while loop to copy
them over to the target_items table
(within the loop you'd begin new record,get the source record, set the source record, end the new record, fetch the next source items record)

close target_items table
close source items table

I believe you could do the same thing with the set,
in fewer steps, but doing it the long way may
not be a bad idea either. If you're inclined to
use the set, you'll need pointers to the primary
table, and to the child table. Once established you
can fetch through the child table records and will
hit eof when there are no more linked to the current
primary table.

-- tom

[Peter.Wayne]

RE: Need help duplicating invoice

you have to qualify the fetch_next() in your set to indicate the level of the fetch. i don't know if the fetch outline level will work with a child record in a set, but you can try it.
try swk.fetch_next(1)
to see if it fixes your problem.

otherwise you will have to recode the example, opening new pointers to the tables in the set.

[russ Boehle]

RE: Need help duplicating invoice

Hi Ron,
I think there's another way to approach this that might be easier. Try using the following with a filter on the invoice number. You will have to run it twice, once for the parent and once for the child. If you are just trying to create a copy in the current table then you can use the copy function in action scripting to do it.

Russ

.COPY_RECORDS_TO()

Syntax

Character Result = .COPY_RECORDS_TO( Table_Name as C [, Filter as C ] )

Argument
Description

Table_Name
The name of the table to receive the copied records.

Filter
Optional. Default = .T. (all records). If Filter is specified, only the records in the source table that satisfy the filter condition are copied. If Filter is not specified, the currently selected records in the source table are copied.


Description

The .COPY_RECORDS_TO() method copies records in the source table referenced by to the target table specified by Table_Name. Data is copied from each field in the source table to the corresponding field with the same name in the target table.

Result is a character string that indicates how many records were copied, and how many records could not be copied because they violated a field rule in the target table. For example, if Result is "230,3", it indicates that 230 records were copied and 3 records were not copied because of a field rule violation. The "violated" table in the Alpha Five program folder shows which records were not copied.

Table_Name can have more fields than the source table. However, at a minimum, it must have all of the fields in the source table, and they must be of the same type and size.

Example

This script copies current selection of records to "OLD_CUSTOMERS"

dim tbl as P



tbl_source = table.current()

query.filter = "state = 'ma'"

indx = tbl_source.query_create()

result = tbl_source.copy_records_to("OLD_CUSTOMERS")

ui_msg_box("Result", word(result, 1) + " Records copied. " + word(result, 2) + " Records not copied (see Violated table).")


This script copies customers from "MA" to "OLD_CUSTOMERS"

dim tbl as P



tbl_source = table.current()

result = tbl_source.copy_records_to("OLD_CUSTOMERS","state = 'ma'")

ui_msg_box("Result", word(result, 1) + " Records copied. " + word(result, 2) + " Records not copied (see Violated table).")


See Also

Table Functions and Methods, .COPY_RECORD_TO(), .COPY(), .RECORD_CLONE(), .MOVE_RECORDS_TO()

Supported By

Alpha Five Version 5 and Above

[Ronald Anusiewicz]

RE: Need help duplicating invoice

Thanks Russ,

I just copied your suggestion and I will study it.

What is happening is that the sometimes my client will generate a very large invoice. 300 or so items that need to be fixed with the child records containing a list of parts, shipping, misc., etc.

sometimes for insurance reasons or by customer request, they need to split the invoice in half or thirds. By duplicating the record, they then delete what they don't want.

Anyways, Dr. Wayne wrote the original code on his web site and it works very nicely. Unfortunately, I'm having trouble with the composite record. I'm probably in over my head, but I've been known to be very persistent.

Right now I'm studying pages 94 thru 100 in the Alpha Five Xbasic Reference Manual. I'm sure this can be done, I just haven't figured it out.

Ron

[russ Boehle]

RE: Need help duplicating invoice

I just copied it out of the docs. Part of it was lost in the translation so you might want to look at the chm on this but I think what you want is:
.COPY_RECORDS_TO()

if its only 1 record (for instance for the header) you would use:
.COPY_RECORD_TO()

I am kind of hoping that Selwyn will put this in action scripting in v6. I think it would simplify it and be useful.

Russ

[russ Boehle]

RE: Need help duplicating invoice

It still cut off the beginning. anyway its

tbl.COPY_RECORDS_TO()

Russ

""

[Richard Froncek]

RE: Need help duplicating invoice

Russ, [filter as C] is the problem. Help please in how the filter is specified.

filter = "effectived " frmdate .OR. effectived = frmdate"
result = tmarketwk.Copy_Records_To("zmarketwk","filter")

I have tried putting the filter expression directly in result as the filter parameter. It always returns a message that the field cannot be found. This is with or without the prefix tmarketwk, i.e., "tmarketwk.effectived " frmdate .OR. tmarketwk.effectived = frmdate"
All records are always copied. "===
Using a query, viz., qeffcvdate as the filter doesn't do it either.
The example always shows the parameter, but its simple "state = ma" is really no help. If the example works, why doesn't my expression as the parameter?
Thanks for any light that you might throw on this matter.
Dick

[russ Boehle]

RE: Need help duplicating invoice

Try the expression as:

filter = ""effectived" " frmdate .OR. "effectived" = frmdate"
result = tmarketwk.Copy_Records_To("zmarketwk","filter")

I am assuming that Frmdate is a field name??
I am not really sure of this, but maybe one of the xbasic whizzes (not me) will advise if this is correct. The other thing you can do is use the expression builder to build this.

Russ

[Richard Froncek]

RE: Need help duplicating invoice

Russ,
doesn't work at all with the double quotes around effectived; however even changing the double to single quotes doesn't work either.
The error message that appears relates to the copy_records_to, but it references a line far beyond what I have in my script. I'm guessing that it refers to the generated code.
Why there is no example for how the filter should be built is beyond me. Actually, the basic table ends up correct, i.e., marketwk only has the selected records but zmarketwk inherits 'em all.
One alternative of course is to show the expanded xbasic code, copy and paste. However, that results in quite a long script. I'd much prefer to get the copy of just the selected records.
Thanks for your input though. Testing all the variations, while frustrating, helps. One might just hit upon the correct syntax.
Dick

[russ Boehle]

RE: Need help duplicating invoice

If this was in action scripting it would sure make it easier. I often do things first in action scripting then convert to xbasic and modify from there since I'm not very good doing xbasic from scratch. I gather that you've tried that approach.

Russ

[Tom Cone Jr]

RE: Need help duplicating invoice

Richard,

Try:

"effectived >= {" + dtoc(frmdate) + "}"

You may find the discussion on filter expressions
in connection with TABLECOUNT() useful.

-- tom