0

Regarding the issue of matching records after splitting the data.

test.ninox
  • I have created three tables: "product," "invoice," and "Create." In the "Create" table, I have set up a button. I want to quickly create orders by clicking the button.
  • For example, after clicking the button, it will create "cnt(select 'Create')" new invoice records, and in each "invoice" record, it will match the product record with the "product.id" corresponding to the number in "product nb." How can I achieve this functionality? Thank you for your response!

Some try.

let aa := this;
for i in select 'Create' do
    for xx from 0 to cnt(i) do
        let ywy := (select 'product')[i.'product nb' = 'Id'];
        let newdd := (create invoice);
        newdd.(
.....

4 replies

null
    • Fred
    • 4 mths ago
    • Reported - view

    1) How does the number of records in Create affect the number to create of Invoices? What if you have 5 records in Create then want to create 3 Invoices then want to create 10 Invoices? Do you have to delete 2 records then create  7 records ?

    It seems like you should have a number field "How many invoices do you need?" then use that.

    2) I would suggest using a dynamic multi choice field to Products, so you already have the correct record Id to use to make the links.

    3) are you making an invoice for each individual product or do you have a Line Item child table that you also need to populate?

      • gold_cat
      • 4 mths ago
      • Reported - view

       

      The records in the Create table are actually temporary. In the future, clicking a button will delete all records in the Create table. Therefore, the number of records in the Create table determines the number of new records to be created in the Invoices table.

      Fred, there is a reason for this design. Since there are many products, for frequently used ones, I only need to remember the "Id" number to quickly enter orders. I have drawn a diagram to help clarify my point.

      I know that using split(product nb, ",") can split the serial numbers within product nb, but I don't know how to match them to the products in the invoice after splitting. This is where the problem lies.

      • Fred
      • 4 mths ago
      • Reported - view

      That is a very helpful picture.

      I'm guessing the Product table has a record for each product you sell.

      That means your picture shows a 1:N (1 to many) relationship between Invoice and Product. So once you create the link between Product 5 and Invoice 1 then you can't create a link between Product 5 and Invoice 2.

      The solution is create a fourth table with a N:N (many to many) relationship between Invoice and Product. We can call this new table invoiceitems and put a relationship field to invoice (with Composition set to Yes) and product.

      Now you can link as many different invoice records to as many different product records as you want.

      So your code could look something like:

      for invoiceLoop in select Create do
    let productArray := split(invoiceLoop.'product nb', ",");
    let newInvoice := (create invoice);
    for productLoop in productArray do
        let getProductRec := first(select product where Id = number(productLoop));
        let newLineItem := (create lineitems);
        newLineItem.(
            invoice := newInvoice;
            product := getProductRec
        )
    end
end

      If you are creating parent and child records through a script then you need to start with the parent record first.

      Line 1, we start with a loop that will create an invoice for each record in Create.

      Line 2, we create a variable that takes the string of product IDs and turn it into an array. We can reference the loop variable to get the string.

      Line 3, we create a variable to store the new invoice record that is created.

      Line 4 is the start of the loop through the array from line 2.

      Line 5, have to find get the proper record Id of the product we want to link.

      Line 6, we create a variable to store the new line item record that is created.

      Lines 7 - 9, we now modify the newly created line item record and can link it to invoice by referencing the newInvoice variable and the product we found with the getProductRec variable.

      You can also wrap the entire code with

      do as server
    code
end

      and it will run faster.

      • gold_cat
      • 4 mths ago
      • Reported - view

       

      Thank you so much for your help! I also need to learn and understand for, in, and other loop statements.

Login to reply

Content aside

  • Status Answered
  • 4 mths agoLast active
  • 4Replies
  • 44Views
  • 2 Following