0

Use of a variable following a Select statement

Using a script as follows:

let myAccount := text(Account);
let myCategory := text(Category);
let myYear := number(text(Year));
let myMonthIndex := monthIndex(text(Month));

sum((select myAccount)[myCategory = text(Category) and myYear = Year and myMonthIndex = Month].Amount)

 

The above snippet does not work when myAccount is used after Select.

If I use the actual table eg. 'myTable' it does work.

Is it possible to use the variable myAccount in this statement.

Could someone advise please.

I have tried let myAccount := "'" + text(Account) + "'" and this also does not work.

Thanks for any help

Terry

5 replies

null
    • Leonid_Semik
    • 6 yrs ago
    • Reported - view

    Hi Terry,
    that is not possible. Ninox understands the variable "myAccount" only as text. How many accounts do you have? If there are not that many, you can use switch-case:

    ---

    let myAccount: = switch text (Account) do
    case "Account1":
    Table1
    case "Account2":
    Table2
    case "Account3":
    Table3
    end;
    let myCategory: = text (Category);
    let myYear: = number (text (Year));
    let myMonthIndex: = monthIndex (text (Month));

    sum ((select myAccount) [myCategory = text (Category) and myYear = Year and myMonthIndex = Month] .Amount)

    ---

    Leo

    • TerryH
    • 6 yrs ago
    • Reported - view

    I didn't quite follow your code but presume you meant something like:

    ----------

    let myCategory: = text (Category);
    let myYear: = number (text (Year));
    let myMonthIndex: = monthIndex (text (Month));

    let myAccount: = switch text (Account) do
    case "Account1":
    sum ((select 'Account1')......
    case "Account2":
    sum ((select 'Account2')....
    case "Account3":
    sum ((select 'Account3') .....
    end;

    ----------

    Terry

    • TerryH
    • 6 yrs ago
    • Reported - view

    An issue with the use of Switch/Case is that as you add and delete tables it invalidates your script.

    For each table addition/deletion you have to modify the script.

    Is there anyway around this limitation?

     

    ----------

    Terry

    • Leonid_Semik
    • 6 yrs ago
    • Reported - view

    If you use a new table for each account, then "yes" - it's a problem. I would try to keep all accounts together in one table (with a checkbox "Account name").

    Leo

    • TerryH
    • 6 yrs ago
    • Reported - view

    Yes, I did have that arrangement with a field for "Account Name" to allow searching etc. but thought I would explore this solution. Will have to revert back to the original method of doing this.

    Ninox need an update to address this shortcoming. My situation is not to bad but someone will I am sure have a need for the arrangement we have discussed in this thread.

    -----

    Thanks

    Terry 

Login to reply

Content aside

  • 6 yrs agoLast active
  • 5Replies
  • 3461Views