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
106replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Roberto,

    1. You can use select with any table in your database. For linked tables, select is

    typically not necessary since you can directly access linked records using the field names (which is a lot faster).
    2. select does also have access to formula fields.

    3. You don’t need select for this case, just use the field names of the relations. E.g. if you have

    City => State => Country

    you could have a formula in City:

    State.Country.Name

    to get the country’s name.

    Best regards
    Like
    • pkrumme
    • pkrumme
    • 5 yrs ago
    • Reported - view
    Hallo Ninox-Team,

    ich suche eine Möglichkeit mit einer Schaltfläche einen Datensatz zu löschen.
    Wenn ich jedoch den hier beschriebenen Befehl anwende,
    delete first(select Person where 'First Name' like "Hans"),
    kommt es immer zu einer Fehlermeldung, dass die Spalte "delete" nicht gefunden werden kann.

    Bitte um Unterstützung.

    Vielen Dank und viele Grüße, Philipp Krumme
    Like
  • Hi,

    Is it possible to add calculated records to a sub-table based on values in the main table?

    For example in the main table I have an amount field and a time field. In the sub-table I have a scheduled amount field and a scheduled time field. I would like to create 12 records in the sub-table with each record containing a scheduled amount := amount/12 and scheduled time := time/12.

    Kind Regards,

    dbox
    Like
  • Hallo Philipp,
    du hast diese Frage schon mal im deutschsprachigen Teil des Blogs gestellt.
    dort habe ich auch geantwortet. Ich vermute, du hast deine Frage nicht mehr gefunden.
    An dieser Stelle meine Bitte an Ninox-Team:
    Bitte ein Forum mit email Benachrichtigung einrichten.

    Zurück zu der Frage. Die Formel wäre:

    let NN := Nr;
    delete (select Lagerbewegung where Nr = NN)

    Damit kannst du den Datensatz löschen, in dem du dich gerade befindest.
    Der Befehl "first" ist angebracht wenn du nur einen Datensatz löschen möchtest, wo Hans in der Vornamen vorkommt.

    Grüße
    Leo
    Like
    • jef chip
    • jef_chip
    • 5 yrs ago
    • Reported - view
    hi, trying to sort out a calc for a field that concatenates 2 fields as well as a third if the third has anything in it (would use IsEmpty in filemaker, just for reference). it is a contacts DB for artists who sometimes use a moniker (a.k.a.). however, the parentheses in the following calc show even when the field is empty, so i get the following results:

    First Artist (nickname)
    Second Artist ()

    the formula:

    'Given Name' + " " + 'Family Name' + (if aka != "" then
    " (" + aka + ")"
    else
    "")

    i have also tried using equals (instead of not equals) and get the same prob:

    'Given Name' + " " + 'Family Name' + (if aka = "" then
    ""
    else
    " (" + aka + ")")

    cheers for any advice.
    jef
    Like
    • Birger H
    • Birger_H
    • 5 yrs ago
    • Reported - view
    Hello Jef,
    the formula should be:
    'Given Name' + " " + 'Family Name' + (if aka then
    " (" + aka + ")"
    else
    "")
    Like
  • Hi jef,

    'Given Name' + ” ” + 'Family Name' + (if aka = null then ""
    else
    " (" + aka + ")")

    Regards
    Leo
    Like
    • jef chip
    • jef_chip
    • 5 yrs ago
    • Reported - view
    yes! fantastic, thanks.
    jef
    Like
    • K Gamble
    • K_Gamble
    • 5 yrs ago
    • Reported - view
    Hi - is there a way to sort the data in an array - for example

    concat ( for s in States (s.Name))

    Assuming 'States' is a subtable with field 'Name' in it - can I modify the above to sort the Names? Something like order by?
    Like
  • Hi,

    you can apply sorting with this formula:
    sort(concat(States.Name))

    Best regards, Alex
    Like
    • K Gamble
    • K_Gamble
    • 5 yrs ago
    • Reported - view
    Thanks - actually - it worked but format was concat(sort(...))
    Like
    • tomek
    • tomek
    • 5 yrs ago
    • Reported - view
    Hi,
    I can set and show map view with one record.
    How can I show on one map view all records which has location field set?
    I want to see all my customers from city and surrounding villages.

    Thanks
    Like
    • Birger H
    • Birger_H
    • 5 yrs ago
    • Reported - view
    Hello Tomek,
    We would love to have this feature and we are working on it. At the moment only one pin on a map is possible.

    Greetings
    Birger - Ninox Support
    Like
    • tomek
    • tomek
    • 5 yrs ago
    • Reported - view
    Thanks for reply :)

    I'll be waiting for this feature.

    One more think, how to open two databases?
    In one I have my contacts and CRM, in second my transaction and sales.
    I don't want to connect them but often I need two (only on MAC).
    Like
    • Birger H
    • Birger_H
    • 5 yrs ago
    • Reported - view
    Hi Tomek,
    You can't open multiple instances of Ninox so in the App you can only have one database open at a time.
    Its a different story for Ninox Cloud.

    Greetings
    Birger - Ninox Support
    Like
    • pbnlzen
    • pbnlzen
    • 5 yrs ago
    • Reported - view
    Is there a more complete Function reference document other than this web page? For example, the first part of this Reference of Functions and Language page is labeled as "Tutorial" and you give *examples* of several functions such as cnt, min and max, but in the section labeled Function *Reference*, there is no description or even mention of those functions. To fully utilize the value invested in your database product it would help to have more detailed documentation.
    Like
    • Birger H
    • Birger_H
    • 5 yrs ago
    • Reported - view
    Hello,
    we know that this reference is far from perfect and are working on major improvements. However "cnt", "min" and "max" are mentioned and explained in the section Working with Table References.

    Greetings
    Birger - Ninox Support
    Like
    • Tim
    • Tim
    • 5 yrs ago
    • Reported - view
    Hi. I love Ninox!

    I need to traverse the records in a table in a particular order, instead of in the Ninox internal sorting order. Is that possible?

    For example, I would like to retrieve the records in the following example sorted by some field in the table rather than in the default internal sorting order:

    for locxrec in (select LocationsXTrips where Trips.Id = thistrpid and 'In Directions') do

    end

    Thank you for your help!
    Like
    • Tim
    • Tim
    • 5 yrs ago
    • Reported - view
    In case my question is not quite clear:

    In a formula script (for a button, in this case), I want to loop through the records in another table, but I want to loop through them in a specific sorted order, not in the Ninox internal sorting order.

    So, if my other table has a Name field, I might want to loop through the records in that table sorted by Name. Is there a way to do that in the following statements, or any other way/function?

    for namerec in (select NamesTable ) do

    bla bla bla

    end

    Thank you for any help!
    Like
    • Birger H
    • Birger_H
    • 5 yrs ago
    • Reported - view
    It is not possible travers through table entry in a particular order. So there has to be a different solution :-)
    What is you scenario?

    Birger - Ninox Support
    Like
    • Tim
    • Tim
    • 5 yrs ago
    • Reported - view
    Thank you, Birger. If this is getting too much for this forum, let me know and I will email instead.

    In this case, I have a table with locations (lat/long), and I have a Number field that allows me to specify the sequence in which to visit the locations. I can then sort that table by sequence, if I choose.

    In another table, I have a button that builds a Google Maps Dir URL into a URL field to generate the directions for visiting those locations in the specified sequence. I do this by using a "for ... do ... end" construct in the button formula, as shown here:


    let thistrpid := Id;
    'Directions URL' := "https://www.google.com/maps/dir/";
    for locxrec in (select LocationsXTrips where Trips.Id = thistrpid and 'In Directions') do
    'Directions URL' := 'Directions URL' + (if locxrec.'This Waypoint Location' then
    locxrec.'This Waypoint Location'
    else
    locxrec.Place.Place) + "/"
    end


    Right now, it generates those directions in the Ninox internal sorting order, so for now I open that generated URL, then manually drag the locations up and down in Google Maps, then copy the resulting URL and paste it back into the URL field in the table. Bu then, if I change anything about the locations or their sequence, I have to do that all over again.

    Thank for you any ideas or suggestions!
    Like
    • Tim
    • Tim
    • 5 yrs ago
    • Reported - view
    I seem to have a solution!

    I stumbled onto the sort function a while ago, and in the middle of last night I thought maybe it applies here, so this is what I am doing now (simplified for example):

    resultfield="";
    for thiskey in sort(Locations.SortKey) do
    let locrec := first(select Locations where SortKey = thiskey);
    resultfield:=resultfield+locrec.SortKey
    end

    I suppose using first(select...) in a "for" loop for thousands of records could be slow, but in my case I will never have more than ten or twenty records in the loop.

    Also, it is up to me to make sure there is only one record from each select (i.e. the SortKey is unique), since I always pick the first record (to make the formula syntax OK).

    Look OK?
    Like
    • Rudy
    • Rudy
    • 5 yrs ago
    • Reported - view
    Good evening,

    In my table, I have a number field for "Drilled depth", i would like to calculate the length on daily basis (each time a new record is entered in the table). The length drill would be today's drilled depth - yesterday's drilled depth. To do so, I would use something like "last record" - "previous record". How can I find the previous record on the table?
    "Last record" or "max record" does not work as every day this information will change.

    Thank you
    Like
  • Hi Rudi,
    try the formula field with this code

    let myId := Id;
    'Drilled depth' - max((select 'my table' where Id < myId).'Drilled depth')

    Regards
    Leo
    Like
  • Thanks for your answer on relating the tables it worked perfectly. I'm now getting a message when I try to run another "Update Multiple Records" process. Ninox is telling me "This formula may not modify data."

    It seems I can't proceed with the update. Here's what I'm trying to do:

    --- In English ---
    - Find the first space character in the "Name" field (should be at the end of the first name) and tell me what character number that space is (from the beginning of the name, where the beginning character number is '0'). The "Name" field holds the full name of the person, sometimes including middle initial. We want to extract the first name from the string.

    - Get the substring from "Name" of the characters from the start of "Name" up to the character right before the first space character.

    - Update the FDICNameID field by combining the "FdicCert#" field, the first name, and the last name (which is already in it's own field called "Last").

    --- The Code ---

    let fnameEnd := index(Name, " ");
    let fname := substring(Name, 0, fnameEnd - 1);
    FDICNameID := 'FdicCert#' + fname + " " + Last

    --- End Code ---
    Like
Like Follow
  • 4 yrs agoLast active
  • 106Replies
  • 35623Views