FRTRN Function

Sage 300 ERP

FRTRN Function

This function retrieves net amounts and net quantities from posted transactions for a specified period. It retrieves actual values and summary totals (by both account and transaction fields), and allows drill down to associated transactions.

FRTRN is similar to FRAMT Function, but FRTRN retrieves totals from transaction history, whereas FRAMT retrieves totals from account history.

Note: This function cannot retrieve budget or provisional amounts, calculate balances, or include rollup amounts or totals.

Syntax

FRTRN(field name, account reference, criteria, currency)

Note: FRTRN returns 0 if no transactions are retrieved from transaction history.

Arguments

Argument Description
field name

The name of a field from the General Ledger fiscal sets with an optional fiscal designator prefix and optional fiscal year suffix (separated from the field name with a period).

The fiscal designator prefix can be:

  • A. Actual amount
  • Q. Quantities
  • QA. Actual quantities

Note: If no prefix is supplied, the default report setting is used (actual).

The fiscal year suffix can be:

  • YYYY. A specific fiscal year, such as 2010
  • Ln. n years ago relative to the current fiscal year; for example, L1 is last year, and L2 is 2 years ago
  • Nn. n years in the future relative to the current fiscal year; for example, N1 is next year, and N2 is the year after next

Note: If no suffix is supplied, the current fiscal year is used.

account reference A string specifying a single account or a range of accounts. See About Account Number References.
criteria (Optional) An expression that imposes selection criteria on the accounts and on transaction fields, including account and transaction optional fields. The account reference and expression together determine the accounts and additional data that will be included in the calculation. See About Selection Criteria for Filtering Accounts.
currency (Optional)

A string containing the currency reference. A currency reference has the form "CCC.T," where CCC is a 3-character currency code (for example, CAD for Canadian dollars, or USD for US dollars), and T is the currency type.

The currency type can be:

  • S. Source currency
  • E. Equivalent source amount in the functional currency
  • F. Functional currency total (including functional equivalents of all source amounts)
  • R. Reporting currency total
    • If the currency code is functional, R includes reporting equivalents of all source amounts.
    • If the currency code is not functional, R provides equivalent amounts in the reporting currency.

Note: If no currency is supplied, functional (home) currency is used.

Financial Reporter can retrieve the following net change fields.

Note: In the Reporter Code Suffix, n is a number that can refer to the fiscal period (from 1 to 13), or to the quarter (from 1 to 4).

Period net change
Field Description Reporter Code Suffix

NET

(net changes)

Current period P NETPNETP, NETnP, NET4Q, NETPQ will also include adjustments from period 14 if the net is requested for the final period of the year. (NET works like BAL.)1
Last period LP NETLP
nth period nP NETnP
n periods ago nPA NETnPA
Quarter net change
Field Description Reporter Code Suffix

NET

(net changes)

Current quarter (to end) Q NETQ
Current quarter to date QTD NETQTD
Last quarter LQ NETLQ
Last quarter to date LQTD NETLQTD
nth quarter nQ NETnQ
nth quarter to dateThis code allows you to compare the current QTD to the QTD of a previous quarter by providing you with to-date figures relative to the current quarter. If you are now in period 2 of the current quarter, QTD will give you the first 2 periods of any previous quarter.2 nQTD NETnQTD
n quarters ago nQA NETnQA
n quarters ago to datenQATD lets you go up to four quarters ago. If you need to specify more, use nQATD.L1.3 nQATD NETnQATD
Preceding quarter (3 periods)PQ handles any three periods, providing you with revolving quarters. The preceding 3 periods include the current period plus the two previous ones.4 PQ NETPQThe NET for a group of periods that crosses a year-end boundary will also include the adjustments in period 14, but not include the closing entry; otherwise, the net would include the zeroing entries in income and expense accounts.5
Preceding quarter (n periods ago) PQnPA NETPQnPA
Half year net change
Field Description Reporter Code Suffix

NET

(net changes)

Current half year (to end) S NETS
Current half year to date STD NETSTD
Last half year LS NETLS
Last half year to date LSTD NETLSTD
nth half year nS NETnS
hth half year to dateThis code allows you to compare the current half year TD to the half year TD of a previous half year by providing you with to-date figures relative to the current half year. If you are now in period 2 of the current half year, half year TD will give you the first 2 periods of any previous half year.6 nSTD NETnSTD
n half years ago nSA NETnSA
n half years ago to datenSATD lets you go up to 2 half years ago. If you need to specify more, use nSATD.L1.7 nSATD NETnSATD
Preceding half year (6 periods)PS handles any six periods, providing you with revolving half years. The preceding six periods includes the current period plus the five previous ones.8 PS NETPS
Preceding half year (n periods ago) PSnPA NETPSnPA
Total year net change
Field Description Reporter Code Suffix

NET

(net changes)

Total year Y NETY
Year to date YTD NETYTD
Preceding year (12 or 13 periods) PY NETPY
Preceding year (n periods ago) PYnPA NETPYnPA
Beginning of year OPEN  
End of yearNETCLOSE provides the closing entry only.9 CLOSE NETCLOSE
Period 14 adjustments ADJ NETADJNETADJ provides the amount from period 14. 10
Note:
  • You can print year-end adjustments and transactions to close income accounts to retained earnings with NETADJ, and print closing figures with NETCLOSE and BALCLOSE.
  • All fiscal field set values are calculated relative to the fiscal period set for the report, with the exception of NETADJ, and NETCLOSE, and fields that reference a specific time period.
    • NETADJ is the amount posted to the adjustment period.
    • NETCLOSE is the amount posted to the closing period.
  • If there are no records for a future period, zero is returned.
  • You can use a fiscal set prefix with NET to return specific values. For example:
    • NETP provides the net amount for the current fiscal period.
    • ANETP provides the actual net change for the current fiscal period.

Examples

Statement Return Value
=FRTRN("NETLQ","1000") Net transactions for the last quarter for account 1000
=FRTRN("NET#P","1000") Net transactions for inquired period for account 1000
=FRTRN("NETQTD","1000") Net transactions for the quarter to date for account 1000

Related Topics