Panorama > Data Types > Arrays > Data Arrays
 
dataarraybuild(
dataarraybuild(
DATABASE
,
FORMULA
,
QUERY
)

The dataarraybuild( function builds a data array by scanning a database and creating an array element for every record (including invisible records) in the database (see Data Arrays).

Parameters

This function has three parameters:

database – is the database that will be scanned. This database must be currently open. If this parameter is "" then the current database will be scanned. Note: The dataarraybuild( function will scan every record in the database, including records that are not currently selected. If you want to build an array from only selected records, use the dataarrayselectedbuild( function.

formula – is the formula that will be used to extract data from the database and build each array element. The entire formula must be enclosed in quotes (see Quotes). The formula usually references fields in the database being scanned. It may also use the seq( function to include the number of each record (within the scan).

query – is an optional formula that determines whether a record should be included in the output array. For example, the query formula Price > 100 could be used if you only wanted to include items with a price greater than 100 in the array. The query parameter can be omitted, in which case all records will be included.

Description

This function creates a Data Array by scanning contents of a database. This function scans ALL records in the database, whether they are visible or not. If you only want to scan visible (selected) records, use the dataarrayselectedbuild( function.

The formula parameter allows you to customize the construction of the array.

dataarraybuild("",{upper(First)}) ☞ BOB,SUE,MARK,STAN,RALPH

You can use any formula you want, but don’t forget that the formula must be quoted. In the examples above the { and } characters are used for quoting, but Panorama has several different quoting options (see Quotes). Here are some other examples using different quoting characters.

dataarraybuild("","upper(First)")
dataarraybuild("",|||upper(First)|||)

It’s usually most convenient to pick a quote character that isn’t actually used in the formula itself.

Since data arrays can contain any type of value, the formula result can be any type of value (in contrast to the arraybuild( function, which only works with text values). This formula builds an array of all of the prices in a price list.

dataarraybuild("Price List",{Price})

You can add a second, optional formula to limit the records that will be included in the output. In this example, only fish that are less than $1.50 will be included in the array.

dataarraybuild("Fish Tank",{Fish},{Price < 1.50}) ☞ Goldfish, Guppy, Neon

This example generates a list of fish that cost more than $30.

dataarraybuild(“Fish Tank”,{Fish},{Price>30}) Boeseman’s Rainbowfish,Black Ghost Knifefish,Harlequin Rasbora

Referencing Fields in the Original Database

Sometimes you may want to reference fields in the current database, especially in the query formula. For example, suppose you have an Invoice database that contains customer names in a Name field, and a Customer database that contains First and Last name fields. This formula can be used in the Customer database to display a list of invoices for the person currently selected in the Customer database.

dataarraybuild(cr(),"Invoice",
    { datepattern(Date,"MM-DD-YYYY")+" "+pattern(Total,"$#,.##") },
    { Name = ««First»»+" "+««Last»» }

Notice that the First and Last fields, used in the last line of this example, are enclosed in double chevrons. The use of double chevrons specifies that a field is in the current database, instead of the database that is being scanned. There are three fields referenced in this formula:

Name ☞ in Invoice database
First ☞ in Customer database (because of double chevrons)
Last ☞ in Customer database (because of double chevrons)

The result of this formula will be a list of invoices for the current customer, something like this:

08-14-2014 $3,498.93
09-27-2014 $1,723.05
11-05-2014 $5,381.60

Advanced Note: Internally, Panorama actually converts double chevrons into the fieldvalue( function. So the example above is internally converted into this:

dataarraybuild(cr(),"Invoice",
    { datepattern(Date,"MM-DD-YYYY")+" "+pattern(Total,"$#,.##") },
    { Name = fieldvalue("Customer",First)+" "+fieldvalue("Customer",Last) }

The double chevron notation is a convenience, making this formula much easier to write, and to understand.

See Also

History

VersionStatusNotes
10.0NewNew in this version.