Looking for help?
Accounting Seed Financial Cube Formula (ASFC)
In order to support the custom ASFC formula for Accounting Seed orgs, you’ll need to create a custom formula in your VBA editor as follows:
- Click on Developer -> Script Editor in Excel
- Create the following functions in VBA editor:
Public Function ASFC(ledger As String, glAccount As String, glav1 As String, glav2 As String, glav3 As String, glav4 As String, period As String, amt_type As String) Dim addin As Office.COMAddIn Dim automationObject As Object Set automationObject = Application.COMAddIns("TaralexLLC.SalesforceEnabler").Object '------------------VALIDATIONS---------------------- If IsNull(ledger) Or IsEmpty(ledger) Then ASFC = "Ledger is required" End End If If IsNull(glAccount) Or IsEmpty(glAccount) Then ASFC = "GL Account is required" End End If If IsNull(glav1) Or IsEmpty(glav1) Then glav1 = "NONE" End If If IsNull(glav2) Or IsEmpty(glav2) Then glav2 = "NONE" End If If IsNull(glav3) Or IsEmpty(glav3) Then glav3 = "NONE" End If If IsNull(glav4) Or IsEmpty(glav4) Then glav4 = "NONE" End If If IsNull(period) Or IsEmpty(period) Then ASFC = "Period is required" End End If If IsNull(amt_type) Or IsEmpty(amt_type) Then ASFC = "Amount Type is required" End End If If Not amt_type = "OPB" And Not amt_type = "BUD" And Not amt_type = "MTD" And Not amt_type = "YTD" Then ASFC = "Invalid parameter: 'Amount type'" End End If '------------------END VALIDATIONS---------------------- If amt_type = "OPB" Then q_amt_type = " sum(AcctSeed__Opening_Balance__c) " End If If amt_type = "BUD" Then q_amt_type = " sum(AcctSeed__Amount__c) " End If If amt_type = "MTD" Then q_amt_type = " sum(AcctSeed__Current_Period__c) " End If If amt_type = "YTD" Then q_amt_type = " sum(AcctSeed__Year_To_Date__c) " End If If glav1 = "NONE" Then q_glav1 = "and AcctSeed__GL_Account_Variable_1__c = NULL " Else If glav1 = "ALL" Then q_glav1 = " " Else q_glav1 = "and AcctSeed__GL_Account_Variable_1__r.Name = '" + glav1 + "' " End If End If If glav2 = "NONE" Then q_glav2 = "and AcctSeed__GL_Account_Variable_2__c = NULL " Else If glav2 = "ALL" Then q_glav2 = " " Else q_glav2 = "and AcctSeed__GL_Account_Variable_2__r.Name = '" + glav2 + "' " End If End If If glav3 = "NONE" Then q_glav3 = "and AcctSeed__GL_Account_Variable_3__c = NULL " Else If glav3 = "ALL" Then q_glav3 = " " Else q_glav3 = "and AcctSeed__GL_Account_Variable_3__r.Name = '" + glav3 + "' " End If End If If glav4 = "NONE" Then q_glav4 = "and AcctSeed__GL_Account_Variable_4__c = NULL " Else If glav4 = "ALL" Then q_glav4 = " " Else q_glav4 = "and AcctSeed__GL_Account_Variable_4__r.Name = '" + glav4 + "' " End If End If q_ledger = EscapeQuote(ledger) q_glAccount = EscapeQuote(glAccount) q_period = EscapeQuote(period) query = "SELECT " + q_amt_type + " FROM AcctSeed__Financial_Cube__c WHERE AcctSeed__Ledger__r.Name = '" + q_ledger + _ "' and AcctSeed__GL_Account__r.Name = '" + q_glAccount + _ "' and AcctSeed__Accounting_Period__r.Name = '" + q_period + _ "' " + q_glav1 + q_glav2 + q_glav3 + q_glav4 ar = automationObject.RetrieveData(query, False, False, errorText) If errorText = "The column(s) you selected in your query is (are) empty in all the records." Then ASFC = 0 ElseIf Not errorText = Empty Then ASFC = errorText Else ASFC = ar(0, 1) End If End Function Function EscapeQuote(value As String) Test = Replace(value, "'", "/'") EscapeQuote = Test End Function
Now if you want this macro to appear in all documents that you open on this computer click on File -> Save As and save the document as an Excel Add-In:
With these formulas you can create comprehensive Financial reports: