Agile Developer

Drop down fields

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

Drop down fields

Drop-down fields show a list of choices for users to select. The drop-down values can have 3 different sources:
It may be from a list of values, from a field in another form or an SQL result, OR the data source can be chosen in the Data panel in the properties section.

Select from List

Choose the “Select from List” option to create a field with the list of drop-down values. The values are provided as a comma-separated string in the “List Values” field.
For example, if we need a gender field as a drop-down, create a drop-down field with the “Select from List” option, and in the list values field enter Male, Female, and Transgender as values.

caption

 

Select from Form

Choose the “Select from Form” option to create a drop-down field that will list data from a field in another form, as drop-down values

caption

Example: Consider a user login form with a drop-down field to select the user to whom the login is to be assigned. The drop-down should list the names of users from User Master. Add a drop-down field; choose the “Select from Form” option. Choose the User Login in the “Data source form” field. The fields in the User Login form will be listed in the “Data source field”. Choose the “axusername” in this field. Axpert automatically frames an SQL & displays it in the SQL text.

Save normalized

Notice that the SQL framed by Axpert in a “Select from Form” case has 2 columns. The first column is the recordid of the source table and the second is the data source column. The drop-down values are listed in run time from the data source column in the SQL statement. If Save Normalized is enabled while saving, the recordid is stored instead of the value.

Select from API

Choose the “Select from API” option to create a drop-down field that fetches data from an API.

caption

Consider you want data from another application that needs to be linked to a particular field. Create an API definition for the field by
inserting the URL and response string. If you do not have the URL, generate it using the Get Axpert API option. Once an API definition is
created, it will be available in the API Name dropdown. Select the API and proceed with the execution.

 

Select from SQL

This is a critical feature to implement many business rules. This may also be used to write complex logic as SQL functions and get the result into this field.

caption

Choose the “Select from SQL” option to create a drop-down field that will list the values returned by an SQL statement as drop-down values. Enter the SQL statement in the “SQL text” field. The first column in the SQL result will be listed as drop-down values.
The pop-out button at the right top corner of this field will open a full Database console. This can be used to inspect all the DB objects, create SQL functions, procedures, triggers, indexes, etc. The SQL result can be executed to check out the result of the SQL statement.
In case, instead of storing the drop-down value in this field in run time, the recordid of the source table has to be stored (save normalized), the first column in the SQL should be the recordid and the second field should be the data source field. The Save Normalized check box should be ticked.

SQL parameters

The fields defined in this tstruct may be passed as parameters to the SQL statement. SQL parameters are prefixed with a colon in an SQL text. At run time, the values entered in these parameter fields will be passed as parameter values to the SQL statement. This enables developers to pass parameters to DB functions/procedures or apply filters and get only relevant data as drop-down values. SQL parameters may be tstruct fields or application variables. Axpert provides a set of default app variables like username’ (logged in user name), user role (the roles assigned to the logged user) etc. The user defines app variables may also be added in the “App variables & params” option under Utils. A list of default application variables is available in the Default App Vars sections.

Example:
Consider a table named CountryData that contains three fields named Country, State & District. This has records of all countries, the states within them, and districts within the states.

Consider a form in which there are 3 fields Country, State/Province & District/Territory. The country is a drop-down field that lists all the countries from CountryData. The State is a drop-down that lists all the states within the selected country. The District/Territory is a drop-down field that lists all the districts within the selected state.

Each field will have a select statement that brings data from CountryData. The SQL in the state field will have a country as a parameter. The SQL in-district field will have :state& :country as parameters.

Handling Large SQL results

Consider cases where an SQL result returns a large set of records that may be more than ten thousand records. Getting this kind of result and binding to a drop-down in a form will lead to a non-responsive web/mobile app. The user may also need an advanced filtering option to do a quick filter from the large result to select the required value.

This is achieved in Axpert by introducing a tag named {dynamicfilter field1, field2, … ~ Field1Caption, Field2Caption, …} in the SQL text. This tag should be added to the where clause of the SQL. The column names on which an advanced search should be provided to the user at the run time follow the dynamic filter tag. The captions that are to be displayed in the search window for each column are provided as comma-separated strings after the ‘~’.

Once a dynamic filter tag is introduced in an SQL, the SQL result data is brought to the front end in blocks of 1000 rows. This can be modified in developer options using the “Dropdown fetch size” property.
1. Select a.username,a.mobile, a.dob from axusers a {dynamicfilter a.username, a.mobile, ~User Name, Mobile No} order by username

2. Select username,nickname,email from axusers where actflag=’T'{dynamicfilter username,nickname,email, ~Username,Nickname,Email}order by 1

3. Select username,nickname,email from AxInternalUsers where actflag=’T’ {dynamicfilter username,nickname,~UserName,NickName }
union all
Select username,usergroup,null from AxExternalUsers{ dynamicfilter username,usergroup~Username,Nickname } order by 1

Drop down fields with the capability to add

Dropdowns may be listing data from other forms. If the required value is not found in the drop-down at run time, users may need an option to add. That means, data should be added to a master form. Select the form name in the master “property”. At run time, the drop-down will have a link to add a new value. With the click of this option, the form (that is set in the master property) will be opened in a new mode. On submission of the data, the drop-down list will be refreshed and the new value will be listed in the drop-down.

Auto select in drop-down

Consider a requirement in an application that has a drop-down field. The value in the drop-down field should be auto-selected if there is only one drop-down value. If there is more than one drop-down value, the user will choose from the drop-down.

caption

Tick Auto select in the developer mode, to enable this functionality. Enabling this without proper need will reduce the performance of the end application. So, this feature should be used only if this functionality is an absolute necessity.

Refresh on Save

The drop-down values may change after saving data in the form. Set this option for such needs. On saving the data in the form the drop-down values of such a field will be fetched from the database. Enabling this without proper need will reduce the performance of the end application. So, this feature should be used only if this functionality is an absolute necessity.

caption

Example: Consider a task allocation form with fields:

  • Task Name – Dropdown from Tasks Form. The drop-down field SQL has a where TaskForms.Status=’New’.
  • Assign to – Dropdown from employee master.

On saving data to this form, the status field in the task form is updated with Assigned. So, when new data is entered after saving, the task name drop-down values should be refreshed.

Fill fields

Fill fields are associated with drop-down fields. The SQL in a drop-down field may have many columns. The columns in the SQL, other than the recordid column and the data source column, may be mapped to a fill-field in this tstruct. The column in the SQL that is mapped to a fill-field is called the source column. At run time, when the user selects a value in a drop-down, the value from the source column of the selected row is populated into the fill-field.

To define a fill-field:

1. Add a text or numeric or date field.

caption

2. Select Mode of entry as “Fill”.

caption

3. Select the associated drop-down field name in the “master” property.

caption

4. Select the source column from SQL in the “Source” property.

For example, consider a form that contains 2 fields viz. EmployeeName & EmpGender. The employee name is a drop-down field in which the user may choose an employee name from the master. The SQL in the employee name field should contain the gender column from the Employee Master. Add a short text field for EmpGender. Select the mode of entry as “Fill”. Select “EmployeeName” in the master property & ‘Gender’ in the source property.

Multiselect fields

A multi-select field is the same as a drop-down field from a source SQL. The only difference is that a user can select one or more values. The selected values will be stored comma-separated in the field.
Consider a case where the user has to select one element from across multiple groups. That is, first a set of choices are listed for selection. On selection, the next set of choices is listed. Then the next set of choices and so on.

caption

Consider a drop-down in which the user has to select a color, a style, and a size. The SQL should result as below. The GroupBy, GroupOrder, Selected & Rule should be columns in the SQL. In this sample result given below, first, the dropdown will show all the colors. Then the styles will be listed, then the sizes will be listed. Users can choose one after another in the same field. The group order column specifies the order in which the choices have to be listed. ‘T’ in the Selected column, auto-select the value into the field by default. The Rule string has one position for each set of choices. M in this string stands for Mandatory. If a particular group is set as mandatory, then the user will have to choose at least one value in this group.

For example, create a multiselect form to manage the details of an employee.

create another form to select the employee details and use SQL to map the previously created form

Go to run site and insert values in the fields created.

The list view of the values inserted

Find the employee multiselect form and choose the input from the drop down

Try Out

In the employee form, try choosing the department of the individual from the dropdown list. The list should have all the departments in the company as options. Next, try selecting the employee’s department from the department master form. Enter appropriate data source and filed names while selecting from the form. Get the name of the manager the employee is reporting to by using SQL query. An employee can have more than one Qualification, enter the qualification of the employee using the multi-select option.

Please save the form after completion!

NoteNote:The Department master has fields like dept code, Department name, role, etc.