Documentation
Home
Evaluation
Summary for programmers
Product limitations
Goals of Axisbase
Quick start
Installation
Using the launchpad and opening databases
Connecting to a sample database
Using building blocks
Planning
Define the purpose
Define the requirements
Borrow existing work
Determine the architecture
Design the data model
Design the process model
Deploy and maintain the product
Tutorials
building blocks
Performing a mailmerge
Bulk e-mailing
Programming
Single-threaded progress indicator in c#
Reference
Database menu items
Import XML
Save Copy As
Integrity Check
Change Password
Database Properties window
Opening the database properties window
Record types tab
Display types tab
Roles and Users tabs
Sidebar tab
Database ID/Links tab
Counters tab
Building blocks
Building blocks window
Editing grids and cells
Hyperlinks and nesting
Data Subset window
Data Outsource window
List window
Window window
Report window
Bulk Operation window
Label Printer window
Choosing a data source
Special topics
Expression syntax
Browse records
Storing building blocks within other building blocks
Programming
Using custom code in building blocks
Using Axisbase as an embedded database
Axis1.Util namespace reference
Axis1.Data namespace reference (Fishnets)
Axis1.Data namespace reference (other)
Axis1.Forms namespace reference
| Data Subset windowThe Data Subset window is accessed from the Building Blocks window. Please refer to Designing the Process Model for information on designing data subsets. A note on popup windowsThe Data Subset window uses a lot of popup menus and popup windows to allow you to see the big picture and hide the details of each step. These windows don't have an "OK" button; changes take effect immediately. Just click outside the window to hide it, or just start working on the next task and the popup will be hidden. Adding and removing tables & stepsRemember that a data subset describes a temporary in-memory relational database (or data set) that consists of a subset of records from the whole database. The in-memory data set can have multiple tables. In the example shown here, there are two tables: FLY.task and FLY.job. Each table has a separate data source and sequence of steps to restrict or manipulate the table. Press Add Table to add a new table. Click the red X by the table name to remove the table. Click the black + symbol to add a new step in any position. Click the red X by the step to remove it. ParametersIt's worth studying and experimenting with parameters because that is one of the main strengths of data subsets. In many business scenarios, you need to display records that meet criteria entered by a user - for example, locating a customer by name or zip code, or some other property. There are two steps:
Once you do these two things, when you load a data subset (by going to the Preview tab), Axisbase will ask for the values for the defined parameters. In a situation where a data subset is invoked from a hyperlink from another building block, parameters can be supplied from the calling block; in this case, Axisbase won't ask for the value. Optional filtersOptional filters come into play when you enter text into the entry "Prompt to control whether this filter is applied". That entry is available for fast filters and complex filters. Here is an example of using an optional filter. Suppose you already defined parameters for company name and zip code for use in a customer search. When you load the data subset, Axisbase forces you to enter both a company name and a zip code, and it uses both entries to filter records. Many times, you want the user to be able to decide whether to search by name or zip code or both. In these cases, you enter a prompt in the filter popup window, such as "Filter by zip code?" or "Filter by company name?". When prompts exist, Axisbase allows the user to choose whether to use that filter. You could build a data subset that has a lot of different filters, each one with an optional filter prompt, to give the user a lot of flexibility in how to locate records. Optional filters can be used with or without parameters. Here is one of each kind:
In the upper example, the user will get the choice of whether to filter by task status equals 'A', or omit the filter. In the lower example, the user will get the choice of whether to filter by task status, and additionally the user will be asked to choose which status code to filter by. Fast filtersA fast filter can only work on one property, and you can only choose among the comparisons: Equals, Starts with, and Is in range. These limitations ensure that indexed searching is used, which is faster than scanning all records. If you need a different kind of comparison, use a complex filter instead. But for good performance, you should generally try to use at least one fast filter (unless there are a small number of records total). For the "starts with" comparison, the search is case-insensitive. It only works with string data. For the range comparison, you can choose a lower limit, an upper limit, or both. Either limit can be a value you enter at design time or a parameter that the user enters at load time. Complex filtersThe Complex filter popup window doesn't give you much to go on, so here are some examples to get you started. Also see Expression syntax for the complete reference to expressions. This one matches all customers whose city contains "burg": match(#MOON.customer.city, "burg") This one matches all customers whose name or notes contains the value of the parameter "findword": (|| means "or") match(#MOON.customer.name, @findword) || match(#MOON.customer.notes, @findword) This one matches employees whose current salary is lower than 10% more than their starting salary: #PLUTO.emp.salary < (#PLUTO.emp.startingsalary * 1.1) Multi-valued parameter filtersMulti-valued parameter filters allow the user to enter more than one value for a parameter, and Axisbase will pass records through the filter that match any of the values entered. This kind of filter has good performance because it uses an index. It's a good alternative to a complex filter when possible. You must define the parameter as multi-valued in order to use it with this kind of filter. SummariesIf you know SQL, summaries are like a 'group by' clause. When you include a summary step in a table, the data subset constructs a new table from a summary of the data in the original table. If you check "retain original table as detail", then the final dataset will contain both tables in a master-detail relationship; otherwise the original detail table is dropped. To create a summary you enter three things on the popup window:
You must press Add for each summary column. The created summary table will have columns for each group-by column as well as a column for each calculated column. When editing a data set that has the detail table attached, Axisbase will recalculate the summary data after each change. Calculated columnsA calculated column step adds a column to the in-memory data table and fills it with the results of a calculation. The calculation can be based on other values in the record, parameter values, and/or constants. The format of the calculated column is NAME=EXPRESSION. The name you enter will be the column name. Note that columns that come from database records will be named with the Axis ID and type, such as 'MOON.customer.name', while calculated columns will have simple names that you define, like 'newsalary'. The expression can be anything defined by Expression syntax. Linking tablesUse the Link table step to connect two tables in the data set in a master/detail relationship, such as sales linked to sale line items, or customers linked to sales. Linked tables are useful for lists and reports when you want nested (grouped) data. The link table step should be added to the master table. In the popup window:
If the above is confusing, remember that in both cases, you are choosing the column that unquely identifies records in the master table. You should generally define the master table first (possibly with filters), then define the detail table (possibly with filters), then add the link step to the master table. By creating a link, you are implicitly creating a filter on the detail table; only those detail records that match one of the master records will be included in the data set. Joining tablesWhen joining tables, Axisbase combines two tables into one. For example if you have a customer table and a sales table, you can join them so that each sale record has copies of the customer columns added on to it. In you know SQL, then you will recognize that joining tables is similar to a SQL inner or outer join. This is also called "denormalizing" and is standard practice in databases for producing reports or other non-editable search results. Since Axisbase supports linking tables, and Axisbase lists are designed to work with linked tables, the role of joining tables is reduced. You define a join step the same way as a link step - please see the notes above on linking tables. Union of tablesCombine two tables in the data set into one table. The columns of the two original tables must be identical, and the rows of both tables are included in the final. Think of this as placing tables one under the other. A common use of unions is to perform two or more unrelated fast filters and get the records that match either filter. This is different from adding two or more filters on the same table. If you have multiple filters on the same table, only records that match all filters will be included. To load a union where both tables are based on the same record type:
|