We did a lot to make it fast and comfortable for users to work with data: our product now uses MS SQL Server database, client and server were optimized so that Business2Go is much faster with large data volume (tens of thousands of records). But what if there are more records in a list, let’s say hundreds of thousands or even millions? And what if the data is accessed via internet? Sadly, the user is going to have a hard time waiting for program response, sometimes a really long time. For example, showing the whole list of cities (specially prepared test data list with roughly 175,000 records) will take 20 seconds. You can extrapolate this on millions of records and imagine the minutes that you are going to spend waiting to see them. And this is, of course, totally unacceptable.
Different systems have different solutions for the big data problem, although often the problem is completely ignored by products for small and medium sized businesses. Enterprise-level products mainly use pagination, load more on scroll or sometimes asynchronous loading. We chose to stick with pagination which is a traditional solution for lists with significant data volume. But in different lists you can choose the mode yourself, whether you need to use pagination to load the data faster or see all your data at once. You might need to avoid such disadvantages of pagination:
If a list counts totals by columns, the only available data for count will be that of current page;
You can export the data only from current page;
Same problem with printing the list.
However, in general pagination provides many advantages one of which is that you avoid lags when you work with huge data lists. Here is what we learned from our test:
Showing all 175,000 records in the list takes around 3 minutes;
With pagination mode on (with a portion of 1,000 records) showing the list takes less than a second.
Let’s take a look at how you use pagination in Business2Go. To turn it on in a list go to the list menu and select Pagination (alternatively, you can select the same command in form menu). This command usually can be found in View ribbon menu as you can see in the picture.
After you turn on pagination mode, you can manage it from the panel below.
From this panel you can navigate the pages using the navigation buttons or enter the page number directly in the field (also you can select one from the dropdown). You can change the number of records per page in Page Size field.
As you see, this is pretty simple. It is also really fast. What’s more important, you can use it only when you need it.
One of our major wins in Business2Go v3 is that we've migrated the product to MS SQL Server, and now the users will be able to work with much larger data volumes without experiencing any freezes. However, we'd like to emphasize that it is still not recommended to work with really huge databases containing dozens of million entries on the client side. For example, our tests detected that selection of 50,000 entries on the client side takes about 3 seconds while selection of a million ones may require a minute or even more. But if you apply a filter to narrow the selection to several thousand entries (from the several million ones stored in the database), then the data selection procedure will complete in about a second. In other words, using filters with relatively small selection (not more than several thousand entries) will allow you to handle the data very quickly and without any latency even for a really giant database of million entries. More details on how to use data filters can be found in the New Features of List Data Filtering article.
The latest release of Business2Go features lots of useful tools that dramatically improve the functionality of the plain data lists compared to the previous versions. For instance, now you can sort data by several fields in the list at once (in ascending or descending order). To sort a list by several fields, all you have to do is to click the header of the first column, and then, while holding the SHIFT key, click the headers of the other columns, one by one. In the figure below, the sample list of cities is sorted by columns in ascending order: Country, State/Province, Name.
GROUPING DATA AND CALCULATING RESULTS
Yet another new thing is that you can group the data in the list by one or more columns. To apply grouping by column, simply right-click these columns and in the context menu that appears choose Group By This Field. Alternatively, you can choose Group By Box in this context menu and then drag the columns from the list to the grouping area. The example below shows a list grouped by the following fields: Country, State/Province.
You can also easily calculate the column results when required. To display the results, right-click a header of any column in the list and then choose Footer, as shown in the figure below.
Then you will see a rectangular area just under the list where you can generate a result next to each column. The following results are supported: Sum, Min, Max, Count, Average. The numeric fields support all five types of results, while the date/time fields support only Min, Max, and Count. Fields of all other types support only Count. The figure below illustrates how to generate the Count result for the Name field.
And that's what we get:
Another major hallmark of the results feature is that you can obtain several results for a single column. In the figure below, you can see the results Sum, Min, and Max generated for the Population column.
And what is more, the results for groups are also supported. For example, let's group columns Country and State/Province. Then let's insert the Count result into the footer, then the Name column group with the Sum result into both the footer and the Population column group. To do this, first right-click the header of the Country column and in the context menu that appears choose Group By This Field and then repeat exactly the same for the State/Province column. You will get something similar to what's shown in the picture below.
Now let's insert the results. To do so, simply right-click any column in the list and choose Footer from the context menu, then right-click it once again and select Group Footers. The output should look like in the following picture.
In the next step, we'll add the Count result both to the general footer and to the footer of the Name column group, and then add the Sum result both to the general footer and to the group footer. The final output will be a list with results in the columns Name and Population along with sub-results in each group. Here it is:
So, to put it in short, the software now boasts a really powerful tool for sorting data and generating results and sub-results in the data lists without any MS Excel spreadsheets or any of the sort.
SETTING UP THE LIST FIELDS
Another new feature of the application is that you can quickly set up any field in a data list. Now you will be able to add or remove a field in the list literally in two clicks. To configure the fields which must be displayed in the list, simply right-click anywhere in the list, choose Customize Items from the context menu and then add or remove fields as you want.
Alternatively, you can adjust the necessary settings using the ribbon menu of the application.
Yet another new and very useful option is that a list can now be displayed in various modes. The basic and default view mode is a plain table. Since many users find it inconvenient to work with really large lists, we've implemented an efficient workaround which allows you to view the data on special cards. Let's see how it works, taking a list of companies' addresses as an example. Here's how this list looks like in the plain table view:
And here's the same list in the card view:
As you see, the card view is a much more clear and structured way to view (and, sometimes, even edit) the information, since everything is grouped by theme and seems quite intuitive. To switch between view modes, you can use either the context menu or the corresponding icon on the toolbar:
We've got excellent news for people who often share or exchange various data with other systems which was a real pain in the previous versions. We've implemented both data import and data export functionality for all lists. And what is more, there are some advanced data exchange features in this release:
You can quickly print a list;
You can copy an active cell, selected lines in the list or even the whole list (either with or without headers) to the Clipboard and then paste them to Excel or similar software;
You can export all data in the list or only selected items to the following formats: Business2Go Data Interchange Format, MS Excel Workbook (97 - 2003, 2007), Adobe Acrobat Document, Text Format, Web Page, XML Document. Business2Go Data Interchange Format is our internal data representation format which is perfect for data exchange between installed copies of Business2Go, e.g. when you need to move a certain information between databases.
The data import is also possible for any editable data list. Now you are encouraged to import any list of products, customers, delivery notes and so on much easier than ever before. The application seamlessly supports all formats you might ever need: Business2Go (other or older databases), Business2Go Data Interchange Format, QuickBooks, Inflow Inventory, MS Access, MS Excel Workbook, MS SQL Server, Text Format (CSV, TAB, Formatted Text).
BULK DATA OPERATIONS
The bulk data operations can save your time and efforts by means of automating routine operations with a large number of entries. In most cases, the basic working mode with lists is when only one entry is selected and considered active. To handle many entries at once, you need to enable the multiple selection mode by choosing Multi Select either from the context menu or on the toolbar.
Once this mode is on, you will be able to select multiple entries one by one (while holding the CTRL key and sequentially clicking the entries) or all at once (by pressing SHIFT+CTRL+A). Then, the selected items can be easily copied to the Clipboard, exported into any supported format (except Adobe Acrobat Document in which you can export only the entire list), duplicated, deleted, populated with a certain value, or even merged together. You will also get access to some specific functions, such as posting all selected invoices to generate the corresponding record in the General Ledger. Another example is assigning geo codes to all selected companies based on their addresses (i.e. calculating latitude/longitude), in order to make it possible to use advanced filtering with the following conditional operators: inside polygon, not inside polygon, within a radius of, not within a radius of. Therefore, the multiple selection feature will be of great use for you and will save your time and efforts every day you work with Business2Go.
Let's see how to efficiently use the multiple selection tool for one of the new functions we've added in this release. Suppose we want to populate a certain field in each selected entry with the same value. We have multiple entries assigned to Admin of the system, and we'd like to re-assign them to another user, such as Batman.
To perform a bulk update, choose Update With. The following window will display:
In this window, find the Assigned To field in the list, type Batman and then click Update. On the next step, the list will be populated with the new value and that's what we'll get finally:
In the new version, all lists and other data input and view controls can handle hyperlinks. Just like in any web browser, you will be able to follow the links to detailed data or perform other operations. A field which contains a hyperlink is easy to recognize - the text is underlined and highlighted in blue, and hovering the cursor over it while holding the CTRL key will change its color just as in a web browser.
Clicking such a link displays the detailed data about a certain item.
The hyperlinks allow you not only to jump to detailed information, but also perform additional operations, e.g. clicking an email address will create a new email message while clicking a phone number will launch Skype.
The drop-down lists provide a convenient way of selecting a value in a field. In most cases, drop-down lists are intuitive and very easy to use. The only thing you need to do is to open the list of available items and choose the one that best fits this field. However, sometimes users expect additional functionality from the drop-down lists, especially in the following situations:
When a drop-down list contains a very large number of items;
When a user wants to edit or customize the items provided by a drop-down list;
When a user wants to add a new data to a drop-down list or update the existing items.
In this release, all drop-down lists allow you to set up the columns, perform multiple sorting and grouping, generate results and sub-results for fields and groups, apply quick filters and advanced filters, and much more. Also, if a drop-down list lacks certain data, you can easily launch a module to create this data directly from the drop-down list (to create a new data, click New; to edit the existing data, choose Edit).
This article covers only new basic features regarding the data lists - that is, the features available almost in every list. But we've also have a plenty of other brilliant tools prepared for you, which are more specific and which we're going to talk about in our future articles.