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
-
Dear All,
Please find attached a database that contains above information. I hope that helps.
Thanks again,
Bas
-
PS Here's a link: https://we.tl/t-dd9ycraYib
PPS What is the best way to share files on this forum?
-
Any ideas?
Thanks,
Bas
-
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?
-
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
-
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
-
Hi Sean,
Just to make sure I am following you on a conceptual level, I created this very elimentary 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_Itemend
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
-
Hi Sean,
Just to make sure I am following you on a conceptual level, I created this very elimentary 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_Itemend
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, This is an example using Trigger on create...
if not contains(text((select Table1 where Id = number(last(select Table1).Id) - 1).Text), "Art") thenlet 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))endI 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
- 5 yrs agoLast active
- 9Replies
- 2096Views