VOLUME LX 11/17/2020
Direct Dealer Services serves a territory from the Green Bay area of Wisconsin through Chicago, and all the way south to Indianapolis. They are agents for vendors in the automotive after-product sector. They sell gap insurance and extended warranties, among other services to automotive dealers and repair facilities. DDS was originally working with individual spreadsheets to keep track of what they had sold and to whom. These spreadsheets were disorganized, and incongruous with each other. Agents would write dealership names differently and files would be inconsistent in placement. It was impossible to keep track of where they needed to collect commission from, forcing them to drive up and down their entire territory unnecessarily.
“Ninox is an affordable, robust database program with a simple user experience. Nioxus is the expert in the marketplace for Ninox, and we know they’ll always do it right, the first time.”
-Joe C, Direct Dealer Services
They decided there had to be a better way, so they researched databases for about a year. DDS tried a few but most were either too expensive or had a poor user experience. That’s when they found a link to YouTube about building databases, and came across Ninox. Using Ninox Training Videos by Nioxus, they were able to teach themselves enough to start building their own database to address their needs. When they reached out for help, Nioxus delivered. Nioxus built DDS a database to track which vendors sold which policies, and who owed them how much. Nioxus also built a dealer payment processing database, to make the process of paying back to insurance companies easier.
Thanks to Nioxus and Ninox, DDS would never again miss payments from a dealership, or waste gas driving to every dealership in their region.
They were also able to identify underperformers, and knew where to target their sales efforts. Nioxus and Ninox opened up a whole new realm of efficiency for Direct Dealer Services. This took on a new meaning when the pandemic came and the state of Illinois shut down. DDS was able to transition to remote work to keep their workers safe and prevent any disruption in their vendor and dealership interactions.
This week I discovered a very simple solution to a couple of challenges that initially had me stumped. What I stumbled upon in my effort to solve a couple of puzzling programming problems is quite simple to put into place, requires no fancy programming to learn and can be used by any Ninox beginner. I call my solution “temporary tables”. These are tables that hold records only for a short period of time while you need them for a specific purpose. They aren’t a part of the database architecture at all. They are merely containers.
Here’s what I mean: I had a report based on records in a table. Upon implementation of my database there are going to be hundreds of records in the table. I needed a subset of those records as the “line items” in my report because a report based on the entire table would be many pages long; too overwhelming to be useful. I needed to find a way to narrow them down. As a programmer, I can apply date filters manually to narrow the records to a date range. But which date range? And, could I expect novice users to go into the report layout screen and apply a filter there?
I decided to try creating an invisible temporary table, and, based on the user’s own entered date range, and populate that table each time the report was run. The temporary table, not the original table, would form my “line items” detail on the report. I placed a button on the screen with along with a beginning and ending date for the report. When the user pressed the button, the code procedure created records in the temporary table that fit the defined date range. Then, it generated an email to the linked customer with the report attached and deleted all the records in the temporary table. Success! I decided to keep this in mind; it seemed like a handy approach.
A few days afterward, another puzzler appeared in my programming life: My client, a music store, needed a visual display of available half-hour time slots for student lessons. Each teacher worked a different set of hours on different days of the week and their schedules were already partly filled with lessons. So, my display had to show a specific teacher’s unique daily schedule with half-hour time slots available (or not available). This would help the user quickly see when another student could be booked in. Here again, I was able to use a temporary table to solve my dilemma.
I made a very simple invisible table with columns for the lesson date, the start time, the end time and a Yes/No field for availability. Then I put a button on my form and a view. The view simply showed the temporary table’s records. The first thing the button’s “on-click” code did was to delete any records already in the temporary table. Then, using stored work start and work stop times for each day of the week in the teacher’s table, the code populated the temporary table with records: one for each half-hour time slot in that teacher’s working day. I used more code to compare the start and end time in each record with the records in Calendar Plus’s hidden event table. I inserted “Yes” if no conflict was found and inserted “No” if it was. The view quickly filled with a useful display of that teacher’s schedule. Later I used formatting to make the available time slots colored green and the others red.
Here’s how my code works:
* if ‘LESSON DATE’ then
delete (select ‘AVAILABLE TIME SLOTS’);
let xCurrRec := number(Id);
let xDate := date(‘LESSON DATE’);
let xTeacherName := TEACHER.’Full Name FNF’;
let TStart := number(‘Teacher Start Time’);
let TEnd := number(‘Teacher End Time’);
let j := do as server
let xNmbrHalfHourSessons := (TEnd – TStart) / 1800000;
for x in range(0, xNmbrHalfHourSessons) do
let i := (create ‘AVAILABLE TIME SLOTS’);
i.(SESSIONS := xCurrRec);
i.(‘Lesson Date’ := xDate);
i.(‘Start Time’ := TStart + 1800000 * x);
i.(‘End Time’ := time(number(i.’Start Time’) + 1800000));
i.(Available := “Yes”)
for j in select ‘AVAILABLE TIME SLOTS’ do
let s := (select ‘CALENDAR PLUS ENTRIES’)[date(‘START DATE’) = xDate and
date(‘END DATE’) = xDate and PARTICIPANTS like xTeacherName];
let a := count(s[‘START TIME’ <= j.’Start Time’ and ‘END TIME’ > j.’Start Time’]);
let b := count(s[‘START TIME’ >= j.’Start Time’ and ‘END TIME’ <= j.’End Time’]);
let c := count(s[‘START TIME’ >= j.’Start Time’ and ‘END TIME’ < j.’End Time’]);
let d := a + b + c;
if d > 0 then
j.(Available := “No”)
alert(“Please enter a lesson date.”)
In this instance my temporary table is named “Available Time Slots”. Its field names are “Lesson
Date” (a date field), “Start Time” (a time field), “End Time” (a time field) and “Available” (a yes/no field). First, I delete all the records in the table. Then, I loop through and create one record in this table for each half-hour interval and then populated the fields accordingly. Another loop evaluates each record for conflicts with calendar entries and sets the “Available” field to “No” when a conflict is found. Do you wonder why some lines of code refer to the number 1800000? It’s because that is the number of milliseconds in a half-hour block of time. Having calculated that, I can easily figure out how many time slots are in a specific teacher’s schedule for the selected day.
I am taking advantage here of Calendar Plus’s hidden table named “Calendar Plus Entries” to interrogate the calendar. The calendar’s “Start Date”, “End Date”, “Start Time”, “End Time”, and “Participants” fields make this code block possible.
Notice that only a portion of this code is executed on the server. That is because date calculations and alerts don’t work well there. Even placing part of this code on the server, however, speeds it up considerably.
Keep in mind that tables can be more than database structural elements. Try using them as temporary containers when you need to store records for a specific purpose to get things done.
*Thanks go to David Gyenes for explaining to me how to evaluate each time slot for conflicts using the variables “a”, “b”, and “c”.
Wow! An amazing Fall so far! With the V3 release of CalendarPLUS and the newest product by Nioxus – Ninox ReportsPLUS – the Nioxus team is finishing 2020 strong.
We are so proud of our newest offering and look forward to continuing to provide world-class training, support and consultation – providing the very best in business services and analytics/reporting at an affordable price. It used to be that companies had to pay a MINIMUM $100k to have such powerful tools! Now, Nioxus is elevating the power of Ninox to a whole new level and will continue to innovate and provide valuable solutions for every business to succeed with low-code technology at an affordable price.
Welcome to data-driven decisions.
Welcome to accurate information when you need it.
Welcome to results.
Thank you for joining us on this journey – and continue to let us know what we can do to help solve your problems and meet your needs. Not 100% sure what you need? Sit down with a Nioxus consultant today!
Join us this Thursday on the 19th at 12:00pm EDT for our open Q&A! Make sure to register and have your questions ready!
Last week in the Learning Lab, after the cameras are turned off and the lights dimmed , I began to reflect on what it is that helps me be fulfilled, with and around my love for Ninox and more.
First if not already that obvious, I don’t mind being told that I am a geek. For me that is a badge of honor. So if anyone want to call me a geek, bring it on. So what is it that makes me a Ninox lover? Ease of use and simplicity. The great power within that allows you to do so many things with the least coding involved. I am probably just like you looking for a well designed database application for the Mac and secondly, not costing an arm and a leg. Ninox accomplishes those with ease, and then some.
Now as much as I love Nioxus (Ninox consulting team and software producers), I also love to do the other programming in languages such as JSON, API, C, etc AND the tools I use (Postman, Visual Studio Code, Nova, BBEdit, etc).
Now, there are so many thing I could say about Ninox but I want to turn the tables and I would like YOU to tell US at Nioxus what is it that you love about Ninox and also what it is about Nioxus that excites you. Tell us about the databases you are working on or those you have already built using Ninox and if you would like to share your experiences. What is it that you do when you are using Ninox but also what programming languages are you using?
Please tell us about you and how you use Ninox and other programming tools. The more we know you and what you are working on and looking to solve, the better off we both will be.
Hope to see you at the Learning Lab and possibly earn some Gold Stars.
Remember, in December, you will be able to exchange your Gold Stars for Nioxus Swag and more. Any questions or comments, please contact me. email@example.com
Till next week,
Emails in the Calendar
By Adam Davidson, Senior Developer
Calendar Plus Gives you the option to automatically send update or reminder emails for events. Here are some tips on how to get the most out of the email functionality in Calendar Plus.
All logos, trademarks and names are the protected property of Nioxus Corporation or their respective owners.
“Ninox,” “Ninox Database” and the blue Ninox owl eye logo are the property of Ninox Berlin and are used with permission.