Update the ecommerce system
Click link to view section
Months prior to the start of this project, Pala purchased a lifetime license for a proprietary, online system that powered thousands of art gallery ecommerce websites. When the sale was made, the web firm and Pala must have thought that the system could be easily modified for Pala's gemstone and mineral specimen businesses. There was, however, a misunderstanding of expectations. Pala thought they'd bought three new websites for the price of one. The web firm thought that they'd sold an online database engine plus a limited number of hours to modify that engine.
Using the analogy of a buying a car, Pala had purchased a fine engine but nothing else. Someone would need to design, build and test the chassis, the transmission, the body, the interior, the instrumentation, and so on.
After we completed the initial scope of work, it soon became clear that the web firm's database would not work without extensive modification. Although the web firm worked closely with us at no cost to overcome these technical limitations, the additional cost and delay to complete the most essential modifications were unacceptable to both the web firm and Pala.
We explored converting to other platforms such as WordPress and Shopify. We also explored updating the existing FileMaker database to a version that could power an ecommerce website. Once again the cost and delay were unacceptable to the client.
The only remaining course of action was to modify the existing code base. This required deciphering the HTML, CSS, PHP, and MySQL code powering a fifteen-year old website design. A key challenge was making the the ecommerce side of the site look and feel like the Squarespace frontend. This required deciphering the design principles behind Squarespace's innovative templates and untangling sophisticated CSS files with over 35,000 lines of code.
Normalize the nomenclature used to search for gemstones<
Gemstone searches usually depend upon selecting a gem type, shape, or color. Ideally these are based upon standards set by a leading gemological institute such as GIA.
The custom in-house FileMaker system allowed Pala's employees to add new gem types, colors, and shapes when entering new inventory. Over time this flexibility had greatly corrupted the integrity of the database. For example, there were over ten different spellings of the color blue-green. Finding all blue-green gemstones required searching for each of the spellings, Gem types and sub-types were even more confused.
Previous Type menu had over 180 different types of gemstones and mixed in special effects such as Cat's Eye and Star.
Previous Shape menu had about 140 different terms with many duplications.
New Shape menu has just 29 terms.
Previous Color menu had 180 different colors and mixed in special effects such as Cat's Eye and Star.
New Color menu has just 20 colors and 3 special effects.
Create the technical specifications for the web firm's proprietary database
The first step was to map the existing in-house FileMaker database's fields to the fields used by the web firm's proprietary on-line database.
Over the years, the in-house database had been extended to accommodate the needs of Pala's three separate businesses plus the personal collections of the founder. Some fields such as color were shared, while others were unique to a business. In addition, a number of fields were no longer used, or whose contents could not be understood. We used Microsoft Excel to manually determine the meaning of the fields, consolidate fields, and prepare a CSV-formatted output field that could be imported into the on-line database.
These attempts to map fields ultimately failed due to technical limitations. A key issue was the limited number of indexed fields within the online database that could contain the key search variables. For gemstones these key fields are type, color, and cut. For mineral specimens these are type, color, and origin.
Overcoming these limitations would have required doubling the cost of the original license and waiting many months.
Convert the in-house FileMaker database
Restructuring the in-house database system powering Pala's inventory and customer systems required refreshing my understanding of FileMaker.