CampSoftware Logo       FileMaker Certified Have a question? Email Us

Home | Products | Contact & About
Blog | Forums | Wiki | Twitter | RSS Feed

Trees for Representing Referrals and Marketing Sources in Xojo and FileMaker

Hal Gumbert

Fuzzy Vision

For some time, we struggled with how to visualize word of mouth referrals and marketing sources for one our favorite clients. We knew that both word of mouth and the advertising were doing something, but it was hard to see the which was stronger with standard reporting tools.

Our goal was to show how much each customer spent and a total of how much each referral spent. Knowing this can tell us if a particular marketing source is working or not. For example, is it better to spend money on Groupons or Radio Ads?

Organizational Trees

Last week, I had an idea to show this information as an organizational chart. I messaged my good friend Tim Dietrich if he ever used or knew of a good org chart and he found Google Charts Org Chart. The example source was very simple. There's some javascript, a bit of html, and one row of data for each item on the tree.

Upside Down Trees

In this database, we have a field for the Referral Source. When people are asked, 'How did you hear about us?', that information is entered. It could be a referral from a friend, radio ad, groupon, or one of many other options. If it's from a friend, we store the friend's name and ID which allows us to show a list of all the people that a particular person referred. Since Tom can refer Sue and Sue can refer Joe, the org chart is a perfect way to show the relationships.

Adding Nutrients

So we created two methods / scripts so we could build the chart from the perspective of a particular customer or a marketing source. The fun part was creating the recursive code. Org charts are represented by a data structure called a "tree" where each node is a "parent" of their "children". This means that a customer / parent can have one or many referrals / children. It's recursive since the code simply adds the customer to the chart and then the code is called again for each of the childrens and yet again for each of their children until someone has no referrals.

Once we had the chart drawing correctly with the customer name, we added a dollar amount for the total of each invoice. Since we knew the total of all the invoices for a particular customer was, it was easy to add total the invoices for each customers direct referrals. Finally, we added a total for all the every one below on the same branch of the tree.

Strong Roots

Below is what one of our Marketing Source Referral Trees look like. The top row represents handing out flyers at the Cantina. The second row are the customers who came in and said they heard about it from the Cantina Flyer. Any customers below the second row are people referred by someone above them.

The first dollar amount is a total of the invoices for that customer. The second amount is the total for their direct referrals. The third amount is the total of everyone below them.

With this new tool, we can create documents for any marketing source and see what is working and what isn't rather than guessing!

Xojo For FileMaker Developers Course: Survey

Hal Gumbert

Way Back

I've been using Xojo since 1998 and FileMaker since 1991. For years, I've used the both FileMaker and Xojo separately, using FileMaker for database solutions and Xojo for building apps. Internally, we'd mix it up and use FileMaker to track our sales of our Xojo apps, but never build a hybrid solution.

Fast Forward

Last last year, Tim Dietrich told me that he was considering moving away from developing FileMaker solutions in favor of building apps especially iPhone apps that could be sold on the App Store. I was also interested in building iPhone apps as I've tried many methods to avoid coding in C like FMTouch, PhoneGap, LiveCode, and others that I can't recall. It was different this time though. Xojo was working to build iPhone apps with their superb object oriented 'BASIC like' language. I was already developing desktop apps for Mac and Windows so being able to build iPhone apps with the same language was a no brainier!

Xojo iOS App on the App Store

Once Xojo announced that we could build iOS Apps, we were all over it. Reference for FileMaker one of the first Xojo iPhone apps on the App Store and has been updated to include the latest changes in FileMaker 14. While Reference for FileMaker includes an internal SQLite database, Tim has been working on iOS apps that utilize FM EasyAPI so his iOS app can access data from a FileMaker Server.

Xojo Web Apps for FileMaker Deprecated Runtimes

FileMaker has placed Runtimes on their to be deprecated list earlier this year. You can continue to build Runtimes in FileMaker 14. After an outcry from developers, FileMaker said that it'll be in the next version too, but no promises after than and nothing has been suggest what may replace Runtimes. If you haven't heard, we've been using Xojo Web Apps as a replacement for Runtimes. We have much more on our blog where we discuss Xojo Xanadu and EasyData.

Tell Us What You Really, Really Want

This is your call to action! Tim and I have the same short survey below and also on Tim's Blog where we'd like to find out what interest you about Xojo. If there's a significant number of people wanting to learn more, we'll move forward with the idea!

Xojo Xanadu - SQLite Audit Log using Triggers

Hal Gumbert

Introducing Xojo Xanadu

Xanadu is the name for our Xojo Web App which we'll use to start our Web App projects for folks we work with. It's still in its infancy but it's quickly evolving into a fantastic foundation for database solutions. We're in love with the Xanadu logo that Ntstuan on Fiverr created for us. If you need an icon created, try out his services!

If you're a database user, you'll want to read this to learn more about what we can do for you. If you're a developer, you'll be interested in the Audit Log and the open source code!

Sometime in the near future, we'll add a web page specifically for Xanadu like we have for our FileMaker Template: FMSBFoundation. We wish we could make Xanadu Open Source. It's really amazing so far, but there are just too many bits of open source and purchased code from other developers. The great news is that everything we use in Xanadu can be included with the apps we develop. That means that if you need an app built, we can build it and you won't need to pay for all the add ons!

Xojo SQLite Audit Log

That said, we can make some things available as open source. We're incredibly appreciative of the Xojo community and love to give back when we can. So, we're making our Audit Log code that uses SQLite Triggers away!

The idea is simple. If you have a SQLite database all you need to do is add one table and one method to add SQLite Triggers to automatically create a log entry for each INSERT, UPDATE, and DELETE for the tables you care about. Check out the video below and the source code below. You'll see that it's really easy to implement.

Once you have it up and running, keep an eye on how much storage the LogAudit takes up. From the start, we wanted to store the Audit Log records in a separate but attached SQLite database file, but that didn't work as SQLite records that are inserted from triggers must be in the same file as the trigger. So, we plan to create a 'dbAuditLogTransporter' method to move the Log records to an attached SQLite database file. We'll make that available sometime, but we need to write it first!

The 'dbAuditLogTriggers' method is close to magical! The method will create three triggers for every table in your database file except for the tables you don't want to log! Our 'People_Insert_TG' looks like this:
People_Insert_TG Trigger
While that looks like a hot mess, it's really simple. First we, define the trigger name and for this trigger, we want it to run AFTER INSERTS. Then between the BEGIN and END, we first update the record to set the Modification Timestamp and Modification Seconds expressed in a fraction of a second. Next, we add a log record to the LogAudit Table. Most of the trigger easy to read, but the value to be stored in the 'FieldValues' is messy. All it does is takes the field name and value and wraps them in an xml like tag. We mixed square and curly brackets on purpose to reduce the chance of a conflict with the embedded data.

Watch How it Works, Source Code Below!


Automatically create Audit Log Triggers in your SQLite Database - Defining the Triggers for the Audit Log is the hard part, but this method makes it really easy! - Download

View the Audit Log in a HTMLViewer - We're showing the Audit Log on a WebDialog to be displayed as a sheet. Just add a sheet and drop a HTMLViewer on the sheet, add this method and call it when you want to see the Audit Log. - Download

We're Dropping Web Site Tracking and Analytics

Hal Gumbert

We're not going to run tracking or analytics on our site anymore.

The past few days I've been listing to the 3.25 hour episode from the The Talk Show With John Gruber podcast Ep. 132: ‘Peace, Porn, and Privacy', With Guest Marco Arment. That link will start at the 1:50 mark where they discuss tracking, analytics, and privacy.

We've been using Google Analytics for a while now and while it's neat to see 'stuff' I really think it's not worth the reduced privacy to the folks that visit. While I'd prefer to have the analytics it comes along with the data being used for more that I would want.

I have the amazing Privacy Badger from the EFF installed. It's great as it 'blocks spying ads and invisible trackers' and it's from who has been fighting for our privacy for years.

Now, when I load our website the Privacy Badger show ZERO trackers on our site. I do plan to add a localized php log to track IP addresses and count the number of page loads, but that would all be private information.

Art Licensing Manager 3 Progress - Multiple Platforms

Hal Gumbert

Platforms Galore, Pics Below

We did some preliminary testing of the Art Licensing Manager 3 on Mac, Windows, iPad, and Android and it's looking great.

You may notice that we're showing two pics for iPad. In 'iPad Safari' we launched Safari and then typed in the server address. After that pic, we saved that web page to the iPad Home Screen 'iPad Saved to Home Screen' as we show in the video for GoLaunch for FileMaker page. That simply saves a bookmark to the home screen, but it also hides the address bar, which saves a bit of space as you can see in the 'iPad Home Screen' pic.

Longer, Getting Faster, Plus Data Separation

It's getting very exciting to see the Art Licensing Manager 3 come so far considering we haven't spent a ton of time on it. We're finding that it takes a a bit longer to develop in Xojo but as we build a reusable code library, we're getting faster and faster. Plus, since the database is using SQLite we get true data separation from the app code. That means with Xojo, we won't have to import data from the prior version. We'll simply use the exact same database file and add any tables the updated version requires.

Reducing Costs with Xojo

Using Xojo to build this app will reduce the costs to our users significantly too. Currently, the ALM is released as a single user FileMaker database (Runtime) which has zero cost to our users who only need single user acess. As soon as someone needs to go to two users, a minimum of two copies of FileMaker Pro would be needed. Beyond five users, FileMaker Server would be needed. As you can imagine the cost of FileMaker adds up quickly, but developing in Xojo takes ALL those costs away. With the purchase the Xojo development environment, we can build and distribute as many apps as we want which means those who use our apps, don't need to pay anything beyond the cost of our product.

Need an app?

Let us know if you need an app. With Xojo, we can help you build an app for iPhone, iPad, Mac, Windows, or a Web app that can run in a browser. All we need to do is talk about what you need and then we can provide a ballpark estimate regarding the costs.

Mac Safari

Mac Safari

Mac Chrome

Mac Chrome

Windows Internet Explorer