Agile Developer

Scripts

Axpert 11
TStructs (Forms)
IViews
Scripts
Axpert Jobs
Axpert Cards
HTML Plug-ins
Users & Roles
Workflow
Axpert API
Application Var/Params
Publish Axpert Apps
Axpert Mobile
Settings
Utilities
Customization
WebServices
Axfast

Scripts

 

About Scripts in Axpert

Scripts are used to achieve complex tasks. A script is written using the Axpert functions. It is given a unique name. Scripts can be executed on events or on a button click. See the section on Axpert Jobs to execute scripts background as scheduled jobs.

// symbol is used in scripts to write comments.

Assignments in scripts

Variable Name=Value
Examples:
Mark=100
MarkPercent=Mark/100
ExamResult=iif(MarkPercent<35, {Fail}, {Pass})
ExamDate=MakeDate({10},{10},{2021}) //returns 10/10/2021 as date var

Loops in scripts

While (Condition)
Statements
If (condition)
Break
Endif
EndWhile

Example 1:
I=1
TotalValue=1
While (i<100)
TotalValue=TotalValue+i
I=i+1
EndWhile
//This function sets value to DisplayTotalValue field
SetFieldValue({DisplayTotalValue}, 1, TotalValue)

Example 2:
Looping through a SQL Result. Consider an SQL that returns project names from project master. Let us write a loop to make a comma-separated string of all project names
//FireSQL executes SQL and the result can be accessed in the given name in 1st param
FireSQL({PNames}, {Select ProjectName from ProjectMaster order by ProjectName})
While (!EOF({PNames})
//SQLGetreutrns the given field name value from current row in PNames Dataset
Name=SQLGet({PNames}, {ProjectName})
ProjectNames=ProjectNames+Name
GoNext({PNames})
EndWhile
SetFieldValue({MyProjects},1, {ProjectNames})

If conditions in scripts

If Condition
Statements
Else
Statements
EndIF

Example:
//checking if project name is empty, MyProjects field is set with “There are no projects”
If ProjectNames={}
SetFieldValue({MyProjects}, 1, {There are no projects})
Else
SetFieldValue({MyProjects}, 1, ProjectNames)
EndIf

Major Axpert functions

Loading a form

Use function LoadForm(Transid, Params, DisplayMode, RefreshOnClose) in a script. If the script is assigned to a button, on the button click. the form will be opened in the new mode.

DisplayMode – d to show form in the same frame, p to popup, n to open in a new tab.

RefreshOnClose – Used in case of the popup. Set to true to close popup on clicking close.

This will open the given tstruct in the new mode. The Params is a string that contains name=value pairs separated by ‘~’. The field values will be assigned at the client level. If it requires a server call, it will be made to fill values to dependent fields.

For example, Load a form from your schema using the LoadForm function. LoadForm({samps},{},{d},{true})
LF-1.PNG

Load a form along with data

Use function LoadFormAndData(Transid, Params, DisplayMode, RefreshOnClose) in a script. If the script is assigned to a button, on button click, a form will be displayed along with data in edit mode.

DisplayMode – d to show form in the same frame, p to popup, n to open in a new tab.

RefreshOnClose – Used in case of the popup. Set to true to close popup on clicking close.

Params – A string that will be used in the where condition of the SQL that will be used to search for a record from the tstruct primary table. Any field in the primary table can be used in the condition. It may also be set as recordid=:value.

This will open the given tstruct along with data in edit mode. The field values will be assigned at the client level. If it requires a server call, it will be made to fill values to dependent fields.

For example, Use LoadFormAndData({samps},{result=:result},{p},{true}) in the script. Load data for the entry abc from the form,
Click on the LoadForm button created

LFD-1.PNG

This will load all the corresponding details of the entry as shown below

LFD-2.PNG

Loading a page

Use function LoadPage(PageName, Params, DisplayMode, RefreshOnClose) to load a page in a script. If the script is assigned to a button, on button click at run time, the given page will be displayed.

DisplayMode – d to show form in the same frame. p to popup, can be popup. n to open in a new tab.

RefreshOnClose – Used in case of the popup. Set to true to close popup on clicking close.

Params – Name-value pairs that may be used as parameters for the widgets on the page.

This will open the given page.
For example, load the item request page at the click of a button. Use LoadPage({PageTsItemR},{},{p},{True}) in the script.

LP-1.PNG

With the click of LoadPage, the item request page opens as shown below.

LP-2.PNG

Clearing a form

Use function ClearForm(Transid, Params) in scripts. If the script is assigned to a button, on button click in run time, the form data will be cleared.
It clears all field values & uploads and puts the form in the new mode. In case a script is assigned to an On New event, that script will also be executed. The field values are given in the Params (as name-value) pairs will be assigned. If required, the FormLoad service will be called.

For example, try clearing the form entries except for the result field (field caption: Name)
ClearForm({samps},{result=:result})

CF-3.PNG

Click on the ClearForm button

CF-1.PNG

All the entries apart from the Name are cleared.

CF-2.PNG

Canceling form data

Use function CancelTransaction(Transid, RecordId, ShowResult) in script. If the script is assigned to a button, on button click in run time, the form data record will be marked as canceled in the database. Such a transaction will be considered deleted and can be filtered in reports so that canceled records are not shown in reports.

It Cancels the record given by the recordid. Success/error messages will be displayed if the ShowResult Param is true.

The response is a success message or error message.

For example, the entry pqr needs to be removed from the database and will be marked as a canceled transaction. Use
CancelTransaction({samps},recordid,{true}) in the script. Make an entry, in the list view choose a particular row you want to cancel,
and click on the clear button.

CT-1.PNG

After the success message, reload the form and find the canceled entry in the listview. It should be marked as a canceled transaction as shown below.

CT-2.PNG

Delete a record in the form

Use DeleteTransaction(Transid, RecordId, ShowResult) in script. If the script is assigned to a button, on button click in run time, the data will be removed from all database tables. It Deletes the record given by the recordid. Success/error messages will be displayed if the ShowResult Param is true.

The response is a success message or error message.

For example, Use DeleteTransaction({samps},recordid,{true}) in the script.
In the run site, load an entry from the list view. Click on the ‘Delete’ button.

D-1.PNG

Note: The canceled transaction cannot be deleted.
Reload the list view. The entry will be deleted permanently.

Refresh Field values in a form

Use Refresh(Names, WithDependents) in script. If the script is assigned to a button, on button click in run time, it refreshes the field value. In the case of the SQL field, the data will be fetched again from the database. Expression fields will be evaluated again and the new value will be set to the field.

Names will contain Field1~<RowNo>, Field2~<RowNo>, … If RowNo is set to zero for a grid field, all rows in that column will be refreshed.

Returns a JSON string of field values after refreshes. If WithDependents is true, it will return along with dependent field values.

Form Control Action

Formcontrol actions can be defined using by below functions in script on Axpert web version.

  • AxHideControls(ElementName:String)
  • AxUnhideControls(ElementName:String)
  • AxEnableControls(ElementName:String)
  • AxDisableControls(ElementName:String)

Example: AxHideControls({fieldname})

  • AxHideControls({fieldname,dcname,buttonname})
  • AxHideControls({fld1,fld2,dc2,dc3,save,search})

For further details on form control click here

Load an IView/Report

Use function LoadIView(IViewName, Params, IsPopupRefreshParent) in script. If the script is assigned to a button, on button click in run time, the given IView will be displayed.

DisplayMode – d to show form in the same frame. p to popup can be popup. n to open in a new tab.

RefreshOnClose – Used in case of the popup. Set to true to close popup on clicking close.

Params – Name-value pairs that may be used as parameters for the widgets on the page.

For example, load an Iview using LoadIview({DeptMast},{Name=:tdep},{d},{true}) in the script. Click on the LoadIview button that
you have created.

LIview-1.PNG

Instantly, the Iview specified in the script is loaded on the page as shown.

LIview-2.PNG

Navigate to an external page

Use function HyperLink(URL) in script. If the script is assigned to a button, on button click during run time, will navigate to the given URL.

Closing a popup window

Use function CloseWindow() in script. If the script is assigned to a button, on button click in run time will close the popup window.

For example, load a form using the script. When the pop-up appears click on the close window option available.

CW-1.PNG

This option is helpful when the display mode is “d”.

Change field captions at run time in forms

Use SetFieldCaption(Name, Caption) in script. If the script is assigned to a button, on button click in run time, the given new caption will be assigned to the field.

Set focus to a component in a form

Use function SetFocus(FieldName, RowNo) in script. If the script is assigned to a button, on button click in run time, sets the focus to the given component.

Showing popup messages in forms

Use function ShowMessage(MessageText, MessageType, MessageOptions) in script. If the script is assigned to a button, on the button click in run time, to popup messages. The Message Type can be Confirmation, Warn and proceed, Exception. In case of a confirmation message, the message options will be displayed as buttons. The selected option will be returned as the result.

ShowMessage({message shown},{Simple},{})

SM-1.PNG

ShowMessage({message shown},{confirmation},{})

SM-2.PNG

ShowMessage({message shown},{warn and proceed},{})

SM-3.PNG

ShowMessage({message shown},{Exception},{})

SM-4.PNG

 

Send EMails

Use function SendEMail(ConfigName, Param) in script. If the script is assigned to a button, on the button click in run time, to send an email.

This function sends an email as per the configuration made. Email configurations can be done in the “Configure Emails” option in Utils in the Developer site.
Check out the section on Email Configuration section to understand the configuration.

Param – The Param should contain recordid for emails from tstructs. For Email from IViews, it will contain comma-separated name-value pairs of Iview param names & param values. To send emails without a configuration, set the following variables in the script before calling this function.

  • Axp_EMailTransid
  • Axp_EMailIView
  • Axp_EMailTo
  • Axp_EMailCC
  • Axp_EMailBCC
  • Axp_EMailSubject
  • Axp_EMailAttachment
  • Axp_EMailBody

The status of Emails sent can be seen in the Email Jobs console that is available in the Admin console option in Axpert run time.

Email Configuration

Emails are configured using the “Configure Email” option under Utils in the developer site. There can be many email configurations that are identified by a unique name.

Email Source :

  • To email tstruct/form data, choose the “TStruct” option
  • To email an iview/report, choose the “IView” option.
  • To send an email for every row in an IView result, choose “IView rows” option.

In all the fields related to email like Email To, CC, BCC, Subject & Body, data from the selected tstruct or Iview can be tagged. The tags are written as: symbol followed by the field/param/column name.

The attachment field –

  • In the case of Emails from forms, the file-upload field may be embedded to send embedded files as attachments. Use P__* to send all print forms related to form data. Use P__<print form name> to send a particular print format as attachment.
  • In case Source is IView – Dropdown options (IVIew as PDF, IView as HTML, IView as MS Excel)
  • In case of emails from IVIew rows – Value cannot be entered.

The body of an Email may contain a :IVIewAsHTML tag. This tag will be replaced with the IView result in the email. This is applicable only when the source is Iview. To know more about sending an email and how to configure it check out the Email definitions section.

Printing forms/reports

Use function AxPrint(Source, StructureName, Param, FileServerName, FileType, DirectPrint) in script to print forms. The print forms are created using Fast reports. Refer to the ‘Fast reports section to know more about formatting fast reports.
This function will send print as per the given definition or parameters.

  • Source – t – tstruct, i – iview or r – iview row
  • SturctureName – Name of tstruct or IView.
  • Param – If tstruct, this should contain the recordid of the form data that is to be printed. For IViews, it will contain comma-separated name-value pairs of iview param names & param values.
  • FileServerName – This is the name of the file server that is configured through “Define Servers” option under utitlity.
  • FileType – PDF, HTML or Excel.
  • DirectPrint – ‘T’ if to be directly printed. This is not applicable when source is iview row.

The configuration parameters can be changed before making this function call. This is done by setting the following variables.

  • Axp_PrintToURL
  • Axp_PrintURLAuthentication //Comma separated name,password
  • Axp_PrintFIlePathAndName
  • Axp_FileType//AsHTML/AsPDF
  • Axp_DirectPrint //True/False, applicable only for IView & TStruct.

The file name along with the folder name will be returned by this function. The status of all print jobs sent can be seen in the Print Jobs console that is available in the Admin console option in Axpert run time.

Data Import

Use function AxImport(ImportDefName, SourceFilePathAndName) in scripts to import data. This function will import data as per the definition made in the import definition from the given source file.
The source file is given along with the path. The results of an import job can be viewed in the Import Jobs Console in the Admin console. The source file may be an excel file or CSV file. In the case of CSV, the separators may be a , or: or |,

Import is done by the following steps –

  1. Uploads the source file into a temporary DB table.
  2. Optionally – The table data can be transformed into any other data format as per need using a stored procedure. The stored procedure should transform data and store The full grids, Genmaps, MD Maps, or scripts associated with DB events. In this, tstruct will be executed while importing. This process is time-consuming and may be set to work as a multi-thread application.
  3. The results are stored in AxpImpJobs table (contains the status of each job and error message) and AxpImpFailedRecords table (contains the list of records that could not be imported due to errors. The records in these tables can be viewed through the admin console option in Axpert run-time options button. Further, IViews may be created from these tables to provide users with an interface for managing imported records.

 

Import definition

Import definition is available as an option in Utils in Axpert Developer site. The definition properties are:

  • The name given in this definition is used as a parameter in the AxImport function.
  • The tstruct/form is selected. Data from the source file is imported in this form.
  • Map in file – Check this if each column heading in the source file contains the field to which the column is mapped.
  • Map source to fields – Select the field names into which the column values from the source are mapped. The 1st column in the source is mapped to the first selected field, the second column is mapped to 2nd selected field, and so on.
  • Primary field – The primary field in the selected tstruct.
  • Group field – The rows in the source file are grouped based on the column that is mapped to this field. The grouped rows are posted as one transaction.
  • Thread Count – When importing a large amount of data (5000+ rows), the import can be speeded up by increasing the thread count. The maximum can be 9.
  • Process mode –Select “Process with errors” to push in all records. The errors will be available in the import console. These may then be corrected and saved. Select “Only valid data” to import only valid records. The errors can be seen in the admin console. “Stop on error” will abort the import process when an error is encountered.
  • Procedure name – The name of the procedure that is used for the transformation of source data into a different table format as per need. The data from the transformed temporary table will then be imported into the tstruct.
  • Std width – Fields in the temporary table are created with the data type and width of the mapped field. If a column in the source is not mapped to a field in this tstruct, then an exception is raised. This exception can be avoided by un-checking the “Bind to tstruct” and “Ignore field exception” properties in the import definition. Such columns will be created in a temporary table as string fields with width as given in the “Std width” property.
  • Text qualifier – Check this if the source file consists of comma-separated values in which the values are within double-quotes.

Example 1:Consider a tstruct as below:
Employee Name [ ]
Gender [ ]
Age [ ]
Designation [ ]
Qualification [ ]

Prior experience-

Company name Designation From To

An excel sheet as below can be a source:

caption

For importing the above source file, set the EmployeeName as the primary & group field. On importing, the record for Prem will be imported with 2 rows in prior experience grid. Record for Niya will contain one row in the prior experience grid.

Map in file can be enabled if the first row contains the field name to which the column is mapped.

Empname Gender Age Desig Qual Orgname Priordesig From year To year
Prem Male 30 GM MBA Just Software Manager 2014 2018
Prem Male 30 GM MBA Control systems Developer 2009 2014
Niya Female 23 Sales exec BBA RM Systems Sales trainee 2018 2019

In case the file has fields that are not in tstruct, if the Bind to tstruct flag is checked, an exception will be raised. If this flag is unchecked, a new field will be added to the temp table to which the data from the source will be uploaded. The field type character and the width will be set as provided in the Std Column Width field in the definition. Similarly, if the field in the excel is mapped to a field that is not in the tstruct, an exception will be raised while importing. The exception can be ignored by checking the Ignore field exception.

Execute API

To import data using API, create an API definition using the option from the utility section.

Define the request string format, as used in the document that is to be imported. The URL is unique for every user.
Note: The file from which the data is to be imported should be placed in the server path.

Then use the API defined, in the tstruct by writing a script as shown below

preqdata := AxExecuteAPI({GetPurReqData})
firesql({a},{call fn_json_table( :preqdata ) } )
sqlstr := {select * from imptemptable order by pono}
sqlpost(sqlstr,{purex},{pono},{pono},{purexpost})

Create a button to execute the API call.

Click on Submit. Save the structure and go to run site. Find the tstruct and click on the user-defined button in the options section. The data will be imported into the tstruct on the button click.

Functions

The functions supported by Axpert are listed in this section.

Grid related Functions in Tstruct

1. Abs(Num:Numeric)

Converts Number to positive.

2.Total(FieldName: string)

This function cumulates the values in a grid column in a TStruct. FieldName is the name of the grid column.
Use in: TStructs Example: Consider a grid frame as follows

S.No Category Amount
1 A 100
2 A 200
3 B 300
4 C 400
5 A 500
6 B 600

Total({Amount}) gives 2100.

3. Sum(FieldName, FieldValue, CumulateField : string)

Does a conditional cumulation. Cumulates the values in CumulateField. Considers only rows in the grid where the value in FieldName is equal to the FieldValue.

Use in : TStructs
Example:Consider a grid frame as follows

S.No Category Amount
1 A 100
2 A 200
3 B 300
4 C 400
5 A 500
6 B 600

Sum({Category}, {A}, {Amount}) will return 800.

4. SumTill(FieldName, FieldValue, CumulateField, RowNo)

Does a conditional cumulation till the given Rowno. Cumulates the values in CumulateField. Considers only rows in the grid where the value in FieldName is equal to the FieldValue and rowno less than or equal to the given rowno.
Use in: TStructs
Example: Consider a grid frame as follows

S.No Category Amount
1 A 100
2 A 200
3 B 300
4 C 400
5 A 500
6 B 600

SumTill({ItemCategory}, {A}, {Amount}, 4) will return 300.

5. GetMax(FieldName)

Returns the row that contains the maximum value in a column in a grid DC.
Example: Consider a grid frame as follows

S.No Category Amount
1 A 100
2 A 200
3 B 300
4 C 400
5 A 500
6 B 600

Max({Amount}) returns 6 because the 6th row contains the maximum value in the Amount column.

6. GetValue(Fieldname: character; RowNo: numeric)

FieldName is the name of a field. This function returns the value of the field at the given row. For fields in a non-grid DC, give the row as 1. Example: Consider a grid frame as follows

S.No Category Amount
1 A 100
2 A 200
3 B 300
4 C 400
5 A 500
6 B 600

GetValue({Category}, 3) gives B.

7. GetId(FieldName: string; RowNo: numeric)

FieldName is the name of a field in the TStruct. This function returns the IdValue of the field at the given row. For fields in a non-grid DC, give the row as 1. This is applicable only for source keys (selectable) from fields.

Example: Consider a grid frame as follows

S.No Category Amount
1 A 100
2 A 200
3 B 300
4 C 400
5 A 500
6 B 600

If Category is a source key true (selectable) field then GetId({Category},2) will return the idvalue of A.

8. GetRow(FieldName, Fieldvalue: string)

Returns the row in the grid that has the field value in the field name column.
Example: Consider a grid frame as follows

S.No Category Amount
1 A 100
2 A 200
3 B 300
4 C 400
5 A 500
6 B 600

GetRow({Category}, {A}) returns 1. Only the first matching rowno is returned.

9. GetRowCount(FieldName: string)

Returns the number of rows in a grid frame that contains the given field.
Example: Consider a grid frame as follows

S.No Category Amount
1 A 100
2 A 200
3 B 300
4 C 400
5 A 500
6 B 600

GetRowCount({Category}) return 6.

10. GetOld(FieldName: string; RowNo: Integer)
Note: Available on Desktop
Returns the old value in a field at the given row. This is useful in the modification of a transaction. In the case of a non-grid field, send 1 as a row. Example: Consider a grid frame as follows

S.No Category Amount
1 A -> B 100
2 A 200
3 B 300
4 C 400
5 A 500
6 B 600

If a transaction is opened in modification mode and the category in the first field is changed from A to B then, GetOld({Category}, 1) will return A.

11. Cell(col, row:numeric)
Note: Deprecated
Returns the value in the grid frame from the cell identified by col, row.
Example: Consider a grid frame as follows

S.No Category Amount
1 A 100
2 A 200
3 B 300
4 C 400
5 A 500
6 B 600

Cell(2, 4) returns C.

12. AddRow(FrameName: string): string; [TStruct]
Available on Desktop

13. DeleteRow(FrameName:string; rowno:integer):String; [TStruct]
Available on Desktop

14. GetMin(FieldName)

Returns the row in the grid that contains the minimum value in the column given by field name. Same as the GetMax function.

Tstruct Related Functions

1. MakeTreeId(id: String; Level: integer)
Note: Available on Desktop only

2. FieldChanged(FieldName, RowNo)

Returns T if the given field at the given row is changed. Send Rowno as 1 for non-grid fields.

3. SetValue(FieldName: string; Rowno: integer;Value: String)

Sets the value for the field at the given row to the given value. For non-grid fields, the row should be 1.

Example: Consider a TStruct that has a field named ItemCode in a Grid DC. If the value of the item code in row 2 is to be changed to ‘x1’ use SetValue({ItemCode},2,{x1}).

4. GetSQLValue(SQLText, ColumnName: String)

Not used now. Maintained for backward compatibility. Use FireSQL instead.

5. SetProperty(fName, propertyname, value: String)
Note: Available on Desktop

Sets the property with the given value for the field given by fName.

PropertyName could be any of the following

  • Readonly
  • visible
  • ModeOfEntry
  • SQL
  • Caption
  • Suggestive
  • Expression
  • ValidateExpression
  • FontName
  • FontSize
  • FontColor
  • FontStyle
  • Color
  • Decimals

Example: Consider a requirement where a field named DocNo should be read-only if field Authorised contains No. This can be achieved as follows:

{OnFormLoad}
flag := iif(Authorised={Yes}, {F}, {T})
SetProperty({DocNo}, {ReadOnly}, flag)

6. SetSequence(FieldName, Prefix:String)

Sets the sequence from the next number should be generated for an autogenerated field.

NoteNote: The functions related to numbering sequence viz SetSequence & SetExtSequence cannot be used in OnFormLoad. Moreover, the case of the parameters should exactly match the ones defined in the tables. That is, if the field name is defined in lower case, the field name should be in lower case in these functions also.

Example: Consider a TStruct that has a field name Type. This can have values A or B. If it is A, then the DocNo field following this field should have the ‘AAA-‘ sequence selected. If the type is not A, then the sequence for DocNo should be ‘BBB-‘.
seq := iif(Type = {A}, {AAA-}, {BBB-})
SetSequence({DocNo}, Seq)

7. ResetActivecomp()
Note: Available on Desktop only
Use this function if some of the components are hidden using the SetProperty function in an expression set. This will focus on the next visible component.

8. LoadTempFile(FileName)
Note: Available in Desktop only
Loads the data the given filename into the transaction form. This data should have been stored by the Save To File option in the transaction form.

9. DeleteTempFile(FileName)
Note: Available on Desktop only
Deletes the temporary file in which transaction data has been stored. The temporary file should have been created by the “Save to file” option.

10. ActivateField(Fieldname, RowNo)

This function will set the focus on the given field and rowno. This function can be used in an expression set to set focus conditionally on a field.

Example: ActivateField({CustCode}, 1)

The above expression set will set focus to the customer name field when exiting from the Docid field.

11. AllowFrameChange(FrameNo, Allow)
Note: Available on Desktop only
This function will disallow changes to any field in the given frameno if Allow is f. It allows changes if allow is t. This can be used in an expression set to set a frame read-only.

Example: To disallow modification of fields in the first frame in modification mode set the expression set as follows:
{AfterLoadData}
AllowFrameChange(1, {f})

12. AllowFieldChange(FieldName, Allow)
Note: Available on Desktop only
Changes to a given field will be disallowed if Allow is f & vice versa if allow is t. This can be used in an expression set to set a field read-only.

Example: To disallow modification of the rate field data entry operators, use the following expression set.
{OnFormLoad}
c := iif(usercategory = {data entry}, {f}, {t})
AllowFieldChange({Rate}, c)

13. PrintDocNames
Note: Available on Desktop only
This is a variable that can be set in an expression set. The document names that need to be printed can be set in this variable.
If there is more than one document, it can be given as a comma-separated string. If this variable is set, the print format selection window will not pop up.

Example:
PrintDocNames := {printform1, printform2}
PrintDocNames := iif(usercategory = {data entry}, {printform1}, {printform1, printform2})

14. ExecuteOption(Caption)
Note: Available on Desktop only
This function can be used in an expression set to execute any functionality associated with any button in the Transaction form. The button caption is sent as a parameter to that function.

Example:
{OnDCEnter GridFrame}
ExecuteOption({FillGrid})

FillGrid is the caption of a button in the TStruct.

15. RefreshField(FieldName)

This function can be used in an expression set. This can be used to refresh the field values of the given field. In case, the field is calculated based on an expression. Any of the fields used in the expression are changed, the effect can be shown immediately on screen using this function in the exit of the parent field.

16. RefreshFrame(FrameNo)

This function will refresh all the fields in a frame. In the case of a grid frame, all the rows will be refreshed.

17. InitGrid(FrameNo)

Delete all rows from a grid frame given by frameno.

18. GetOld(FieldName, RowNo)
Note: Available on Desktop only
This function will return the old value of the given field & row. This is valid only when a transaction is modified. The old value is the value that was stored in the database before the transaction was loaded for modification.

19. GetOldId(FieldName, Rowno)
Note: Available on Desktop only
This function will return the old id value of the given field & row.

20. SQLRegVar(SQLText)

This function will register the result of the SQL for using them in expressions. The SQL result should have the following columns: VarName, VarType, VarValue.

The function will register the given varname with the varvalue. For example, if the SQL result is as follows:

VarName VarType VarValue
a1 n 100.00
a2 c abc
a3 d 01/04/2004

Three variables will be registered as follows :
a1 = 100.00
a2 = abc
a3 = 01/04/2004

Now these, vars could be used in expressions.

21. CheckStock(ItemId, OldItemId, DocDate, OldDocDate, PlusOrMinus, Qty, OldQty)
Note: Available on Desktop only
This function will check if enough stock is available to carry out the transaction for the given ItemId as on the given DocDate for the Qty.
In the case of modification mode, the old values will be used if the modification can be allowed. The function will return T if the transaction can be allowed or else a message will be returned.

22. AllowedUserNames
Note: Available on Desktop only
This is a system variable. The sequence window allows users to both select & edit the sequences. In case there is a need to allow only certain users/user categories to modify or add to the sequence list, it can be achieved as follows:

{OnFormLoad}
AllowedUserNames := {user1,user2}

In case the control has been set on user categories then
{OnFormLoad}
AllowedUserCategories := {usercategory1, usercategory2}

23. EnableButton(caption, flag)
Note: Available on Desktop only
Any button in a TStruct can be conditionally enabled/disabled. For example to disable the save button if user1 has logged in and enabled it otherwise, define an expression set as follows

{onformload}
f := iif(username = {user1}, {F}, {T})
enablebutton({save}, f)

24. GetSubTotal(FieldName, ParentRow)
Note: Available on Desktop only
This function can be used in TStructs to get the total of any field from a subform into a field in the parent grid. The field given by field name (which is to be in the subform) is summed and returned. Only rows related to the given parent row will be summed.

“Example:” In the accounts voucher, to get the sum of the cost amount and store it in another column in the accounts grid set the expression as follows GetSubTotal({CostAmount}, ActiveRow)

25. RoudedDiff(FieldName, RowNo, RoundTo)

This function will round off the value in the field name to the nearest whole number based on the value given in RoundTo. The difference between the rounded value and the actual value will be returned by this function. If rowno is greater than zero then all the values in the column till Rowno will be cumulated and then rounded.

“Example:” Consider the amount column in a grid in a TStruct. This needs to be cumulated and rounded to the nearest rupee. The difference has been got into the roundoff field. To do this an expression as below can be defined for the roundoff field
RoundedDiff({Amount}, -1, 100)

If the cumulation has to be done till active row then the expression would be
RoundedDiff({Amount}, ActiveRow-1, 100)

26. SetSystemVar(Varname, Value)

Any of the system vars can be changed from a TStruct. This can be done by using the SetSystemVar function.

27. SQLPost(SQLText, Target Transid, GroupField, PrimaryField,SQLName:String)

SQLPost is an Axpert function.
The syntax for this function is given below.
SQLPost(SQLText, Target Transid, GroupField, PrimaryField,SQLName:String)

General Functions

1. TrimVal(Value: string)

Returns a trimmed value of the given string. Example: TrimVal({ abc }) returns ABC

2. Getinteger(Value :numeric)

Returns the integer part in a decimal number. Example: GetInteger(12.33) returns 12

3. GetLength(value : string)

Returns the length of the string. Example: GetLength({xyz}) returns 3

4. FindAndReplace(S, FindWhat, ReplaceWith: String)

Finds the string given by FindWhat & replaces it with ReplaceWith in String S & returns the new string. Example: FindAndReplace({xxxyyyzzz}, {xxx}, {aa}) returns aayyyzz.

5. FormatAmount(Value,DecLen:numeric,withComma,MillionRep,NegativeRep,PositiveRep : String)

This will format the number given in the Value parameter.

DecLen is a number of decimals

WithComma – if {T} will insert commas
MillionRep – if {T} will insert commas as it is in a million system
NegativeRep – if {br} then shows negative amounts within parenthesis else appends the given string to the negative number

PositiveRep – if {br} then shows negative amounts within parenthesis else appends the given string to the positive number

Example:
FormatAmount(100000, 2, {F}, {br}, {}) -> 1,00,000.00
FormatAmount(-100000, 2, {F}, {br}, {}) -> (1,00,000.00)
FormatAmount(100000, 2, {F}, {Cr}, {}) -> 1,00,000.00 Cr
FormatAmount(100000, 2, {T}, {}, {Db}) -> 100,000.00 Db

6. Function FormatAccAmount(Value,DecLen:numeric, withComma, MillionRep, ALIE, Rep :String)

Converts the given numeric value into a formatted string

DecLen is a number of decimals.

WithComma – if {T} will insert commas.
MillionRep – if {T} will insert commas as it is in a million system.
ALIE – could have a value a,l,i, or e. A stands for Assets, l for Liability, i for income & e for Expense. For Assets & Expenses value greater than zero is considered as positive & less than zero is considered as negative. For Liabilities & Income, value < 0 is considered as positive & > 0 is considered as negative.

Rep – if {br}, negative amounts will be enclosed in parenthesis else the string will be appended to the negative amount.

Example:
FormatAmount(100000, 2, {F}, {a}, {br}) -> 1,00,000.00
FormatAmount(-100000, 2, {F}, {a}, {br}) -> (1,00,000.00)
FormatAmount(100000, 2, {F}, {l}, {br}) -> (1,00,000.00)
FormatAmount(-100000, 2, {T}, {i}, {Db}) -> 100,000.00
FormatAmount(100000, 2, {T}, {i}, {Db}) -> 100,000.00 Db

7. FormatValue(value, DecLen numeric; ApplyCommas,MillionRep: String) : Format the given numeric value & returns a formatted string.

DecLen – specifies the number digits after decimal in the result.

WithComma – if {T} will insert commas.

MillionRep – if {T} will insert commas as it is in a million system.

Example:
FormatValue(100000, 2, {F}, {F}) -> 100000.00
FormatValue(100000, 2, {T}, {F}) -> 1,00,000.00
FormatValue(100000, 2, {T}, {T}) -> 100,000.00

7. LeftPad(S: String; MaxLength: integer; c: Char)

Pads the given string S to the left to make the length of the string equal to MaxLength. The character that is padded is given in c.

Example: LeftPad('abc', 5, 'x') -> ‘xxabc’

8. Pad(S: String; MaxLength: integer; c: Char)

Pads the string S to the right with the character given in C.
Example: LeftPad('abc', 5, 'x') -> ‘abcxx’

9. Upper(Str : String)

Converts the given string to upper case.

10. Lower(Str : String)

Converts the given string to lower case.

11. DTOC(PDate : TDateTime)

Converts a Datetime to String.

12. CTOD(PDate : String)

Converts a character to DateTime type.

13. CMonthYear(Dt : TDateTime)

Returns the character month on the given date.
Example: CMonthYear('01/04/2004') -> April

14. Rnd(Amount, RoundTo : Numeric)

Rounds off the given amount to the nearest given RoundTo figure. The RoundTo could be 100 to round off the amount to the nearest 100.
If RoundTo is 50, the amount will be rounded to the nearest 50. This could be useful to roundoff amounts to the nearest rupee or nearest 50 paise etc.

Example:
Rnd(100.20, 50) -> 100.00
Rnd(100.45, 50) -> 100.50
Rnd(100.65, 50) - > 100.50
Rnd(100.20, 100) -> 100.00
Rnd(100.65, 100) -> 101.00

15. Stuff(Str1,Str2 : String; P : Numeric)

Inserts the str2 into str1 at position p.

Example:
Stuff({abcd}, {x}, 2) -> axbcd
Stuff({abcd}, {}, 2) -> acd
Stuff({abcd}, {xyz}, 2) -> axyzbcd

16. Round(Num , Decimals:Numeric)

Rounds the given number to given decimals.

Example:
Round(100.334,2) -> 100.33
Round(100.337,2) -> 100.34

17. IIF(Expression, True_Result, False_Result : String)

Evaluate the expression. If the expression returns True, then the True_result is returned else the false_result is returned.

Example:
iif(10>5, {Greater}, {Lesser}) -> Greater
iif((10-6)>5, {Greater}, {Lesser}) -> Lesser
iif(10 < 5, {Lesser 1}, iif(10 < 11, {Greater 2}, {Lesser 2})) -> Greater 2
iif(10 < 5, {Lesser 1}, iif(10 > 11, {Greater 2}, {Lesser 2})) -> Lesser 2
iif((10-6) < 5, {Lesser 1}, iif(10 > 11, {Greater 2}, {Lesser 2})) -> Lesser 1

18. AmtWord(Amount : Numeric)

Converts the given amount to words.
Example: AmtWord(1356.50) -> One thousand three hundred and fifty-six and paise fifty only.

19. CurrAmtWord(Amount : Numeric, Currency, SubCurrency, InMillions: string, Decimals :integer)

Converts the given amount to words as per the specification.

Currency – Name of the currency to convert to.

SubCurrency – Name of the sub currency
InMillions – {T} if amount should be in millions.

Decimals – number of decimal digits in the amount.

Example:
CurrAmtword(1200000, {Dollars}, {Cents}, {T}, 2) -> One million two hundred thousand only.
CurrAmtword(1200000, {Rupees}, {Paise}, {T}, 2) -> Twelve lacs only.

20. Val(Num:String)

Converts the given string to numeric. Val({123}) will be 123.

21. Str(Num :Numeric)

Converts the given number to a string. Str(123) will be 123.

22. SubStr(S:string;posn,Num:numeric)

Returns num characters from Posn in the string S. Example: Substr({123Abc}, 4, 3) -> Abc

23. AddToDate(Dt:Date;Num:numeric)

Adds the Num to Dt

Example: AddToDate(Ctod({01/04/2005}, 10) -> 10/04/2005

24. Encode(Num:Extended;StrCode:String) and Decode(StrCode:String;Code:String)

Not in use

25. MandY(PDate : Date)

Returns the month & year on the given date.

Example:
MandY({01/04/2005}) -> 200504
MandY({01/10/2006}) -> 200610

26. HashChar(S:String)

Returns the sum of byte values in the string s.

27. DaysElapsed(D1,D2:Date)

Returns the difference in dates between d2 & d1.

Example: DaysElapsed({01/04/2005},{10/04/2005}) -> 10

28. TimeElapsed(T1,T2:String)

Returns the difference in terms of seconds between t1 & t1.

“Example:” TimeElapsed({01/04/2004 12:00:00 PM}, {02/04/2004 12:00:00 PM}) -> 24.00

29. AddToTime(TimeVar:String; Num:Numeric)

Not in use.

30. AddToMonth(D:Date; N:numeric)

Adds the number to the month in the date.

Example:
AddToMonth({01/04/2004}, 10) -> 2
AddToMonth({01/02/2004}, 5) -> 9

31. LastDayOfMonth(D:Date)

Returns the last day of the month on the given date.

Example:
LastDayOfMonth({01/01/2004}) -> 31
LastDayOfMonth({01/02/2000}) -> 29
LastDayOfMonth({01/02/2001}) -> 28

32. MakeDate(d, m, y)

Converts the given year, month, day to date.

Example: MakeDate(2001, 1, 12) -> 12/01/2001

33. ValidEncodeDate(year, month, day:numeric)

Converts the given year, month, day to date. After converting it to date it validates and adjusts to the last date of the month.

Example:
ValidEncodeDate(2001, 1, 12) -> 12/01/2001
ValidEncodeDate(2001, 2, 30) -> 28/02/2000
ValidEncodeDate(2000, 2, 30) -> 29/02/2000

34. IsEmptyValue(Value, DataType:String)

Returns T if the given value is empty. For a numeric type 0 is not considered as an empty value. Only or ‘null’ is considered as an empty value.

35. Eval(Expr:String)

Evaluates the expression and gives the result.

Example: Eval(s) -> evaluates the expression stored in S.
If S contains “A+B”. If A is 5 & B is 10 the result of the eval will be 15.

36. Lpad(S: String;Width: integer;padChar: Char)

Same as LeftPad.

37. Rpad(S: String;Width: integer;padChar: Char)

Same as RightPad.

38. Power(Num, Power:Numeric)

Multiplies the Num by itself power times.

Example: Power(2, 5) -> 32.

39. GetMod(Num, Divisor : Numeric)

Returns the remainder after dividing num by divisor.
Example: GetMod(10/3) -> 1

40. ISEmpty(Value)

Returns T if the value is empty.

41. Date()

Returns the current date of the local machine.

42. Time()

Returns the current time of the local machine.

43. Dayofdate(date)

Returns the day in the date.

Example: DayOfDate({01/04/2005}) -> 01

44. Monthofdate(date)

Returns the month in the date.

Example: MonthOfDate({01/04/2005}) -> 05

45. Yearofdate(date) : numeric [TStruct/IView]

Returns the year in the date.

Example: YearOfDate({01/04/2005}) -> 2005

46. Gen_id(S, N) :
Note: Available on Desktop only
Returns a unique number. The number will be always globally unique if N=0 else it will be unique for that session in that local machine. S is not used. So send {} as a parameter for S.

47. FormatDateTime(FormatString, DateTime)

Formats the given date & time using the format string.

The FormatString could be as given below.

String Detail
c Displays the date using the format given by the ShortDateFormat global variable, followed by the time using the format given by the LongTimeFormat global variable. The time is not displayed if the date-time value indicates midnight precisely.
d Displays the day as a number without a leading zero (1-31).
dd Displays the day as a number with a leading zero (01-31).
ddd Displays the day as an abbreviation (Sun-Sat) using the strings given by the ShortDayNames global variable.
dddd Displays the day as a full name (Sunday-Saturday) using the strings given by the LongDayNames global variable.
ddddd Displays the date using the format given by the ShortDateFormat global variable.
dddddd Displays the date using the format given by the LongDateFormat global variable.
e Displays the year in the current period/era as a number without a leading zero (Japanese, Korean and Taiwanese locales only).
ee Displays the year in the current period/era as a number with a leading zero (Japanese, Korean and Taiwanese locales only).
g Displays the period/era as an abbreviation (Japanese and Taiwanese locales only).
gg Displays the period/era as a full name. (Japanese and Taiwanese locales only).
m Displays the month as a number without a leading zero (1-12). If the m specifier immediately follows an h or hh specifier, the minute rather than the month is displayed.
mm Displays the month as a number with a leading zero (01-12). If the mm specifier immediately follows an h or hh specifier, the minute rather than the month is displayed.
mmm Displays the month as an abbreviation (Jan-Dec) using the strings given by the ShortMonthNames global variable.
mmmm Displays the month as a full name (January-December) using the strings given by the LongMonthNames global variable.
yy Displays the year as a two-digit number (00-99).
yyyy Displays the year as a four-digit number (0000-9999).
h Displays the hour without a leading zero (0-23).
hh Displays the hour with a leading zero (00-23).
n Displays the minute without a leading zero (0-59).
nn Displays the minute with a leading zero (00-59).
s Displays the second without a leading zero (0-59).
ss Displays the second with a leading zero (00-59).
z Displays the millisecond without a leading zero (0-999).
zzz Displays the millisecond with a leading zero (000-999).
t Displays the time using the format given by the ShortTimeFormat global variable.
tt Displays the time using the format given by the LongTimeFormat global variable.
am/pm Uses the 12-hour clock for the preceding h or hh specifier, and displays ‘am’ for an hour before noon and ‘pm’ for any hour after noon. The am/pm specifier can use lower, upper, or mixed cases, and the result is displayed accordingly.
a/p Uses the 12-hour clock for the preceding h or hh specifier, and displays ‘a’ for an hour before noon, and ‘p’ for any hour afternoon. The a/p specifier can use lower, upper, or mixed cases, and the result is displayed accordingly.
ampm Uses the 12-hour clock for the preceding h or hh specifier, and displays the contents of the TimeAMString global variable for any hour before noon, and the contents of the TimePMString global variable for any hour afternoon.
/ Displays the date separator character given by the DateSeparator global variable.
: Displays the time separator character given by the TimeSeparator global variable.
‘xx’/”xx” Characters enclosed in single or double quotes are displayed as-is and do not affect formatting.

48. Trim(S:String)

Removes the leading & trailing spaces & special characters from S.

Firesql(SqlName, SQLText)
Fires the SQL given in SQLText. The SQL result can be accessed in FindRecord & SQLGet functions using the SQLName.

Example:
FireSQL({SQL1},{Select name, basic from Employees}) – Fires SQL & stores result in SQL1.
FindRecord({SQL1}, {name}, {Emp-1}) – Finds the record that has name = Emp-1 in SQL1.
B := SQLGet({SQL1}, {basic}) – Returns the value in the basic column in SQL1 from the current record. So the above 3 statements will get all employee details, finds the record with name = emp1 & returns Emp-1’s basic salary.

49. FindRecord(SQLName, SearchColumn, SearchValue) )

Finds the row in the SQL result identified by SQLName that has the SearchValue in the SearchColumn.

Example:
FireSQL({SQL1},{Select name, basic from Employees}) – Fires SQL & stores result in SQL1.
FindRecord({SQL1}, {name}, {Emp-1}) – Finds the record that has name = Emp-1 in SQL1.
B := SQLGet({SQL1}, {basic}) – Returns the value in the basic column in SQL1 from the current record.
So the above 3 statements will get all employee details, finds the record with name = emp1 & returns Emp-1’s basic salary.

50. SQLGet(SQLName, ColumnName)

Returns the value of the given ColumnName in the current row in the SQL result identified by SQLName.

Example:
FireSQL({SQL1},{Select name, basic from Employees}) – Fires SQL & stores result in SQL1.
FindRecord({SQL1}, {name}, {Emp-1}) – Finds the record that has name = Emp-1 in SQL1.
B := SQLGet({SQL1}, {basic}) – Returns the value in the basic column in SQL1 from the current record. So the above 3 statements will get all employee details, finds the record with name = emp1 & returns Emp-1’s basic salary.

51. DPImport(FileName)
Note: Available on Desktop only
This function will import data from a data packet that is stored in a file given by the Filename parameter. This will work just as the import program works.

52. DPExport(DefName, FileName, ExpType, SiteNo)
Note: Available on Desktop only
This function will export the data into an Ascii file.

DefName – Name of the export definition.

FileName – Name of the ASCII file into which the data should be exported. The format of the export file cannot be changed.

ExpType :

Set ExpType to ‘exportall’ to export all the data from the transactions defined in the database.

Set this to ‘export’ to export only the latest data that has been entered or modified after the last export.

To export data that has been entered/modified after a given date & time, set the date & time to the exptype parameter.

53.FixTree(TransId)
Note: Available on Desktop only
This function will run the fix tree utility that was introduced in 493. Refer to the 493 release document for info on fix tree utility.

54. PostMDMap(Transid)

This function will run the Re-Update MD Map utility that was introduced in 493. Refer to the 493 release document for info on this utility.

55. Repost(Transid, Condstr)

This function will run the re-save transaction utility that was introduced in 493. Refer to the 493 release document for info on the utility.

56. Regenerate(Transid)
Note: Available on Desktop only
This function will run the regenerate transaction utility that was introduced in 493. Refer to the 493 release document for info on the utility.

57. PostGenMap(Transid, GenMaps, ConditionString)

This function will run the Post Gen Map utility that was introduced in 493. Refer to the 493 release document for info on the utility. GenMaps is a common delimited string of LinkEntryDocId that needs to be posted.

58.Gettransid(TransDesc)
Note: Available on Desktop only
Returns the transid of the transaction. Transdesc is the caption for the transaction.

59.GetDelimitedStr(SQLResult, FieldName, Delimiter)

This function creates a string from the SQL Result. The values in the given field name will be made into one string delimited with the given Delimiter.

Example: If an SQL result is as follows :

Col1 Col2
A 10
B 20
C 30

GetDelimitedStr({SQLResult1}, {Col2} ,{,}) -> 10,20,30

Stock Related Functions

1. GetStockValue(ItemId:numeric, DocDate : Date, IssueQty: numeric, VMethod:Char)

All stock-related transactions are stored in the StockValue TStruct. This function will return the value of the quantity issued.
It will apply the valuation method given by VMethod. Valuation methods could be FIFO or Weighted Average.

2. GetClosingStock(ItemId: numeric; DocDate: Date; VMethod: string): )

This function returns the closing stock of the given item on the given DocDate using the valuation method given by VMethod. The closing stock will be registered in the following variables:

  • Op_StockQty
  • Op_StockValue
  • Rec_StockQty
  • Rec_StockValue
  • Iss_StockQty
  • Iss_StockValue

These variables can be used in expressions. These will have the appropriate values after the function is called.

Default Variables

UserName Name of User logged into the s/w.
UserGroup Name of User group

 

User Defined Variables

CompanyName Name of the company into which user has logged in. This is from the control table.
UserCateogry The Category under which the user is classified.
UserDepartment The department under which the user is classified.
DbRep Debit Representation in Financial system.
CrRep Credit Representation in Financial system.
ProfitPath The path in which P5RM is installed.
_MainCurr Main currency set for the project (Eg. Rupees, Dollars, etc.)
_SubCurr Sub currency set for the project (Eg. Paise, Cents, etc.)
_CurrDecimal Decimal defined for the project currency.
finyrst Financial year start date.
finyred Financial year-end.
afinyrst Used in case of the extended financial period.
afinyred Used in case of the extended financial period.
_millions T if amount fields have to be represented in millions.