0

Function and Language Reference

Is it possible to create views called "This Month" and "Last Month"? The same formula could be used for "This Year" and "Last Year" as well as "This Week" and "Last Week". I'm trying to create views for donations received this month and last month.
51replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hello CliveW,
    you can apply sorting on columns. Click on the column head. From the dialog choose "sort".
    Best regards, Alex
    Like
    • Temp
    • Temp
    • 6 yrs ago
    • Reported - view
    Thanks Alex,

    Unfortunately not as simple as that. I think I have resolved my issue by using a filter in a function. Still fine tuning it, but think I am heading in the right direction.

    Clive
    Like
  • Dear readers,

    I am looking for the following:

    I build a booking system and the first step is to check if the dates are available, which I do by the following code:

    let a := 'Request Arrival Date';
    let b := 'Request Departure Date';

    (let p := first(select Boekingsproces where 'Arrival date' a or 'Arrival date' >= a and 'Arrival date' < b);


    So I check the requested date in the 'boekingsproces' table where are the reservations

    Next I show the 1e reservation that conflicts or I create a new record in the table 'boekingsproces' through the following code

    if p then
    ('Conflict with' := p.Naam;
    Arrival := p.'Arrival date';
    Departure := p.'Departure date')
    else
    ('Conflict with' := "Geen conflict";
    (let n := (create Boekingsproces);
    n.('Arrival date' := a);
    n.('Departure date' := b);


    In case there is no conflict I would like to open the newly created record with the statement:

    popupRecord(record(Boekingsproces,last))

    However I cannot find documentation how to address the latest record, only the example:
    openRecord(record(Invoices, 1))

    Would love to know how to address the last record or a record with certain value for instance: where NAME is 'Janssen'

    Anyone who can help me?

    Kind regards Hedzer
    Like
  • Hello Hedzer

    try this:

    in the beginning of your formula, save a variable with the last record like this:
    let NNN := last(select Boekingsproces).Nr;

    and if you want to popup that record, try:
    popupRecord(record(Boekingsproces, number(NNN)))

    best wishes. rene
    Like
  • Thanks very much Rene,

    de function number() was key. I already found another way to find the record by using max() for my boekingID, but received a variable that obvious was no integer.

    Another remark is your record field Nr did not work. I see that in my databases the automatic field is Id

    So my routine is now:

    let Nn := last(select Boekingsproces).Id;
    popupRecord(record(Boekingsproces,number(Nn)))


    Can you point out where functions like last() and number() are being described in the manual?

    Regards Hedzer
    Like
    • Danilo
    • Danilo
    • 5 yrs ago
    • Reported - view
    Hi there,

    I'm very happy about your software and all its features.

    I've just a few questions regarding advanced use of if/then/else and select/where statements when enclosed in button codes which may be of interest to other users.

    Firstly, how to enter multiple lines in an if/then/else statement. The following code returns the error "End expected at line 2" ONLY WHEN I enter the second line for "code output 02" field:

    if 1 = 1 then
    'code output 01' := "Hi"
    'code output 02' := "there"
    else " "

    Is adding a "+" at the end of the first row the right solution:

    if 1 = 1 then
    'code output 01' := "Hi" +
    'code output 02' := "there"
    else " "

    Secondly, how to get the ID of the record selected using the select/where statement?
    The following example code returns an error:

    if cnt(select Invoice where Customers.Name = "Nick") = 1 then
    let invoiceID := select Invoice.Id where Customers.Name = "Nick"
    else " "

    Is there maybe a more robust way (as the above one works only if the result record is just unique) to select/get record IDs?

    Thanks,

    Danilo
    Like
    • Danilo
    • Danilo
    • 5 yrs ago
    • Reported - view
    Apparently, the solution to the "multiple lines" issue is to use the semicolumn at the end of each line but the last one AND enclose everithing within brackets, as follows:

    if 1 = 1 then
    (‘code output 01′ := “Hi”;
    ‘code output 02′ := “there”;
    ‘code output 03′ := “!”)
    else ” ”
    Like
    • tomek
    • tomek
    • 5 yrs ago
    • Reported - view
    Hi,
    Can you tell me how to use color function?
    I want to set different colors depends on content in table and text boxes.
    I try to use if/then color(red)
    Thanks for help
    Like
    • Birger H
    • Birger_H
    • 5 yrs ago
    • Reported - view
    Hi Tomek,
    say you have a color field named "myColor". Try it like this:

    if xxx then myColor := "red" else myColor := "#880088"

    HTML named colors or hex values are possible.
    Like
    • tomek
    • tomek
    • 5 yrs ago
    • Reported - view
    Can I change color of text box, number box or function field?
    Like
    • Birger H
    • Birger_H
    • 5 yrs ago
    • Reported - view
    For number fields or text fields it is not possible to set color. For formula fields try this:
    if Number > 10 then color("green") else color("red")
    Like
    • thinsch
    • thinsch
    • 5 yrs ago
    • Reported - view
    Hallo,
    ich möchte den Inhalt von Zellen mittels eines Scripts zählen.
    Ich scheitere allerdings an der Verwendung der Variable.
    Fehlermeldung: "Error: Eine Tabellenspalte wurde nicht gefunden: “vB” in Zelle 2, Spalte 14."

    Folgendes Script habe ich verwenden wollen:

    let vB := 0;
    if "Marker01" = true then
    vb := vB + 1
    else
    vb := vB

    Wo liegt der Fehler?
    Danke im Voraus!
    Like
  • Hallo thinsch,
    versuch mal die Anführungszeichen bei Marker01 weg zu lassen.
    Wo liegt die Formel? als Trigger bei Marker01 oder woanders?

    Grüße Leo
    Like
    • Birger H
    • Birger_H
    • 5 yrs ago
    • Reported - view
    Doppelte Anführungszeichen markieren "Text". Wenn der Feldname Sonderzeichen enthält, wird der Name des Feldes in einfachen Anführungszeichen angegeben: 'Mein Feld'.
    Der Feldname - Marker01 - wird (da ohne Sonderzeichen) auch ohne erkannt.

    Gruß Birger - Ninox Support
    Like
    • thinsch
    • thinsch
    • 5 yrs ago
    • Reported - view
    Danke für Eure Antworten.
    Sorry mein Fehler - ich habe das Script aus dem Kopf geschrieben.
    Marker01 hat die einfachen Anführungszeichen 'Marker01'.
    Der Fehler zeigt auf Zeile 3: vb := vB + 1
    Wenn ich vB weglasse und gegen eine Zelle aus der Tabelle ersetze, ist das Problem nicht mehr da bzw. springt auf Zeile 5: vb := vB
    Bitte entschuldigt den Umstand!
    Like
    • thinsch
    • thinsch
    • 5 yrs ago
    • Reported - view
    Das Script wird in Verbindung mit einer Schaltfläche verwendet.
    Like
    • geral
    • geral
    • 5 yrs ago
    • Reported - view
    Hello,
    for a product table with several suppliers for each product I'm trying to get the best price, I managed to get it using: min('supplier1', supplier2, supplier3, supplier3, supplier4), now, my question is; how can I get the supplier name along with the lowest price?
    Thanks in advance,
    mike
    Like
    • Birger H
    • Birger_H
    • 5 yrs ago
    • Reported - view
    So you can do something like this:
    –––
    let myBestPrice :=
    min(supplier1, supplier2, supplier3, supplier3, supplier4);
    last(select 'table name' where 'price field' = myBestPrice).'Supplier Name'
    –––

    Greetings
    Birger - Ninox Support
    Like
    • tomek
    • tomek
    • 5 yrs ago
    • Reported - view
    Hi,

    I want to use in one function field put some text and color which is depending on text. How can I do this?
    When I set color, then I don't see text.
    Like
    • Birger H
    • Birger_H
    • 5 yrs ago
    • Reported - view
    Hi Tomek,
    formula fields can hold text or (background) color - but not both. Is is only choice fields where you can set text an color.

    Greetings
    Birger - Ninox Support
    Like
    • gb_eh
    • gb_eh
    • 5 yrs ago
    • Reported - view
    Can anyone provide a more in depth source of language reference. I'm a MS SQL programmer and this program is perfect for what I need around the house but I'd like to know more on how to leverage the code side of things. Struggling somewhat ... looked for online resources and they are far and few between, and are really basic / out dated.. any one point me to a good resource (other than here)?
    Like
    • Birger H
    • Birger_H
    • 5 yrs ago
    • Reported - view
    Download this manual as a single pdf

    Birger - Ninox Support
    Like
    • Christoph
    • Christoph
    • 4 yrs ago
    • Reported - view
    Hallo,

    in der Version 1.7. hat die folgende Funktion wunderbar funktioniert und hat alle Tasks mit der Untertabelle "Arbeitsschritte" verknüpft.


    for i in select Tasks do
    for p in select FSA do
    let q := (create Arbeitsschritte);
    q.(Tasks := i.Nr);
    q.(FSA := p.Nr);
    end
    end


    Seit dem letzten Update bleibt die Datenbank dann hängen und es geht nicht mehr. Ich vermute er kommt nicht mehr aus der Schleife raus, aber ich verstehe nicht, was jetzt das Problem ist.

    Hat da jemand eine Idee?
    Like
  • Was ich versuchen würde:
    ---
    for i in select Tasks do
    for p in select FSA do
    let q := (create Arbeitsschritte);
    q.(Tasks.Nr := i.Nr);
    q.(FSA.Nr := p.Nr)
    end
    end

    ---
    Es wurde irgendwas an IDs geändert, so dass diese jetzt nicht mehr als Texte agieren, aber nicht konsequent. Und es war bei dir ein Semikolon am ende zu viel.

    Grüße
    Leo
    Like
    • Christoph
    • Christoph
    • 4 yrs ago
    • Reported - view
    Hallo Leo,

    danke für die Antwort. Das Semikolon hatte ich schon entfernt. Dein Vorschlag führt leider auch nicht zum Ziel, wobei sich die Datenbank damit nicht jedesmal aufhängt, sondern nur sporadisch. Manchmal passiert auch einfach nichts. Ich hab mal probeweise was eingefügt, damit die Schleife nach einer bestimmten Zeit verlassen wird und ich sehe dadurch, dass die Einträge immer wieder erstellt wurden und es nicht stoppt nachdem er am Ende ist.


    let time_start := number(now());
    let stop := false;
    for i in select Tasks do
    if stop = false then
    for p in select FSA do
    if stop = false then
    let q := (create Arbeitsschritte);
    q.(Tasks := i.Nr);
    q.(FSA := p.Nr);
    let time_end := number(now());
    if time_end - time_start > 5000 then
    stop := true
    end
    end
    end
    end
    end
    Like
Like Follow
  • 4 yrs agoLast active
  • 51Replies
  • 19454Views