Axpert 11
TStructs (Forms)
- Create Form
- Create DC
- Create Fields
- Grid DC/frames
- Formulae in fields
- SQL in fields
- Drop down fields
- Autogenerate fields
- Table fields
- File upload fields in forms
- Photos &Image fields
- Barcode /QR Code Scanner
- Fill Grid
- Posting data – Gen maps
- Updating fields in DB tables – MD Map
- Toolbars in tstructs
- Tracking changes/Audit trial
- Try it yourself
IViews
Scripts
Axpert Jobs
Axpert Cards
HTML Plug-ins
Users & Roles
- Users, roles & responsibilities
- Responsibilities
- User Role
- User Login
- SSO Authentication
- Stay Sign In
Workflow
Axpert API
Application Var/Params
Publish Axpert Apps
Axpert Mobile
Settings
- Axpert installation
- Change password
- Forgot Password
- Developer Options
- Global Settings
- In-Memory DB
- Notifications for Long Running Webservice’s
- Axpert Configuration on web
- Axpert Licensing
Utilities
Customization
- Main Page Customization
- Home Page Customization
- More API
- Custom User Interface
For Reports - Custom HTML In Forms
- CSS And JS Customization
- Developer Notes
- Hooks In Forms
- Third Party SSO Integration
WebServices
Axfast
IViews –Reports & Lists
About IViews in Axpert
IViews provide the result of an SQL statement displayed as a tabular report. The SQL may have parameters. Parameter values are accepted by the user at run time. Each column may have hyperlinks that may be used to drill down to another IView or open TStruct/form along with form data. Scripts may be written to do special functions in an IView. These may be executed with the click of a button.
IView parameters
Parameters SQL are automatically considered as IView parameters. Users can enter values for each IView parameter. Each parameter value will be auto-populated or accepted by a user based on the definition made for each parameter.
The parameter values may be calculated using a formula that can be provided in the expression property. The expression may use other parameter names as variables. App variables may also be used in expressions. Use the validate-expression property to validate the user input for the parameter. If valid values are not provided, the IView will not bring results.
Parameter values may be selected from a drop-down. The drop-down values may be populated from a SQL result. The SQL text can be defined in the SQL property. Other parameters & app variables may be used as SQL parameters. A picklist is used when the SQL result is expected to bring more than a few thousand rows. In this case, the data will be fetched from the database in blocks of a few hundred records depending on the characters entered in the parameter. For example, Create an Iview with employee details as shown below
In the params section, Select the mode of entry as ‘Pick List’ and provide the corresponding query like select ename from ivifo1 {dynamicfilter ename ~ name} as shown below
Navigate to the run site and find an existing value from the Iview in the search box. If the corresponding values exist, it is displayed on the screen
Multiselect parameters
The IView SQL should be joined with an Axpert table named axpselectionc. This table will be populated with user-selected values in a multi-select parameter.
In the params tab, add a parameter. Set its mode of entry as Multi-select. The multi-select is like a drop-down parameter. It allows users to select more than one value. The selected values are added as rows to the axpselectionc. The selected values are stored in the Selection field in this table.
Example: Consider an IView that displays Employee details based on selected employees.
IView SQL : Select EmployeeName, Gender, Age, GrossSalary from EmployeeMasterA, AxpSelectionC B where A.EmployeeName = B.Selection
Add a parameter name pEmpName. This name can be anything. Set mode of entry as Multi-Select.
Parameter SQL – Select EmployeeName from EmployeeMaster order by EmployeeName
IView columns
The SQL columns will automatically be added as columns in the IView. Select any column and change its column properties. Columns may be hidden. The table kind of a view displayed at the top can be used to resize column width and reorder them. New columns may be added to the IView. The values for the user added columns may be set using column expressions. The expressions may use other column names as vars and app vars.
Hyperlinks
The values in a column may be displayed as a hyperlink. The option to create a hyperlink is available under the options button that is displayed on each column at the top of the table in the column section. The hyperlink may be configured to load form data, open a struct, or another Iview. The data from the Iview columns/params can be passed as parameters. The parameter may be assigned to a field in a selected tstruct or another parameter in an Iview.
Dynamic Hyperlinks
We employ one on one mapping, manually to set a hyperlink. It becomes a tedious task if there are more than 20 rows that need a mapping from corresponding tstructs into a single form. Individual rows can define separate forms. For instance, A Bank transaction can have multiple entries, it can have transactions related to different individuals and companies associated with it. Each row will be linked to a corresponding form detail.
Create a new form with a suitable name like DHF1. Create an autogenerate field for id, with appropriate prefix and length. Similarly, create another form DHF2 with autogenerate field for id, add appropriate prefix and length. Create an Iview with the name dhliv1 (dynamic hyperlink F1). In the SQL section add the snippet mentioned below
Select parameter_name from transid
Create an Iview with the name dhliv2 (dynamic hyperlink report). In the SQL section add the snippet mentioned below
In the above code snippet, the syntax includes a ‘t’ as a prefix for tstruct, ‘i’ as a prefix for iview followed by transid of tstruct or iview respectively.
IView properties
The Iview properties are used to change the look and behavior of an IView. The group field can be used to group rows and show them as one row. All rows that have the same value in the group field will be shown as one row in the IView.
Load TStruct property can be selected to load a form from the IView. In this case, the IView toolbar will have an “Add New” option. This will load the selected tstruct form in the new mode for the user to add new data. It will also provide a hyperlink to load a record in edit mode.
Include multiple report headers in Iview’s excel format
By default the Iview data in excel format has single header as below
To add multiple headers, go to Iview properties and enter suitable names in the report fields
After changing the headers in the Iview properties, the excel data will be as follows:
On DbClick
Define a script in IView properties
Select the script under On Dbclick and save the Iview.
On the run site, select an entry and double click on it. The Message specified in the script appears on the screen.
IView Layout
There are four types of Iview layout
- Table – This is a default layout. It has an additional configuration to set width and alignment.
- Tile – This layout displays cards next to one another
- Card – This layout displays cards one below the other.
- Custom – This layout supports customization using HTML.
There are a few mandatory keywords to consider while creating a Card or Tile layout.
- axp_icon – This keyword allows to set a 3 character text, that will be displayed as an icon. If axp_icon_img is specified then this column can have an empty string ‘ ‘ as a value.
Example: AGL, SBI, KMB - axp_icon_img – The image which needs to be displayed should be available in the following path
../{{webDirectory}}/{{projectConnectionName}}/Images/
If axp_icon keyword is used then the column can have empty string ‘ ‘ as a value
Example: a.jpeg, abc.png - axp_title – A mandatory keyword which is used to specify the title on every card.
- axp_description – A mandatory keyword which describes the functions available in the particular card.
- axp_status – An optional keyword that is used to specify the status of the project. The values in axp_status can be active, delayed, progress
- axrowoptions – An optional field that needs input in the format as shown below
Name,Caption,action/hyperlink/script(type),materialIcon
Use the below code snippet and try creating Iview layouts providing suitable values:
SELECT 'wifi.png' as axp_icon_img, '' as axp_icon, 'Axpert 10' as axp_title, 'People –3 Developers, 2 Consultants, Monthly Billing INR 5 Lakhs' as axp_description, 'Active' as axp_status, 'link_ttl,Commercials,hyperlink,home~billing,Billing,script,add~tasks,Tasks,script,folder~statusupdate,Status Update,script,face' as axrowoptions from dual
UNION
SELECT 'agile.png' as axp_icon_img, '' as axp_icon, 'Axpert 11' as axp_title, 'People –3 Developers, 2 Consultants, Monthly Billing INR 5 Lakhs' as axp_description, 'Delayed' as axp_status, 'commercials,Commercials,script,home~billing,Billing,script,add~tasks,Tasks,script,folder~statusupdate,Status Update,script,face' as axrowoptions from dual
UNION
SELECT '' as axp_icon_img, 'abc' as axp_icon, 'Axpert 9' as axp_title, 'People –3 Developers, 2 Consultants, Monthly Billing INR 5 Lakhs' as axp_description, 'Active' as axp_status, 'link_ttl,Commericals,hyperlink,home~billing,Billing,script,add~tasks,Tasks,script,folder~statusupdate,Status Update,script,face' as axrowoptions from dual
UNION
SELECT '' as axp_icon_img, 'png' as axp_icon, 'Axpert X' as axp_title, 'People –3 Developers, 2 Consultants, Monthly Billing INR 5 Lakhs' as axp_description, 'Delayed' as axp_status, 'commericals,Commericals,script,home~billing,Billing,script,add~tasks,Tasks,script,folder~statusupdate,Status Update,script,face' as axrowoptions from dual
Create an Iview and add SQL query. Submit and save the structure. Visit the run site and find the Iview created.
Custom Layout Query example
SELECT '' as axp_icon_img, 'shopping_basket' as axp_icon, 'Card 1' as axp_title, 'Card is a responsive content container with an extensible option for headers, footers, images, and a wide variety of content.' as axp_description, 'Active' as axp_status, 'profile,Profile,hyperlink,account_circle~schedule,Schedule,script,pending_actions~notifications,Notifications,script,circle_notifications~settings,Settings,script,settings_suggest' as axrowoptions, '#FF00FF' as cardcolors from dual
UNION
SELECT '' as axp_icon_img, 'card_giftcard' as axp_icon, 'Card 2' as axp_title, 'Card is a responsive content container with an extensible option for headers, footers, images, and a wide variety of content. Many examples and tutorials.A card is a flexible and extensible content container.' as axp_description, 'Delayed' as axp_status, 'profile,Profile,hyperlink,account_circle~schedule,Schedule,script,pending_actions~notifications,Notifications,script,circle_notifications~settings,Settings,script,settings_suggest' as axrowoptions,'#33691E' as cardcolors from dual
Pivot in IViews/Reports
Tabular reports are collections of rows and columns. In a pivot report, the data at the pivot column will be transposed from rows into columns. The cell values that follow the pivot column will be plotted as values in the transposed columns.
This is defined in an IView by naming the pivot column as “pivot”. The IView should be ordered by the pivot column to ensure the same values in pivot columns are listed together.
Example:
Consider a report as below:
Month | Product Group | Sale Qty | Sale Value |
---|---|---|---|
Jan | Electronics | 500 | 50,00,000 |
Feb | Electronics | 3000 | 18,00,000 |
Mar | Electronics | 1500 | 20,00,000 |
Jan | Mobile | 400 | 45,00,000 |
Feb | Mobile | 3200 | 20,00,000 |
Mar | Mobile | 1100 | 18,00,000 |
Jan | Wearables | 600 | 58,00,000 |
Feb | Wearables | 3500 | 28,00,000 |
Mar | Wearables | 1800 | 30,00,000 |
If the above report is pivoted at Product Group, the result will be:
Month | Electronics | Mobile | Wearables | |||
Qty | Value | Qty | Value | Qty | Value | |
---|---|---|---|---|---|---|
Jan | 500 | 50,00,000 | 3000 | 20,00,000 | 600 | 58,00,000 |
Feb | 3000 | 18,00,000 | 400 | 45,00,000 | 3500 | 28,00,000 |
Mar | 1500 | 20,00,000 | 3200 | 20,00,000 | 1800 | 30,00,000 |
Tool bars in IViews
IView will have the following options by default:
- New (if a tstruct is attached to the IView)
- Search
- Data
- Parameter
- Add view
- Graphs
- Options
- User added options
Row options
Add a column named AxRowOptions in the IView. It may be added as part of the IView SQL. The value should be a string as below:
- (Name1),(Caption1),(hyperlink name or script name),(MaterialIcon1) ~ (Name2),(Caption2),(hyperlink name or script name),(MaterialIcon2)…
This definition will provide the given set of options for every row in the row options column.
Download files from IViews
Download the file/attachments in IViews when provided with the mapped path. To achieve this, the below view configurations need to be applied.
Concerned Reviews should have the following columns by default.
- transid: transid of the tstruct
- If isdbattach column value is true then transid column value should be (“transid+fieldname”)
- If isdbattach column value is true and is DB attachment is header attachment then only “transid” should be passed.
- fieldname: field name in tstruct(to be kept empty for header attachment)
- recordid: transaction record id.
- isdbattach: this is an optional column which determines wheater the attachment is db attachment or file attachment, if the attachment type is db attachment then this column value should be true
- axp_attach (can be any one of the following):
- Text to be shown on download hyperlink (this column is a flag to enable/disable attachments component)
- Grid attachment name (value of grid attachment)
- Full mapped path if transid, field name, recordid is not provided
- ResolvePathForTemplates: “resolve attachment path” property is used for view templates in case of resolving image path to show image in iview templates. “Resolve attachment path” Advanced Setting configuration property with property code as “resolve attachment path” to be added as true or false.
Note: If File Server and App Server are different systems, then File Server needs to be authenticated for downloading or uploading the files with the following Application Variables, provided both servers should be in same domain.
- axp_mapusername
- Username for the domain
- axp_mappwd
- Password for the domain
- axp_domain
- Domain Name
Example:
-
- DB Attachment
- select ‘gddwn’ transid_real,gddwn1id recordid,’dc2_image’ fieldname,dc2_image axp_attach,’gddwndc2_image’transid, ‘true’ isdbattach from gddwn2
Non DB Attachment
-
- select null transid_real,gddwn1id recordid,null fieldname,replace(axpfilepath_img,’*’,”)||axpfile_img axp_attach,null transid, ‘false’ isdbattach from gddwn1
DB + Non DB Attachment
- select ‘gddwn’ transid_real,gddwn1id recordid,’dc2_image’ fieldname,dc2_image axp_attach,’gddwndc2_image’transid, ‘true’ isdbattach from gddwn2
union all
select null transid_real,gddwn1id recordid,null fieldname,replace(axpfilepath_img,’*’,”)||axpfile_img axp_attach,null transid, ‘false’ isdbattach from gddwn1.
Try Out
List all the active employees in the employee master using IViews.