VOLUME LXI 11/24/2020
Zanaris is a global Managed Service Provider based in Canada, delivering excellent service, dev-ops, and cloud transfer services. Roger M., their Chief Operations Officer, uses Ninox to manage Zanaris. He built their entire CRM system in Ninox himself, but he always found that he had to export his data to create reports worthy of showing his clients. When he heard that Nioxus was going to be releasing ReportsPLUS, he was thrilled. Roger purchased ReportsPLUS the moment it was available.
“ReportsPLUS takes data and turns it into information. Now when you’re building a Ninox database, you will tackle the overall problem differently, because of what ReportsPLUS brings to the equation.”
-Roger M, Chief Operating Officer of Zanaris
ReportsPLUS utilizes a powerful reporting engine, and specializes it for the user’s of Ninox. Roger used to have to export his data out of Ninox and use other products to create easy to understand management level reports for his clients. “The reports I’m trying to produce, by their very nature, require a slightly different variation of the data model. It either meant in Ninox that I had to create a whole bunch of temporary tables and populate those temporary tables to create that data translation, and of course with ReportsPLUS, by its nature, there’s a data translation in building the data sources.” Now he can use ReportsPLUS to automate, keep his data in Ninox, and send it to the people it needs to go to.
Project Dashboard created in Ninox Reports Plus
When asked about the dashboard portal, Roger said it would be something he uses regularly. “That was another deciding factor for me….I can have a dashboard that’s useful, interactive, with a lot less pain, than trying to do the same thing in Ninox.” ReportsPLUS creates better reports, faster. It enables Roger and Zanaris to create reports effortlessly, allowing them to focus on the rest of the business.
Would you like to tell the world about your Nioxus/Ninox solution? Let me know at email@example.com and I’ll be in touch soon.
unnecessarily complex from a practicality perspective. And this unnecessary complexity means we could spend way too much time debugging the code and/or trying to figure it out when we come back and look at it later in our project.
Well, the holidays are upon us here in the US and we could not be more appreciative for all of our friends, family and others who support us. Whether you have been with us for months or found us recently, we hope you enjoy being a part of our community and look forward to seeing you again soon.
Although we are not having a webinar this week, there’s still the video vault stuffed with past sessions and for Standard (and up) members, the templates are available too. So if you are having too much fun with building software, we still ‘got ya covered’ – any time of day or night.
It’s been an exhausting year and as we take a breath for the long weekend, it’s nice to appreciate the good things in life. I am glad to be a part of a team of helpful, knowledgeable and creative characters. I am grateful to you for being a part of the Nioxus experience, whether through training, consultation, individual one-on-one training, or for just opening our email.
We have so much to be thankful for this year: we have realized the dream of building a world-class reporting and analytics engine that any business can afford, we built a stand-alone scheduling tool and are about to realize our long term goal known as the gold star program (starting in December, so check back soon).
Much joy to you and yours. Many thanks, as always. Stay safe and warm and peaceful.
This week’s Learning Lab is cancelled, have a happy Thanksgiving!
The results are in and the Nioxus elves are hard at work finalizing the details of our Gold Star rewards program! Information as to what awards will be offered, and how many stars each item will “cost” will be released shortly.
The Gold Star program has two main reasons behind it. One, we want to share some joy with you, our members, the guests at Learning Lab, our clients and friends. We hope that the time you spend with us each week as Andy is teaching both the new users on Ninox as well as the ones who have been using Ninox for years is as enjoyable for you as it is for us. Not only do we learn about Ninox and coding, attendees are now helping each other. We are so grateful to provide a platform for Ninox users worldwide to come together each week (except next week) and any other major holiday.
The second reason is to get to know all the Ninox community, and have a bit of fun – we like to keep the webinars fun and even have a few laughs at Andy’s trivia and jokes. So I hope you have as much of a good time listening to our goofiness as we do every Thursday.
I am looking to get some feedback as to just how much the Learning Lab is helping you. How can we make it even better? I still see many who may be too shy to ask questions. Let us know what topics you would like to see, what classes or training or videos would help you on your Ninox journey.
Please email me at firstname.lastname@example.org with your feedback, I look forward to hearing from you and working to always keep improving. Have a great Thanksgiving, everyone!
Don’t be Arrested by Arrays
by Jennifer Neighbors, Senior Consultant
When I first started working in Ninox, arrays sounded like an exotic and difficult concept that I wanted to avoid dealing with as long as possible. After a while, however, I couldn’t keep putting arrays off. I think one or two programming problems arose that convinced me to take a wary and cautious look at them. Once I gathered the courage to face arrays, I found them to be far more pedestrian that I’d anticipated. I now approach them without any hesitation at all, and you can, too.
I think of arrays as multiple data elements fitted into a single field. The data elements are separated by commas. An array can store any number of values from just a few to hundreds or even thousands. Let’s take a look:
select Invoices [month(‘Invoice Date’) = 11 and year(‘Invoice Date’) = 2020].Id
This statement is going to return an array of all the invoice Id numbers of the invoices generated in November of this year. There may be only one or two or there could be hundreds of invoices in this array. Regardless of the count, an array will be returned when this code is executed. You may wonder why, if only one id number is found, an array will still be created. That’s because whenever the count of values COULD be more than one, it’s an array.
What does our array look like? If the first invoice found is, say, id number 22, and none have been deleted, the array will look like this:
“22, 23, 24, 25, 26, 27, ….” until all the invoice id numbers are listed.
The first thing most of us learn about arrays is that you can run calculations on them. Of course, in the real world there wouldn’t be a use for totaling or averaging the id numbers of invoices, but once your array exists you could use the “sum” and “avg” methods to do so. More relevant in our scenario is the possibility of picking out the first, last, smallest or largest number from the array using the “first”, “last”, “min” and “max” methods. And finally, you can count the values in an array using the “cnt” or “count” methods. Let’s give our array a name, pick out the last value from it, and store that value as a variable.
let Ids := select Invoices [month(‘Invoice Date’) = 11 and year(‘Invoice Date’) = 2020].Id;
Let xLast := last(Ids)
Suppose you needed to know the number of invoices generated during November in your business? Which method would you use?
Another easy and frequently used command is the “concat” method. This method returns all the values in the array with commas in between, exactly like the array shown above. And, if our array has duplicates which you want to remove, simply use the “unique” method to return only the unique values in the array. You might also want to sort the values in your array. Did you know you can sort them by ascending or descending order? You can! Use the “sort” and the “rsort” methods respectively. So far, so good, right?
Let’s take a look at some less commonly used but no less important methods you can try with your arrays. Once you start to experiment with them in your code, I think you’ll see that they are not hard to understand and use.
Do not be hesitant, like I used to be, to use the “item” method. It’s a powerful tool for picking out a single value from an array by counting its “index”, or position number, in the array. To do that, remember that the first value in the array has an index of zero, the second one has an index value of one, and so on. In other words, the index is “zero based”. To pick out the sixth invoice generated in the month of November, your code will need to identify the index number to select, like this:
let Ids := select Invoices [month(‘Invoice Date’) = 11 and year(‘Invoice Date’) = 2020].Id;
let xNum := item(Ids,5)
Understanding the concept of a zero based index will serve you well when you want to “slice” an array. Let’s say you want to extract the 10th through the 15th Invoice Id numbers from your array. Try this:
let xNums := slice(Ids, 9 , 14)
And finally, let’s imagine you want to create an array from a string which has repeating embedded characters. All you need to do is use those repeating characters by turning them into commas and you’re done:
let Fruit := split(“apples pears bananas oranges peaches strawberries”, “ “)
The code above replaced the space character with a comma and returns an array named Fruit that looks like this:
“apples”, “pears”, “bananas”, “oranges”, “peaches”, “strawberries”
Customizing Calendar Plus
By Adam Davidson, Senior Developer
There are a number of ways in which you can customize the data that is attached to events in the Calendar Application. Any configuration of the Calendar Plus application is done by editing the fields in the Calendar Plus Events (CPE) table in your Ninox database. There are certain fields in the CPE table that have to be set up a certain way; Start Date, End Date, etc., but others are variable and can be configured in a variety of ways. Here are some ways you can edit the CPE table to configure the Calendar Plus application:
What are some configuration options that you would like to see supported in Calendar Plus? Let me know at email@example.com.
by David Gyenes, Senior Consultant
Many people ask the same question about table relations (Parent – Child, know as “inner join” or equivalent, also called “outer join”) but they don’t seem to have the right answer. They wonder when or why we see a “plus” with a “search” or just the “search” sign within the link field or the “unlink” option.
We all know that Ninox has one-to-many relationships between two tables that have two types. When we create a “link”, by default it is an outer join, or a sub-table that will be a child table to the parent we are in, inner join. This is the key we need to understand. Both sides of the outer join can exist without the other while a child cannot without its parent.
If we have an outer join we will see both the “plus” and the “search” icons in the link field. So we not only can connect a record to the one we are in, but we can create a new one as well. The reason behind this is the linked table is not a child of any other table. If the linked table was a child of another table we could not create a new record (we wouldn’t see the “plus” icon) because a child cannot exist without a parent. We would need to create the child record from the parent record first then connect the record to the “foreign” table record.
The other side is when a child record is created we don’t have the option to unlink from the parent. Again, child cannot exist without a parent, we cannot separate (unlink) we only have the option to delete the child record.
Dial into that Phone Format
By Jennifer Neighbors, Senior Consultant
One of the most constant concerns of those who create lots of databases is the issue of phone number formats. Users like it if phone numbers are look consistent and are appropriate for their region. Making this a challenge is that users may enter the number using a variety of styles and add spaces, dashes, periods or plus signs as they like, which creates a mixed bag of phone number results!
Not long ago I was lucky enough to be given a tool by my colleague David Gyenes that comes in especially handy for this. It’s a function that strips everything but digits from anything a user enters. For example if the name of your phone number field is “Phone”, then this function will return only the numbers from anything the user might enter into that field:
replacex(Phone, “\D”, “g”, “”)
I understand how this works only very imperfectly, so I’m hoping David will break that down for you himself next week!
Here’s how I use it to make phone number formatting simple: In the trigger after update function of the phone field, I store the results of the function replacex(Phone, “\D”, “g”, “”) in a variable. I then use the “length” and the “substr” functions to transform the variable into the format I want. For example, I may want to return the long form of a French telephone number regardless of whether the user enters the long form (with international dialing code), or the short form (the way a person inside France would call) of the number. Using the “length” function to measure the number of digits in my variable, I can see which version of the number was entered and pick out the numbers I want to reuse. Then I can easily use Ninox’s string operators and the “substr” function to form the phone number as I want it to appear in the Phone field and put it there. Wonderfully, my phone number fields accept any style of input and then appear in exactly the manner I want as the user exits the field!
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.