0

Parameters it is possible to pass to a function

Hi, fairly new to Ninox and now in the process of "tidying" up a database which has become a bit untidy in terms of number of fields and duplication, this thread sort of fits with what I am trying to achieve but I can't seem to make it work my aim is to have a master table and 2 child tables (soon to add a third) and for the master to pull the data from the child tables. What I have ended up with is 3 functions which I would ideally like as a single function and the key is to pass the table name & required fields into the select statement.

So what I have is this 

Formula P1

let t := this;
let SELFIL := (select table2 where 'order-id' = t.'Channel reference');
SELFIL;
SELFIL.'product-name'

Formula P2

let t := this;
let SELFIL := (select table3 where 'Order Number' = t.'Channel reference');
SELFIL;
SELFIL.'Product Name'

Formula Product Name

if P1 = "" then P2 else P1 end

Sorry if this is basic but I am not a coder

Thanks in advance

10 replies

null
    • Ninox developper
    • Jacques_TUR
    • 2 yrs ago
    • Reported - view

    You cannot pass the table name to the select statement because the Ninox language is pre-compiled and does not support dynamic types like JavaScript.
    But you can pass your current record (this) to a function that calculates which subtable is available and retrieves a good product name.
     

    function getProductName( currentRecord : mainTable ) do

    var subRec1 := first(select table1 where 'Order Number' = currentRecord.'Channel refrence');
    if subRec1 then
        subRec1.'Produc Name';
    else
        var subRec2 := first(select table2 where 'Order Number' = currentRecord.'Channel refrence');
        if subRec2 then
            subRec2.'Produc Name';
        else
            void;
        end;
    end;
end;

    The function is put in the global functions and you can use it anywhere in your database.

    On your mainTable, you can put this code in a formula.

    getProductName( this );
      • support.1
      • 2 yrs ago
      • Reported - view

      Jacques TUR Thanks for the suggestion I'll certainly give that a go

      • CISOFT_Sarl
      • 6 mths ago
      • Reported - view

        Hello, i hope you are fine from so long time. Just a little question : what is the type mainTable in your parmeter function? Have a nice day Dear Jacques. 

      • Ninox developper
      • Jacques_TUR
      • 6 mths ago
      • Reported - view

       I Robert, it’s so nice to read you.
      mainTable is the name of one table in the database. The parameter currentRecord is a record of mainTable. You can call getCurrentName(first(select mainTable where …)).

    • Ninox developper
    • Jacques_TUR
    • 2 yrs ago
    • Reported - view

    Why create two tables with the same structure and different data? Perhaps the best solution is to merge the two tables and differentiate them by a field named 'data type'.
    This would simplify your work.

      • support.1
      • 2 yrs ago
      • Reported - view

      Jacques TUR the data sources have different data there are only a couple of common fields, merging them is what I originally did and as I progress with Ninox realise it is not that straight forward, most of my database experience is pre-historic using Lotus Approach and this would have been ridiculously easy as you could create links based on a single field channel reference in this case as its unique would populate the field with a simple if statement. The end game is to have all the relevant info on a single form and keeping the data in separate tables will greatly simplify the reporting needs. Thanks for the suggestion. 

    • Ninox partner
    • RoSoft_Steven.1
    • 2 yrs ago
    • Reported - view

    What about using the eval() function like:

    let myTable := "Table1";

    eval("(select " + myTable + ")", this)

    EDIT: This only works on Ninox cloud

    • support.1
    • 2 yrs ago
    • Reported - view

    Thanks I did try something similar to this but not using EVAL, which only returned data from 1 of the tables, I am using the MAC app and will also try using EVAL 

    • support.1
    • 2 yrs ago
    • Reported - view

    After a bit of testing, I decided to go back to basics and something I tried before without success as I only used SELFIL at the end rather than as each part of the IF statement so have settled on the below utilising a common field from the parent table

    let t := this;
    if Channel like "abc" then
        let SELFIL := (select table1 where 'order-id' = t.'Channel reference');
        SELFIL;
        SELFIL.'product-name'
    else
        if Channel like "xyz" then
            let SELFIL := (select table2 where 'Order Number' = t.'Channel reference');
            SELFIL;
            SELFIL.'Product Name'
        end
    end

      • Ninox developper
      • Jacques_TUR
      • 2 yrs ago
      • Reported - view

      support The best code is this one with you feel comfortable👍

      You can simplify like this : 

      let t := this;
if Channel like "abc" then
    (select table1 where 'order-id' = t.'Channel reference').'product-name'
else
    if Channel like "xyz" then
        (select table2 where 'Order Number' = t.'Channel reference').'Product Name'
    end;
end
Login to reply

Content aside

  • Status Answered
  • 6 mths agoLast active
  • 10Replies
  • 257Views
  • 4 Following