SI.EXPANDPROJECTESTIMATERANGE

Overview

The SI.EXPANDPROJECTESTIMATERANGE function returns an array containing one or more Sage Intacct Project Estimate IDs (based on the specified projects and range).

Syntax

CODE

=SI.EXPANDPROJECTESTIMATERANGE(ConnectionName, Project, EstimateId, Status, IsPrimary, EstimateType)

Arguments

The SI. EXPANDPROJECTESTIMATE RANGE function uses the following arguments (See Filtering Velixo Functions for how to include multiple values in a argument):

Argument	Required/ Optional	Description
`ConnectionName`	Required	The name of the connection as configured in the Connection Manager
`Project`	Optional	A range filter specifying the Project IDs to be used to determine the Estimate IDs to be displayed.
`EstimateId`	Optional	A range filter specifying the IDs of the Estimates to be displayed.
`Status`	Optional	An indicator of which status(es) of project to include in the results. `"A"` - active `"I"` - inactive `"F"` - finalized A combination may be used. The default is `"AIF"` to include all projects.
`IsPrimary`	Optional	`TRUE` or `FALSE` (default) Setting this argument to `TRUE` allows us to filter the Primary estimate when reporting on estimate forecasts (there can only be one primary estimate, and only the primary estimate includes original and change requests)
`EstimateType`	Optional	Valid options (as defined in Sage Intacct): Setting this argument allows us only to include or filter out non-forecast estimates when reporting an estimate amount over time.

Examples

For examples 1 through 3, we are assuming the following configuration within Sage Intacct:

Example 1 - all Estimate IDs for a specific project

CODE

=SI.EXPANDPROJECTETIMATERANGE("Sage","20-001")

Description
Returns all Estimate IDs used in the 20-001 project.

Results

(the ProjectRange argument has been represented by a cell reference)

Example 2 - all Estimate IDs for projects starting with the same name

CODE

=SI.EXPANDPROJECTESTIMATERANGE("Sage","HAWK???")

Description
Returns all Estimate IDs for projects whose ID begins with HAWK

Results

Example 3 - all Estimate IDs for multiple projects

CODE

=SI.EXPANDPROJECTESTIMATERANGE("Sage","20-001;20-004")

Description
Returns all Estimate IDs used in the two specified projects.

Results

Example 4 - all Estimate IDs with a specific status

Assuming the following configuration within Sage Intacct:

CODE

 =SI.EXPANDPROJECTESTIMATERANGE("Sage",,"FI")

Description
Returns all Estimate IDs with a status of either Finalized or Inactive

Results

For examples 5 and 6, we are assuming the following estimates for Project Test-01:

Example 5 - exclude Draft estimates from the results

CODE

=SI.EXPANDPROJECTESTIMATERANGE(Connection,"Test-01",,,,"*;-draft")

Description
Returns the non-draft Estimate IDs for project Test-01.

Results

(the ProjectRange argument has been represented by a cell reference)

If an estimate is not assigned an Estimate Type, it will be filtered out when Draft types are filtered out.

Example 6 - include only the Primary estimate for a project

CODE

=SI.EXPANDPROJECTESTIMATERANGE(Connection,"Test-01",,,TRUE)

Description
Returns the primary Estimate ID for project Test-01.

Results

(the ProjectRange argument has been represented by a cell reference)

Troubleshooting

When using the EstimateType argument, if either:

The filter specified in the argument eliminates all possible records OR
There is an invalid type specified in the argument

The ExpandProjectEstimateRange function will return #VALUE!

(all possible records eliminated)

(filter includes an invalid type)

along with the message:

No 'Estimate type' matches the requested value or range