How to create an invoice for a collection of orders?

If your data model looks like this

Then each shipment is related on one customer and to many orders. this code in a button on the shipment will consolodate the order lines into a single invoice

Regards

John

Just to double check. You want a button that goes through your Shipments table and creates a record in your Invoice table? Do you need to do this everytime you push the button? Or do you only need to create new invoices when you push the button?

Have you tried making a button that just creates an invoice of the current Shipments record you are on just to validate that you can create a record and link the two tables (Shipments and Customer)? Something like:

let curRec := this;
let newInvoice := (create Invoices);
newInvoice.(Shipments := curRec);
newInvoice.(Customers := curRec.Orders.Customers)

If you have, then some thoughts on your code:

1) wouldn't you want to do a select in Shipments not Orders as you on your Shipments table and need to create an Invoice?

2) if you in Shipments then you don't need to do: let xCustomer := unique(ORDERS.CUSTOMER) since Shipments already, if I read your comemnts correctly, has a link to Customers and you captured all this info in your first line: let xcurRec := Id.

So re-written:

let xCurrRec := Id;
for i in select Shipments where CUSTOMER = xCurrRec.Customer do
let j := (create INVOICES);
j.SHIPMENTS := xCurrRec;
j.CUSTOMER := xCurrRec.Customer;
end

This will create a record in Invoices for every shipment for the customer that is on the current record in Shipment you are viewing.

If you want to create a record in Invoices for every record in Shipment then you would write it as:

for i in select Shipments do
let j := (create INVOICES);
j.SHIPMENTS := i.SHIPMENTS;
j.CUSTOMER := i.CUSTOMER;
end

If you only want new invoices then you will need to filter based on a field in Shipment.

Once you solve this then your Invoice Item table can be solved by either copying all the data from Orders Items to Invoice Items or you can use formula fields to gather the data from the Orders table based on the relationships between Invoices > Shipments > Orders.

As always, John is much more elegant.

Hi Martijn

Using the same data structure I showed this updated code works (I have run it so I know it works). It creates an invoice per customer in the shipment record and consolidates the order lines, summing the quantity. I don't think you need a SHIPPING LIST table as well to achieve what you want.

If you want me to explain how it works, let me know. I think you can adapt it to your workflow as needed.

Regards

John

Sorry, I made a mistake. I started writing it to also cosolidate all Shipment records and left a select statement in that wasn't neededTo work per Shipment record it should be

Regards

John

... and again. All select statements removed now and tested with maore than one Shipment record!

Damn, John beat me again.

Just as I figured out a way to create an invoice for each customer of the current shipment in a button:

let curRec := this;
let xCust := Customers;
let arrCount := count(xCust);
for loop2 in range(0, arrCount) do
let newInvoice := (create Invoices);
newInvoice.(Shipments := curRec);
newInvoice.(Customers := item(xCust, loop2))
end

Line 1: The change here is I created a formula field (Customers, yea not the best name as it can be confused with the table name) in shipment that created an array of the all the customers on the current shipment.

Line 2: Put the formula field into a variable

Line 3: Created an array that counted up how many customers are in the shipment (or more accurately the field).

Line 4: Started my loop and did a range to tell it how many times to loop based on the number that was counted in arrCount variable

Line 5 and 6: standard create record and linking to Shipment record.

Line 7: takes the number of loop Ninox is on and pulls the corresponding Customer record and links it to the Invoice.

Anyways, hope this all helps.

Our pleasure Martijn

In searching for answers to a similar kind of problem I came across this thread and was able to adapt the code examples given to my use case.

Coming from some limited experience in other database systems and programming environemnts/languages I would tend to follow the logic of Fred's code more naturally, since it seems to break things down into smaller logical blocks of the kind that I am used to from these other systems. It seems less Ninoxian and more generic, if that makes sense?

John's code looks much more Ninox specific though, with lots of nested functions and I want to learn more. I was able to adapt it though I don't fully understand the syntax and functions being used and it would be really helpful to have a bit of a breakdown of it, especially now that I am trying to expand what I have working so far and hitting brick walls...

Sorry John and Fred. 

That post was meant to start with thanking you but I missed that bit out of the cut and paste when I was forced to log back in to post my message!

Thank you so much for posting your code, it has helped me get over some big hurdles.

Now I'm stuck at the next one I wonder if a better understanfing of the functions/syntax and logic used here would help me tackle it..

Best. Q