Developer Tips of the Week

Ninox & CalendarPLUS Dev Tips of the Week

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” 

Confidence with arrays is just around the corner. Give these methods a try and, with just a little experience, you’ll quickly be an array expert.

Jennifer can be reached at