HORNEKS ANS

ACF Library

CreateContaInvoice

Back to List

Description: Create an invoice in CONTA, and send it. Based on FileMaker front-end.

FileMaker Prototype: 

Set Variable [$res; ACF_Run("CreateContaInvoice"; string_OrderPrimaryKey;  bool_allreadysent)]

Category: CONTA API

Dependencies

NOTE: This function serves as a reference template. To use it effectively, you may need to adjust certain parts of the code, such as field names, database structures, and specific data lists to fit the requirements of your system. This customization allows the function to work seamlessly within your environment.

The CreateContaInvoice Function create an invoice in Conta Accounting system, and optionaly send it to the customers, by mail or eFaktura, EHF, etc.

The function is comprehensive, as it handles multiple tasks. Here are the steps it performs:

  1. Verifies if the connected customer already exists in Conta (i.e., has a Conta ID). If not, the customer is first created in Conta.
  2. Checks if all products referenced in the OrderDetails also exist in Conta. If not, the function creates any missing products.
  3. Retrieves the order details and associated products,
  4. Performing all necessary invoice calculations.
  5. Retrieves the OrderHead and builds a JSON object containing all the OrderHead details (from the Orders table).
  6. Processes the OrderDetails lines, constructing the invoiceLines array within the JSON object.
  7. Queries the Contacts belonging to the customer, all invoice recipients. If none found, query the customer instead and use the e-mail and contact info on the customer record.
  8. Produces the invoiceRecipientsarray based on the above.
  9. Prepares the API URLs and API keys, and sends the stringified JSON to the API endpoint.
  10. Verifies the response, extracting the ID of the created invoice from the returned data.
  11. Updates the order with the InvoiceID to prevent duplicate submissions.

Example

Set Variable [$res; ACF_Run("CreateContaInvoice"; Orders::PrimaryKey; 0)]
If [$res = "OK"]
   // Invoice created successfully, and order is updated
Else
   // Handle any errors, e.g., Show Custom Dialog, log the error, or other error management
End If

The whole ACF-Conta packet is on GitHub

You can find the whole packet with all the conta-related functions in it at GitHub ACF-Conta

Function source:

function CreateContaInvoice (string OrderPrimaryKey, bool allreadysent)
   // Verify if the customer is created
   
   string sql, res; 
   bool b1;
   array string aCustPrimKey, aCustContaID; 
   res = ExecuteSQL ("SELECT Kunder.PrimaryKey, Kunder.ContaCustomerID FROM Orders
   LEFT JOIN Kunder ON Kunder.PrimaryKey = Orders.CustomerFK
   WHERE Orders.PrimaryKey = :OrderPrimaryKey
   
   INTO :aCustPrimKey, :aCustContaID");
   case
      : (sizeof (aCustPrimKey) == 0)
         throw ("Order - Customer link broken"); 
      : (aCustContaID[1] == "") 
         res = ContaMakeCustomer(aCustPrimKey[1]); // Save customer first
         b1 = assert ( res, "OK", "Error saving customer to Conta: " + res); 
         aCustContaID[1] = $$CustContaID; // to be included in the API request. 
      default
         // Link is OK, and customer saved, 
   end case
      
   // Verify all the products are saved. 
   array int aContProdID, aContaCount; 
   array string aProdPrimKey; 
   res = ExecuteSQL ( "SELECT pr.ContaProductID, pr.PrimaryKey, count(od.OrderFK) FROM OrdersDetails AS od
         LEFT OUTER JOIN Products AS pr ON pr.PrimaryKey = od.ProductFK
         WHERE od.OrderFK = :OrderPrimaryKey
         GROUP BY pr.ContaProductID, pr.PrimaryKey
         
         INTO :aContProdID, :aProdPrimKey, :aContaCount"); 
   int i, j = sizeof (aContProdID); 
   if (j == 0) then
      throw "No order lines in invoice"; 
   end if
   print "Done checking"+ "\n";  
   for (i=1, j)
      case
         :(aProdPrimKey[i] == "")
            throw Format ("Product not linked, on %d lines.",aContaCount[i]); 
         :(aContProdID[i] == 0)
            res = ContaCreateProduct(aProdPrimKey[i]);
            assert ( res , "OK", "Error saving product " + aProdPrimKey[i] + " " + res); 
      end case
   end for
   // All the products has been saved, conta-id has been updated. 
   
   array string odProductFK, odProductName, odDiscount,
   odTaxType, prPrimaryKey, prProductNo, prProductName, prVatCode, 
   prBookKepingAccount;
   array float odPrice, odQty, odLineTotal, odTaxPercent, odSumInclVAT, prPrice;
   array int prContaProductID, odLineNo;
   
   // Now, pull the Orderdetails data
   sql = "SELECT  od.LineNo, od.ProductFK, od.ProductName, od.Discount, od.TaxType, 
         od.SumInclVAT, pr.PrimaryKey, pr.ProductNo, pr.ProductName, pr.VatCode, pr.BookKepingAccount, 
         od.Price, od.Qty, od.LineTotal, od.TaxPercent, od.SumInclVAT, pr.Price, pr.ContaProductID
        FROM OrdersDetails AS od
      LEFT JOIN Products AS pr ON pr.PrimaryKey = od.ProductFK
      WHERE od.OrderFK = :OrderPrimaryKey
      
      INTO :odLineNo, :odProductFK, :odProductName, :odDiscount, :odTaxType, :odSumInclVAT, :prPrimaryKey, :prProductNo, :prProductName, :prVatCode, :prBookKepingAccount, :odPrice, :odQty, :odLineTotal, :odTaxPercent, :odSumInclVAT, :prPrice, :prContaProductID
       ";
   res = ExecuteSQL ( sql ); 
       
   float sumNetInvoice, sumVATInvoice, sumTotalInvoice, LineVAT, DiscountAM, DiscountPro; 
   int lines = sizeof (odLineNo); 
   array float odDiscountAM, odDiscountPro; 
   // Do Invoice Calculations
   print "Lines = " + lines + "\n"; 
   array string ValidVATcodes = {"high", "medium", "low", "zero.rate", "exempted", "export"}; 

   for (i=1, lines)
      b1 = AssertInArray(prVatCode[i], ValidVATcodes, "Invalid VAT code '"+prVatCode[i]+"', should be: " + implode (",", ValidVATcodes));
      sumNetInvoice += odLineTotal[i];
      LineVAT = odLineTotal[i]*odTaxPercent[i]/100; 
      sumVATInvoice += LineVAT; 
      DiscountAM = DiscountCalc(odQty[i], odPrice[i], odDiscount[i]); 
      odDiscountAM[] = DiscountAM; 
      DiscountPro = (DiscountAM /(odQty[i] * odPrice[i]))*100; 
      odDiscountPro[] = DiscountPro; 
      sumTotalInvoice += odLineTotal[i]+LineVAT; 
   end for
   print "Done calc"+ "\n"; 
   // Then pull the data from the orderhead. 
   array string ohCustomerName, ohOrderDescription, ohOrderStatus, ohYourRef, ohOurRef;
   array date ohOrderDate, ohOrderDeliveryDate, ohInvoiceDate, ohDueDate;
   array float ohSumNet, ohPaidAmount;
   array int ohOrderID, ohContaInvoiceID, ohContaDraftInvoiceID, ohInvoiceNo;
   
   sql = "SELECT CustomerName, OrderDescription, OrderStatus, OrderDate, OrderDeliveryDate, 
         InvoiceDate, DueDate, SumNet, OrderID, ContaInvoiceID, ContaDraftInvoiceID, InvoiceNo, PaidAmount,
         YourRef,OurRef
      FROM Orders
      WHERE PrimaryKey = :OrderPrimaryKey

      INTO  :ohCustomerName, :ohOrderDescription, :ohOrderStatus, :ohOrderDate, :ohOrderDeliveryDate, 
      :ohInvoiceDate, :ohDueDate, :ohSumNet, :ohOrderID, :ohContaInvoiceID, :ohContaDraftInvoiceID, :ohInvoiceNo, :ohPaidAmount, 
      :ohYourRef, :ohOurRef
      ";
   res = ExecuteSQL ( sql ); 
   print "\n\nSQL: " + res + "\n\n"; 
   j = sizeof ( ohCustomerName); 
   if ( j == 0 ) then
      throw "Didn't find the order with " + OrderPrimaryKey; 
   end if
   
   // Now, check if we allready have an invoice ID. 
   if ( ohContaInvoiceID[1] != 0) then
      return format ( "Order %d is allready invoiced with number %d, wisit Conta.no to check",ohOrderID[1], ohContaInvoiceID[1]); 
   end if
   
   if (abs(ohSumNet[1] - sumNetInvoice)>0.001) then
      throw format("Invoice calculation does not add up. Head: %.3f, Lines: %.3f", ohSumNet[1], sumNetInvoice); 
   end if
   
   print "Done checking, j="+j+ "\n"; 
   // Now, lets create the JSON nessessary for the API. 
   ohYourRef[1] =  ((ohYourRef[1] == "")?ohCustomerName[1]:ohYourRef[1]);
   JSON order; 
   order = JSON(
       "customerId", aCustContaID[1],
       "customerReference", ohYourRef[1],
       // "attachmentFileId", "9223372036854776000",
       // "customerGroupId", "9223372036854776000",
       // "departmentId", "9223372036854776000",
       "exchangeRate", 1,
       "exchangeRateReferenceDate", string(ohInvoiceDate[1], "%Y-%m-%d"),
       "invoiceCurrency", "NOK",
       "invoiceLanguage", "NO",
      "isCreatedInOtherSystem", allreadysent, 
      "invoiceDate", string(ohInvoiceDate[1], "%Y-%m-%d"),
      "invoiceDueDate", string(ohDueDate[1], "%Y-%m-%d"),
       "personalMessage", ohOrderDescription[1],
       // "projectId", "9223372036854776000",
       // "registrationSource", "CONTA",
       // "saleId", "9223372036854776000",
       "type", "NORMAL"
   );
   if (ohContaDraftInvoiceID[1] != 0 ) then
      order["id"] = ohContaDraftInvoiceID[1]; 
   end if
   print "Done OH"+ "\n"; 
   string vat;
   for (i=1, lines)
      vat = prVatCode[i]; 
      order["invoiceLines[]"] = JSON(
          "description", odProductName[i],
          "discount", odDiscountPro[i],
          "lineNo", odLineNo[i],
          "price", odPrice[i],
          "productId", prContaProductID[i],
          "quantity", odQty[i],
          "sumDiscount", odDiscountAM[i], // or calculated if discount is a percentage
          "sumNet", odLineTotal[i],
          "vatCode", vat
         );
   end for
   
   JSON apidata = getContaApiKeyandNumber(); 
   if ( apidata["APIkey"] == "") then
      alert ("No API key found"); 
      return "ERROR: Missing APIkey"; 
   end if
   string InvoEmailSub = apidata["EmailSubject"];
   string InvoEmailCont = apidata["EmailContent"];
   
   // Add the invoice recipients
   array string coFirstName, coLastName, coName, coMobile, coEmail;
   array string cuKundeNavn, cuAdresse1, cuAdresse2, cuPostNr, cuSted, cuCountry; 
   array string cuMobil, cuEpost; 
   string MyCompany = ExecuteSQL ( "SELECT CompanyName FROM Preferences"); 
   array int coID; 
   string CustomerPrimKey = aCustPrimKey[1]; 
   // Get the Contaxts from the Contacts table. 
   if ( ! allreadysent ) then
      print "\nEmail-recipients...\n";
      sql = "SELECT FirstName, LastName, Name, Mobile, Email, id
        FROM Contacts
        WHERE CustomerFK = :CustomerPrimKey  AND InvoiceRecipient = 'Yes'

        INTO :coFirstName, :coLastName, :coName, :coMobile, :coEmail, :coID";
      res = ExecuteSQL ( sql ); 
      if ( res != "OK") then
         alert (res); 
         return res; 
      end if
   
      j= sizeof (coFirstName); 
      if (j == 0) then
         sql = "SELECT  KundeNavn, Adresse1, Adresse2, PostNr, Sted, 
            Country, Mobil, Epost
             FROM Kunder
             WHERE PrimaryKey = :CustomerPrimKey

             INTO :cuKundeNavn, :cuAdresse1, :cuAdresse2, :cuPostNr, :cuSted, :cuCountry, :cuMobil, :cuEpost
         "; 
         res = executeSQL ( sql ) ; 
         if ( sizeof ( cuKundeNavn) == 0) then
            return "No email registered on the customer, add a email to send it"; 
         end if
         // Create E-mail recipients from the fields on the customer. 
         order["invoiceRecipients[]"] = JSON(
         "customerId",aCustContaID[1],
         // "ehfRecipient","IeNenkUZHGUor6TcgV7iZZYML3mwDl35Mk-708RCcf-k Na4jQ",
         "emailAddress",cuEpost[1],
         "emailContent",InvoEmailCont, // Here we must have some content preferences
         "emailRecipientType","EMAIL_TO",
         "emailSubject", InvoEmailSub,
         "includeDefaultAttachment",true,
         "includeInvoiceAttachment",true,
         "includeInvoiceInfo",true,
         /*"mailAddressCity","string",
         "mailAddressCountryCode","st",
         "mailAddressLine1","string",
         "mailAddressLine2","string",
         "mailAddressPostcode","string",*/
         "name",cuKundeNavn[1],
         "phoneNo",cuMobil[1],
         "type","EMAIL");
      else
         for (i=1, j)
            order["invoiceRecipients[]"] = JSON(
            "customerId",aCustContaID[1],
            // "ehfRecipient","IeNenkUZHGUor6TcgV7iZZYML3mwDl35Mk-708RCcf-k Na4jQ",
            "emailAddress",coEmail[i],
            "emailContent",InvoEmailCont, // Here we must have some content preferences
            "emailRecipientType","EMAIL_TO",
            "emailSubject", InvoEmailSub,
            "includeDefaultAttachment",true,
            "includeInvoiceAttachment",true,
            "includeInvoiceInfo",true,
            /*"mailAddressCity","string",
            "mailAddressCountryCode","st",
            "mailAddressLine1","string",
            "mailAddressLine2","string",
            "mailAddressPostcode","string",*/
            "name",coName[i],
            "phoneNo",coMobile[i],
            "type","EMAIL");
         end for
      end if
   end if
   print string(order); 
   
   // Debugg stopp
   // return order; 
   
   // Do the API request
   
   
   string APIkey = apidata["APIkey"]; 
   bool production = (apidata["Production"]=="1");
   int compOrg = int(apidata["CompNo"]); 
   
   string hdr = 'apiKey:'+APIkey; 
   // /invoice/organizations/{opContextOrgId}/invoices
   string url = getContaBaseURL(production)+"/invoice/organizations/"+compOrg+"/invoices";
   print "\n"+url; 
   string data = string(order); 
   res = HTTP_POST ( url, data, hdr);
   print "\n"+res;
   int contaInvID;
   // Verify the result- 
   JSON apiRes = res; 
   if (ContaVerifyAPIresponce(apiRes)) then
      contaInvID = int(apires["id"]); 
      print "\nContaInvoiceID = " + contaInvID+"\n"; 
      // Update ContaProductID on the Product. 
      sql = "UPDATE Orders SET ContaInvoiceID = :contaInvID
      WHERE PrimaryKey = :OrderPrimaryKey"; 
      res = ExecuteSQL( sql ); 
      if ( res != "") then
         alert ( res ); 
         return "ERROR Update ContaInvoiceID..."+res; 
      end if
   end if
   
   
   return "OK"; 
end

FileMaker Table structure

As we are creating the invoice from an order in the order structure, here is the fields from the three tables we are using. You probably have different names, so the SQL sentences in the function must be updated to yours system. Some fields might be added. If you have other VAT codes in your system, you might add a calculated field that translate your VAT codes to Conta's codes (Shown below).

Customers (Kunder in Norwegian)

Field Name Type Description
PrimaryKey Text. Unique UUID for this customer.
KundeID Number Customer Number.
KundeNavn Text Name of the customer
Adresse1 Text Primary address line for the customer
Adresse2 Text Secondary address line (optional)
PostNr Text Postal code for the primary address
Sted Text City or locality for the primary address
Country Text Country code or name for the primary address
LAdresse1 Text Delivery address line 1
LAdresse2 Text Delivery address line 2 (optional)
LPostNr Text Postal code for the delivery address
LSted Text City or locality for the delivery address
LCountry Text Country code or name for the delivery address
Mobil Text Mobile phone number of the customer
Epost Text Email address of the customer
Kundetype Text Customer type or category
OrgNr Text Organization or business registration number

Orders

Field Name Type Description
PrimaryKey Text Primary key for Orders
CustomerFK Text Relation field to Customers Primary Key
CustomerName Text Name of the customer associated with the order
OrderDescription Text Description or notes about the order
OrderStatus Text Current status of the order
OrderDate Date Date the order was placed
OrderDeliveryDate Date Expected or scheduled delivery date for the order
InvoiceDate Date Date the invoice was generated
DueDate Date Payment due date for the invoice
SumNet Number Net sum total for the order (excluding VAT)
OrderID Number Unique identifier for the order
ContaInvoiceID Number Conta-specific invoice ID

OrderDetails

Field Name Type Description
OrderFK Text Foreign key linking to the Orders Primary Key.
LineNo Number Line number within the invoice
ProductFK Text Foreign key linking to the product's Primary Key
ProductName Text Name of the product in this line
Discount Number Discount applied to this line, as amount, or percentage with "%" sign in the end.
TaxType Text Type of tax applied to this line (high, medium, low, zero.rate, exempted, export)
SumInclVAT Number Total line amount including VAT
Price Number Price per unit
Qty Number Quantity of the product in this line
LineTotal Number Total amount for this line after discounts, before VAT
TaxPercent Number Tax percentage for this line

Products

Field Name Type Description
PrimaryKey Text Primary key of the product
ProductNo String Unique product identifier
ProductName Text Name of the product
VatCode Text VAT code associated with the product (high, medium, low, zero.rate, exempted, export)
BookKepingAccount String Bookkeeping account number for financial tracking
Price Number Default price of the product
ContaProductID Number Conta-specific product ID
Back to List