Example - Calculate Invoice Lines using SQL

Function: Example - Calculate Invoice Lines using SQL

In FileMaker, scripting applies to the current layout in the relationship graph. For an Invoice, the layout position is for the Invoice table, and the InvoiceLines are in a related table shown in a portal on the invoice layout. Calculations on the invoice line can be done as calculated fields or as calculations done when lines change. In this solution (a real example), three sets of sum fields were used on the invoice line. One set was calculated fields, one set was indexed fields, and the last set was calculations that either picked values from the calculated field or the indexed field depending on the invoice approval state. The indexed fields had to be updated in the approval routine. As FileMaker's function for this had some issues, this ACF function was created to perform the calculations on the fields instead.

The ACF Functions:

package InvoiceCalculations "Functions to do invoice calculations"; 

// Allow us to check the version of the routines and reload if too old or not installed. 
function InvCalc_Version ()
    return 12.0;
end

// Decide what fields need to be updated and create SQL assignments for the update part. 
// Output on inconsistency: <field>=<new value>
function checkConsistency (float dbval, float calcval, string FieldName)
    if (dbVal == calcval) then
        return ""; 
    else
        return format ("%s=%f", FieldName, calcval);
    end if
end 

// Do the calculation for all invoice lines and update those that need an update. 

function ApproveInvoiceLinesCalc (string invoiceNumber)
    // Get a resultset of all the invoice lines for the invoice. 
    int lq = SQL_query ("SELECT Auto_nr, Antall, Kostpris, Salgspris_u_MVA, MVA_kode, MVA_prosent, Rabatt_belop, Rabatt_prosent, in_Linjesum_avgiftspliktig, in_Linjesum_avgiftsfritt, in_Linjesum_u_MVA, in_Linjesum_kostpris, in_Linjesum_fritt_og_pliktig, in_Linjesum_rabatt, in_Linjesum_m_MVA, in_Linjesum_MVA, in_Linjesum_DG, in_Linjesum_DB FROM db_InvoiceDescriptions WHERE Faktura_nr=" + invoiceNumber); 

    // Declarations
    float Linjesum_avgiftspliktig, Linjesum_avgiftsfritt, Linjesum_u_MVA, Linjesum_kostpris, Linjesum_fritt_og_pliktig, Linjesum_rabatt, Linjesum_m_MVA, Linjesum_MVA, Linjesum_DG, Linjesum_DB;
    float line_net, linemva, mvapro, rabpro, rabbel, line_kostpris, line_db, line_dg; 

    int num_rows = SQL_getRowCount (lq); 

    print format ("Number of Invoice Lines: %d\n", num_rows); 

    int i, ll; 
    string res, upds; 
    array string row, upd, empty; 

    // Loop through the invoice lines
    for (i = 1, num_rows, 1)
        // The SQL_getRow returns an array, allowing us to access each column in the recordset individually. 
        // The first parameter is the handle, and the second is the row number, starting with "0". 
        
        row = SQL_getRow (lq, i-1); 
        // print ("Columns: " + string (sizeof (row)) + " : " + row[1]);
        // As all columns are text from the SQL, we need to use the float function to convert to float (actually double)
        Linjesum_avgiftspliktig = float (row[9]);
        Linjesum_avgiftsfritt = float (row[10]);
        Linjesum_u_MVA = float (row[11]);
        Linjesum_kostpris = float (row[12]);
        Linjesum_fritt_og_pliktig = float (row[13]);
        Linjesum_rabatt = float (row[14]);
        Linjesum_m_MVA = float (row[15]);
        Linjesum_MVA = float (row[16]);
        Linjesum_DG = float (row[17]);
        Linjesum_DB = float (row[18]);
        mvapro = float (row[6]);
        
        line_net = float (row[2]) * float (row[4]);
        if (row[8] == "") then
            rabbel = float (row[7]); 
        else
            rabbel = line_net * float(row[8]) / 100.0;
        end if
        line_net = line_net - rabbel;
        
        upd = empty; // Empty array
        line_kostpris = float (row[3]) * float (row[2]);
        line_db = line_net - line_kostpris; 
        
        // Calculate linjesum-DG
        if (line_net == 0 && line_db == 0) then 
            line_dg = 0; 
        elseif (line_db < 0 && line_net == 0) then 
            line_dg = -100; 
        elseif (line_db < 0) then
            line_dg = abs(line_db) * 100 / line_net * -1;
        elseif (line_net == 0) then
            line_dg = 100; 
        else     
            line_dg = line_db * 100 / line_net; 
        end if

        // Check consistency between db-values and our calculations. 
        // The checkConsistency functions is defined near the top. 
        if (mvapro == 0) then
            upd[] = checkConsistency (Linjesum_avgiftspliktig, 0, "in_Linjesum_avgiftspliktig"); 
            upd[] = checkConsistency (Linjesum_avgiftsfritt, line_net, "in_Linjesum_avgiftsfritt"); 
            upd[] = checkConsistency (Linjesum_u_MVA, line_net, "in_Linjesum_u_MVA"); 
            upd[] = checkConsistency (Linjesum_fritt_og_pliktig, line_net, "in_Linjesum_fritt_og_pliktig"); 
            upd[] = checkConsistency (Linjesum_MVA, 0.0, "in_Linjesum_MVA");
            upd[] = checkConsistency (Linjesum_m_MVA, line_net, "in_Linjesum_m_MVA");
        else
            linemva = line_net * mvapro / 100; 
            upd[] = checkConsistency (Linjesum_avgiftspliktig, line_net, "in_Linjesum_avgiftspliktig"); 
            upd[] = checkConsistency (Linjesum_avgiftsfritt, 0, "in_Linjesum_avgiftsfritt"); 
            upd[] = checkConsistency (Linjesum_u_MVA, line_net, "in_Linjesum_u_MVA"); 
            upd[] = checkConsistency (Linjesum_fritt_og_pliktig, line_net, "in_Linjesum_fritt_og_pliktig"); 
            upd[] = checkConsistency (Linjesum_MVA, linemva, "in_Linjesum_MVA");
            upd[] = checkConsistency (Linjesum_m_MVA, line_net + linemva, "in_Linjesum_m_MVA");
        end if  
        upd[] = checkConsistency (Linjesum_kostpris, line_kostpris, "in_Linjesum_kostpris");
        upd[] = checkConsistency (Linjesum_DB, line_db, "in_Linjesum_DB");
        upd[] = checkConsistency (Linjesum_DG, line_dg, "in_Linjesum_DG");
         
        // Now we have the "upd" array, containing assignments for update, or empty if the field does not need an update. 
        // Implode creates a comma-separated string with potential to have excessive commas in it because of the empty array rows.
        // Get rid of the excessive commas. 
        upds = implode (",", upd); 
        upds = substitute (upds, ",,", ","); // We need to do this Several times, since
        upds = substitute (upds, ",,", ","); // ,,,, => ,,
        upds = substitute (upds, ",,", ","); // ,, => , and so on. 
        upds = substitute (upds, ",,", ","); 
        upds = substitute (upds, ",,", ","); 
        
        // There might be a comma at the end; we need to get rid of this. 
        // If the last empty rows end up with a single comma. 
        ll = length(upds);
        if (ll != 0) then
            if (right (upds, 1) == ",") then
                upds = left (upds, ll-1);
            end if
        end if

        // Anything to do? Run the Update SQL. "upds" now contains a comma-separated list of assignments. 
        if (upds != "") then    
            // Craft the SQL
            upds = format ("UPDATE db_InvoiceDescriptions SET %s WHERE Auto_nr=%s", upds, row[1]); 
            // Run it. 
            res = ExecuteSQL (upds); // Execute the UPDATE;  
            print (upds + "\n" + res + "\n"); // For debugging, see the console. 
            if (res != "") then // Non-blank result indicates an SQL error. 
                SQL_close (lq); 
                return res; 
            end if
        end if
    end for
    SQL_close (lq); 
    return "OK";
end

In the approval script, we now just call this, instead of the old function that was buggy.

    // In the FileMaker Script to approve the invoice. 
    Set Variable [ $res; ACF_Run("ApproveInvoiceLinesCalc"; InvoiceHeader::InvoiceNo)]
    if ( $res = "OK")
        // Everything went OK
    else
        // Handle the error. 
    end if

This implementation handled the unstability issue 100%.

References:

SQL functions