HORNEKS ANS

ACF Library

CreateInvoiceDraft

Back to List

Description: Create an invoice draft in CONTA based on a Order in FileMaker

FileMaker Prototype: 

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

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 CreateInvoiceDraft function creates an invoice draft in Conta. This draft can then be edited, finalized, and approved directly in the Conta web interface.

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, performing all necessary invoice calculations.
  4. Retrieves the OrderHead and builds a JSON object containing all the OrderHead details (from the Orders table).
  5. Processes the OrderDetails lines, constructing the invoiceDraftLines array within the JSON object.
  6. Prepares the API URLs and API keys, and sends the stringified JSON to the API endpoint.
  7. Verifies the response, extracting the ID of the created invoice from the returned data.
  8. Updates the order with the InvoiceDraftID to prevent duplicate submissions.

Example

Set Variable [$res; ACF_Run("CreateInvoiceDraft"; Orders::PrimaryKey)]
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

Function source:

function CreateInvoiceDraft (string OrderPrimaryKey)
   // 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
   array string ValidVATcodes = {"high", "medium", "low", "zero.rate", "exempted", "export"}; 
   print "Lines = " + lines + "\n"; 
   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;
   array date ohOrderDate, ohOrderDeliveryDate, ohInvoiceDate, ohDueDate;
   array float ohSumNet;
   array int ohOrderID, ohContaInvoiceID;
   
   sql = "SELECT CustomerName, OrderDescription, OrderStatus, OrderDate, OrderDeliveryDate, 
         InvoiceDate, DueDate, SumNet, OrderID, ContaDraftInvoiceID
      FROM Orders
      WHERE PrimaryKey = :OrderPrimaryKey

      INTO  :ohCustomerName, :ohOrderDescription, :ohOrderStatus, :ohOrderDate, :ohOrderDeliveryDate, :ohInvoiceDate, :ohDueDate, :ohSumNet, :ohOrderID, :ohContaInvoiceID
      ";
   res = ExecuteSQL ( sql ); 
   
   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 made draft 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. 
   JSON order; 
   order = JSON(
       "customerId", aCustContaID[1],
       "customerReference", ohCustomerName[1],
       // "attachmentFileId", "9223372036854776000",
       // "customerGroupId", "9223372036854776000",
       // "departmentId", "9223372036854776000",
       "exchangeRate", 1,
       "exchangeRateReferenceDate", string(ohInvoiceDate[1], "%Y-%m-%d"),
       "invoiceCurrency", "NOK",
       "invoiceLanguage", "NO",
       "personalMessage", ohOrderDescription[1],
       // "projectId", "9223372036854776000",
       "registrationSource", "CONTA",
       // "saleId", "9223372036854776000",
       // Not for drafts: "sumNet", ohSumNet[1],
       // "sumRemaining", "99999999999", // not sure what this is?
       // Not for drafts: "sumTotal", sumTotalInvoice,
       // Not for drafts: "sumVAT", sumVATInvoice,
       "type", "NORMAL"
   );
   print "Done OH"+ "\n"; 
   string vat;
   for (i=1, lines)
      vat = prVatCode[i]; 
      order["invoiceDraftLines[]"] = JSON(
          // Not for drafts: "bookkeepingAccountNo", prBookKepingAccount[i],
          "description", odProductName[i],
          "discount", odDiscountPro[i],
          // "id", "9223372036854776000",
          // "invoiceDraftId", "9223372036854776000",
          "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
          //"vatId", "9223372036854776000"
         );
   end for
   print string(order); 
   
   // Do the API request
   JSON apidata = getContaApiKeyandNumber(); 
   if ( apidata["APIkey"] == "") then
      alert ("No API key found"); 
      return "ERROR: Missing APIkey"; 
   end if
   
   string APIkey = apidata["APIkey"]; 
   bool production = (apidata["Production"]=="1");
   int compOrg = int(apidata["CompNo"]); 
   
   string hdr = 'apiKey:'+APIkey; 
   // /invoice/organizations/{opContextOrgId}/invoice-drafts
   string url = getContaBaseURL(production)+"/invoice/organizations/"+compOrg+"/invoice-drafts";
   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 ContaDraftInvoiceID = :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