New Features
Catalog: Public tables/views can be browsed
In the top-level organization-wide data catalog public tables and views are now displayed by default and it is possible for any xMart user to access their content. This improves the usefulness of the data catalog because it surfaces the most complete and publicly shared datasets to all users. This means any xMart user can access the data view page of any public object and filter, sort, export and access the public object’s API.
Note that an object is considered public when it is made available to anonymous users via the OData API.
It is still possible to view non-public objects to which you have access (check “Private Data”) and to see the titles and descriptions of non-public objects to which you do not have access (check “Other Data”).
The behavior of mart-level data catalogs has not changed.
The eye icon indicates a public object.
It is possible to export and access the API of public objects.
New Custom SQL Views Page and Change History
A new page dedicated to a mart’s custom SQL views has been added under the Model tab. From this page it is possible to see all custom views which are registered (available in the UI and API) or unregistered (only available in the DB). You can also register or unregiser them on this page and edit a view’s properties (title, description, categories, tags, security roles) in the same way that they can be edited on the Admin/Catalog page.
The structure of the view is displayed (list of fields and their SQL data types) as well as the SQL definition of the view. The view definition is read-only; it is still necessary to create and modify custom SQL views via the DB.
Finally, it is also possible to see the change history of any registered or unregistered custom SQL view and compare different versions graphically.
Please see the new how-to article on custom SQL views
Data Entry SubForms (edit table on form)
A new “sub forms” feature has been added to custom data entry forms allowing a “parent” form to contain an editable table of “child” records. This is useful for scenarios such as a data entry form for lab samples where each sample has 1 or more tests. A form can be created to enter a single sample which includes a sub form for the tests. The sub form is displayed as an editable table.
Usability note (in progress): currently, the sub-form only becomes visible after first submitting the main form and re-opening it. This should be addressed in a future version.
Please consult the how-to article on sub forms
Enhancements
Forms
Multiple field item selector
When a field on a form is a foreign key field, it is now possible to display multiple fields from the foreign key table in the dropdown list. Although it’s not really a dropdown list anymore but rather a popup showing a searchable table, like this “EPID Picker” on a lab sample data entry form:
In this example the EPID (Epidemiological ID) field is a foreign key to the CASE table. In the form definition, you can enter a comma-separated list of fields in the CASE table to display in the selector:
Forms Welcome text supports html
The welcome text that appears at the top of a form now supports html so that clickable URLs and basic formatting can be shown.
Catalog
Render URLs as hyperlinks in gridview
Data tables or views which contain URLs are now automatically rendered as hyperlinks in the gridview.
Ability to download REFMART data model
Users of REFMART (the reference database) can now download the model, which helps serve as a data dictionary by providing field descriptions and datatypes.
Pipelines
Improved FormatDate behavior and support for Hijri dates
The default behavior of the FormatDate command has been improved to handle a much wider variety of mixed date formats. To take advantange of the new abilities, simply do not specify the InputFormat property on the FormatDate command. By ommitting the InputFormat property, FormatDate determines the date format on a per record basis and can therefore handle a wide variety of date formats and issues.
The new abilities include:
- test for various common date separators (/,-,.)
- check for Hijri dates and convert to Gregorian
- automatically determine whether a format is day/month or month/day
- check for Excel date format (integer representing days since January 1, 1900) and convert to Gregorian
New GenerateHash command
A new GenerateHash command is available which generates a single hash value per record based on 1 or more values and an algorithm (only “md5” currently supported). A hash value is like a fingerprint that combines the essential elements of a list of values.
For more details, consult the GenerateHash command in the pipeline script guide.
GenerateHash command examples, showing hashing of a single column, 2 columns or all columns:
New pipeline variable LAST_BATCH_SUCCESS_TIME
To make it easier to pull only source data which was modified since the last successful batch, a new LAST_BATCH_SUCCESS_TIME variable is available in pipelines. This evaluates to the timestamp that an origin last extracted source data and resulted in a successful commit.
By using this variable in filters of various Get commands, and presuming there is some type of “modified date” timestamp in source data, it is possible to extract just the delta since the last successful batch. This greatly speeds up these kinds of loads and allows them to be run more frequently.
Please see the article on using system variables in pipelines for more information.
Conditionally trigger PostRun depending on batch status
There is now an option to conditionally launch PostRun tasks such as RunPipeline and CallWebService, depending upon the final status of the batch.
Multiple statuses can be entered.
Performance
Reorganized background job queus to prevent blockages
Behind the scenes, background jobs were reorganized in a way to prevent priority jobs from being blocked by lower priority jobs. In some cases this will speed up interactivity with the system.
Upgraded webapp + api app to dotnet 6
Both the user interface and API applications have been upgraded from dotnet 4 to dotnet 6, which should yield some performance improvements.
Other changes
4.24.0
- #4579: Batch preview top 5 values should only show currently selected rows
- #4501: Increase the length of the DBView field in CATALOG_CUSTOM_VIEWS to 450
- #4521: Ability to control sort direction in FK_FIELD_TO_SORT_BY in Forms
- #4312: Batch Error Log record has no LogBatchID
- #4526: Unable to download only filtered duplicated data rows
- #4524: Leaving off the TableNames Attribute on MergeTables causes XML parse error
- #4539: Make log details screen much wider or resizable
- #4551: data_export_queue is on 2 hangfire servers
- #4554: Mart import should not automatically create batches unless user chooses
- #4558: Remove “Statistics” line in batch history
- #4561: Load page UI improvement
- #4570: Add Tables Collection to Custom Script.
- #4492: GetExcel Validation doesn’t allow a variable for StartingRow
- #4504: Support http OPTIONS (http 405 errors)?
- #4575: Data to model - display which file types are supported
- #4556: Mart Import Export doesn’t include the connections
- #4606: Public API url returns 200 but with no body - 0 size file in data lake
- #4608: Error downloading form template
- #4609: GetMart should extract NULLs as NULLs
- #4616: Unable to hard delete table in NT Stage because of validators
- #4614: Do not require Forms.Description (in NT Stage)
- #4592: Filter correct on the API for Contains (when column name contains space)
- #4625: “Something went wrong” during record check -> GUID validation
- #4626: Can’t see subform (Issue when form_code == table_code?)
- #4335: Support ODK Central Bearer connection type (not oauth but close)
- #4623: Exception while loading top 5 values in batch data view table filter on System Pipelines
4.23.1 - 4.23.9
- #4470: Enable reprocess batch while waiting approval
- #4516: GetExcel: Exclude namedRanges’s names depending on TableType when listing names
- #4580: Why do invalid pipelines on mart health page take so long?
- #4613: Data view filter’s Sorting and Filtering breaks after filtering
- #4568: Form labels and descriptions
- #4584: Newly registered custom view has no stats (error on browsing)
- #4356: Organise Azure Subscriptions/Resource Groups
- #4586: Dynamic url to api does not take the “in” filter into account
- #4594: Incorrect Error shown for conversion error
- #4553: Unexpected “stopping!” message when refreshing view stats
- #4568: Form labels and descriptions -> change visibility for permission
- #4540: Catalog view stats is using mart rather than sys connection string
- #4489: Home page figures different from Catalog page figures
- #4508: Many public OData requests in DB for object that does not exist
- #4507: FindReplace doesn’t verify if the ReplaceWith has been set
- #4501: Increase the length of the DBView field in CATALOG_CUSTOM_VIEWS to 450
- #4467: Published Pipelines shown as being Draft
- #4445: Return server error if mart folder does not exist to prevent relay caching by