Financial designs

Essentially, a financial design is an Excel worksheet that incorporates labels, ActivReporter functions, and Excel formulas to produce a customized financial statement. Because the functions are generalized, you can reuse a financial design from period to period and year to year.

Note

Financial designs always create a file in a certain baseline Excel file format regardless of which version of Excel is installed on your workstation. This means users can generally open and work with financial design worksheets no matter which version of Excel is installed.

Financial design worksheets can be created and stored in ActivReporter or external to it. There are several advantages to maintaining your designs within ActivReporter:

  • Worksheets are backed up with the rest of the ActivReporter database.
  • It is easy to share your worksheets with other ActivReporter users.
  • Worksheets can be secured with standard ActivReporter security.
  • Worksheets are versioned so that historical worksheets can be viewed and restored.

Preparing to design a financial statement

Before you start designing a financial, examine your current financial statement. Note that it consists of rows and columns. Each row and each column on the statement has an identifying characteristic or rule that can be associated with the row or column.

Rows typically identify which account or group of accounts appear on that row. In most cases, the group of accounts relates to a rollup code. Rows may also contain formulas which total sections of the report.

Columns usually either contain descriptions of lines, financial data, budget data, or calculations (such as variances and/or percentages). This type of information is retrieved from ActivReporter via financial functions. Often the formula for all cells in a column section is the same so that you can enter a formula in the cell at the top of the column section and drag down to copy the formula to all rows of that section of the financial.

The purpose of the remaining information in this topic is to help you translate your paper statements into generalized designs which can be used today and for years to come.

Create a financial design record

Work with design versions

Load a design from an external location

Save a design to an external location

Delete a financial design record

x

Account Conditions

Condition Expression

Enter a SQL condition statement here to exclude or include information from the financial view. Alternatively, you can use the table below to describe the conditions you need and ActivReporter will build and add the conditions to this text box.

Conditions table
Use this table to construct the conditions you need in order to accurately select information for the financial view.
Item
From the drop-down list, select the account entity, or the segment, rollup, department, etc., to define a range or mask on. The items in the dropdown are filtered based on the selected function and are represented by their fully qualified names.
Mask

Enter a mask for the selected item values when the values you need to select have common characters but are not in a particular order.

Press F3 to look up the value.

If a value is already selected, you can press F4 to open the record in its native editor.

Begin

Enter the first value in the range of item values you need to select. Use a range when the values are in a defined order.

Press F3 to look up the value.

If a value is already selected, you can press F4 to open the record in its native editor.

End

Enter the last value in the range of item values you need to select. Use a range when the values are in a defined order.

Press F3 to look up the value.

If a value is already selected, you can press F4 to open the record in its native editor.

Check Conditions button
Click this button to check for SQL errors in the condition statement.

 

x

Financial Functions

Activity

Calculates the activity of a set of accounts as of the specified period.

Activity(Period,Items1,Items2,Company,Unit)

Examples

Activity("Current - 1 year", "Inc = Phone", "Ledger=A")

Retrieves the net activity of last year's current month for the phone expense accounts of ledger A.

Activity("Current", "GL.Account=1000-00-A")

Retrieves the net activity of the current month for account 1000-00-A.

-Activity("Current - 1", "Inc=Rev", "Ledger=B")

Retrieves the net activity of the budget for the revenue accounts in the previous period. The minus (-) in front changes the credit sign to positive for printing purposes.

Activity("Current", "Inc=" & $A22, "Ledger=A")

Retrieves the net activity in the current period for expense accounts that match the "Inc" rollup item in cell A22. Only accounts in ledger A are included.

Activity(" ", $A6 & "=" & $B6, "Ledger="D$1)

Retrieves the net activity in the current period for the accounts that match the rollup item specified in cell B6 of the rollup specified in cell A6. Only accounts that contain the ledger item in cell D1 are included.

Activity(" ", "Inc=" & $A6 & " and Dept=12", "Ledger="D$1)

Retrieves the net activity in the current period for the accounts that match the rollup item in cell A6 of "Inc" and also assigned to Dept 12. Only accounts that contain the ledger item in cell D1 are included.

Round(Activity("Current", "Inc=Supply", "Ledger=A"), 2)

Rounds the current activity of the supply expense accounts to even dollars (the two decimal places are rounded). Round is a standard Excel function.

Balance

Calculates the balance of a set of accounts as of the specified period.

Balance(Period,Items1,Items2,Company,Unit)

Examples

Balance("Current", "Bal=CASH", "Ledger=A")

Retrieves the balance of the accounts in the "Bal" rollup with a rollup item of "CASH" as of the ending date of the current period.

-Balance("Period 1 of Year", "Inc=Rev", "Ledger=B")

(Total year's budget amount for a revenue account) Reverses the sign of the balance retrieved for the only period in a "Year" calendar for the accounts with a rollup item of "Rev" in the "Inc" rollup.

BeginBalance

Calculates the beginning balance of a set of accounts as of the specified period.

BeginBalance(Period,Items1,Items2,Company,Unit)

YearToDate

Calculates the year-to-date activity of a set of accounts as of the specified period.

YearToDate(Period,Items1,Items2,Company,Unit)

Examples

YearToDate("Current", "Inc=Supplies", "Ledger=A")

Returns the year-to-date through this month of the office supply expenses.

YearToDate("Period 12", "Inc=Phone", "Ledger=B")

(Alternate method for reporting the total year's budget amount) Retrieves the end-of-year balance of the accounts with the rollup item "Phone" in the "Inc" rollup.

YearToDate("Period 12", "Inc=Phone", "Ledger=B")*PeriodNumber("")/12

(Calculates year-to-date budget amount from total year's budget amount) Retrieves the total year budget, then multiplies it by the period number and divides by 12.

DebitActivity

Calculates the debit activity of a set of accounts as of the specified period.

DebitActivity(Period,Items1,Items2,Company,Unit)

Example

DebitActivity("Period 12 - 1 year", "Inc=Phone", "Ledger=A")

(Debits for the period) Retrieves the debit activity for the previous year's last period of the accounts with a rollup item of "Phone" in the "Inc" rollup. Only accounts in ledger A are included.

DebitBalance

Calculates the debit balance of a set of accounts as of the specified period.

DebitBalance(Period,Items1,Items2,Company,Unit)

DebitYearToDate

Calculates the year-to-date debit activity of a set of accounts as of the specified period.

DebitYearToDate(Period,Items1,Items2,Company,Unit)

CreditActivity

Calculates the credit activity of a set of accounts as of the specified period.

CreditActivity(Period,Items1,Items2,Company,Unit)

Example

CreditActivity("Period 12 - 1 year", "Inc=Phone", "Ledger=A")

(Credits for the period) Retrieves the credit activity for the previous year's last period of the accounts with a rollup item of "Phone" in the "Inc" rollup. Only accounts in ledger A are included.

CreditBalance

Calculates the credit balance of a set of accounts as of the specified period.

CreditBalance(Period,Items1,Items2,Company,Unit)

CreditYearToDate

Calculates the year-to-date credit activity of a set of accounts as of the specified period.

CreditYearToDate(Period,Items1,Items2,Company,Unit)

 

x

Segments Functions

Segment

Returns the name of the specified segment.

Segment(SegmentCode,Company)

SegmentPluralName

Returns the plural name of the specified segment.

SegmentPluralName(SegmentCode,Company)

SegmentMemo

Returns the memo of the specified segment.

SegmentMemo(SegmentCode,Company)

SegmentCustomField

Returns the value of a custom field for the specified segment.

SegmentCustomField(CustomField,SegmentCode,Company)

SegmentItem

Returns the segment item of the specified segment and optional item.

SegmentItem(Segment,Item,Company)

SegmentItemDescription

Returns the description of the segment item for the specified segment and optional item.

SegmentItemDescription(Segment,Item,Company)

Examples

SegmentItemDescription("Inc", "REV")

(Line description) Retrieves the description of the "REV" rollup item in the "Inc" rollup.

SegmentItemDescription("Inc", A6)

Retrieves the description of the rollup item in cell A6 of the "Inc" rollup.

SegmentItemShortDescription

Returns the short description of the segment item for the specified segment and optional item.

SegmentItemShortDescription(Segment,Item,Company)

SegmentItemName

Returns the name of the segment item for the specified segment and optional item.

SegmentItemName(Segment,Item,Company)

SegmentItemAddress1

Returns the first address line of the segment item for the specified segment and optional item.

SegmentItemAddress1(Segment,Item,Company)

SegmentItemAddress2

Returns the second address line of the segment item for the specified segment and optional item.

SegmentItemAddress2(Segment,Item,Company)

SegmentItemCity

Returns the city of the segment item for the specified segment and optional item.

SegmentItemCity(Segment,Item,Company)

SegmentItemState

Returns the state of the segment item for the specified segment and optional item.

SegmentItemState(Segment,Item,Company)

SegmentItemZipCode

Returns the Zip Code of the segment item for the specified segment and optional item.

SegmentItemZipCode(Segment,Item,Company)

SegmentItemCountry

Returns the country of the segment item for the specified segment and optional item.

SegmentItemCountry(Segment,Item,Company)

SegmentItemMemo

Returns the memo of the segment item for the specified segment and optional item.

SegmentItemMemo(Segment,Item,Company)

SegmentItemCustomField

Returns the custom field value of the segment item for the specified segment and optional item.

SegmentItemCustomField(CustomField,Segment,Item,Company)

 

x

Accounts Functions

Account

Returns the account code of the specified account.

Account(AccountCode,Company)

AccountDescription

Returns the description of the specified account.

AccountDescription(AccountCode,Company)

AccountMemo

Returns the memo for the specified account.

AccountMemo(AccountCode,Company)

AccountCustomField

Returns the value of a custom field for the specified account.

AccountCustomField(Field,AccountCode,Company)

 

x

Periods Functions

As of Period End

A hybrid Excel-ActivReporter function which produces a heading which includes the period end date.

= "As of " & TEXT(PeriodEnd(), "mmmm dd, yyyy")

For the Period Beginning

A hybrid Excel-ActivReporter function which produces a heading which includes period begin date.

= "For the Period Beginning " & TEXT(PeriodBegin(), "mmmm dd, yyyy")

For the Period Ending

A hybrid Excel-ActivReporter function which produces a heading which includes the period end date.

= "For the Period Ending " & TEXT(PeriodEnd(), "mmmm dd, yyyy")

For the Period Beginning and Ending

A hybrid Excel-ActivReporter function which produces a heading which includes the period begin and end dates.

= "For the Period Beginning " & TEXT(PeriodBegin(), "mmmm dd, yyyy") & " and Ending " & TEXT(PeriodEnd(), "mmmm dd, yyyy")


PeriodNumber

Returns the period number of the specified period.

PeriodNumber(Period,Company)

PeriodDescription

Returns the description of the specified period.

PeriodDescription(Period,Company)

PeriodYear

Returns the year of the specified period.

PeriodYear(Period,Company)

Example

PeriodYear("Current")

Prints the year in a column heading.

PeriodYearDescription

Returns the description of the year for the specified period.

PeriodYearDescription(Period,Company)

PeriodCalendar

Returns the calendar of the specified period.

PeriodCalendar(Period,Company)

PeriodCalendarDescription

Returns the description of the calendar for the specified period.

PeriodCalendarDescription(Period,Company)

PeriodCalendarMemo

Returns the memo on the calendar for the specified period.

PeriodCalendarMemo(Period,Company)

PeriodCalendarCustomField

Returns the value of the custom field on the calendar for the specified period.

PeriodCalendarCustomField(CustomField,Period,Company)

PeriodWorkdays

Returns the number of workdays in the specified period.

PeriodWorkdays(Period,Company)

PeriodBegin

Returns the beginning date of the specified period.

PeriodBegin(Period,Company)

PeriodEnd

Returns the ending date of the specified period.

PeriodEnd(Period,Company)

Examples

PeriodEnd("Current")

Retrieves the ending date of the current period.

"As of" & Text(PeriodEnd("Current"), "mmmm dd, yyyy")

Combines Excel formulas and ActivityHD functions to build a heading description like "As of March 31, 2014". The date is the end date of the current period. Text is a standard Excel function.

PeriodYearBegin

Returns the beginning date of the year for the specified period.

PeriodYearBegin(Period,Company)

PeriodYearEnd

Returns the ending date of the year for the specified period.

PeriodYearEnd(Period,Company)

 

x

Miscellaneous Functions

AccountItem

Accepts a partial account code and a ledger and returns a full account code.

AccountItem(Account,Ledger)

AccountExpression

Accepts a partial account code and a ledger and returns an expression that can be used to limit a financial function.

AccountExpression(Account,Ledger)

ItemExpression

Accepts an unlimited number of item type and item value pairs and returns an expression that can be used to limit a financial function.

ItemExpression(Item1,Value1,Item2,Value2,Item3,Value3,...)

Iteration
IterationItem

Accepts no arguments and returns a list of the iteration items that are applied to the financial, or accepts an item type and returns the iteration item of that type.

IterationItem(ItemType,Company)

IterationDescription

Accepts no arguments and returns a list of iteration item descriptions that are applied to the financial, or accepts an item type and returns the iteration item description of that type.

IterationDescription(ItemType,Company)

IterationShortDescription

Accepts no arguments and returns a list of iteration item short descriptions that are applied to the financial, or accepts an item type and returns the iteration item short description of that type.

IterationShortDescription(ItemType,Company)

Units
Unit

Accepts a unit and returns the code of the unit, or accepts no unit and returns the code of the currency unit from the Workbook Options dialog box.

Unit(Unit,Company)

UnitDescription

Accepts a unit and returns the description of the unit, or accepts no unit and returns the description of the currency unit from the Workbook Options dialog box.

UnitDescription(Unit,Company)

UnitMemo

Accepts a unit and returns the memo from the unit, or accepts no unit and returns the memo from the currency unit from the Workbook Options dialog box.

UnitMemo(Unit,Company)

UnitCustomField

Accepts a unit and returns the custom field value for the specified custom field on the unit, or accepts no unit and returns the custom field value for the specified custom field on the currency unit from the Workbook Options dialog box.

Unit(Field,Unit,Company)

IsLiveData

Returns "true" if the data is live; otherwise, returns "false".

IsLiveData(Company)

 

x

Company Functions

CompanyName

Returns the name of the optional company.

CompanyName(Company)

CompanyLegalName

Returns the legal name of the optional company.

CompanyLegalName(Company)

CompanyAddress1

Returns the first address line for the optional company.

CompanyAddress1(Company)

CompanyAddress2

Returns the second address line for the optional company.

CompanyAddress2(Company)

CompanyCity

Returns the city for the optional company.

CompanyCity(Company)

CompanyState

Returns the state for the optional company.

CompanyState(Company)

CompanyZipCode

Returns the Zip Code for the optional company.

CompanyZipCode(Company)

CompanyCountry

Returns the name of the optional company.

CompanyName(Company)

CompanyMemo

Returns the memo for the optional company.

CompanyMemo(Company)

CompanyCustomField

Returns the value of the custom field for the optional company.

CompanyCustomField(CustomField,Company)

 

x

Accounts Receivable Functions

ARSales

Calculates the total AR sales (revenue) for the specified period.

ARSales(Period,Items1,Items2,Items3,Company,Unit)

Example

ARSales("Current", "Main=4050 AND Ledger=A", "Event LIKE 27*")

Retrieves period total sales for events that start with "27" for the 4050 main GL account number.

ARSalesYTD

Calculates the year-to-date AR sales (revenue) for the specified period.

ARSalesYTD(Period,Items1,Items2,Items3,Company,Unit)

Example

ARSalesYTD("Current", "Main=4050 AND Ledger=A", , "ARCode=TICKETS")

Retrieves the year-to-date AR sales amount for the 4050 main GL account number limited to the "TICKETS" ARCode.

ARSalesQuantity

Calculates the total AR sales quantity for the specified period.

ARSalesQuantity(Period,Items1,Items2,Items3,Company)

ARSalesQuantityYTD

Calculates the year-to-date AR sales quantity for the specified period.

ARSalesQuantityYTD(Period,Items1,Items2,Items3,Company)

ARExtraActivity

Calculates the extra GL activity as of the specified period.

ARExtraActivity(Period,Items,Company,Unit)

ARExtraDebitActivity

Calculates the extra GL debit activity as of the specified period.

ARExtraDebitActivity(Period,Items,Company,Unit)

ARExtraCreditActivity

Calculates the extra GL credit activity as of the specified period.

ARExtraCreditActivity(Period,Items,Company,Unit)

ARExtraYearToDate

Calculates the year-to-date extra GL activity as of the specified period.

ARExtraYearToDate(Period,Items,Company,Unit)

ARExtraDebitYearToDate

Calculates the year-to-date extra GL debit activity as of the specified period.

ARExtraDebitYearToDate(Period,Items,Company,Unit)

ARExtraCreditYearToDate

Calculates the year-to-date extra GL credit activity as of the specified period.

ARExtraCreditYearToDate(Period,Items,Company,Unit)

ARCodes
ARCode

Returns the code of the specified ARCode.

ARCode(ARCode,Company)

ARCodeDescription

Returns the description of the specified ARCode.

ARCodeDescription(ARCode,Company)

ARCodeMemo

Returns the memo on the specified ARCode.

ARCodeMemo(ARCode,Company)

ARCodeCustomField

Returns the value of a custom field on the specified ARCode.

ARCodeCustomField(CustomField,ARCode,Company)

Attributes
ARAttribute

Returns the name of the specified attribute.

ARAttribute(AttributeName,Company)

ARAttributePluralName

Returns the plural name of the specified attribute.

ARAttributePluralName(AttributeName,Company)

ARAttributeMemo

Returns the memo on the specified attribute.

ARAttributeMemo(AttributeName,Company)

ARAttributeCustomField

Returns the value of a custom field on the specified attribute.

ARAttributeCustomField(CustomField,AttributeName,Company)

ARAttributeItem

Returns the name of the specified attribute item.

ARAttributeItem(AttributeName,Item,Company)

ARAttributeItemDescription

Returns the description of the specified attribute item.

ARAttributeItemDescription(AttributeName,Item,Company)

ARAttributeItemMemo

Returns the memo on the specified attribute item.

ARAttributeItemMemo(AttributeName,Item,Company)

ARAttributeItemCustomField

Returns the value of a custom field on the specified attribute item.

ARAttributeItemCustomField(CustomField,AttributeName,Item,Company)

Customers
ARCustomer

Returns the customer code of the specified AR customer.

ARCustomer(CustomerCode,Company)

ARCustomerName

Returns the name of the specified AR customer.

ARCustomerName(CustomerCode,Company)

ARCustomerMemo

Returns the memo on the specified AR customer.

ARCustomerMemo(CustomerCode,Company)

ARCustomerCustomField

Returns the value of a custom field on the specified AR customer.

ARCustomerCustomField(CustomField,CustomerCode,Company)

Customer Classes
ARClass

Returns the class code of the specified AR class.

ARClass(ARClassCode,Company)

ARClassDescription

Returns the description of the specified AR class.

ARClassDescription(ARClassCode,Company)

ARClassMemo

Returns the memo on the specified AR class.

ARClassMemo(ARClassCode,Company)

ARClassCustomField

Returns the value of a custom field on the specified AR class.

ARClassCustomField(CustomField,ARClassCode,Company)

Salespersons
ARSalesperson

Returns the salesperson code of the specified salesperson.

ARSalesperson(SalespersonCode,Company)

ARSalespersonFirstNameFirst

Returns the name of the specified salesperson with first name first.

ARSalespersonFirstNameFirst(SalespersonCode,Company)

ARSalespersonLastNameFirst

Returns the name of the specified salesperson with last name first.

ARSalespersonLastNameFirst(SalespersonCode,Company)

ARSalespersonMemo

Returns the memo on the specified salesperson.

ARSalespersonMemo(SalespersonCode,Company)

ARSalespersonCustomField

Returns the value of a custom field on the specified salesperson.

ARSalespersonCustomField(CustomField,SalespersonCode,Company)

 

x

Payroll Functions

PRExpense

Calculates the payroll expense total for the specified period.

PRExpense(Period,Items1,Items2,Items3,Company,Unit)

PRExpenseSource

Calculates the payroll expense source total for the specified period.

PRExpenseSource(Period,Items1,Items2,Items3,Company)

PRLiability

Calculates the payroll liability total for the specified period.

PRLiability(Period,Items1,Items2,Items3,Company,Unit)

PRLiabilitySource

Calculates the payroll liability source total for the specified period.

PRLiabilitySource(Period,Items1,Items2,Items3,Company)

PRExpenseYTD

Calculates the year-to-date payroll expense total for the specified period.

PRExpenseYTD(Period,Items1,Items2,Items3,Company,Unit)

PRExpenseSourceYTD

Calculates the year-to-date payroll expense source total for the specified period.

PRExpenseSourceYTD(Period,Items1,Items2,Items3,Company)

PRLiabilityYTD

Calculates the year-to-date payroll liability total for the specified period.

PRLiabilityYTD(Period,Items1,Items2,Items3,Company,Unit)

PRLiabilitySourceYTD

Calculates the year-to-date payroll liability source total for the specified period.

PRLiabilitySourceYTD(Period,Items1,Items2,Items3,Company)

PRGroupExpense

Calculates the payroll group total of expenses for the specified period.

PRGroupExpense(Group,Period,Items1,Items2,Company,Unit)

PRGroupLiability

Calculates the payroll group total of liabilities for the specified period.

PRGroupLiability(Group,Period,Items1,Items2,Company,Unit)

PRGroupExpenseYTD

Calculates the year-to-date payroll group total of expenses for the specified period.

PRGroupExpenseYTD(Group,Period,Items1,Items2,Company,Unit)

PRGroupLiabilityYTD

Calculates the year-to-date payroll group total of liabilities for the specified period.

PRGroupLiabilityYTD(Group,Period,Items1,Items2,Company,Unit)

PRExpenseCount

Calculates the count of employees with expenses for the specified period.

PRExpenseCount(Period,Items1,Items2,Items3,Company)

PRExpenseCountYTD

Calculates the count of employees with year-to-date expenses for the specified period.

PRExpenseCount(Period,Items1,Items2,Items3,Company)

PRLiabilityCount

Calculates the count of employees with liabilities for the specified period.

PRLiabilityCount(Period,Items1,Items2,Items3,Company)

PRLiabilityCountYTD

Calculates the count of employees with year-to-date liabilities for the specified period.

PRLiabilityCountYTD(Period,Items1,Items2,Items3,Company)

Attributes
PRAttribute

Returns the name of the specified payroll attribute.

PRAttribute(AttributeName,Company)

PRAttributePluralName

Returns the plural name of the specified payroll attribute.

PRAttributePluralName(AttributeName,Company)

PRAttributeMemo

Returns the memo on the specified payroll attribute.

PRAttributeMemo(AttributeName,Company)

PRAttributeCustomField

Returns the value of a custom field on the specified payroll attribute.

PRAttributeCustomField(CustomField,AttributeName,Company)

PRAttributeItem

Returns the code of the specified payroll attribute item.

PRAttributeItem(AttributeName,Item,Company)

PRAttributeItemDescription

Returns the description of the specified payroll attribute item.

PRAttributeItemDescription(AttributeName,Item,Company)

PRAttributeItemMemo

Returns the memo on the specified payroll attribute item.

PRAttributeItemMemo(AttributeName,Item,Company)

PRAttributeItemCustomField

Returns the value of a custom field on the specified payroll attribute item.

PRAttributeItemCustomField(CustomField,AttributeName,Item,Company)

Groups
PRGroup

Returns the name of the specified payroll group.

PRGroup(Group,Company)

PRGroupDescription

Returns the description of the specified payroll group.

PRGroupDescription(Group,Company)

PRGroupSourceName

Returns the source name of the specified payroll group.

PRGroupSourceName(Group,Company)

PRGroupResultName

Returns the result name of the specified payroll group.

PRGroupResultName(Group,Company)

PRGroupMemo

Returns the memo on the specified payroll group.

PRGroupMemo(Group,Company)

PRGroupCustomField

Returns the value of a custom field on the specified payroll group.

PRGroupCustomField(CustomField,Group,Company)

PRCodes
PRCodeSegment

Returns the name of the specified PRCode segment.

PRCodeSegment(Segment,Company)

PRCodeSegmentDescription

Returns the description of the specified PRCode segment.

PRCodeSegmentDescription(Segment,Company)

PRCodeSegmentPluralName

Returns the plural name of the specified PRCode segment.

PRCodeSegmentPluralName(Segment,Company)

PRCodeSegmentMemo

Returns the memo on the specified PRCode segment.

PRCodeSegmentMemo(Segment,Company)

PRCodeSegmentCustomField

Returns the value of a custom field on the specified PRCode segment.

PRCodeSegmentCustomField(CustomField,Segment,Company)

PRCodeItem

Returns the PRCode item of the specified PRCode segment and item.

PRCodeItem(Segment,Item,Company)

PRCodeItemDescription

Returns the description of the PRCode item of the specified PRCode segment and item.

PRCodeItemDescription(Segment,Item,Company)

PRCodeItemShortDescription

Returns the short description of the PRCode item of the specified PRCode segment and item.

PRCodeItemShortDescription(Segment,Item,Company)

PRCodeItemMemo

Returns the memo on the PRCode item of the specified PRCode segment and item.

PRCodeItemMemo(Segment,Item,Company)

PRCodeItemCustomField

Returns the value of a custom field on the PRCode item of the specified PRCode segment and item.

PRCodeItemCustomField(CustomField,Segment,Item,Company)

Tax Entities
PRTaxEntity

Returns the code of the specified tax entity.

PRTaxEntity(TaxEntity,Company)

PRTaxEntityDescription

Returns the description of the specified tax entity.

PRTaxEntityDescription(TaxEntity,Company)

PRTaxEntityMemo

Returns the memo on the specified tax entity.

PRTaxEntityMemo(TaxEntity,Company)

PRTaxEntityCustomField

Returns the value of a custom field on the specified tax entity.

PRTaxEntityCustomField(CustomField,TaxEntity,Company)

 

x

Purchasing Functions

POAmount

Calculates the total purchase amount for the specified period.

POAmount(Period,Items1,Items2,Company,Unit)

Example

POAmount("Current", "Main=5520 AND Ledger=A", "Event=Race AND UOM=Each") or

POAmount("Current", "Main=5520 AND Ledger=A", "Event=Race AND 'Unit of Measure'=Each").

Retrieves the current amount of purchases for the 5520 main GL account number limited to the "Race" event attribute and with "Each" as the unit of measure.

POAmountYTD

Calculates the year-to-date total purchase amount for the specified period.

POAmountYTD(Period,Items1,Items2,Company,Unit)

Example

POAmountYTD("Current", "Main LIKE 5* AND Ledger=A", "Vendor=Wholesale")

Retrieves the year-to-date amount of purchases for all GL cost accounts for the "Wholesale" vendor.

POQuantity

Calculates the total purchase quantity for the specified period.

POQuantity(Period,Items1,Items2,Company)

Example

POQuantity("Current", "Main LIKE 5* AND Ledger=A", "Product=Camera AND Event=Fair")

Returns the quantity of purchases for the current period for all GL cost accounts limited to the "Camera" product and the "Fair" event.

POQuantityYTD

Calculates the year-to-date total purchase quantity for the specified period.

POQuantityYTD(Period,Items1,Items2,Company)

Attributes
POAttribute

Returns the name of the specified purchasing attribute.

POAttribute(AttributeName,Company)

POAttributeDescription

Returns the description of the specified purchasing attribute.

POAttributeDescription(AttributeName,Company)

POAttributeMemo

Returns the memo on the specified purchasing attribute.

POAttributeMemo(AttributeName,Company)

POAttributeCustomField

Returns the value of a custom field on the specified purchasing attribute.

POAttributeCustomField(CustomField,AttributeName,Company)

POAttributeItem

Returns the name of the specified purchasing attribute item.

POAttributeItem(AttributeName,Item,Company)

POAttributeItemDescription

Returns the description of the specified purchasing attribute item.

POAttributeItemDescription(AttributeName,Item,Company)

POAttributeItemMemo

Returns the memo on the specified purchasing attribute item.

POAttributeItemMemo(AttributeName,Item,Company)

POAttributeItemCustomField

Returns the value of a custom field on the specified purchasing attribute item.

POAttributeItemCustomField(CustomField,AttributeName,Item,Company)

Departments
PODepartment

Returns the name of the specified purchasing department.

PODepartment(Department,Company)

PODepartmentDescription

Returns the description of the specified purchasing department.

PODepartmentDescription(Department,Company)

PODepartmentMemo

Returns the memo on the specified purchasing department.

PODepartmentMemo(Department,Company)

PODepartmentCustomField

Returns the value of a custom field on the specified purchasing department.

PODepartmentCustomField(CustomField,Department,Company)

Products
POProduct

Returns the name of the specified product.

POProduct(Product,Company)

POProductDescription

Returns the description of the specified product.

POProductDescription(Product,Company)

POProductMemo

Returns the memo on the specified product.

POProductMemo(Product,Company)

POProductCustomField

Returns the value of a custom field on the specified product.

POProductCustomField(CustomField,Product,Company)

Units of Measure
POUnitOfMeasure

Returns the name of the unit of measure.

POUnitOfMeasure(Unit,Company)

POUnitOfMeasureDescription

Returns the description of the unit of measure.

POUnitOfMeasureDescription(Unit,Company)

POUnitOfMeasureMemo

Returns the memo on the unit of measure.

POUnitOfMeasureMemo(Unit,Company)

POUnitOfMeasureCustomField

Returns the value of a custom field on the unit of measure.

POUnitOfMeasureCustomField(CustomField,Unit,Company)

Vendors
Vendor

Returns the name of the specified vendor.

Vendor(VendorCode,Company)

VendorDescription

Returns the description of the specified vendor.

VendorDescription(Vendor,Company)

VendorMemo

Returns the memo on the specified vendor.

VendorMemo(Vendor,Company)

VendorCustomField

Returns the value of a custom field on the specified vendor.

VendorCustomField(CustomField,Vendor,Company)