0

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

null
    • Fred
    • 1 mth ago
    • Reported - view

    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.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 mth ago
      • Reported - view

       

      It is my first attempt at using get(), and have to say it was pretty easy.

      Excelent Fred 👍 Now I have the idea of how to use it

      • Leo_Woer
      • 1 mth ago
      • Reported - view

       Hi Rafael

      can I get you to change the email to in the database I posted - I have by now received 10 mails from you with the csv file 😂

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 mth ago
      • Reported - view

       yes sorry😣

      • Leo_Woer
      • 1 mth ago
      • Reported - view

       No problem - then we know it works 👍

      • Leo_Woer
      • 1 mth ago
      • Reported - view

        Again this night I received 8 mails from you with the CSV file. You have to change the send to in both the create and send again button, you can just change leowoer to a nonesensense address - here is where you change it :

      sendEmail({
              from: userEmail(),
              to: "leowoer@icloud.com",
              subject: "Thank you for your email",

    • Leo_Woer
    • 1 mth ago
    • Reported - view

    Hi Fred - you are quite right, and I will try ASAP and return to you with the result

    thanks so far

    Leo 

    • Leo_Woer
    • 1 mth ago
    • Reported - view

    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

      • Fred
      • 1 mth ago
      • Reported - view

       When you get a chance please mark the post answered. Thanks,

      • Leo_Woer
      • 1 mth ago
      • Reported - view

       Sure I will - I have now also figured out how I in the same button can create and send a csv file. and it works pretty good - are you interested in seeing the database how it looks now with the extended scripts ?

      • Fred
      • 1 mth ago
      • Reported - view

      We love seeing what people are doing. Please post the updated DB.

    • Leo_Woer
    • 1 mth ago
    • Reported - view

    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

    • Fred
    • 1 mth ago
    • Reported - view
     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.

      • Leo_Woer
      • 1 mth ago
      • Reported - view

       yes if I want to create a plain csv  from the gear when I am in the invoiceitem table I have to do it - maybe I am wrong, but this I haven’t solved. The reason is that I don’t want my customer to be able to create csv from other places than the button, and then he should not be able to create again as then he risk to do bookkeeping the same rekord twice - but I would still be able to do it via the gear to correct some discrepancies.  Hope this gives meaning.

    • Fred
    • 1 mth ago
    • Reported - view
     said:
    The reason is that I don’t want my customer to be able to create csv from other places than the button

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

      • Leo_Woer
      • 1 mth ago
      • Reported - view

        I will do that - but How will you then be able from the original setup to create the the CSV with 3 items (which Economacs can handle) where item 1 should be date, Item 2 should be a description so that th bookkeeper can find out how to book and Item 3 which is the amount. I would love to do it more elegantly - if possible. Try to look at how I managed to do this with the following script - likewise see the screenshot I have attached :

      do as server
          for topLoop in select posteringslinier where 'CSV Created' != 1 do
              "create new invoice record";
              topLoop.('CSV Created' := 1);
              'CSV Indhold' := " ";
              'CSV Fil' := null;
              let Nyfakt := (create 'Faktura Enhed');
              Nyfakt.(
                  Dato := topLoop.Fakturadato;
                  Kunde := topLoop.Kundenavn;
                  Fakturanummer := topLoop.'Fakturanr.'
              );
              "create a line item for each item field";
              for loop1 in range(1, 4) do
                  if get(topLoop, "item" + loop1) != null then
                      let newLineItem := (create CSVlinier);
                      newLineItem.(
                          'Faktura Enhed' := Nyfakt;
                          Dato := get(topLoop, "Fakturadato");
                          Beskrivelse := "Fakturanummer " + get(topLoop, "Fakturanr.") +
                              switch loop1 do
                              case 1:
                                  "  Personaleindtægt :  "
                              case 2:
                                  "  Materialer :  "
                              case 3:
                                  "  Kørsel :  "
                              case 4:
                                  "  Ø. omk.  :"
                              default:
                                  ""
                              end;
                          'Beløb' := get(topLoop, "item" + loop1)
                      )
                  end
              end
          end
      end;
      if (select posteringslinier).'CSV Created' != 1 then
          let xText := "Date,Description,Amount
      ";
          for loop in select CSVlinier do
              xText := xText + text(loop.Dato + "," + loop.Beskrivelse + "," + loop.'Beløb') +
                  "
      ";
              'CSV Indhold' := xText
          end;
          'CSV Fil' := createTextFile(this, raw('CSV Indhold'), "Bogføringsliste.csv");
          sendEmail({
              from: userEmail(),
              to: "leowoer@icloud.com",
              subject: "Thank you for your email",
              text: "We received your request to get another copy of your invoice",
              html: "Some text with markup",
              attachments: 'CSV Fil'
          })
      end

Content aside

  • Status Answered
  • 1 mth agoLast active
  • 16Replies
  • 83Views
  • 3 Following