0

how to add to variables

Hi,

This seems like a basic question, but I can't work out what I am doing wrong.

I have two variables, and I want to create a third variable that is to be the result of taking the second from the first.

So I have written this:

let result1 := (select 'PL Matches').'Mark/Ben Total';
let result2 := (select 'PL Matches').'Ben/Mark Total';
let result3 := result1 - result2

The third line isn't accepted by Ninox (though it will accept the third line if I replace the "-" with a "+" ,which I find baffling). I have tried using 'sum' to calculate result3 but this doesn't make any difference.

Can anyone point out my (presumably obvious) error?

Many thanks.

Mark

3 replies

null
    • John_Halls
    • 7 mths ago
    • Reported - view

    Hi Mark

    This is a common newbie gotcha! Your line 

    (select 'PL Matches').'Mark/Ben Total'

    doesn't return a number. It returns an array, even if it only finds one record. If you are confident it will indeed only find one record you can turn this into a number by using the first() function. It take the value of the first element of the array. So your code needs to say

    let result1 := first((select 'PL Matches').'Mark/Ben Total');
let result2 := first((select 'PL Matches').'Ben/Mark Total');
let result3 := result1 - result2

    It is useful to read up all about arrays in Ninox. They are a foundational part of the code set and learning how to manipulate them will stand you in good stead.

    Regards John

      • mark_robinson
      • 7 mths ago
      • Reported - view

       Thanks John. That's really helpful.

      'Number' seems to do the same thing. I didn't realise that select wouldn't return a number even if it is a number in the selected field. Great stuff!!

      Mark

      • Fred
      • 7 mths ago
      • Reported - view

      It is not that Ninox is not returning a number. It is, but if you don't use one of the array commands, then Ninox will return an array of numbers.

      To see this in action, in a new formula field put the following code:

      let result1 := first((select 'PL Matches').'Mark/Ben Total');
debugValueInfo(result1)

      The debugValueInfo() command is not documented, but it is a very helpful debug tool. It shows you what kind of data you have and in what form.

      Since you said number() works, you will most likely see:

      number([4]) <- I don't know what value it is so I just put a random number here.

      The square brackets around the number tells you that Ninox has returned an array. Currently the array only has one value, but it can hold more

      Now if you did the same thing where Ninox returned more than 1 value then number() will not work anymore.

      This is where you need to use first() or last() or item(), so you only get 1 value.

      As said, arrays are foundational to DB scripting.

Login to reply

Content aside

  • 7 mths agoLast active
  • 3Replies
  • 70Views
  • 3 Following