SI.EXPANDPROJECTRANGE

Overview

The SI.EXPANDPROJECTRANGE function returns an array containing one or more Project IDs (based on the specified range).

Syntax

=SI.EXPANDPROJECTRANGE(
    ConnectionName,
    Project,
    ProjectType,
    IncludeInactive
)

Arguments

The SI. EXPANDPROJECTRANGE function uses the following arguments:

Argument	Required/Optional	Description
`ConnectionName`	Optional	Provide one of the following values: Connection name as defined in Connection Manager. Velixo range expression defining a set of connection names. An array of settings as described in the Multiple connections per formula article. We recommend using the VX.SETTINGS function to construct the array. OR Omit the argument to return results for all compatible connections with default aggregation settings.
`Project`	Optional	A range filtering specifying the projects to be displayed.
`ProjectType`	Optional	`1` - include projects both with and without children (default) `2` - include only "parent" projects (with at least one child project) `3` - include only "leaf" projects (those without any child projects)
`IncludeInactive`	Optional	`TRUE` (default) or `FALSE` Determines whether or not to include entries with a Status field set to Inactive in the results. (available in version 2023.8 and higher) More info about Status

Examples

Example #1

=SI.EXPANDPROJECTRANGE(
    "Sage"
)

Description
Returns the Project ID for every project in the specified connection.

Results

https://s3.ca-central-1.amazonaws.com/cdn.velixo.com/helpdesk/k8JYQ62SSXDB9lBPWpINuBE6aWpZUPRn8w.png

Example #2

=SI.EXPANDPROJECTRANGE(
    "Sage", 
    "IN???"
)

Description
Returns all Project IDs starting with "IN".

Results

https://s3.ca-central-1.amazonaws.com/cdn.velixo.com/helpdesk/-nUlz0yfWhqgzIzd3oLdCb2wMuywmpDPoA.png

Example #3

=SI.EXPANDPROJECTRANGE(
    "Sage", 
    "*;-100??"
)

Description
Returns all Project IDs that do not start with "100".

Results

https://s3.ca-central-1.amazonaws.com/cdn.velixo.com/helpdesk/SByL_QKI2s-pZNDGecBJbCvvTR1n3RpW9g.png

Example #4

Assuming the following configuration within Sage Intacct:

https://s3.ca-central-1.amazonaws.com/cdn.velixo.com/helpdesk/nQG5H78y5jSSs8LYnvW7tFtwGDsKzTugzg.png

=SI.EXPANDPROJECTRANGE(
    "Sage",
    ,
    2,
    FALSE
)

Description
Returns the Project IDs for all active parent projects.

Results

https://s3.ca-central-1.amazonaws.com/cdn.velixo.com/helpdesk/7kecCoLaiaKGPkexO3zFzAMnV4J7exa0tA.png

The ID for the only other parent project - 10000 - is not included

About Project Statuses

Please note that Sage Intacct tracks two separate status values for each project.

https://s3.ca-central-1.amazonaws.com/cdn.velixo.com/helpdesk/1Eb3ZLfgYWv8DmwAE0jPZwL4Y0Ra5e-kDg.png

Status - for tracking whether a project is currently Active or Inactive:

and

Project Status - for tracking additional details about the projects.

https://s3.ca-central-1.amazonaws.com/cdn.velixo.com/helpdesk/7eqplk6Z1BaMGBSKdB6pFP3pteyOiAnGyQ.png

The IncludeInactive argument of the SI.EXPANDPROJECTRANGE function references the STATUS (active/inactive) field (not the Project Status field).