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
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
-
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 - -
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 -
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. -
Well, after some further trying:
delete select Addresses where not Email
gets rid of all empty fields. -
Hi Gordon,
you can try also:
delete select Addresses where Email = null
Leo -
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 -
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 -
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 -
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? -
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 -
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?
-
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 -
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. -
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 -
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 -
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 -
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 -
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 -
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, -
Hi Logicwar,
your formula looks fine. There is no convenience function to calculate this.
Best
Frank -
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 -
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 -
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, -
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 -
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
- 6 yrs agoLast active
- 106Replies
- 35932Views