Re-Creating invoices from the Line table

Sam said:
I would appreciate some suggestions on how I could re-create invoices from a table of lines.

 are you talking about importing data from a file?

Here is the code for a new button that goes through each record in Line and either creates a new Invoice record and links the two records or links to an already existing record.

for loop1 in select Line do
    let loopInvoice := (select Invoice where 'Invoice No' = loop1.'Invoice Number');
    if count(loopInvoice) = 0 then
        let newRec := (create Invoice);
        newRec.('Invoice No' := loop1.'Invoice Number');
        loop1.(Invoice := newRec)
    else
        loop1.(Invoice := first(loopInvoice))
    end
end

Line 1: starts our for loop by selecting all records from the Line table

Line 2: we move this line inside the loop so we can find any invoices that match the Invoice Number of the record in the loop we are on.

Line 3 - 9: is like the single record except we have to add references to the loop so we can get the proper data.

Sam said:
I've tried to adapt the code you last provided to result in having a separate invoice for each customer within the current Appointment record. The first invoice is correct with the code I have adapted from yours. But, the subsequent invoices are not produced. Instead, the Lines from the current record are added to the first invoices (per customer).

I tried the 'Post Invoices' button in the Appointment table for the record Id of 31 (Black Beauty). There are three owners and two items (halter (2), and wound spray (1)). The button created three invoices 1 for each owner and add the two items to each invoice. It also copied over the correct ownership % and the invoice total for all three equal that of the real total cost of the items.

It seems like it is working. Am I missing something?

You can also streamline your code a bit:

do as server
    if Posted = false then
        Posted := true;
        let a := this;
        for b in unique(ApptPet.Pet.Owner) do
            let c := (create Invoice);
            c.(Customer := b.Customer);
            c.(APPOINTMENT := a);
            for d in ApptPet.Pet do
                for f in a.ApptLine do
                    (create Line).(
                        Invoice := c;
                        Item := f.Item;
                        Pet := d;
                        Customer := b.Customer;
                        'Ownership %' := b.'Ownership %';
                        Quantity := f.Quantity
                    )
                end
            end
        end
    end
end

I modified line 5 to keep it at the Owner level, since you did a select of Owner again when you didn't need to. Best to keep away from selects whenever possible.

Then I had to modify line 7 to point to the customer that is linked to the owner.

Then I removed this line:

for e in (select Owner)[Customer = b and Pet = d] do

Since we already have the owner info in line 5. Also removed the corresponding end. Also modified line 15 to fix the link to Customer and line 16 to fix the reference to the proper owner record.

Sam said:
You will also see that there is no longer an appointment table. That is why I am hoping to RE-Create the invoices.  Each appointment ID should have the corresponding number of invoices = to the number of Owners/Customers. My count is 4 groups of Appt ID's consisting of 9 invoices for the 4 ID Invoice groups.

Just to make sure I understand what you want.

Your Invoice.Item table has data for ApptID but no data for Invoice Number.

Do you want to recreate Invoices or Appointments? or both?

Having an Invoice Number is not important as we can just create new numbers, but I'm guessing you have that data already.

How do you want to use ApptID in Invoices? There isn't a field called ApptID in Invoices.

Do you want to copy ApptID in Lines to Invoice Number in Invoices?

or

Do you want to create a new field called ApptID and then copy that data over?

Once you get this figured out then you can just modify my original code to use ApptID instead of Invoice Number.

Sam said:
I did the substitution and it resulted in One invoice for the Appointment with all the lines from both owners. Instead of 2 invoices with One owners per invoice.

Oops, sorry. Didn't think it through completely. Try this for your button:

for loop1 in select Line do
    let loopInvoice := (select Invoice where Customer = loop1.Customer and ApptID = loop1.'Appt ID');
    if count(loopInvoice) = 0 then
        let newRec := (create Invoice);
        newRec.(
            ApptID := loop1.'Appt ID';
            Customer := loop1.Customer
        );
        loop1.(Invoice := newRec)
    else
        loop1.(Invoice := first(loopInvoice))
    end
end

Just check that the field in Invoice is called ApptID. If not then change it in lines 2 and 6.