0

Re-Creating invoices from the Line table

Hi All,

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

Preferably creating one invoice at a time from the Line table. A batch will work if one at a time is not possible.

The Line table has retained all of the original lines.

The appointment ID

Date

Customer

Patient

Item

Qty

Price

Thank for looking!

20 replies

null
    • Sam.1
    • 1 yr ago
    • Reported - view

    wish there was a way to edit... 

    should be--- has retained all of the original fields

    • Fred
    • 1 yr ago
    • Reported - view
    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?

      • Sam.1
      • 1 yr ago
      • Reported - view

      Fred No.  The child portion of the Invoice/Line(Line being the subtable) relationship is what I have. The Ninox Invoice template  names their Parent/Child relationship -- Invoice/Invoice Item. I called my child subtable Line. I need a new parent Table to go with the sole child table. Being that the fields are all present in the child table I was hoping there is a way to work backwards.

    • Fred
    • 1 yr ago
    • Reported - view
    Sam said:
    I need a new parent Table to go with the sole child table. Being that the fields are all present in the child table I was hoping there is a way to work backwards.

     Thanks more making things clearer. Now we need to know:

    What is the name of the new Parent table?

    what field in your Line table will be used to link to the new Parent table?

    I'm guessing this will be a one time operation?

    If you can, upload a sample DB so we can see the structure.

    • Sam.1
    • 1 yr ago
    • Reported - view

    Hi Fred, 

    Would have responded sooner if possible. Here is the things you asked for:

    What is the name of the new Parent table?        Invoice

    what field in your Line table will be used to link to the new Parent table?  Not sure. I guess Invoice No

    I'm guessing this will be a one time operation? not positive

    If you can, upload a sample DB so we can see the structure. First try at it here.

    • Fred
    • 1 yr ago
    • Reported - view
    Sam said:
    I'm guessing this will be a one time operation? not positive

     Well I hope when you get the new Invoice table setup that you will be adding records in Line through the Invoice table and not directly in the Line table.

    Thanks for the sample DB. It makes so much faster to figure things out.

    Try this in a button in the Line table:

    let t := this;
    let findInvoice := (select Invoice where 'Invoice No' = t.'Invoice Number');
    if count(findInvoice) = 0 then
        let newRec := (create Invoice);
        newRec.('Invoice No' := t.'Invoice Number');
        Invoice := newRec
    else
        Invoice := first(findInvoice)
    end

    Line 1: get the current record you are on

    Line 2: gathers all records in the Invoice table where the Invoice No matches the Invoice Number.

    Lines 3 - 9: checks to see if the count of invoices found in line 2 is 0 then we we need to create a new record in Invoice (line 4) then copy the data from Line.Invoice Number to Invoice.Invoice No. then we link the records (line 5) by pointing the Invoice reference field in Lines to the newly created record.

    If count is not 0 then we can just link the Invoice reference field to the first record in our search from line 2. We have to use a first() (or last()) command here because Ninox always assumes a select() command will return multiple records so it wants to know how it should process it.

    I'll post how to modify it so go through all of the records in the Line table in another post.

    • Fred
    • 1 yr ago
    • Reported - view

    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.1
      • 1 yr ago
      • Reported - view

      Fred thanks for the speedy response Fred! I’ll let you know how it works on my end.

      I really appreciate your time and effort!!

      cheers

      • Sam.1
      • 1 yr ago
      • Reported - view

      Fred thanks again Fred! I’ll give it go and update you.

      • Sam.1
      • 1 yr ago
      • Reported - view

      Fred Hi Fred,

      I was able to see my shortcoming in providing all of the info and references needed for you.

      Attached will be found a revised sample database with the added references-- customer and Appt ID.

      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).

      You will see the code I have adapted in the sample database.

      Thanks again for looking!!

      Sam

    • Fred
    • 1 yr ago
    • Reported - view
    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.1
      • 1 yr ago
      • Reported - view

      Fred 

      Hi Fred,
      It is working as described and thanks for the streamlined code. However, in this sample I'm sure you'll see better than an explanation. You will see there are 4 appointment ID's in the ID column.
      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.
      Appt 31- 3 owners
      Appt 32- 2 owners
      Appt 33- 2 owners
      Appt 34- 2 owners

      • Sam.1
      • 1 yr ago
      • Reported - view

      Sam oops, got messed up with the naming. Here is the one without the Appointment table.

    • Fred
    • 1 yr ago
    • Reported - view
    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.

      • Sam.1
      • 1 yr ago
      • Reported - view

      Fred yes, you are correct. And, only the recreation of the invoices.

      Thank you for your patience!

      Sam

    • Fred
    • 1 yr ago
    • Reported - view

    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.1
      • 1 yr ago
      • Reported - view

      Fred yes,for---- Do you want to create a new field called ApptID and then copy that data over?

      I deleted the formula field and added the Appt ID data field to Invoices.

      I will now modify as suggested and let you know.

      cheers

      • Sam.1
      • 1 yr ago
      • Reported - view

      Sam 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.

      Attached is a revised sample with all the blank records present. I had the button in the Appointment form.

    • Fred
    • 1 yr ago
    • Reported - view
    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.

      • Sam.1
      • 1 yr ago
      • Reported - view

      Fred PERFECT!! 

      You're the best Fred! Thank You so much for all your help and patience.

      Sam

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 20Replies
  • 157Views
  • 2 Following