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

8replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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 );
    Like
      • support
      • support.1
      • 2 mths ago
      • Reported - view

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

      Like
  • 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.

    Like
      • support
      • support.1
      • 2 mths 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. 

      Like
  • What about using the eval() function like:

    let myTable := "Table1";

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

    EDIT: This only works on Ninox cloud

    Like
    • support
    • support.1
    • 2 mths 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 

    Like
    • support
    • support.1
    • 2 mths 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

    Like
    • 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
      
      Like 1
Like Follow
  • Status Answered
  • 2 mths agoLast active
  • 8Replies
  • 68Views
  • 3 Following