Create lines for CSV
Hi there
has anybody done a transfer from one table to another where one record in the in the main table become 3 or more records in another table - the thing I am trying to solve is that I today has one table ("posteringslinier") which have 6 fields :
1 a date
2 customer name
3 Invoice no.
4 item 1 to be invoiced
5 item 2 to be invoiced
6 item 3 to be invoiced
The target table should be 3 records with following fields :
1 a concatenation of 1+2+"something" from "posteringslinier"
2 field 4 or 5 or 6 from posteringslinier (3 records)
I will appreciate very much if someone has a generic way to do this in a loop as "posteringslinier" can have many records.
Hope you guys can help me here.
regards
Leo
16 replies
-
It looks like you want to take a flat table (where you have fields like item 1, item 2, item 3) and want to create a more relational structure. That is a very good idea and will make things easier in the long run.
It looks like you want to create an invoice structure. The "new table" would consist of two table one parent and one child. The parent could be called Invoice and the child could be called InvoiceItems.
Luckily you are working in the same database so you don't need to export anything.
I've attached a sample DB. If you open the posteringslinier table you will see two buttons, one to copy only the current record, and one to copy all records.
It is my first attempt at using get(), and have to say it was pretty easy.
Let us know how it goes.
-
Hi Fred - you are quite right, and I will try ASAP and return to you with the result
thanks so far
Leo
-
Hi again Fred - a very simple and ingenious solution. Thank you so much. And when reading the code, I see that it is really simple to extend the fields if my customer at some time wants more, that is absolutely possible that he wants this.. The next challenge my customer probably will give - I am sure -will be to view it all in on view, so I can create a csv file for integration with some of his other systems. When this challenge comes I might need to ask you again - hope that is OK ?
regards and thank you for this elegant solution.
Leo
-
Hi Fred
hereby the database - the only thing I haven't figured out out is, how to mark the records as handled when the csv has been created. I have had some attempts now, where I try to put them in te toploop - but even all my attempts the only place it got marked is the record I am in when pushing the create and send button.
any Idea ?
rgds
Leo
-
said:
I haven't figured out out is, how to mark the records as handled when the csv has been created.I see you wrote this:
for topLoop in select posteringslinier do "create new invoice record"; 'Marked csv handled' := 1;
If you don't reference the loop variable (topLoop) then Ninox will assume you are referencing the current record. Try:
for topLoop in select posteringslinier do "create new invoice record"; topLoop.'Marked csv handled' := 1;
On a side note, do you really need an extra date field in InvoiceItem table? You have a data field in the Invoice table and since all InvoiceItems have a parent then you can always reference it.
-
said:
The reason is that I don’t want my customer to be able to create csv from other places than the buttonIf you are creating a DB for users, then may I recommend that you look into creating dashboards. Just look for Ninox dashboards in a web search.
You will be creating a new UI for the end user to use. They can't do anything unless you provide them with the ability to do it. So you can make sure data is entered properly, or make sure there are not duplicates, or have them see reports one set of reports versus another.
Content aside
- Status Answered
- 1 mth agoLast active
- 16Replies
- 83Views
-
3
Following