0

Reference of Functions and Language

Hi,

I understood the use of the select statement, but I need a confirmation, please...

1.
I can use the select statement with a not linked table too, from any table in the DB?
2.
The select statement run with data fields only, or with formula fields too?
3.
I have three tables; Table A, linked to Table B; Table B linked to Table C; I can use the select statement from the Table A to obtain the Table C data (the Tables A and C are not directed linked); is it correct?
4.
Can I receive a lot of example with a little DB, to understand better the "How to do", please?

Thanks for all
Best regards
Roberto

106 replies

null
    • Leonid_Semik
    • 6 yrs ago
    • Reported - view
    This is correct, the formula can not modify any data other fields.
    You must set Update Multiple Records to the FDICNameID field, and the code would be:
    - The Code -

    FnameEnd: = index (name, " ");
    Let fname: = substring (name, 0, fnameEnd-1);
    'FdicCert # '+ fname + " " + Load

    - End Code -
    • Rudy
    • 6 yrs ago
    • Reported - view
    Good morning Leo,

    Thank you for the answer. I had to replace Max with Last and it worked perfectly.
    For some reason, each time I want to use the Max function in any formula it brings erroneous data, I have not been able to use the Max function so far. But this was not the subject of my question.

    Thank you again
    • Gordon
    • 6 yrs ago
    • Reported - view
    Hi,

    A complete language reference would really be good. In the meantime, how can I delete all records with an empty field. I tried the following in the Console of the database (say I have a table 'Addresses'):

    delete select Addresses where 'Email' = ""

    Other attempts failed as well:

    delete select Addresses where Addresses.Email = ""


    for p in (select Addresses)
    let em := p.Email
    if contains(em, "@") then
    p.Email = em
    else
    delete p


    Given the scarcity of documentation, it is really hard to figure out tasks involving even some basic coding. That's a pity.
    • Gordon
    • 6 yrs ago
    • Reported - view
    Well, after some further trying:

    delete select Addresses where not Email

    gets rid of all empty fields.
    • Leonid_Semik
    • 6 yrs ago
    • Reported - view
    Hi Gordon,
    you can try also:

    delete select Addresses where Email = null

    Leo
    • CISOFT_Sarl
    • 6 yrs ago
    • Reported - view
    Hello,
    In the functions: openRecord (recordId) or popupRecord (recordId) or another or there is recordId, is it possible to pass the name of the table as a parameter?
    How do I use the function recordId (param_1, param_2)?
    Does param_1 = name of the table?
    param_2 = ????
    Thank you in advance for your response.
    Best Regards.
    Robert
    • Birger_H
    • 6 yrs ago
    • Reported - view
    There is no function recordId(). You can identify the record with i.e.
    –––
    let myRecord := first(select 'table name' where 'field name' = value)
    let myRecordID := myRecord.Id
    –––

    Birger - Ninox Support
    • CISOFT_Sarl
    • 6 yrs ago
    • Reported - view
    Hello,
    Sorry, not function recordid() but record().
    I formulate another my question bellow:
    In the functions: openRecord (record()) or popupRecord (record()) or another or there is record(), is it possible to pass the name of the table as a parameter?
    How do I use the function record(param_1, param_2)?
    Does param_1 = name of the table?
    param_2 = ????
    Thank you in advance for your response.
    Best Regards.
    Robert
    • agentxp22
    • 6 yrs ago
    • Reported - view
    Where is first() and last() documented?

    I cannot figure out how to compare the value of two fields from different records in different tables.

    Let's say I have a table called Authors and Books. I want Authors.'Last Name' to be shown, but only on the first occurrence of each author. I could test whether the Nr of the Author.'Last Name' is the first occurrence is the same as the current Nr or something, but how?
    • Birger_H
    • 6 yrs ago
    • Reported - view
    Find the documentation on this page in the section "Working with Table References".

    If you could send me a copy of the Ninox database you are working on (Main menu -> "Save Archvie as...") I'm sure that I can find a solution to your problem.

    Birger - Ninox Support
    • jaypyatt
    • 6 yrs ago
    • Reported - view
    I am trying to do a left join between tables, so I can see all customers with no invoice records. How do you accomplish this in Ninox?
    • Birger_H
    • 6 yrs ago
    • Reported - view
    Ninox is not based on SQL. So "Left Join" is not an available command. You can set table relations analog to left join be using "Mass data update" as explained here:
    http://manual.ninoxdb.de/en/relations/

    Birger - Ninox Support
    • jaypyatt
    • 6 yrs ago
    • Reported - view
    Birger,
    Thanks for that info. I have thought of another way to approach my problem and I am using a trigger to add information to an existing table. Here is my new issue: how do you build a table reference in the formula editor? I can assign an Id value but only as a number, so I don't have a table link that I need.
    Is there a way to create a table link between two tables using a trigger?

    Thanks in advance.
    • Birger_H
    • 6 yrs ago
    • Reported - view
    It is not possible to create a field - and a table reference is is field - with a trigger. The structure of the database can not be modified with scripting.

    Birger - Ninox Support
    • feketeviktor
    • 6 yrs ago
    • Reported - view
    Hi,

    I am a beginner with Ninox, could you help me with the syntax of select and where? I am working on a formula and I tried like this

    let a := 'Customer code';

    select 'Customers'.'Customer name' where 'Customers'.'Customer code' = a

    Could you help please?

    Thanks,
    Viktor
    • Leonid_Semik
    • 6 yrs ago
    • Reported - view
    Hi Viktor,
    try this:

    let MyCC := ‘Customer code’;
    first(select 'Customers' where 'Customer code'=MyCC).'Customer name'

    Simple characters (a, b, c ...) sometimes use Ninox as system variables.

    regards
    Leo
    • FLOQdesign
    • 6 yrs ago
    • Reported - view
    Hi
    Is it possible to have 2 conditional statements working at the same time, like so:
    ```js
    let debit := 'Dr / Cr';
    let clear := Cleared;
    let mycolor := if clear = 1 then
    if debit = 1 then
    "tomato"
    else
    "olive"
    else
    "silver";
    let myamount := if clear = 1 then
    if debit = 1 then
    Amount * -1
    else
    Amount
    else
    0;
    color(mycolor);
    myamount
    ```
    Cheers
    • Birger_H
    • 6 yrs ago
    • Reported - view
    This looks good to me. But: If is not (yet) possible to set a value and a color to a formula field. You can do either not both.

    Birger - Ninox Support
    • Logicwar
    • 6 yrs ago
    • Reported - view
    Hi,

    Is there any way (fonction) to return the number of minutes from a 'duration' field. For now, I use the following: number('duration field')/60000

    Thanks,
    • Frank_Bohmer
    • 6 yrs ago
    • Reported - view
    Hi Logicwar,
    your formula looks fine. There is no convenience function to calculate this.
    Best
    Frank
    • Adriaan
    • 6 yrs ago
    • Reported - view
    Hi,

    Is it possible to extract the string values between commas? I am trying to create a summary of all the information needed for a project by concatenating the values from a multiple choice field in the subtasks. The problem is that the unique command doesn't compare the list of values in the field but rather the full field. I understand why it does that but not how to make it compare the individual values.
    I hope I managed to clarify my question. If not, please let me know.

    With best regards,

    Adriaan
    • Birger_H
    • 6 yrs ago
    • Reported - view
    contains("string", "match") will return true if "string" contains "match".
    See here for more information:
    http://manual.ninoxdb.de/en/function-and-language-reference-2/

    Birger - Ninox Support
    • Adriaan
    • 6 yrs ago
    • Reported - view
    Thanks for the fantastic support so far.

    I have a question about using the "trigger on create" to add a record in another table.

    I have a project table with several sub-tables like proposal, contract, kickoff etc. Whenever I create one of these for a project I want to trigger the creation of a task (in a task table) that is linked to the record that created it.

    The task table has references to all the different project elements and I tried to use the following formula for "trigger on create"
    let p := create task;
    p.Proposal := number(Id)

    A new record is created but I can't get it to write the Id to that field.

    The goal is to build a project management and documentation system where I can easily see the tasks to complete.

    Looking forward to your advice,
    • Leonid_Semik
    • 6 yrs ago
    • Reported - view
    Hi Adriaan,
    You must first declare the ID of the record as a variable.

    let myID:=Id;
    let newTask:=create task;
    newTask.proposal:=myID

    Regards
    Leo
    • jenireid28
    • 6 yrs ago
    • Reported - view
    Hi, how do you tell Ninox to do an action if a field is empty or completed? For example, I would like a new record to be created in another table when a date field is completed. In this case it is a recruitment database and I want the system to create a record on the "Placement" table when the "Accepted" date is filled in on my "Interview" table. So I want to say "do this if 'Accepted' is not blank"
    Thank you!

Content aside

  • 5 yrs agoLast active
  • 106Replies
  • 35874Views