Skip to content

Get

Executes the specified query and returns the results as a DataTable instance.

$Database.Get(query: Query):DataTable (Query, DataTable)

Parameters

Query query
    The query to execute.

Returns

A DataTable instance containing the query results.

Remarks

Without Parameters.TargetDataStore, the proxy resolves the script context's default data store. Repository errors are converted to script errors. The returned table is mutable in memory; call DataTable.Save only when the intended changes should be persisted.

Basic Query

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
var myTable = $Database.Get({
    Parameters : {
        TargetSchema : 'HR',
        TargetTable : 'Groups'
    },
    Where : {
        Criteria : [
            {
                Name : 'Name',
                Value : 'Administrators'
            }
        ]
    },
    Order : [
        {Name : 'Order', Type : 'Ascending'}
    ]
});

In the example below, the

Groups table is queried. The result is ordered in descending order and contains only one row because Start is set to 0 and MaxLength is set to 1. 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
var myTable = $Database.Get({
    Parameters : {
        TargetSchema : 'HR',
        TargetTable : 'Groups'
    },
    Columns : [
        {Name : 'Order'}
    ],
    Start : 0,
    MaxLength : 1,
    Where : {
        Criteria : [
            {Name : 'Name', Value : 'Administrators', Comparison : 'Different'}
        ]
    },
    Order : [
        {Name : 'Name', Type : 'Descending'}
    ]
});

If you need a more complex query containing multiple

AND and OR conditions, such as (X OR Y OR Z) AND W, you can use the Blocks option. See the code sample below. 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
var myTable = $Database.Get({
    Parameters : {
        TargetSchema : 'HR',
        TargetTable : 'UserLogons'
    },
    Where : {
        Blocks : [{
            Condition : 'And',
            Criteria : [{
                Name : 'User.EMailAddress',
                Value : '...',
                Condition : 'Or'
            }, {
                Name : 'User.EMailAddress',
                Value : '...',
                Condition : 'Or'
            }, {
                Name : 'User.EMailAddress',
                Value : '...'
            },]
        }],
        Criteria : [{
            Name : 'User.Disabled',
            Value : 'true',
            Condition : 'And',
            Comparison : 'Different'
        }]
    }
});

In the query below, the days gained by an employee are selected using the

Sum function. 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
var daysGained = $Database.Get({
    Parameters : {
        TargetSchema : 'HR',
        TargetTable : 'LeaveAllowance'
    },
    Columns : [{
        Name : 'DaysGained',
        Expression : "Sum(DaysGained)"
    }],
    Start : 0,
    MaxLength : 1,
    Where : {
        Criteria : [{
            Name : 'Employee',
            Value : $Initiator.Id
        }]
    }
});

$Xml.SetValue('GeneralInfo/EarnedDays', daysGained.Rows()[0]["DaysGained"]);

// OR

totalLeaves.Each(function () {
    $Xml.SetValue('GeneralInfo/EarnedDays', this.DaysGained);
});

Info

In the example below, supplier offers are grouped by day along with their counts.

Query with Expression - Advanced

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
var offerCountByDayTable = $Database.Get({
    Parameters : {
        TargetSchema : 'OFR',
        TargetTable : 'Offers'
    },
    Columns : [{
        Name : "Day",
        Expression : "DateTrunc('Day', OfferDate)"
    },
        {
            Name : "Amount",
            Expression : "Count(DateTrunc('Day', OfferDate))"

        }],
    Order : [
        {Name : "DateTrunc('Day',OfferDate)", Type : 'Ascending'}
    ]
});

offerCountByDayTable.Each(function () {
    console.info(this.Amount + ' offers made on the date ' + this.Day.toISOString());
});

See Also