In this post we are going to start building a new app for recording time worked on different Jobs (or Projects etc). I have chosen to base this series on Tadabase to provide a little variety for readers, and to showcase some of the features of this particular platform - but you could just as easily choose Knack or some other database system. This is roughly what the finished article will look like:
Let me start by describing the problem I am trying to solve.
The Problem
I would like to be able to record my time in the simplest way possible against the jobs or projects I have on at any given time. Ideally I would like to be able to select a project and click Start - and then Stop to finish. I would like to be able to record time against multiple projects multiple times a day and to see these displayed and totalled by both the job and the user.
I want the app to be multi user and to automatically record the time for the logged in user and show each user their own time recording screen.
Thinking About the Database Structure
If you have read my posts about "Relating to Relational Databases" you will be familiar with the concept of the data structure of a database being a close model of real word things and processes.
In this example, then, we clearly need 3 tables in our database:
Users: A table containing a record for each user of the system
Jobs: To hold details of each of the jobs we could work on
Time: Holding start and end times for each period of work etc.
We'll decide on the specific fields needed in each table as we progress (and will no doubt revisit as we flesh out the app).
So Let's Go...
We are going to start from scratch here with creating a new app (not from a template):
If you are going to follow this then feel free to use your own name!!
In Tadabase, when you create a new app, the process automatically sets up two tables for you - Users (which we will be using) and Sample Data (which I would suggest you delete).
Here we are looking at the Data Builder and you can see the two tables - if you look at the Page Builder section, you will also see that there are some default screens created - again you can delete the ones for Sample Data (Sample Page).
Back in the data builder, you should now just have the Users table looking like this (in the Fields section):
These fields are all standard fields and are required for controlling access to the application - I am not going to spend a great deal of time at this stage considering this but we may come back to it later - but for now, the table has all the fields we need - so lets add a Jobs table:
You can see here that I have already added fields for Job Name (Text), Job Description (Long Text) and Created Date/Time - for which I have set a default of Current Date/Time and I am in the process of adding the last field for now which is an Auto Increment field that I will call Job Number.
Finally, we need somewhere to record our time - and in this case each record also needs to connect the person working on the job and the job itself - so let's create a new Table for Time Records and start to add the fields we need: User: A connection to a User record
Job: A connection to a Job record
Start Time: A date/time field
End Time: A date/time field
Note that Tadabase will have set up a default Time Record Name field with can be deleted.
Start by creating the connection fields - here is the screen for the connection to Users:
I have called mine User - now add the other 3 fields.
Calculating Time Spent
Now we have all out input fields, it's time for the magic to happen - to use Equations in Tadabase to calculate the time spent for each time record and then, finally add up the Total against each Job. We'll start with a Date Equation to calculate the hours (in hours and decimals if you set it as below):
This is probably a good point at which to set up a User record, a Job record and then add a Time Record to see that the calculation we just added works. This can be done from the Records tab in Data Builder.
A couple of points about setting up users - you will have to set a password, role and status - as below:
For this exercise we are only going to need the Default user Role - but you can have several and use this to control people's access to the application.
If you have now added a record to each table you should see something like this in the Time Records table:
You can see that 1.2 hours was spent on the project (1 hour 12 minutes) - we are now able to record our time!!
Totalling Time Spent for Each Job
Tadabase gives us the ability to sum values from connected records (and count, min, max, etc.) so we would expect to simply go to the Jobs table and create our total field (a sum of the Time Spent values from the Time Records). However, there is a slight complication in that the Time Spent field is the result of a Date Formula and a bit of a special case because it is not just a number of hours (although it is formatted as such). So we first need to set up a field in Time Records using an basic formula - like this:
I've called mine Time Spent (Hours) and you can see that I have selected the Time Spent field - and you get the option to select the Hours part when you add it:
Finally, we can now add the total field to the Jobs table - this is just a matter of adding a Sum field like this:
Summary
So now we have the basics of the database all set up - in Part two, I will begin the process of configuring the screens for our application - so watch this space!
Regular readers will know that I spend a lot of time working with Knack - this project could just as easily be built with this but I thought it would be interesting to build on a different platform for a change - and at the end of the process I will share so thoughts on the relative advantages / disadvantages of these two platforms.
If you would like to find out more about Tadabase then please click here.
Comments