Kicking off the New Year Testing New Starfish ETL Functionality

This new Starfish ETL functionality builds on top of the SQLite Xref and SQLite Connector functionality already in Starfish. The SQLite Xref functionality greatly increased the speed and reliability of the Xref system and allowed us to update Xref lists as opposed to just inserting new, possibly duplicate, records. That's great, but it was still kind of limiting. We wanted a way for users to be able to fully interact with the local Starfish SQLite database. Thus, we've introduced 3 new functions:

Insert
var = SQLiteInsert("test_table", "field1,field2", "'@@ORG:DirectoryRelative@@','@@ORG:Name@@'")
This returns a boolean, true if successful.

Update
var = SQLiteUpdate(“test_table”,” textfield1='hey', numfield3=45", "name=@@ORG:Name@@")
This returns a boolean, true if successful.

Delete
var = SQLiteDelete("test_table", "field2='@@ORG:Name@@'")

Select
var = SQLiteSelect("test_table", "field1,field2", "field2='@@ORG:Name@@'")
This returns a multi dimensional array which you can loop through:

Now, you have the Origin you're reading from and the Destination your writing to AND you also have your local SQLite database you can fully interact with. What does this do for you? Well, you could pull down some values from the Destination to your local SQLite database and use those local values to compare against as opposed to trying to do possibly slow lookups into the Destination. The possibilities are endless.

New Starfish ETL Functionality

The second new piece of functionality is adding the SQLite Levenshtein distance function to the SQLite connector AND the new functions mentioned above. Levenshtein distance is a great way to do "fuzzy" comparisons to check for duplicates. This part gets fairly complicated fairly quickly, but you can essentially populate your local SQLite database with Account names, for example, and as your going, look for possible duplicates. If you find a possible duplicate, mark it as a possible duplicate!

Stay tuned to the Starfish Wiki and Starfish News for more information.

Justin Kuehlthau
Director, SugarCRM Practice

Justin is a Customer Relationship Management, CRM, software business analyst and consultant with ten years of experience in the industry. Justin started at Technology Advisors on the support desk where he learned extensively about all facets of CRM software. He was quickly promoted to the role of consultant where he learned the entire process of software consulting from pre-sales to post production support. He now works as the senior Sugar Technical Architect with new and existing customers and as the Director of the Sugar Practice at Technology Advisors.  Justin also co-hosts the Sugar Cubed CRM Talk podcast with Megan Sheehan.

Related Articles

January 9, 2019

Remember last year when that zany dance move called Flossing came out? It caught like wildfire and spread all over the world. While significantly less cool and unlikely to go viral, integrated software may finally have its own “Flossing” moment in 2019.

Unlike Flossing, software integration is not a passing fad. Top strategists have been pushing the power of integrated software for years, but it seems only recently that businesses have started to listen.  

July 27, 2015

G2 Crowd recently listed Starfish ETL among Chicago’s top-rated business software programs based on user satisfaction and market presence. The top companies were determined using G2 Crowd’s first Chicagoland GridScape, which uses real-time visualization of business software products to evaluate each offering.