B4J Library [B4X][class] RecordSet: A class that extends the functionality of lists of same length arrays

A recordset is a data structure that consists of a collection of database records.
For example, in StringUtils the result of su.LoadCSV(Dir, FileName, Separator) is a recordset, a list of same length arrays.
But so is:

Dim galaxies As List
galaxies.Initialize
galaxies.Add(Array("Tadpole", 400))
galaxies.Add(Array("Pinwheel", 25))
galaxies.Add(Array("Milky Way", 0))
galaxies.Add(Array("Andromeda", 3))

The RecordSet Class (Standard) adds the following functions to standard recordsets, without the use of a database such as MySQL or SQLite:
1. Named fields or alternatively loading external .csv/.txt files with field headers
2. Auto-detection of field type (numeric, categorical, or date)
3. Full indexing - all values in all fields are indexed (surprisingly, this works for recordsets up to 50000 records or more, depending on number of fields)
4. Sorting (Up/Down, Numeric/Categorical/ Date) (The indexing function uses B4XOrderedMaps, which are sorted during initialization, and after changes)
5. Searching
6. Filtering to subsets
7. Inserting, Deleting, Replacing records
8. Cloning
9. Data Summaries (descriptive statistics of any field (whether numeric, categorical, or date)

The class was designed as a generic tool, therefore it doesn't require any user interface by itself and it is cross-platform.
Dependencies: DateUtils, StringUtils, and B4XCollections
Lines of source code = 684 (the majority of these are related to statistical summaries).

In order to show its power in simplifying complex tasks, I have included two other classes in the demo: RecordSetBrowser (535 lines)and RecordSetReport(107 lines).
I could package RecordSet as a B4XLib, but at this point I want to make it easy for others to see how it works. As a Library I would rename it.

The demo starts with small lists that show its functionality for small tables that normally wouldn't be thought of as recordsets.
When you try RecordSetBrowser, clicking on a header selects that column and a set of field oriented tools appear.
When you click on a cell nothing appears to happen but the calling context is notified and actions can be taken there.
When you try RecordSetReport, clicking on a report notifies the calling context. The reports are catered automatically to the field data type.
In the demo after the RecordSetBrowser page is closed, B4XMainPage is notified and moves onto the reports.

I have included copious comments. Feedback is always welcome.

RecordSetScreen1.png


RecordSetScreen2.png
 

William Lancee

Well-Known Member
Licensed User
Longtime User
One quick way to try out the demo is to use one of your Excel sheets that contains a section of headers and data rows.

Step 1. If the data grid does not fill the sheet, copy the grid (including headers) to a empty sheet
Step 2. Save As .txt (Tab delimited)
Step 3. Copy to Shared Files in the demo
Step 4. Use the countriesRS and workordersRS as templates to experiment with your record set.
 

William Lancee

Well-Known Member
Licensed User
Longtime User
For a more advanced use for RecordSet, here is a working app that performs standard statistical tests:
Crosstabs/Chi squared, Breakdown/ANOVA/Error bars, Correlations/Line plot, and Multiple Linear Regression.

The data types and type of tests are automatically determined. Appropriate graphs accompany the reports.
On my desktop system, with 1000 records the results appear seemingly instantaneously.
On my Android Tablet, there are small delays (usually less than 1 second).

On both systems, you can do a lot of work without needing to type anything.

Disclaimer:
1. This App is intended for exploratory analysis only.
Published reports require confirmation with an standard statistical package.

2. All statistical work requires the understanding of limitations and the context in which the data was collected.
Without these, reports can be deceptive and lead to wrong conclusions and actions.

3. There is complexity in the algorithms, and despite extensive testing there could be errors.
I decline responsibility for these and for how the App is used.

StatsDemoScreen1.png


StatsDemoScreen2.png
 

Attachments

  • StatsDemo.zip
    69.7 KB · Views: 210
Last edited:

LucaMs

Expert
Licensed User
Longtime User

William Lancee

Well-Known Member
Licensed User
Longtime User
Funny you should say that. This project started as an extension to XLUtils. But, I needed something that would work on my android tablet.
I also decided to keep the RecordSet Class UI-free.

However, it is not hard and doesn't take much time to convert an Excel sheet to a Tab delimited txt file.
 

William Lancee

Well-Known Member
Licensed User
Longtime User
I have made a small change in RecordBrowser to correct the format of certain numeric fields in select if and search for functions.
The files in #1 and #3 are updated.
 
Top