XCustomerOrder

XCustomerOrder() - Total amount of customer orders for a given period

Description

  This function is only available for Sage 50 Canadian edition

This function returns the sum of all customer's order amounts made between two specific dates.

Parameters

Customers [Optional]

Customer name for which the sum of all orders amounts is required.

By default, when this parameter remains empty, the sum of all customers' order amounts will be generated.

Beginning [Optional]

Beginning date of the period for which the amount is requested. If omitted, it will return the balance of the account(s).

When entering dates in Excel, use the format for your computer's regional settings or use the ISO format (YYYY-MM-DD).

End [Optional]

Ending date of the period for which the amount is requested. If omitted, it will use the latest transaction date.

When entering dates in Excel, use the format for your computer's regional settings or use the ISO format (YYYY-MM-DD).

Status [Optional]

Order status required: initial order, received order or remaining order. By default the remaining order amount is returned.

• Initial order returns the sum of all initial order amounts.
• Received order returns the sum of all invoiced order amounts.
• Remaining order returns the sum of all order amounts that remain to be invoiced.

Adding the word cleared to the request will include all amounts of orders already cleared in the system.

Accounts [Optional]

General ledger account number or name for which the sum of all

transactions is requested.

If this parameter remains empty, the Current Earnings value will be reported.

See accounts parameter for more information on how you can use this parameter.

Departments [Optional]

Ending part of the General Ledger Account Number for which the sum of all

transactions is requested. Use this parameter to control further the accounts to be used by looking at the end of the account number.

If this parameter remains empty, only the accounts parameter will be used.

See the departments parameter for more information on how you can use this parameter.

Employees [Optional]

Employee name (Salesperson) for which the sum of all orders amounts is requested.

Inventory [Optional]

Inventory items number for which the sum of all orders amounts is requested.

Projects [Optional]

Project name for which the budget is requested.

Currency [Optional]

Currency code for which the sum of all order amounts is requested, for example, CAD or USD.

Freight [Optional]

Included to obtain the sum of all order amounts, including freight fees, or Excluded to exclude freight fees. Freight is included by default unless an inventory item is specified

Connections [Optional]

Sage 50 Connection to query. If left empty, the default connection will be used.

Usage

All past and future transactions can be considered in this function. You just need to mention the beginning of the first transaction date and the end of the last transaction date. The function XCustomerOrder will calculate the sum of all order amounts between those dates.

Examples

It is strongly recommended to use Excel cells address (ex. A1:B19) containing the information of the parameters requested in all XLGL functions, such as customers' names, dates, account numbers, department codes, employees' names, etc. This practice adds flexibility to your worksheet and will ensure the coherence of your results. Moreover, the use of quotation marks (") is essential when the parameters are manually written in the arguments function window.

Returns the sum of all customers' order amounts

=XCustomerOrder()

Returns the sum of all customer orders made by Kent industries Inc. with the status "initial order" in the account number "4010-4090"

=XCustomerOrder("Kent Industries Inc.",,," Initial order", "4010-4090")

Returns the sum of all orders amounts made by Kent industries Inc. in account number 4020 between January 1st and December 31st of 2006, including freight fee

=XCustomerOrder("Kent Industries Inc.","01/01/2006","31/12/2006","Initial Order","4020",,,,,,"Included")