Reference of Functions and Language

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

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

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

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

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

Hello Jef,
the formula should be:
'Given Name' + " " + 'Family Name' + (if aka then
" (" + aka + ")"
else
"")

Hi jef,

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

Regards
Leo

yes! fantastic, thanks.
jef

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?

Hi,

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

Best regards, Alex

Thanks - actually - it worked but format was concat(sort(...))

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

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

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).

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

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.

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

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!

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!

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

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!

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?

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

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

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 ---