Budibase aims to make solving common problems very easy, and I can understand the design decision to not letting more complex scenarios clutter up their UI. But it still causes headaches for me. I ran into problems trying to display data across multiple tables, and then I learned creating a multi-table record in Budibase can get convoluted for related reasons. The first restriction is the built-in relationship picker tool, which helpfully presents the display column text (Target) and when selected enters the foreign key (476) into the table. But relationship picker has a hard coded ceiling of 100 entries. I don’t have 100 accounts or 100 categories so those will work, but I have definitely shopped at more than 100 vendors! I had to create my own UI to manage the vendor relationship.

I want to write down the next problem here even though it is not yet well understood. Since Budibase couldn’t help me create these multi-table entries directly, I wrote my own interface using Budibase form objects, button objects, and actions associated with button click. The idea is that each form would be a row in one table, and when I click a button, I would use several “Update Field Value” actions to update multiple forms with data generated by my data binding. Then I would use the “Save Row” action on each of those forms to submit my multi-table entry. The idea is to have a mechanism to handle regularly occurring repetitive tasks with a single click.

In practice, my single button would intermittently fail with “Null not allowed” error. If I click my button again, it would always succeed, but then I might have duplicate entries in my database due to my multi-table hack failing partway. This feels like an asynchronous race condition with “Save Row” being executed before all previous “Update Field Value” actions had completed. When I click again a second later, all of the fields are already updated so save row works.

Many databases have a mechanism to designate set of operations as a single “transaction” unit. The database is responsible for guaranteeing “all or nothing”. Either every part of the transaction is successful and written to the database (“commit”) or if something goes wrong, it should be as if nothing had happened. (“revert”) I looked for transaction support via Budibase but didn’t find anything.

Looking for a workaround, I searched for a “Wait for previous action to complete before proceeding” action but didn’t find one, nor did I find anything like a “Wait X second” action. So my workaround is to use two buttons: one has all the “Update Field Value” actions, and a second button with all “Save Row” actions. I click one, then I click the other. Two button clicks is double the amount I thought it should take, but it is still better than doing all the repetitive steps by hand. Still, it doesn’t mask the fact the whole situation is pretty silly and I think reflects a bug in Budibase. But since I’m a beginner it could be my own fault for reasons I don’t understand yet. I’ll have to play with Budibase some more.

Once I got a working knowledge of Budibase data binding, I feel like I’m past the “I have no idea what I am doing” beginner phase and entered into the “I can probably figure out what key words to search for an answer” intermediate phase. The road from here to “I know how to do this” experienced phase is likely paved with a lot of hands-on time. I think Budibase will be able to cover 90% of what I want with very little effort on my part, fulfilling its promise. But that last 10% could motivate me to examine other tools to add to my toolbox.

One example is how Budibase handles table relationships. For every database table, we have to designate one column as a special display column. When displaying tables with defined relationships to other tables, Budibase can automatically replace an foreign key reference with the display column from that column table. My “vendors” table has a “name” column as its display column. Each row in my “transactions” table has a “vendor_id” column that points to a row in “vendors” table. Budibase can combine them so when it shows a row from my “transactions” table, instead of showing the vendor ID (476) it queries my “vendors” table and shows the “name” for that ID instead (Target). This is great and it covers majority of what people would need.

But if I want to go deeper, I’m on my own. My example is my “payments” table, which was my way of implementing classic double-entry bookkeeping and each row has two transaction IDs. One is a deduction transaction from my checking account, the other a credit transaction for the same amount to my Target card account. I wanted to create Budibase dashboard showing a payment amount from “Checking” to “Target”, but those names are two levels away (“payments” to “transactions” to “vendors“) and I couldn’t find a way to do it within Budibase. I ended up solving my problem with a custom PostgreSQL query that performed multiple table joins, then returned the selected results to Budibase, which then dutifully displayed it as a table. I may find a more elegant way to accomplish what I want in the future, but for today this is good enough while I struggle with other problems.

After migrating from a spreadsheet into a PostgreSQL database, I could get to work building an interface for my data with Budibase. The big sales pitch for Budibase is that I could be up and running in minutes. And that is indeed true as long as the interface I want is a big spreadsheet-like table. If I want something more specifically tailored to my data, it’s going to take more than a few minutes. Laying out a page with data input/output elements is a pretty straightforward drag-and-drop WYSIWYG affair, but bringing that page to life with real data requires Budibase data binding.

Immediately following introduction of data binding, Budibase documentation jumps into discussing the two supported languages. (“Handlebars” and “JavaScript”.) I felt they should have covered data binding scope first. In hindsight understand that would have been a useful mental boundary before diving into syntax details of the two methods.

Handlebars

The syntax more commonly seen in Budibase documentation is referred to as “handlebars”, and it can be recognized by the two curly braces that open and close a handlebar expression. Judging by a package.json file, this was built on top the HandlebarsJS library alongside a Budibase helper library. My first exposure to it in Budibase documentation was this example from the Design/Components/Table page:

{{ toFixed (sum (pluck Table Block 1.Selected Rows 'KGS')) 2 }} KG

This extracts data from the “KGS” column of all selected rows in Table Block 1, add those values together, round the sum to two decimal places, then append a “KG” suffix for display. Handlebars syntax allowed specifying such common operations quickly, a really powerful tool but completely inscrutable to a beginner not versed in the compact syntax. It also has its limitations. A significant one is that output of every handlebar expression is always a string. (Called out at the end of the formula page.) Plus, its extensive helper library can’t cover everything. There would inevitably come a time when we need access to a general-purpose language for the job.

JavaScript

If I want to create a binding that can’t be done in handlebars (either fundamental limitation or just I don’t know how) I can click a button to switch to JavaScript mode. This opens up a small text window for a JavaScript code snippet editor and my first step from “no-code” Budibase to “low-code” Budibase. Input comes from an expression that starts with a jQuery-like “$”. The counterpart to the handlebars example above would be something like $('Table Block 1.Selected Rows'). That expression returns a JavaScript array (one element is one row) of dictionaries (key/value pairs of column name and value). From there, standard JavaScript syntax and libraries apply, and my JavaScript code runs until a return statement at the end with a processed value for Budibase to use.

Since the pattern is to consume an array and produce a single output, many of the JavaScript data binding examples use reduce(). That seems to be the most popular one, though other JavaScript array iterators are applicable. I think it’s because handlebars can do most of the rest. In contrast, reduce() takes a snippet of custom logic and applies it. Generally speaking there are times not to use reduce() but as far as I can tell a Budibase binding function is unlikely to touch upon those scenarios.

To venture beyond Budibase default data presentation of a big spreadsheet-like table, I needed a bare minimum working knowledge of data binding in order to build my own interfaces. It didn’t take long before I encountered problems, as expected for a beginner diving in for the first time.

I took my personal finance tracking spreadsheet and reorganized it into a set of normalized database tables each in a CSV (comma-separated values) file. They imported into Budibase fine but I could not define declare logical relationships between them. Looking for another entry point for Budibase built-in database, I found JSON was another supported format so I had my Python code output my spreadsheet data as JSON. Each entry looks something like this:

{
  "Date": "12/18/2016",
  "Vendor": "Target",
  "Total": 41.24,
  "Items": [
    {
      "Price": 26.27, 
      "Category": "Food"
    },
    {
      "Price": 14.97,
      "Category": "Durable"
    }
  ]
}

This is no longer a set of normalized database tables. It’s closer to how things were laid out in my spreadsheet. A date, a vendor name, and a total that may be divided across multiple items each in a different category. Except each category had a column in my spreadsheet and now it is a nested array in my JSON.

I don’t like this layout as much as my normalized tables. The vendor name (“Target”) is repeated over and over in this JSON format, instead of split off into a dedicated vendor table and referenced via ID. This is vulnerable to data entry errors like a misspelling, which could be found all over my original spreadsheet. The nested item categories are likewise repeated instead of sitting in their own table of categories.

Despite my dislike, I was willing to give it a shot. This data schema is vaguely familiar from the time I briefly played with MongoDB, another NoSQL database. Now I built this table with my Python processing code to see what CouchDB (what Budibase built-in database is based upon) can do with this data. The answer: CouchDB can handle this just fine, but building interactions for this data is too far off the beaten Budibase path.

To Budibase’s credit, it was very easy to build UI to interact with all the top-level items: date, vendor, and total. But then I ran into a brick wall with those nested Items remaining opaque. I could only get my list of two items to show up as:

Items: [ (object), (object) ]

Which is not very useful at all! Searching around Budibase GitHub discussion for help, I found #3394 How to create form to accept an array of items which pointed to #2568 JSON (Key / Value) type. It is possible to define a schema for JSON data that includes nested arrays. However, there’s a catch: this option is only available for JSON data tables newly created within Budibase. This is not available for existing data that was imported. Darn! This is probably on Budibase to-do list for the future, but today I will have to abandon JSON approach and try something else.

I want to migrate my personal finance tracking from a spreadsheet to a database. I knew my spreadsheet had accumulated errors over the years, but I didn’t really know how big of a mess it was. Not until I tried to perform database normalization where I brought up a Jupyter notebook to quickly iterate through this side quest. It handled all the repetitious fixes, some search/replace rules to fix inconsistency, and split the data into multiple tables as per normalization rules. While slicing the data up for multiple tables, my code is also responsible for generating unique identification numbers in a table (primary keys) to be used in references from other tables (foreign keys).

I had used Python default CSV (comma-separated value) library to read data exported from Excel into dictionary objects. I performed my error correction and data normalization with Python dictionaries and at the end I wrote those processed dictionaries back out as CSV files. One file per dictionary representing one database table. I thought I was in good shape as I went into Budibase data import menu to upload these CSV into the Budibase built-in database. The file uploads were uneventful, but then I tried to define relationships between those tables and got stuck. It seems Budibase does not allow relationships to be defined for tables of data uploaded from CSV files, which feels like an odd oversight.

Defining relationships between tables is definitely supported in Budibase. Almost every supported data source type has a “Define existing relationships” section. There’s one for MySQL/MariaDB, there’s a section for Oracle, another for Microsoft SQL Server, and a section for PostgreSQL. There is no such section for CSV Import, but I had thought that was merely a documentation omission. Surely there is a way to define existing relationships between tables! But the option is absent from Budibase user interface, so it wasn’t an omission after all. I probably should have tested this assumption earlier, before I put in all the work for data cleanup and normalization. Now I have to figure out some other way forward. Next candidate: JSON data import.

Reading through Budibase documentation and playing around with the quick start tutorial app, I learned many important details I will need to know as I embark on my own project: converting my personal finance tracking spreadsheet into a database app. Before I start working on the app, though, I’ll need to migrate my data over to a Budibase-friendly format.

Budibase accepts uploading data in CSV (comma-separated values) or JSON (JavaScript Object Notation) format. Excel can export a spreadsheet into CSV file format so I thought it would be easy, but once I loaded up my spreadsheet and took a good close look I realized it won’t be that simple. My multi-sheet Excel file has built up many idiosyncrasies. Most of it was because I evolved my system over time but I didn’t go back and change my older entries to match. My brain can parse the inconsistencies within, because it’s the same brain that created them, but they’d trip up a computer database.

I made a copy of my spreadsheet and started editing it to prepare for migration. I plan to create new analysis tools around my migrated database, so I deleted all of my analysis cells leaving just raw data. I removed all empty rows and columns I used for visual separation, because they’d just be noise in the migration. I exported these results to a CSV, imported into Budibase, and it was still a mess. I need even more cleanup.

My next step was a Jupyter notebook. Data cleaning is a pretty common task for data scientists and Panda is a common tool for the job. I don’t think I need that level of complexity, though. Python has a built-in class to parse CSV into a dictionary and that was sufficient for my needs. It was enough to let me iterate through my records and write rules to repair my data entry errors. Typos in names, inconsistent date formats, stuff like that.

I started updating structure of data as well, at first just to maintain data consistency between old and new data. But then I started changing things more drastically because my brain shifted gears from a spreadsheet table to a database. I started normalizing my data to the best of my ability (I think I got to 4NF but I’m not sure) because I understood that allows more efficient database operation. I’m pretty pleased at how it turned out! I’m sure I could have created spreadsheet macros to enforce data consistency, but a properly normalized database makes many of my old mistakes outright impossible. This was the point when I really felt I am making the right move: my personal finance tracking system should have been a database all along. I like my set of CSV files, one per table, and the next step is to upload them into my Budibase project.

I took a quick look under the hood of Budibase to get a rough idea of its capabilities. Before I start reading Budibase documentation in detail, I also want to designate a “north star” project. I’ve found this helps organize my thoughts when I explore a new piece of technology. Without a goal, I risk wandering aimlessly and losing interest. Keeping a project idea in mind helps me stay focused and absorb information in terms of how it relates to my project. For Budibase, I decided to dust off my idea to build personal finance software tailored exactly for myself.

I’ve used personal finance software to track my money ever since I had money to track. I started with Microsoft Money. After Microsoft shut it down, I switched to Intuit Quicken. As the world became more online, I switched to Mint. It didn’t take long before I decided I didn’t like doing my personal finances that way. (Well before Intuit bought and eventually shut down its competitor.)

When I started with Money, the usage pattern is for me to enter my financial transactions into my PC software. Then when I received my bank statements, reconcile what I entered with my statement to make sure everything matched up. By the time of Mint, such usage was still possible but no longer the focus. I was encouraged to connect to my financial services online to download account activity. Everything is entered and reconciled automatically. I totally understand why this was done. Most people don’t want to spend the time to manually enter their transactions, then manually reconcile them. The modern usage pattern takes no time. But to my mind, such automation took away the first layer of awareness of my own finances. It’s easy for small fraudulent transactions to slip through unnoticed, for example. I didn’t like it.

After transactions are entered and reconciled, all personal finance software include analysis tools to help the user better understand where their money has gone. But as a data nerd, such graphs and charts were never good enough. I can bring up a chart, click to see more details about what contributed to that graph, and the drill-down always ends before I am satisfied with the level of detail. I understand that most people wouldn’t care to spend time for such analysis, but I do! Spending reports. Flow of money. Account activity. More, I crave more!

I have come to accept the fact my own desires are not aligned with the general populace. Which meant I will have to roll up my sleeves and do it myself. Microsoft Excel seemed like an obvious choice as spreadsheets are intrinsically linked to finance. I started a spreadsheet to track my expenses and things worked well enough at the beginning. As my records grew, though, I started hitting limits of my Excel skills. It was good motivation for me to learn more Excel as I go, but eventually I recognized my mistake. Yes, Excel is the king of finance, but I’m not doing any financial calculations. (No compound interest, etc.) What I’m really doing is record keeping, which is a database job! I can continue learning Excel techniques to compensate for the fact it isn’t a database, or I can switch to using a database.

I’ve approached this project idea many times. I had this in the back of my mind when I took Codecademy’s PostgreSQL and MongoDB database classes, for example. I even got as far as creating a GitHub repository called “rogmoney”, but I didn’t do anything beyond setting up a Node.js environment. Looking at the timestamp I believe my thought was to build a personal finance app on Angular but I never even got as far as committing an empty Angular boilerplate app. Now I will make another attempt. It will help me learn Budibase whether the attempt results in my ideal personal finance tool or not.