I have an insane problem that is pickling my brain at the moment.
I’m creating a learning app. When users click buttons within the app, a thing called ‘Log Entry’ is created for that user (just the basic fields so I can filter by User).
What I want is to be able to show how long they were active for, considering that anything more than 15 minutes of inactivity constitutes becoming inactive. If the user clicks something after this 15 minute period, I want that to be considered as the start of a new period of activity.
I want the app admins to be able to generate a table of active periods, for example:
Date: 19/03/18, Start active time: 11:00, End active time 14:30
Date: 20/03/18, Start active time: 12:30, End active time 13:05
etc.
The calculation of Start active time and End active time is a result of a Bubble search that finds the 12:30 entry because no other entries before it are less than 15 mins away. All the entries between 12:30 and 13:45 are less than 15 mins apart. The next entry after 13:05 is more than 15 mins away. Here’s what the database used for that calculation would look like:
This makes perfect sense. You did a great job explaining it Richard. It looks like you just need to restructure your database. As a result, some workflows will need to be rearranged.
Create an “Activity Period” table. Add the field “Log Entries” which will be a list of Log Entries. It should also have a User field. I’d also add a “Number of Seconds Active” number field (check the end of this post to see why).
Add an Activity Period (single) to your Log Entry table.
When a Log Entry is created, it’ll be added to an Activity Period’s Log Entries. However, it’ll only be added to an existing Activity Period if the most recent Log Entry in that Activity Period’s list of Log Entries is less than 15 minutes from the current time. If it’s not, a new Activity Period will be created. After its creation, the Log Entry will be added.
This is an important step! Make sure you add an Activity Periods field to the User table. It’ll be a list of Activity Periods.
Workflow Example #1: Scenario - I (the current user) have 0 Activity Periods. I click a button.
Workflow Actions: Step 1 - A new Activity Period is created. It contains the Current User in its User field. Step 2 - Make changes to Current User. This is where you add Result of Step 1’s Activity Period into the User’s list of Activity Periods. Step 3 - Create a new Log Entry. It should have all of the necessary data saved. Step 4 - Make changes to Result of Step 1’s Activity Period. Add Step 3’s Log Entry to this Activity Period’s list of Log Entries.
Workflow Example #2: Scenario - I (the current user) have multiple Activity Periods with the most recent Activity Period’s most recent Log Entry being within 15 minutes of the current time. I click a button.
Workflow Actions: Step 1 - Create a new Log Entry. It should have all of the necessary data saved. Step 2 - Make changes to Result of Step 1’s Log Entry’s Activity Period. Add Step 1’s Log Entry to this Activity Period’s list of Log Entries.
Workflow Example #3: Scenario - I (the current user) have multiple Activity Periods but the most recent Activity Period’s most recent Log Entry is NOT within 15 minutes of the current time. I click a button.
Workflow Actions: Step 1 - A new Activity Period is created. It contains the Current User in its User field. Step 2 - Make changes to Current User. This is where you add Result of Step 1’s Activity Period into the User’s list of Activity Periods. Step 3 - Create a new Log Entry. It should have all of the necessary data saved. Step 4 - Make changes to Result of Step 1’s Activity Period. Add Step 3’s Log Entry to this Activity Period’s list of Log Entries.
2nd to last point. Let’s discuss the “Number of Seconds Active” field in the Activity Period table. When a new Log is being added, it’s a good idea to track the number of seconds accumulating over this Period. You could obviously do the Last Log Entry’s date - 1st Log Entry’s date: formatted as seconds but if you don’t have a number field, you won’t be able to sort/filter Activity Periods using this data.
Last point. It would be smart for these workflows to take place as API Workflows. This way, the performance on the site won’t decrease when quite a long workflow is taking place.
Hey Daniel. Just wanted to thank you. I finally got it working and learned a lot about API workflows which I’d never used before. Your advice was just what I needed! I really appreciate it.
