Think twice before trying to import 20 000 records to Zoho Creator

Skimming through websites of products sometimes I just can’t help but try the functionality. Its Zoho Creator this time. The check of importing capability of 20 000 records.

This is how we start with Zoho, creating an app from Excel:


As I expected, there was no opportunity to import the data directly from file, so the only way to do this was through copy/paste. Let’s give Zoho a chance to show its worth.


I am working on Dell Notebook (2.00 Ghz Core 2 Duo) and had no luck to see "Checking …" results after 15 min of my data processing. So since there was no feedback of the system I just canceled the request in order to import the data gradually.

I started the app creation from the first with 10 records to create application structure and then try to import data by 5K pack:


Pressing Submit one can see what happens next:


I must disappoint you, I didn’t manage to get any result of import 5K records. IE just stopped responding and I restarted it all over again.

As a result:

I asked Zoho Support to give me a hand. At first I was told something was wrong with a link to my file with 20 000 records. I sent out a file with my data, but nobody got back to me still. For each answer I was waiting for like a day and I think it just takes for too long for a user to reslove this simple task at the very start. I am still hoping they will get back to me. I will keep you informed.

Dependent Dropdown

One more thing I’d like to clear up it’s a dependent dropdown function. This is what it’s all about:
When the values of some dropdown are set and they depend on the selection of other dropdown or on the selection of values of another element of the form.

For example if you have a drop down (or pick-list) that stores a list of countries and another drop down that stores a list of cities; it is possible to show only cities of the selected country by marking the "city" drop down dependent on the "country" drop down. This is the brief description of how it works.

So I d like to prove the value of apps in regards to this function. We’ll see.

Calculate a running total

Wanna see an up-to-the moment total of the data at the time of inspection?
This is what the running total of data recorded represents. The thing is you can keep track on total of each sum for it is automatically recorded each time, adding the total of each operation to the common total as the next operation (commodity sale or whatever) is conducted.
Running total is responsible for that.

I like this function a lot for several reasons.
Firstly, you can get a report on a total data of all positions immediately at any time.It displays the exact total data of the certain time point of recording. Secondly, return or compensation is also automatically subtracted.

This is an example of how it looks:

Sale 1: $80.00 -- Running total: $80.00
Sale 2: $20 -- Running total: $100
Sale 3: $48 -- Running total: $148
Sale 4: Refund: $22 -- Running total: $126
Sale 5: $51 -- Running total: $177

That’s why I just couldn’t pass up this function and decided to give it a try and figure out the ways of its setup in different systems.

TeamDesk master-detail sets an example

Taking decision based on facts is the only way not to give your money up for lost. I believe only this can lead to reasonable choices and give a hand in taking the most from the products.

In continuation of my test of master-detail function of QuickBase, Coghead, TrackVia and Dabble DB I decided to finish trying TeamDesk capability of managing this.

Using the same example with the invoice as I did in other tests I created a simple app, so you can check out the organization of TeamDesk master-detail:


Actually, the set up was easy enough, I managed to create 2 tables: Invoice with Company and Date columns and Items with columns Description, Quantity and Price. After that I created Total formula column in table Item:


I think there won’t be any problem handling formula columns, if one have ever worked with Excel or studied math at school.

To create one-to-many relation of these tables my next step was linking of Invoice and Items:


What I especially liked in TeamDesk it’s creating relations, what gives an opportunity to automatically handle all boring work you should do to have a full-fledged master-detail, namely to create reference column, choose how to display details, to set the way of how to select records from master:


These operations are not difficult, but the fact the system can accomplish it for you lets focus on business logic of the app, not on the primitive operations. You just give it a final touch creating Grand Total summary column that calculates the total of all invoice items:


And it’s done. Not bad. Finishing my coverage of TeamDesk I'd like to mention how master-detail works from the stand point of end user and what peculiar moment should be taken into consideration:

• One can see on the first screenshot layout that master and detail are displayed on one and the same screen, but on different screens when editing.


• The button "Add Similar" doesn’t add a new invoice with items, but just creates the new empty copy.


• As well as Delete button in the invoice table don't do anything with items.

Conclusion:

Frankly, despite these moments TeamDesk proved to be a powerful mechanism for master-detail implementation and I especially liked the system capability to handle a lot of implicit work on its own what speeds up the process of getting the working app prototype.

Be prepared to work with duplicates in Quickbase

Trying to find out how a certain goal can be achieved through certain means (and hoping to define the killerest way of this) here is my coverage of QuickBase capability to merge and handle duplicates.

To get to the point at once I contacted the support and they gave me a hand. This is the feedback that Eric Hansen provided:

"I found a few postings in our knowledgebase that discuss how to handle duplicate records. In brief, once you have several records in QuickBase, you can self check for duplicates by creating report and then sorting and grouping by fields that should have unique values. If no one field is unique but a summary of 3 fields would help make it unique, you could create a summary field that combines the values of any 3 fields from your record and this field can then be reported on to find duplicates. (This field could even be a hidden field of course.)
I am sorry however at this time there is not an automated way to prevent or remove duplicate records built in to QuickBase."

So I decided to check it in practice applying to my app with 20 000 records. Having added formula field Name in which I united First and Last columns through blank space and built a summary report grouped by Name. This is what I have got:


Unfortunately Quickbase doent provide any means to eliminate rows from report where Number Of Customers = 1. By pressing Name we open records which have the same name and only then for each record it is possible to manually decide what to do.


Conclusions:

It is possible to find duplicates in Quickbase, but it’s a pity you can’t eliminate unique records from report. For the rest you can be confident you’ll manage to handle it. There is no problem to delete a record. But the thing is it’s a problem to merge all details and to move them to one needed record. It can be accomplished manually of course, but this is the difficult part. Quickbase has no means to help out here.

If you manage to tune master-detail of TrackVia let me know!

Dealing with web-based systems I keep on looking for a so-called "holy grail" of SaaS solutions. Ok, not a holy grail, but at least that one, which would correspond to at least 40% of "we are the best out there" crap. Hopefully, I will manage to do that before the World Ocean dries out.

So, let’s get down to business and check the organization of master-detail relation of TrackVia. I won’t deviate from previous algorithm of testing, as I did it with invoices and items of Dabble DB, Coghead and Quickbase. This is how the invoice looks like:


And here you can check the creation of Invoice Item:


I couldn’t find an option to display my invoice with items on one and the same screen and to calculate Grand Total of all invoice items and reflect this sum in the invoice.

So I decided to ask TrackVia support to give me a hand at this and this is what Ed Dunigan offered:

"With TrackVia, you can edit parent and child records at the same time through a custom view. You would create a view and denote the fields you want included in the view. You would then select to display the view in Edit All where you can then edit all fields desired."

I took Ed’s advice and built this custom view, this is how it looks like in Edit All mode:


Summing up:

What can I say … This option is definitely not enough for master detail support: the user doesn’t edit items of all invoices simultaneously and the very point of master detail is in displaying the data as the comprehensive whole, I mean users want to display invoice with items, not separate titles and positions.

It seems to me that currently TrackVia program is developed for single table/database applications, let alone the possibility to create relation between tables and any calculation of this relation is very limited.

Do SaaS vendors really hate small businesses?

Keeping an eye on what is going on with SaaS market I couldn’t help but notice that small businesses are oftentimes neglected and a lot of services don’t even have an option for such companies. Most of small businesses are challenged by the price offerings presented on the market now. I was pretty embarrassed that lately this tendency had gathered pace and in the majority of cases small businesses were left aside.

In case you want to store your data and just share it with everybody in the company it’s necessary to pay additional money for the functionality you will never use or if you don’t meet the preset quantity of users you have no option in the final count.
What I am trying to find out here is when it comes to small businesses is there any appropriate variants left to provide a good service for a fair price?

You can quickly check here the present price offerings of some vendors and decide for yourself if you urgently need to increase the volume of your data or additionally hire 5 people to meet your providers’ expectations:

Product	Minimal price / Users	Limitations
Quickbase	$249 / 10 users	25 000 records 100 MB file attachment
TrackVia	$249 / 20 users	300 000 records 4 GB file attachment
TeamDesk	$9 / 1 user	Unlimited records and file attachments
Dabble DB	$8 / 1 user	100 000 records 250 MB file attachment
Coghead	$25 / 1 user	10 000 records 1 GB storage

Coghead master-detail. The matter of choice.

Coghead has two ways of master-detail form organization: local grid and linked grid. I contacted Coghead support guys to find out which way is better for master-detail organization. So, I am going to follow the recommendations and test both variants.

This is what Jonathan McKibbin suggested on local grid:

"Local Grids allow you to add something similar to 'sub records' to a single record. These individual sub records are only displayable in the form of the record that they belong to.

An example on how to create a calculated field which sums the column of a local grid can be found in Tutorial 3: Section 3.

The advantage of this approach is that you can easily add, edit or remove rows from the local grid. The disadvantage is that it is more difficult to report on the local grid and you would need to create calculated summation fields and use those fields in the collection tab's view."

The explanation is really thorough so I created an Invoice using local grid and this is how it looks like:


I easily created a calculated Total field for each item row and calculated summary field Grand Total. In general editing works quite well and the program calculates everything on the fly, but I think the local grid is better to use in case you need to save several one-type objects within the record, without further processing, such as many addresses of the company or several phone numbers. If you need to build any reports based on this data in the future I think the access to the detail only through calculated summation fields is obviously insufficient.

As far as I am through with local grid, let’s take a look how linked grid functions.

Jonathan offered to apply the following with a detailed explanation:

"Linked Grids allow you to display a list of records from another collection. One of the most common use cases is to display related records in a parent-child relationship between two collections. Since the child records are in a different collection this would give you more control over reporting through both views in the child collection or through additional linked grids in other collections.

An example of how to create the parent-child type relationship between collections can be found in Tutorial 2.

The advantage to this approach is that it gives you greater flexibility when it comes to reporting. You can create views within the Items collection or use record tabs to create a dashboard-like interface for reporting. The disadvantage to this approach is that it is a bit more difficult to set up and that you would have to navigate to the Items collection, by clicking on the row of the linked grid in the Invoice record, in order to edit or delete the row."

This is an Invoice if use linked Grid:


Despite the fact its’ structure looks so much alike to the local grid (except for the opportunity to add Items at once), frankly, it was not so easy to create a parent-child relation.

The very process of Invoice and Item table creation was not difficult, but my next steps of relation creation were not that simple:

1. I used Link widget for Item edit form to make it possible to refer to a particular Invoice.


2. Default grid widget displays all Items to Invoice edit form, so I had to set an additional filter to enable the Item selection of a specific invoice.


3. To create Items from selected Invoice and have them automatically linked one should create Custom Action.

Conclusions:

Its nice to have two different mechanisms for master-detail organization, but none of them is an ideal way of implementation.

Local grid is a way too primitive for anything but just gives the opportunity to the customer to enter a variable number of attributes (phone numbers, for example) and Linked grid along with custom action allowed me to tune everything I actually needed, I had such an impression I used a sledge-hammer to crack nuts though.

A “but” of Quickbase master-detail

My next testing target is Quickbase. Using the same invoice sample with items like in Dabble DB test, I create two tables with needed fields and tie it together through one-to-many relation.

This is how the invoice looks like:


I seem to have liked the opportunity to easily add Total (Quantity*Price) calculation for each item and calculate Grand Total of all items through summary fields in relation.

One should pay attention: Header and Items can’t be modified simultaneously. For example, creating of new invoice I was offered to save the Header at the very beginning, though everything looked so as if I could add Items at once:


I think such an interface misleads the user. This is how Item add/edit function looks like in invoice:


Summary:

Quickbase master–detail form is easy to create and it works quite well, if you are ready to accept you cant add/edit items on one and the same screen with header.

Dabble DB master-detail and duplicate records handling.

In this post I am gonna cover how Dabble DB master-detail works and how to handle duplicate records.

Having built a simple invoice application I created two entries.Invoice (master) where I will save the header:


And Item (detail) for storage of invoice items:


This is how invoice editing looks like:


Pay attention to the type of difficulties I as the user faced (the peculiarities of Dabble DB master-detail implementation):

• Dabble DB doesn’t reflect the detail in the grid form with column names;


• I couldnt calculate and display the Total column of Items on the same screen;


• Failed to find an option to calculate invoice grand Total on this very screen;


• Couldnt edit invoice Item on this screen, cause the system redirects me to another page.

Let’s take a look now which means are there for search of the duplicates. Dabble DB has no built in tools for duplicates search. As I’ve looked through Dabble DB forum, the support team offers to do following things:

"The easiest thing is probably to group the entries by a column that should be unique. Any unique entry should be a group of one, while duplicates will be grouped together. You can then use the Actions menu to merge all groups, producing only unique entries."

So, let’s follow the advice and check what happens while doing this:


This is what I’ve got as I pressed Go:


When testing the function the system takes first master record and all details from deleted master records are transferred to it.

The good news is the system merges detail records to the new master.

But! The user can’t control which master to pick as a base and define the right field combination for resulting master record.

Tracking simultaneous record editing of TrackVia

What about the TrackVia simultaneous record editing capability?

Yes, I took it for a run too. It doesn’t take much time to test it.

The algorithm is all the same: I open the record in two web browsers (IE and Firefox).

Changed the record in IE: edited First and Phone fields, then typed a note and chose the file:


In Firefox I changed File, Email and added Note. The file is chosen too. I just saved the changes.


As one can see on the screenshot the system doesn’t highlight edited fields. Ok then, I actually save my changes in IE at first. And no luck! It seems in TrackVia you can’t operate within the system using one and the same login for multiple users…


The sole approach to the issue I must say. For the security it’s a beneficial feature of course, but what about the user utility? My point is if you can’t use one login for multiple users in case they don’t need separate permissions or just in order to spare a little money…

So, I created the second user to try again. This is a new record of another user in IE:


Saving the record at first there was no problem:


In Firefox the things went well too:


How does the record look now?


To sum up:

As one can see TrackVia developers applied another method of simultaneous record editing. No alerts or warnings to inform the user of data change or the risk of previous changes overwriting. It is substituted by the complete report of record changes.

To my opinion the detailed history of changes is of a great help for this function allows tracking each record editing, what was modified and exactly when. On the other hand, even if the changes of each user were reasonable, eventually it can bring the data into a total mess, and it won’t be easy to handle and track these changes. For example reports will display incorrect results or workflow logic will be broken.

Master-Detail Form. Let’s check the way of its organization!

I’ve got to figure out the best way of the implementation of such an issue now:

One of the most common types of form in database applications is "Master-Detail" form. "Master-Detail" form display a master row and multiple detail rows within a single page. With this form, users can query, insert, update, and delete values from two tables.

I want to find out and compare which ways for master-detail forms organization offer present-day web-databases. So one can define what is the killerest solution.

With TeamDesk simultaneous record editing is a piece of cake!

To test TeamDesk I decided to try their Document Library application template.

So in the application I’ve chosen one of their sample documents:


Without further ado I opened the record for editing in 2 browsers. As always my next steps were simple and clear. In IE I changed Type , Title and File fields:


In Firefox I changed Category, Type, Title and File:


It was of a surprise TeamDesk doesn’t highlight the fields. Anyway, let’s save the changes. At first in IE. Ok, TeamDesk easily saved the data:


The next step is saving all the changes through Firefox. And wow! The system warned me there was a possibility to overwrite the data. Good approach to the issue.


What can I say? TeamDesk provides a detailed info on who, when and what has changed in the record. In a warning one can see each alteration of the data and what I am trying to save. Besides, the user decides and chooses for each field which value is to save (the value that already exists in the database or the value you have just changed).

Well, how about that? I liked it a lot.



My summary:

At last I have found the solution which properly solves the issue. It should be noted that TeamDesk developers took seriously the problem of simultaneous record editing. This is the only system of all tested ones, which supports this mechanism for file attachment.

It would be good to highlight edited fields though. But all in all TeamDesk implemented the task perfectly.

Good job guys!

Find and merge duplicate records

Dealing with various web-based programs I can’t help but mention why actually so many people turn to on-demand services. In the first place it’s the issue of taking control of the data and keeping it clean. This is the core reason why people turn from Excel to web-database systems.

This approach really adds a tremendous value to data utilization, adding at the same time issues to solve. You’ve got to find a way to manage a variety of things.

I’ll give you a short preview of what I am going to talk about in the upcoming posts.

Currently I am trying to figure out what ways the services use for search of duplicate record and what methods are offered to help out if any.

Actually I am going to check how the systems handle the case when duplicate records have relations and what happens by record merge when it’s necessary to move foreign key information from the duplicated record(s) to the primary record.

What are Dabble DB odds while record editing?

Whether Dabble DB support answers my question on large data handling or not (I am waiting for a reply, still…why I didn’t manage to import 20 000 records) it won’t prevent me from further product testing of simultaneous record editing by multiple users.


Having opened the Dabble DB record lets edit the data and try to save it through 2 different web browsers at the same time. I changed the fields First and Street in IE.


The same alteration of First and last field was implemented in Firefox too:


Ok, that’s everything I need to check out the service capability of simultaneous data editing. Let’s try to save changes. At first in IE the system has easily saved all my data:


I looked forward to seeing this in other browser as well. What will happen while saving it in Firefox? I saved the changes with no questions and Dabble DB just overwrote the fields:


Conclusion:

It looks like currently Dabble DB doesn’t have any support of simultaneous editing at all. The user changes are just substituted by the changes of the other. The system doesn’t provide any warning or notification in order to inform the user what’s going on.

Obviously, Dabble DB customers should take into account that when dealing with record editing it is necessary to double check all alterations.