0

Parsing records from 1 table to another

Dear Ninox community,

Let’s say you have the following 10 records in the following order (contained in Table “List of Items”):
Art1
3
Art2
4
Art3
8
Art4
10
Art2
4

The uneven records are article numbers and the even records contain the quantities for each item.

The article numbers belong to the table ARTICLE NUMBERS.

The quantities belong to a ("help") table called POSSIBLE QUANTITIES.

Do any of you Ninox wizards - and those still learning off course - if it’s possible to convert such a Table into another Table called “Items and Quantities”.

This table would look like this:
ARTICLE NUMBER - QUANTITY
Art 1 - 3
Art 2 - 4
Art 3 - 8
Art 4 - 10
Art 2 - 4

I am also open to other conceptual solutions that would achieve something similar...

Also, would it be easier if a already upload a DB containing all info?

Thanks a lot and I am very curious already,
Bas

9 replies

null
    • Bas
    • 4 yrs ago
    • Reported - view

    Dear All,

     

    Please find attached a database that contains above information.  I hope that helps.

     

    Thanks again,

    Bas

    • Bas
    • 4 yrs ago
    • Reported - view

    PS Here's a link: https://we.tl/t-dd9ycraYib

    PPS What is the best way to share files on this forum?

    • Bas
    • 4 yrs ago
    • Reported - view

    Any ideas?

    Thanks,

    Bas

    • Sean
    • 4 yrs ago
    • Reported - view

    I don't follow why you use a separate record for Article Number and Quantities. Why not create a formula field instead and concatenate the the fields?

    • Bas
    • 4 yrs ago
    • Reported - view

    Hi Sean,

     

    Thanks for thinking a long.

     

    The idea is that the operator would use a barcode scanner to scan products and also scan their associated quantities. I think you could gain quite a lot of time by scanning both. After the initial scanning, you would convert the "raw data" to make it useable downstream so to speak.

     

    Does that make sense to you?

    -Bas

    • Sean
    • 4 yrs ago
    • Reported - view

    Hi Bas,

     

    Yes, it makes more sense now. In a perfect world you could use a "Trigger on create" to create a new record in "Items and Quantities" and concatenate the fields in the new record, but I imagine you would have to deal with the occasional mis-scan or double scan. Once you verify the data is correct, you could use code in a button to loop through the records and create new records in "Items and Quantities" and copy/concatenate the fields.

     

    Sean

    • Bas
    • 4 yrs ago
    • Reported - view

    Hi Sean,

    Just to make sure I am following you on a conceptual level, I created this very elimentary draft...

    Draft

     

    You would then set up a Trigger after update for the field "Raw Input"

    that would contain code that would do this:

     

    let Array_Of_All_Article_Numbers := '2) Articles'

    let Current_Item := 'Raw Input'

    if contains(Array_Of_All_Article_Numbers,Current_Item)

    then
    'Type of Input'=1 of Current_Item

    end

     

    Is this indeed going in the right direction? If so; I will try to find the correct formula's because for now I am still an expert at compiling errors; haha!!

     

    -Bas

    • Bas
    • 4 yrs ago
    • Reported - view

    Hi Sean,

    Just to make sure I am following you on a conceptual level, I created this very elimentary draft...

    Screenshot 2019-09-14 at 19.52.25

     

    You would then set up a Trigger after update for the field "Raw Input"

    that would contain code that would do this:

     

    let Array_Of_All_Article_Numbers := '2) Articles'

    let Current_Item := 'Raw Input'

    if contains(Array_Of_All_Article_Numbers,Current_Item)

    then
    'Type of Input'=1 of Current_Item

    end

     

    Is this indeed going in the right direction? If so; I will try to find the correct formula's because for now I am still an expert at compiling errors; haha!!

     

    -Bas

    • Sean
    • 4 yrs ago
    • Reported - view

    Bas, This is an example using Trigger on create...

     

    if not contains(text((select Table1 where Id = number(last(select Table1).Id) - 1).Text), "Art") then
    let newRec := (create Table2);
    newRec.(Text := text((select Table1 where Id = number(last(select Table1).Id) - 2).Text) + " - " + text((select Table1 where Id = number(last(select Table1).Id) - 1).Text))
    end

     

    I don't think you would want to use Trigger on update. It's a bit convoluted, but it works! If you want to use a button it will be different, but that's for another day.

Content aside

  • 4 yrs agoLast active
  • 9Replies
  • 2095Views