Page 12: Data Management

Mobile Data Studio supports a full range of data management functions, for the individual, through to the largest of enterprises. For individuals and small businesses, simple and efficient export to HTML (web page format) and Excel may suffice (therefore don't be deterred by some of the more complex systems below, they are applicable to large enterprises).

Open projects in Mobile Data Studio, and via 'Project Tasks' (see above) access the powerful functions in 'Database' mode.

The information collected in a project session with Mobile Data on a mobile device can be viewed in Mobile Data Studio server side, exported as HTML, XML, KML and KMZ files, and to Excel, and exported to database systems via ADO (eg Microsoft Access, Microsoft SQL Server, Oracle, SAP, dBase, MySQL, FileMaker) and Data Script.

There are data formats and management facilities for many applications, accessed from the Database window and menu:

Formats - such as for a useful spreadsheet in Excel or receipt of information wirelessly into an enterprise database server using XML; and Management - manually moved through data management stages or automatically processed through to files for storage or use in other programs in 'real time'.

Data Pathway

The Data Pathway is the control centre for automated management of data collected from a mobile device (or many mobile devices), and other information handling functions.

View and edit the Data Pathway for a project via the Data Pathway button at top left of the Database toolbar. The dialog that appears allows choice of the Outgoing output targets to which new data are exported automatically, and an advanced Incoming pathway to load XML input into sessions for dispatch to mobile devices.

Often it is desirable to have sessions/data automatically processed as soon as they arrive from a mobile device, rather than processing them manually from the Data Window (see below); for example, job information being received wirelessly from field service engineers in the field. To enable this, Mobile Data Studio supports a configurable Data Pathway which complements the Database window (see below) for 'manual' management of data.

The data pathway for a project configures the sequence of events that occur each time a new set of data arrives from a mobile device, usually via a wireless or other TCP/IP connection.

Via the Data Pathway, for each individual project Outgoing data set, there is the option of automated export to any combination of HTML, XML, KML and Database/ADO. Additionally, data can be stored in the Mobile Data Studio Database window, which is the default behaviour for a project.

If session data are stored in the internal Database, there is the option of exporting the entire accumulated data to Microsoft Excel, HTML and KML. Export of sessions to XML and ADO output targets can also be managed by selection of sessions in the Database window. However, XML and ADO are typically managed from the Data Pathway menu with the facility to automatically move session data through to target databases and programs.

When clicking to select a specific output target for data, eg XML, a new dialog will appear requesting additional information specific to that target.

Automation and Data Script - for Programmers

Automation and Data Script is an advanced function which allows session flow events to be managed by segments of VBScript to fully automate data processing from and to the mobile device client, and to execute additional processes such as emailing data received from a mobile device. The default script is shown in the Data Script window. Please refer to technical information on on page 14 Automation and Data Script if programming is required, eg to update specific fields in a database, to send telegrams and emails.

Data - Database Window - Viewing the Data

Following synchronisation between a project on a mobile device and a desktop PC, project sessions data may be stored on the desktop PC in the Database window and be accessible via the Data icon. Sessions can also be loaded into the Data Window via New Session run on the desktop PC and sent to mobile devices in the field. The Database window is an important facility for gathering and dispatching information, both text and images.

Double click the Data icon in the project window to open the Data Window (view of Field Service Report example project shown below).

The Database window displays the data accumulated from all project sessions chronologically. The way session data display on the screen and chronologically can be modified using options on the Data Menu (right mouse click).

Session data sets can be selected and deleted; simply click the top of a Data column followed by Delete on the Edit menu, a query will be posed to confirm deletion. Deleted data cannot be recovered on the desktop PC, and will not be available on the mobile device either. The Delete function is essential for data files management, but generally should be exercised only after Export of data to Excel, HTML, XML or a database.

Duplicate makes a copy of the selected session which is identical but is a unique session with its own GUID globally unique identifier used for tracking purposes).

To select multiple session data, click and drag the mouse over the session data columns or rows.

Right click the mouse once data are selected and chose options for management of the content, including Export To Excel, HTML, XML, KML and Databases, and Send To mobile devices, usually wirelessly via TCP/IP, including the mobile phone network (page 11), and Data Processing Pathway.

Marked sessions are identified for synchronise via TCP/IP when a wireless client with corresponding unit ID connects to the Mobile Data Studio server (page 11).

Database Search, Filter and Sort Functions

The Mobile Data Studio server side Database was rebuilt in Mobile Data Studio version 8 to support Search, Filter and Sort functions and provide greater capacity; see image below. Project databases have associated Data .ppd and Data Pathway .ppr files, the previous data Index .ppi file has been left behind. Previous Mobile Data Studio versions session data opened in version 8 is converted to the new format. A new Search and Filter function searches data within project points by either 'Exact match' of values or 'contains' values and the sessions are presented filtered from the total database. The filtered outcome is shown in a bar across the top of the sessions and return to the full database is by click on red X at top right. The Search function is accessed via either the Database top toolbar 'Search' button, right click of mouse and Database menu, or 'Ctrl F' in Database mode. A session Sort function operates by click to top or side of point columns or rows (except GUID). The Sort function follows the standard Windows process where clicking on the column header once sorts in ascending order, and clicking on the same header again sorts in reverse order. By default sorting is alphabetical; however, by doing a Shift + click the sort is numeric instead, useful to sort numbers such as '1, 02, 10' which would by the alphabetical method sort as '02, 1, 10'.

Practice the options for management of data from the Database window, as it is an important epicentre for Mobile Data Studio operations. The Database window can be used for interim management of up to 1,000 sessions, though there is no enforced limit. The Database window is normally used in conjunction with Outgoing and Incoming session data management, that is, not the sole data management area.

Note: Database window sessions must be set out in the default 'Oldest First' order for correct numeric identification of a selection of sessions to be Exported from the Database window.

Data - Database Window - Exporting to Microsoft Excel

Mobile Data Studio will export session data to a Microsoft Excel workbook for further analysis or reporting.

To export project data to Excel, follow these steps using the Excel Export Setup and Excel Output Options shown below:

1. To export a limited set of session data, select them in the Database Window. To continuously export all data, there is an automation option in the Data Pathway, see above and 'Data Pathway'.
2. Choose the Export To... Excel... option from the Database menu or use right mouse click Export To...Excel.
3. Select the desired export settings in the Export Setup dialog box, as shown below, and click OK...
4. In Excel Output Options choose a drive, folder and filename for the Excel workbook; for example D:\MDSExcel\SoilSample_Data.xlsx; Excel files are saved as 'Open XML Excel Workbook' (XLSX).
5. Select Orientation of results.
6. Select Options for display of spreadsheet by either point ID names or point captions. Excel Output Options include an option to exclude project page and point information by unchecking Options Include header rows or columns. This Option provides an Excel file which may be more convenient for use in third party applications; for example statistical software, or saving as CSV for use in a database.
7. Select an option for management of Images.  Mobile Data Studio provides three practical options when processing session data with images to Excel, as shown below; being no output of images, save with display of image filename, or save with hyperlink to image. And click OK...

Mobile Data Studio will create an Excel workbook with a spreadsheet representation of all or selected stored sesssion data, similar to the view in the Database window (partial view of Breathalyser results shown below). This process can be automated via the Data Pathway, continuously sending the entire contents of the Database window to an Excel spreadsheet. Data from sessions can be taken back into the field with Excel for reference.

Export to Excel is not optimal for management of images; output to HTML, Databases and XML effectively manage images within sessions in context with data. However, Mobile Data Studio provides three options when processing session data with images to Excel. Below is shown an example, with a hyperlink to an image stored in the same folder as the Excel spreadsheet. Click on the hyperlink in the cell in Excel displays the image. This may be sufficient for many purposes; for example, to check a signature in a report or quotation, to display an image when tracking assets.

To include session GUID in export to Excel, record the session GUID in an Edit point at session creation time, where the Default Value can be set to <%_GUID%>.

Note,  Excel 2016, 2013, 2010 and 2007 have no limits of concern for Columns (16,384) or Rows (1,048,576).

Data - HTML Output

HTML, the format for web pages, is probably the most universal medium used on computers. Session data exported to HTML can readily be viewed in web browsers.

Basic: To set up convenient HTML storage, select sessions in the Data Window and follow the same approach described above for export from the Data Window to Excel, but chose the HTML option and a convenient file name, eg breathalyser.htm.

HTML files can be opened in a web browser and printed, it's also a convenient format to publish tabulated results either on the Internet or an Intranet, or to attach to or insert into an email for dispatch to others (partial view of Breathalyser data shown below).

Advanced: An advanced automated HTML output can be performed either for individual sessions, or for the entire Data Window each time new sessions are added; this is set up via the Outgoing tab functions.

To set up an HTML Output system for individual sessions, a target folder and filename must be selected. Although the target folder is static, the filename will be evaluated for each data set passing through. It is useful therefore to include variable 'placeholders' in the filename that will evaluate to a unique name for each data set. The available variables are:

• %pointIDname% - data from the session, associated with a point
• %.guid% - a globally unique identifier used for tracking purposes.
• %.time% - the time (HH-MM) the data was saved.
• %.date% - the date (YYYY-MM-DD) the data was saved.
• %.unitID% - the Unit ID of the origin mobile device.

While to some extent these are advanced features, they can be very useful for everyday use for naming systems for files associated with a session.

For example:
Folder: C:\Breathalyser
Filename: %NameDriver% %Road% %.date%.htm delivers - Jim Hall Coast Hwy 2015-09-22.htm

By default, HTML output will be formatted with a generic template, displaying data either horizontally or vertically. In many cases it is desirable to produce a more user friendly document, tailored to the specific project's application. Mobile Data Studio supports designation of a custom template with which to output HTML files.

A HTML template suitable for use with Mobile Data Studio is a standard HTML file, with one important exception:

- whenever Mobile Data Studio detects a tag of the form <% NameOfPoint %>, it will replace the entire tag with the value of the named point (which in this case point ID Name was NameOfPoint), taken from the data being processed. To show and print an image the 'img src' HTML should be used, for example <img src="<%DeliverySignature%>">.

Using HTML templates, fully formatted reports for printing can be generated automatically by Mobile Data Studio using a custom template designed for the project. Click to see an example HTML output, the HTML template used (and View Source), a Sales Force order and an example government form accepting output from Mobile Data Studio. The Sales Force HTML template is included with Mobile Data Studio. For a commercial service to develop HTML Templates contact Impulse Multimedia .

Data - ADO output to Databases

Mobile Data Studio can output sessions to a database (Microsoft SQL Server, Microsoft Access, Oracle, SAP, MySQL, SQL Anywhere, Foxpro, dBASE, FileMaker and others), provided it is accessible via an ADO (ActiveX Data Object) driver. ADO is more practical than the out-dated ODBC system for database connectivity.

To export data to a database:

1. To export a limited set of data, select data in the Data Window. To export all data, this will be an option later in the process.
2. Choose the Export To option from the Data menu, and then choose Database...
3. Select a complete or selective export, and click OK.
4. Type an ADO connection string for the database you wish to use, or alternatively, open a Microsoft Access .accdb database file or .mdb database file.
5. Select the table and fields to populate with Mobile Data Studio data, and click OK.

Note that this process may vary slightly depending on the nature of the database exporting to. There may be prompts for additional information necessary to access and modify the database.

Access 2010 through to Access 2019 requires installation of a specific driver to enable external applications to connect to Access accdb files; download the Access driver for 32 bit programs from Microsoft.

After the successful selection of a data source, the field mapping dialog will appear. If the data source needs to be reconfigured at any time, it can be modified via the Database button, see below.

In some databases, a list of available tables will appear in the Table Name selection. Select the table to store results in or enter the name of the table manually.

Once a valid table name has been entered, a list of fields in the table will appear in the right-most list under Table Fields. To the left under Project Fields is a list of points in the project that can contain data. To create a mapping between a Mobile Data Studio point and a field in the table, first select the point from the left list. The previous mapping, if any, will be selected in the right list. Click on a new field in the right list to change the mapping.

Note the Guess Mapping button can be used to attempt to automatically create a mapping between the project and the fields in the table, by matching point names with field names. It will report which points were mapped successfully. If required, the system _UnitID field in Mobile Data Studio won’t normally ‘Guess Map’ to a SQL table field of UnitID; however if after Guess Mapping other fields the _UnitID and UnitID are manually selected and mapped thereafter all should Guess Map. The unique session _GUID can be similarly manually mapped to a field in a database.

Display errors will provide information on mismatches between project point data and field data types in databases. The default display is 'Popup Message Box' which is useful when testing set up; however, in operation the alternative of display of errors in 'Activity Window' is preferable as it will not interrupt data flow, and will also record in text Log File.

Mobile Data Studio ADO output processes images, video and audio as Long Binary Data, so it can be universally and efficiently placed in the fields of Access, SQL Server, Oracle, SAP, MySQL and other major databases. Please see article re Access and OLE object fields,where useful Ammara DBPix software is provided to display images stored as Long Binary Data without use of scripting.

A Database Schema function is available to assist alignment of project points with database field names and field types using a CSV profile. This function is accessed with a project open, via File > Export > 'Export point names to CSV'. Where there is no 'Limit' for a string or number for a point it is shown as -1.

Typically, flow of project session data to a database via ADO would be automated via the Data Pathway for the project; this would not require manual 'export' once the Data Pathway was set up in Data Options, session data from the mobile device will simply flow through to the external database.

Previous Page

Jump to page:   WelcomePage 1: IntroductionPage 2: Project PropertiesPage 3: Adding Point FunctionsPage 4: Adding Point FunctionsPage 5: Adding Point FunctionsPage 6: Adding Point FunctionsPage 7: Android - Installation and UsePage 8: Apple - Installation and UsePage 9: Windows 10 - Installation and UsePage 10: MDS Cloud Relay - Setup and UsePage 11: Wireless (TCP/IP) FunctionsPage 12: Data ManagementPage 13: Data ManagementPage 14: Advanced - Automation and Data Script

Next Page