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
-
wish there was a way to edit...
should be--- has retained all of the original fields
-
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 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.
-
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.
-
Sam said:
I'm guessing this will be a one time operation? not positiveWell 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.
-
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.
Content aside
- Status Answered
- 1 yr agoLast active
- 20Replies
- 158Views
-
2
Following