Microsoft DynamicsFree referencePDF, Excel, Word exportMicrosoft Dynamics NAVAll Microsoft Dynamics NAV and Business Central on-prem tools

Dynamics NAV C/AL Record and Report Functions Cheat Sheet

This cheat sheet lists the C/AL record, validation, report and text functions NAV 2013 to 2018 developers use daily, with the signature, a one-line example, the AL equivalent for Business Central on-premises, and the gotcha behind most bugs. Search by function name or group.

Download:

Function (signature)	Group	C/AL example	AL (BC on-prem)	Gotcha
Rec.SETRANGE(Field [, From] [, To])	Filtering	SalesLine.SETRANGE("Document No.",SalesHeader."No.");	SetRange	SETRANGE(Field) with no values removes the filter on that field
Rec.SETFILTER(Field, String [, Value1, ...])	Filtering	Cust.SETFILTER("No.",'%1..%2\|%3','10000','20000','30000');	SetFilter	Use %1 placeholders, not concatenation, or values with & \| ( break the filter
Filter operators	Filtering	Item.SETFILTER(Description,'@bolt'); Item.SETFILTER("Unit Cost",'>%1&<%2',10,50);	Same syntax	.. range, \| or, & and, <> not equal, * wildcard, @ case-insensitive
Rec.GETFILTERS	Filtering	MESSAGE(Cust.GETFILTERS);	GetFilters	Returns a readable string; GETFILTER(Field) returns one field's filter
Rec.RESET	Filtering	SalesLine.RESET;	Reset	Clears filters and resets the key to the primary key
Rec.SETCURRENTKEY(Field1 [, Field2, ...])	Filtering	CustLedgEntry.SETCURRENTKEY("Customer No.","Posting Date");	SetCurrentKey	In NAV the key must exist in the table's key list, or a runtime error occurs
Rec.FINDSET([ForUpdate] [, UpdateKey])	Reading	IF SalesLine.FINDSET THEN REPEAT ... UNTIL SalesLine.NEXT = 0;	FindSet	Use FINDSET(TRUE) if you modify records in the loop; FINDSET(TRUE,TRUE) if you change key fields
Rec.FINDFIRST	Reading	IF SalesLine.FINDFIRST THEN ...	FindFirst	Use only when you need one record; do not loop after FINDFIRST
Rec.FINDLAST	Reading	IF GLEntry.FINDLAST THEN NextEntryNo := GLEntry."Entry No." + 1;	FindLast	Respects current key and filters
Rec.FIND([Which])	Reading	IF Item.FIND('-') THEN ...	Find	Legacy pattern; FINDSET is cheaper for forward loops
Rec.NEXT([Steps])	Reading	UNTIL SalesLine.NEXT = 0;	Next	Returns steps moved; 0 means end of set
Rec.GET([Value1, ...])	Reading	Cust.GET('10000');	Get	Ignores filters; errors if not found unless you use IF Cust.GET(...) THEN
Rec.ISEMPTY	Reading	IF SalesLine.ISEMPTY THEN ERROR('No lines');	IsEmpty	Cheaper than COUNT = 0
Rec.COUNT	Reading	NoOfLines := SalesLine.COUNT;	Count	Exact count on SQL; COUNTAPPROX is cheaper for progress bars
Rec.CALCFIELDS(Field1 [, Field2, ...])	FlowFields	Cust.CALCFIELDS("Balance (LCY)");	CalcFields	FlowFields are 0 or empty until calculated; also needed for BLOB fields
Rec.CALCSUMS(Field1 [, Field2, ...])	FlowFields	CustLedgEntry.CALCSUMS(Amount);	CalcSums	Sums a normal decimal field over the filtered set
Rec.SETAUTOCALCFIELDS([Field1, ...])	FlowFields	Cust.SETAUTOCALCFIELDS("Balance (LCY)");	SetAutoCalcFields	NAV 2013+. Calculates on each FIND/NEXT, avoiding one CALCFIELDS per row
Rec.INIT	Writing	SalesLine.INIT;	Init	Does not clear primary key fields
Rec.INSERT([RunTrigger])	Writing	SalesLine.INSERT(TRUE);	Insert	Without TRUE the OnInsert trigger does not run
Rec.MODIFY([RunTrigger])	Writing	Item.MODIFY(TRUE);	Modify	MODIFY without TRUE skips OnModify, so last-modified dates and related logic do not run
Rec.MODIFYALL(Field, NewValue [, RunTrigger])	Writing	SalesLine.MODIFYALL("Shipment Date",NewDate);	ModifyAll	Does not run OnValidate for the field
Rec.DELETE([RunTrigger])	Writing	SalesHeader.DELETE(TRUE);	Delete	Without TRUE related lines are not deleted by OnDelete
Rec.DELETEALL([RunTrigger])	Writing	TempBuffer.DELETEALL;	DeleteAll	Deletes the filtered set; check filters first
Rec.LOCKTABLE	Writing	GLEntry.LOCKTABLE;	LockTable	Locks until COMMIT or end of the transaction
Rec.TRANSFERFIELDS(FromRecord [, InitPrimaryKeyFields])	Writing	SalesInvLine.TRANSFERFIELDS(SalesLine);	TransferFields	Copies by field number, not name; custom fields need matching numbers
Rec.VALIDATE(Field [, NewValue])	Validation	SalesLine.VALIDATE(Quantity,5);	Validate	Runs OnValidate; a direct assignment does not
Rec.TESTFIELD(Field [, Value])	Validation	SalesHeader.TESTFIELD(Status,SalesHeader.Status::Open);	TestField	Without Value it errors if the field is empty or zero
Rec.FIELDERROR(Field [, Text])	Validation	SalesLine.FIELDERROR(Quantity,'must be positive');	FieldError	Message includes the field caption and record key
ERROR(String [, Value1, ...])	Validation	ERROR('Item %1 is blocked.',Item."No.");	Error	Rolls back the whole write transaction
CONFIRM(String [, Default] [, Value1, ...])	Validation	IF NOT CONFIRM('Post %1?',FALSE,SalesHeader."No.") THEN EXIT;	Confirm	Fails under NAS or web services; check GUIALLOWED first
MESSAGE(String [, Value1, ...])	Validation	MESSAGE('%1 lines updated.',Counter);	Message	Shown after the trigger ends, not immediately
GUIALLOWED	Validation	IF GUIALLOWED THEN Window.OPEN('#1####');	GuiAllowed	FALSE in job queue, NAS and web service sessions
COMMIT	Writing	COMMIT;	Commit	Required before RUNMODAL of a page or report inside a write transaction
REPORT.RUN(Number [, ReqWindow] [, SystemPrinter] [, Record])	Reports and pages	REPORT.RUN(REPORT::"Sales - Invoice",TRUE,FALSE,SalesInvHeader);	Report.Run	Record passes its filters to the report's first data item
REPORT.RUNMODAL(Number [, ReqWindow] [, SystemPrinter] [, Record])	Reports and pages	REPORT.RUNMODAL(206,FALSE,FALSE,SalesInvHeader);	Report.RunModal	Waits for the report to finish; needs COMMIT first inside a write transaction
ReportVar.SETTABLEVIEW(Record) + USEREQUESTPAGE(Bool) + RUNMODAL	Reports and pages	SalesInvRpt.SETTABLEVIEW(SalesInvHeader); SalesInvRpt.USEREQUESTPAGE(FALSE); SalesInvRpt.RUNMODAL;	SetTableView, UseRequestPage, RunModal	Use a report variable when you also need to set globals through a custom function
REPORT.SAVEASPDF(Number, FileName [, Record])	Reports and pages	REPORT.SAVEASPDF(206,'C:\Temp\SI-103001.pdf',SalesInvHeader);	Report.SaveAsPdf (OnPrem) or Report.SaveAs to an OutStream	Runs on the service tier; the path is on the server, not the client
PAGE.RUNMODAL(Number [, Record] [, Field])	Reports and pages	IF PAGE.RUNMODAL(PAGE::"Customer List",Cust) = ACTION::LookupOK THEN ...	Page.RunModal = Action::LookupOK	Returns an Action; only LookupOK means the user picked a record
CODEUNIT.RUN(Number [, Record])	Reports and pages	IF NOT CODEUNIT.RUN(CODEUNIT::"Sales-Post",SalesHeader) THEN MESSAGE(GETLASTERRORTEXT);	Codeunit.Run	Using the return value traps errors, but it cannot be called that way inside an open write transaction: COMMIT first
STRSUBSTNO(String [, Value1, ...])	Text	Txt := STRSUBSTNO('%1 - %2',Cust."No.",Cust.Name);	StrSubstNo	Values are formatted with FORMAT, so decimals follow regional settings
STRPOS(String, SubString)	Text	Pos := STRPOS(ExtDocNo,'-');	StrPos or Text.IndexOf	1-based, case-sensitive, 0 if not found
COPYSTR(String, Position [, Length])	Text	Prefix := COPYSTR(ExtDocNo,1,Pos - 1);	CopyStr	Common use: COPYSTR(Txt,1,MAXSTRLEN(Field)) to avoid overflow errors
INCSTR(String)	Text	NextNo := INCSTR('PO-0099');	IncStr	Returns empty text if the string has no number
DELCHR(String [, Where] [, Which])	Text	Clean := DELCHR(Phone,'=',' -()');	DelChr	Which is a set of characters, not a substring

Signatures follow the NAV 2018 C/AL reference. In AL the same methods exist with PascalCase names; report and page IDs can be referenced by name, e.g. Report::"Sales - Invoice". Report 206 is Sales - Invoice in the W1 database; NAV 2017 and 2018 report selections default to report 1306 Standard Sales - Invoice, and localized databases may use another invoice report.

Converting C/AL to AL?

Send us an object list and we will flag which modifications can become event subscribers and which need redesign.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Search by function (SETFILTER, CALCFIELDS) or group (Filtering, FlowFields, Reports and pages). The Example column is real C/AL using standard W1 objects: Customer, Sales Header, Sales Line, Sales Invoice Header, Item, G/L Entry. Paste it into the C/AL editor, rename variables to yours, and read the Gotcha column before you ship. The AL column shows the name in Business Central so the same sheet helps when you convert code with Txt2Al and clean it up.

Worked example: run Sales Invoice report 206 for one posted invoice

To print or save one posted invoice without the request page, filter a Sales Invoice Header record and pass it to the report. In C/AL: SalesInvHeader.SETRANGE("No.",'103001'); REPORT.RUNMODAL(REPORT::"Sales - Invoice",FALSE,FALSE,SalesInvHeader);. The second parameter FALSE hides the request page and the third FALSE uses the report's printer selection rather than the Windows default. To produce a PDF on the service tier, call REPORT.SAVEASPDF(206,'C:\Temp\SI-103001.pdf',SalesInvHeader);. If you are inside a posting routine, COMMIT before RUNMODAL or NAV raises an error about running modal objects inside a write transaction.

Gotchas that cause real bugs

FINDSET is the right choice for forward loops; FIND('-') and FINDFIRST followed by NEXT fetch records less efficiently on SQL Server. MODIFY without TRUE skips the OnModify trigger, so fields like Last Date Modified and change-log logic are bypassed. Assigning a field directly (SalesLine.Quantity := 5) skips OnValidate, so amounts, reservations and dimensions are not recalculated; use VALIDATE. TESTFIELD without a value errors on empty or zero. SETFILTER with concatenated strings breaks when the value contains filter characters such as & or |.

Use FINDSET(TRUE) when the loop modifies records
Use ISEMPTY instead of COUNT = 0
Use SETAUTOCALCFIELDS before a loop that reads FlowFields

Version differences

SETAUTOCALCFIELDS, REPORT.SAVEASPDF on the service tier and the RDLC/Word report layouts belong to NAV 2013 and later; NAV 2009 classic code may use FORM.RUNMODAL and other functions that no longer exist. In Business Central AL the names are PascalCase, Report.SaveAsPdf is limited to on-premises scope, and Report.SaveAs to an OutStream is the cross-platform option. Events (subscribers) replace direct code changes in standard objects, so a C/AL modification usually becomes an event subscriber or a table or page extension.

Frequently Asked Questions

How do I use SETFILTER with multiple values in NAV?

Use placeholders: Cust.SETFILTER("No.",'%1..%2|%3','10000','20000','30000') filters customers 10000 to 20000 plus 30000. The .. operator is a range, | is or, & is and, <> is not equal, * is a wildcard and @ makes the comparison case-insensitive. Placeholders avoid errors when values contain filter characters.

What is the difference between TESTFIELD and VALIDATE?

TESTFIELD checks a field and raises an error if it is empty or not equal to the value you pass; it changes nothing. VALIDATE assigns a value and runs the field's OnValidate trigger, so dependent fields such as amounts or descriptions are recalculated. Use TESTFIELD for preconditions and VALIDATE for writes.

How do I run a report with parameters in Dynamics NAV?

For filters, set them on a record and pass it: REPORT.RUNMODAL(Number, ReqWindow, SystemPrinter, Record). For other options, declare a report variable, call SETTABLEVIEW and a custom public function on the report that sets its globals, then USEREQUESTPAGE(FALSE) and RUNMODAL. Report request page options cannot be set directly from outside without such a function.

Why does MODIFY not update my last modified date?

MODIFY without the TRUE parameter does not run the table's OnModify trigger, where fields like Last Date Modified are usually set. Call Rec.MODIFY(TRUE) when business logic should run. Use plain MODIFY only for technical updates where you deliberately want to bypass triggers, and document why.

If you want NAV users to ask questions about customers, orders and ledgers in plain English on your own servers, read-only and respecting NAV permissions, we can set that up.

Talk to an Expert Explore More Free Tools