SI.QUERY

Overview

To get the data from your Intacct instance into your workbook, you can use the SI.QUERY function, which returns the contents of the specified Sage object (as either an Excel array or - optionally - as an Excel table).

Syntax

=SI.QUERY(
    ConnectionName,
    Object,
    Filter,
    Select,
    IncludeHeader,
    Settings,
    OutputColumn1,
    ...
)

Arguments

=SI.OBJECTDEFINITION(
    "Sage",
    "INVDOCUMENT (Adjustment Decrease Value)"
)

=SI.QUERY(
    "Sage",
    "TaxDetail",
    "DESCRIPTION LIKE('%Sales%')",    "RecordNo,DetailID,Description,TaxType,Value,Include,GLAccount,TaxAuthority,Status",
    HSTACK(
        {"Sort"; "Limit"; "CaseInsensitive"},
        {"RecordNo:DESC"; 200; TRUE}
    )
)

Excel Online

Querying subtypes of objects

The SI.QUERY function can retrieve data for specific subtypes of objects in Sage Intacct. This is particularly useful for template objects with multiple variations, such as:

• SODOCUMENT (Contract Change Order, Project Contract Invoice)

• PODOCUMENT (Purchase Order, Subcontract Invoice)

• INVDOCUMENT (Adjustment Decrease, Inventory Receipt)

Examples

First 10 records

=SI.QUERY(
    "Sage",
    "GLBatch",
    ,
    ,
    ,
    10
)

Description:
Returns the top 10 records returned by the GLBatch object (after any filtering is applied).

Result:

Filter example 1

=SI.QUERY(
    "Sage",
    "TaxSolution",
    "TaxMethod='VAT or GST'",
    "SolutionID,TaxMethod"
)

Description:
Returns the SolutionID and TaxMethod fields from the TaxSolution object where the TaxMethod field is set to VAT or GST:

Result:

Filter example 2

=SI.QUERY(
    "Sage",
    "TaxDetail",
    "DESCRIPTION LIKE('%Sales%')", "RecordNo,DetailID,Description,TaxType,Value,Include,GLAccount,TaxAuthority,Status"
)

Description:
Returns the specified fields (in the order specified) from the TaxDetail object where the Description field contains the case-sensitive substring Sales

Result:

Include columns from related objects

=SI.QUERY(
    "Sage", 
    "Project", 
    (PROJECTID='22-002'), 
    "PROJECTID,NAME,MANAGER.EMPLOYEEPOSITION.NAME"
)

Description:
This SI.QUERY example queries the PROJECT object and returns 3 columns for a single, specified ProjectID.

The third specified field (NAME) originates from the EMPLOYEEPOSITION object that is related to the MANAGER object which is then related to the PROJECT object (demonstrating multi-level "lookups").

Here are the relationships of the related objects for the above example:

• From the PROJECT object, the Employee relationship relates to object: MANAGER

• From the MANAGER object: the Employee position relationship relates to the EMPLOYEEPOSITION object

• The field NAME is then used from the EMPLOYEEPOSITION Object to return the name of the Employee Position

Send output to an Excel table

This and other examples of creating Excel tables can be found in Table Mirroring.

=SI.QUERY(
    "Sage",
    "Project",
    ,
    "RECORDNO,PROJECTID,PROJECTTYPE,NAME",
    TRUE,
    ,
    A5,
    B5,
    D5,
    C5
)

Description:
Instead of displaying the results of the query starting in the cell containing the SI.QUERY function, the function Cell A2 displays the specified fields from the Project object in an Excel data table located in the cells specified by the OutputColumns arguments (cells A5, B5, D5, and C5)

Aggregation examples

Aggregate a single field

=SI.QUERY(
    "Sage",
    "Project",
    ,
    "PROJECTTYPE, SUM(ACTUALQTY)"
)

Displays a sum of ACTUALQTY grouped by PROJECTTYPE

Multiple aggregations for a single field

=SI.QUERY(
    "Sage",
    "Project",
    ,
    "PROJECTTYPE, SUM(ACTUALQTY), AVG(ACTUALQTY)"
)

Displays a sum and average of ACTUALQTY grouped by PROJECTTYPE

Aggregations for multiple fields

=SI.QUERY(
    "Sage",
    "Project",
    ,
    "PROJECTTYPE, SUM(ACTUALQTY), SUM(BUDGETAMOUNT)"
)

Displays a sum of both ACTUALQTY and BUDGETAMOUNT grouped by PROJECTTYPE

Aggregation with multiple levels of grouping

=SORT(
    SI.QUERY(
        "Sage",
        "PROJECT",
        ,
        "MANAGERCONTACTNAME,PROJECTTYPE,SUM(ACTUALQTY)",
        FALSE
    ),
    {1,2,3},
    1
)

Displays a sum of ACTUALQTY grouped first by MANAGERCONTACTNAME and then by PROJECTYPE. The Excel SORT function is used to combine the grouped aggregations.

You can change the order of the fields being retrieved in order to change how the data is grouped.

=SORT(
    SI.QUERY(
        "Sage",
        "PROJECT",
        ,
        "PROJECTTYPE, MANAGERCONTACTNAME, SUM(ACTUALQTY)",
        FALSE
    ),
    {1,2,3},
    1
)

Displays a sum of ACTUALQTY grouped first by PROJECTYPE and then by MANAGERCONTACTNAME.

Unsupported objects

The objects listed below do not use the ID or RECORDNO field, and consequently cannot be queried using the SI.QUERY function.

• ACCOUNTLABEL

• APCLOSESUMMARY

• APDETAIL

• APOPENSUMMARY

• APPOSTEDADVANCEENTRY

• APPYMTENTRY

• APRECORD

• ARCLOSESUMMARY

• ARDETAIL

• AROPENSUMMARY

• ARPOSTEDOVERPAYMENTENTRY

• ARPYMTENTRY

• ARRECORD

• ASSIGNENTITYTOBANKTRANSACTION

• BANKTXNRULERUN

• BASEACCOUNT

• BILLABLEEXPENSES

• BILLBACKTEMPLATE

• BUDGETHEADER

• CHECKLAYOUT

• CMDETAIL

• CMRECORD

• COGSCLOSEDJE

• COMPANY

• COMPLIANCEDEFASSOCIATIONS

• COMPLIANCERECORDDETAIL

• CONTRACTPAYMENTSCHEDULE

• CONTRACTRSLVADDLDATA

• CONTRACTSCHEDULESRESOLVE

• CONTRACTSCHFORECAST

• COSTTYPENGROUPPICK

• COSTTYPEPICK

• CREDITACCTRECON

• CUSTOMERCREDITCARD

• DEGLPOSTING

• DEGLSUBTOTALPOSTING

• DEPRSCHRUNSUMMARY

• DOCRECALLS

• DOCUMENT

• DOCUMENTENTRYTRACKDETAIL

• DOCUMENTPARINVGL

• DOCUMENTPARPRGL

• DOCUMENTPARSUBTOTAL

• DUNNINGCUSTOMER

• DUNNINGNOTICE

• EEDETAIL

• EERECORD

• ENTITYGLGROUP

• EXPENSESAPPROVAL

• FILE1099

• FINANCIALINSTITUTION

• FIXEDASSET

• GCCONSOLIDATIONENTRY

• GENERATERECEIPT

• GIRUNSUMMARY

• GLBUDGET

• GLIETPOSTING

• GLOBALRUNOBJECTSUMMARY

• GLRESTRICTIONRELEASE

• INTEGRATIONDETAIL

• INVRECURDOCUMENT

• INVRECURSUBTOTALS

• ITEMVENDOR

• KITCOSTING

• OAUTHUSER

• PCLTASK

• PODOCUMENTAPPROVAL

• PRENTRY

• PRENTRYCURRDETAIL

• PRGLPOSTING

• PRIORPERIODCOGSPOSTING

• PROJECTTOTALS

• PRRECORD

• PRTAXENTRY

• RUNOBJECTSUMMARY

• SUBSIDIARY

• SUMMARYBYENTITY

• TASKNGROUPPICK

• TASKPICK

• TASKRESOURCEBILLINGRATE

• TSVARGLRESOLVE

• WIPACTUALSDRILLDOWN

• WIPPROJECTMANAGERFORECAST

• WIPSETUP

• WIPSETUPACCOUNT