0

How to create an invoice for a collection of orders?

Hi all,

 

I really need some help on this.

I have a table ORDERS. Each order has 1 customer and multiple items (in the child table ITEMS).

Then I have a table SHIPMENTS, containing multiple orders for multiple clients, so linked to the ORDERS table.

 

Upon actually shipping out, I need Ninox to create one invoice PER customer in the SHIPMENTS table, and the items per order to be copied to an items per invoice table and add up the amounts.

 

I have tried a coding a button resembling something like this:

 

let xCurrRec := Id;
let xCustomer := unique(ORDERS.CUSTOMER);
for i in select ORDERS where SHIPMENTS = xCurrRec and CUSTOMER = xCustomer do
let j := (create INVOICES);
j.SHIPMENTS := xCurrRec;
j.CUSTOMER := xCustomer;

end

Ninox doesn't accept this because xCustomer returns multiple variables.

Furthermore I would need to have a second loop in the first loop to copy over and add up the items and their amounts.

 

Any suggestions how to code this?

 

Thank you!

12 replies

null
    • John_Halls
    • 2 yrs ago
    • Reported - view

    If your data model looks like this

     

    Data Model

     

    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

     

    Script

     

    Regards

     

    John

    • Fred
    • 2 yrs ago
    • Reported - view

    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.

    • Fred
    • 2 yrs ago
    • Reported - view

    As always, John is much more elegant.

    • Martijn_Burghoorn
    • 2 yrs ago
    • Reported - view

    Thank you for your quick help, gents!

     

    But I am afraid I did not explain clearly enough...

    Both solutions do not work as they assume one single customer in a shipment.

     

    However, what I have constructed so far is:

     

    ORDERS TABLE

    - has a customer selected

    - has a child table ITEMS

     

    SHIPMENTS TABLE

    - has multiple orders linked to it (I created a SHIPPING LIST subtable in SHIPMENTS, containing one field (reference to ORDERS). Upon finalizing SHIPPING LIST - via button click - the ORDERS selected in this subtable are linked to the SHIPMENT (as per ORDERS.SHIPMENTS := SHIPMENTS.id). Records in the SHIPPING LIST table are then deleted.

    Then these ORDERS are shown in a view in the SHIPMENTS table.

    - I thus have a SHIPMENT record, with multiple orders for multiple customers linked to it. I need to 'batch create' the invoices as it will be too much work to create them one by one or manually.

    So the following is the case:

    SHIPMENT 1 has:

    - ORDER x for CUSTOMER a, containing ITEMS 1, 2 and 3

    - ORDER y for CUSTOMER b, containing ITEMS 1, 4 and 5

    - ORDER z for CUSTOMER a, container ITEMS 2, 3 and 4

    i now need to create:

    - CONSOLIDATED INVOICE 1 to CUSTOMER a for items 1, 2(2x), 3(2x) and 4

    - CONSOLIDATED INVOICE 2 to CUSTOMER b for items 1, 4 and 5.

     

    Any new ideas on this?

     

    Thank you very much again!

    However it doesn't work to create a variable xCustomer for CUSTOMERS as it returns multiple variables...

    • John_Halls
    • 2 yrs ago
    • Reported - view

    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.

     

    Screenshot 2021-05-15 at 17.11.44

    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

    • John_Halls
    • 2 yrs ago
    • Reported - view

    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

     

    Screenshot 2021-05-15 at 17.22.56

     

    Regards

     

    John

    • John_Halls
    • 2 yrs ago
    • Reported - view

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

     

    Screenshot 2021-05-15 at 17.27.22

    • Fred
    • 2 yrs ago
    • Reported - view

    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.

    • Martijn_Burghoorn
    • 2 yrs ago
    • Reported - view

    Guys, this is amazing! I have used John's code and it works!

    Thank you so much, as I would have never figured this one out by myself!

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Our pleasure Martijn

    • Generation D Ltd
    • Quentin_Brown
    • 2 yrs ago
    • Reported - view

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

    • Generation D Ltd
    • Quentin_Brown
    • 2 yrs ago
    • Reported - view

    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

Content aside

  • 2 yrs agoLast active
  • 12Replies
  • 1160Views
  • 1 Following