Other Useful Functions

Showing an Amount in Words

This function is useful for printing cheques. You can find it in Language Tab > Functions > String:

AmountStr(aAmount);

Where aAmount is the numeric amount you want to convert.

Example in a Variable's OnGetText event handler:

Text := AmountStr(DR_Trans['Amount']);

An amount of 300.00 would return 'Three hundred dollars only'.

Loading a Picture From a File Path

Sometimes file paths to pictures are stored in database fields. This function enables images to be loaded into image components at run time via a file path. This function is not listed in the code toolbox.

[image component name].picture.loadfromfile([filename]);

Example to load the image of a stock item into a picture component, using an extended field on STOCK_ITEMS:

image1.Picture.LoadFromFile(STOCK_ITEMS['X_PicturePath']);

Creating an AutoSearch Criteria in Code

This function would be required where two different search criteria needed to be applied from one selected parameter value e.g. a trial balance report for a selected period requires <= period value for balance sheet accounts and <= Period value and = period year age for profit and loss accounts. Having two runtime parameters and getting the user to select the same value twice could accomplish this but it is a lot tidier and more professional to have only one.

Another example would be where join restrictions between data sources make the usual method of assigning search criteria (via runtime parameters and joins) impossible.

This function must be placed in the OnGetAutosearchValues event of the Report Object.

Objects Tab > Report > Create Autosearch Criteria

Syntax:

Report.CreateAutoSearchCriteria(aDPName, aFieldName, aOperator, aExpression, aMandatory)

Where:

aDPName = name of the data source
aFieldName = name of field within data source
aOperator = pick from the list under Language tab > EnumeratedType >TppSearchOperatorType
aExpression = value for the search criteria

Example:

Report.CreateAutoSearchCriteria('Master', 'Age', soEqual,
plParams['PeriodValue'],true);

This would create an Autosearch criteria on the data source called 'Master' where field 'Master.Age' = [value selected for parameter 'Period'].

Note: This function is a method of the report object (see the code toolbox, Objects tab and select 'Report'). This is why it starts with “Report.”. The Objects tab lists both methods (functions) and properties (attributes) associated with each object.

Execute SQL Command from Within Clarity

You can also execute almost any SQL Server command from within Clarity. An example is shown below:

ExecuteSQL('update stock_items set status = "L" where stockcode = ' + '"LABOUR"');

While this is potentially very powerful (and a handful of reports use it for non-critical data processing), it is also potentially very dangerous. For this reason, it is only allowed if the associated MYOB Exo Business profile setting has been enabled. The profile setting 'Allow Clarity ExecSQL Function' controls the visibility and access to ExecSQL function in Clarity.

Profile options:

None - Does not show in the Clarity designer and will not execute at runtime
Runtime - Does not show in the Clarity designer but will execute at runtime
Design and Runtime - Shows in the Clarity designer and will execute at runtime

Open a GL account

You can open a specified GL account from a Clarity report with the procedure ShowGLCode(GLCode: string; HasBranch: Boolean);. The GLCode can be either a single number, e.g. 1000, or a formatted string, e.g. 1000-10 or 2-1000-10 with the branch.

Assuming a DBText field called GLAccNo, add an OnDrawCommandClick handler as follows:

procedure GLAccNoOnDrawCommandClick(aDrawCommand: TObject);
var
    lDrawText: TppDrawText;
begin
    lDrawText := TppDrawText(aDrawCommand);
    if lDrawText.Text <> '' then
      ShowGLCode(lDrawText.Text, False);
end;

Return an Exo protocol link

You can return an Exo protocol link using the FormatExoProtocolLink function, e.g.

Link := FormatExoProtocolLink('draccount', 10);

This will return "exo://<current alias>/draccount(10)" into the link.

This function can be used with the DBText.Hyperlink property in Clarity. Add a BeforePrint handler to the detail band, e.g.

procedure DetailBeforePrint;
begin
DBText1.HyperLink := FormatExoProtocolLink('draccount', MASTER['ACCNO']);
end;

Clicking on this link will then launch/open Exo Business, then open the relevant Debtor account.

Note: ShowGLCode uses the Exo protocol handler to enforce security constraints, i.e. as with all Exo protocol links, users cannot use them to access part of the Exo Business system that they do not have access rights for.