0

Duplicate multiple records to another table

Hi

I would like to succeed in creating a button allowing me to transform quotes into invoices.

 

In Ninox, my quotes are built like this:

When I create an estimate, a record is created in the "Estimate" table which I will name for this example "Estimate-01".

A subtable named "Services in quote" allows me to add services:
- Service 1 attached to Estimate-01 in which I enter the name of the service, the unit price, the quantity, etc.
- Service 2 attached to Estimate-01 in which I enter the name of the service, the unit price, the quantity, etc.
- Service 2 attached to Estimate-01 in which I enter the name of the service, the unit price, the quantity, etc.

 

In Ninox, my invoices are built like this:

When I create an invoice, a record is created in the "Transactions" table which I will name for this example "Transaction-01".

A subtable named "Billed Services" allows me to add services:
- Service 1 attached to Transaction-01 in which I enter the name of the service, the unit price, the quantity, etc.
- Service 2 attached to Transaction-01 in which I enter the name of the service, the unit price, the quantity, etc.
- Service 2 attached to Transaction-01 in which I enter the name of the service, the unit price, the quantity, etc.

 

The first part of my code works. It allows me to create a record in the Transactions table from a record in the Quotes table

let me := this;
---
Création de la transaction
---;
let newRecTransaction := (create Transactions);
newRecTransaction.(Compte := me.Compte);
newRecTransaction.('Activités' := me.'Activités');
newRecTransaction.('Opportunités' := me.'Opportunités');
newRecTransaction.(Projets := me.Projets);
newRecTransaction.(Entreprise := me.Entreprise);
newRecTransaction.('Taux de cotisation URSSAF pour les prestations de services' := me.Compte.Entreprise.'Taux de cotisation URSSAF pour les services');
newRecTransaction.('Taux de cotisation URSSAF pour les marchandises vendues' := me.Compte.Entreprise.'Taux de cotisation URSSAF pour les marchandises');
newRecTransaction.('Nom complet' := me.'Nom complet');
newRecTransaction.('Téléphone' := me.'Téléphone');
newRecTransaction.('E-mail' := me.'E-mail');
newRecTransaction.(Adresse := me.Adresse);
newRecTransaction.('N° de TVA' := me.'N° de TVA');
newRecTransaction.('N° de SIRET' := me.'N° de SIRET');
newRecTransaction.('Numéro du devis' := me.'N° de devis');
popupRecord(newRecTransaction);

I can't create the code that allows me to take all the records from the "Services offered" sub-table that are linked to a record from the Estimates table. Once all the records have been taken over, there should be as many records created in the "Invoiced services" sub-table that are linked to the new record in the Transactions table.

Please note that the fields of the Services offered table and the Services billed table are identical and include the following information: service name, price only, quantity, etc.

I tried the following code but it does not work because I have the following message: The expression gives multiple results.

---
Création du service à facturer
---;
let attServices := 'Services en devis 1:N'.'Produits et services';
let newRecService := (create 'Services facturés');
newRecService.(Transaction := newRecTransaction);
newRecService.('Produits et services' := newRecTransaction.attServices);
newRecService.('Date du service' := newRecTransaction.'Date de facturation');
newRecService.('Produits et services' := newRecTransaction.'Activités'.'Produits et services');
newRecService.('Durée' := newRecTransaction.'Activités'.'Durée');
newRecService.(TVA := number(newRecTransaction.Entreprise.'Montant de la TVA calculé'));
popupRecord(newRecService)

Can you help me complete the code allowing me to create the new records in the Service billed sub-table from the data in the Services offered table? Thanks for your help.

5 replies

null
    • Kruna
    • 9 mths ago
    • Reported - view

    Hi Sebastian, can you pls upload a sample db or your db without content, so its easier for me to help you.

    regards Kruna

      • Créateur de bien-être
      • Sebastien_Guillet
      • 9 mths ago
      • Reported - view

       Here is an extract from the database. I left the useful tables to simplify understanding.

    • Kruna
    • 9 mths ago
    • Reported - view

    Hi Sebastian, unfortunately I cant figure out where to find the respective button. I am somehow lost in your sample db😅

    as far as I know when you have to handle with subtables, the code should go something like this:

    let me := this;
        for i in me.'Services facturés' do
                let newRecService := (create 'Services facturés');
                newRecService.(
                    Transactions := newRecTransaction;
                   field1 := i.field1;
                   field2 := i.field2;
                   field3 := i.field3;
    
                )
      • Créateur de bien-être
      • Sebastien_Guillet
      • 9 mths ago
      • Reported - view

       THANKS !

      With your example I managed to create the right formula. Here is the result which can serve as an example for similar cases.

      When I click the button, all the records in the subtable that are related to the main table record are successfully copied to the other tables.

       

      Here is some info to understand the method used in this code:

      The information under "Création de la transaction" relates to the main table information.

      The information under "Création du service à facturer" concerns the information of the sub-table which is likely to contain several records linked to the main table. All the records (and their information) are copied one by one into other tables (Estimate -> Invoice).

      let me := this;
      ---
      Création de la transaction
      ---;
      let newRecTransaction := (create Transactions);
      newRecTransaction.(Compte := me.Compte);
      newRecTransaction.(Entreprise := me.Entreprise);
      newRecTransaction.('Activités' := me.'Activités');
      newRecTransaction.('Opportunités' := me.'Opportunités');
      newRecTransaction.(Projets := me.Projets);
      newRecTransaction.('Taux de cotisation URSSAF pour les prestations de services' := me.Compte.Entreprise.'Taux de cotisation URSSAF pour les services');
      newRecTransaction.('Taux de cotisation URSSAF pour les marchandises vendues' := me.Compte.Entreprise.'Taux de cotisation URSSAF pour les marchandises');
      newRecTransaction.(Commentaires := me.Commentaires);
      newRecTransaction.('Nom complet' := me.'Nom complet');
      newRecTransaction.('Téléphone' := me.'Téléphone');
      newRecTransaction.('E-mail' := me.'E-mail');
      newRecTransaction.(Adresse := me.Adresse);
      newRecTransaction.('N° de TVA' := me.'N° de TVA');
      newRecTransaction.('N° de SIRET' := me.'N° de SIRET');
      newRecTransaction.('Numéro du devis' := me.'N° de devis');
      popupRecord(newRecTransaction);
      ---
      Création du service à facturer
      ---;
      for i in me.'Services en devis 1:N' do
          let newRecService := (create 'Services facturés');
          newRecService.(
              Transaction := newRecTransaction;
              'Produits et services' := i.'Produits et services';
              'Date du service' := i.newRecTransaction.'Date de facturation';
              'Durée' := i.'Durée';
              'Tarif unitaire' := i.'Tarif unitaire';
              'Quantité' := i.'Quantité';
              Commentaires := i.Commentaires;
              'Appliquer la remise du compte par défaut ?' := i.'Appliquer la remise du compte par défaut ?';
              'Remise par défaut du service' := i.'Remise par défaut du service';
              'Remise manuelle HT en €' := 'Remise manuelle HT en €';
              TVA := i.number(newRecTransaction.Entreprise.'Montant de la TVA calculé')
          )
      end
      
      • Kruna
      • 9 mths ago
      • Reported - view

      thats great, I am glad that you were able to achieve it.

      I use something similar in my db when it comes to create an invoice after an offer.

      I also implemented the following right before  let me := this;

      if dialog(upper("Take all records"),"All the services will be taken over " +
      "Are you sure that you want to proceed?", ["Yes", "No"]) = "Yes" then

      of course its not crucial at all , its just a little gimmick :-)

Content aside

  • Status Answered
  • 9 mths agoLast active
  • 5Replies
  • 72Views
  • 2 Following