Menu
VOLUME LXXIII 02/17/2021
The type of field I use the most has got to be the formula field. Formula fields are useful for a variety of purposes on table forms and their ability to trigger code makes them doubly useful.
Here is an example of a form with some formula fields. Can you tell which fields are formula fields?
There are eighteen field objects on this form. If you found sixteen formula fields and two fields that are not formula fields, you are correct. The nonformula fields are the choice field “LOCATION” and the date field “ESTABLISHED DATE”.
The field in the upper left corner contains an image. The image is stored in a table named Utils and the image is an image type field there named “Whale”. The formula to retrieve and insert that image in this form is:
select Utils.Whale
The form header field next to it is a formula that contains HTML and uses some internal style settings for the font size and color. The HTML code is this:
html("<b>BLUE WHALE</b> <br>        Canteens</br>")
Here is the choice field called LOCATION:
The field named LOCATION NO refers to the choice field named LOCATION next to it. The formula retrieves the number of the choice, like this:
number(LOCATION)
The EMPLOYEE COUNT field is the count of employee records in a table named EMPLOYEES. To insert the count of employees, the formula is:
count(select EMPLOYEES) [LOCATION = 2]
To compute the number of years in business, the formula is:
age('ESTABLISHED DATE')
What about the header in the middle of the form that says “2020 GROSS REVENUE”? That, too is formula – a formula that just inserts text. A text-only formula places the text in double quotation marks and that’s all. The formula looks like this:
“2020 GROSS REVENUE”
The remaining fields on the form are formulas, too. Some are text only and these make for great field labels when you need greater emphasis that the usual field label provides. In this case, you will need to hide the field labels of the fields that correspond to your formula field labels.
As illustrated in these examples, formula fields can do a variety of interesting things on your forms. They can display images. They can create headers and labels. They can display calculations. Remember, too, that you can use them to trigger code. This shows the resulting form if the user clicks on the Total Gross Revenue amount.
The detail lines have disappeared and only the total is in view. If the user clicks again on the Total Gross Revenue, the detail lines appear again. This sort of “toggle” is created by first making a hidden Yes/No field and then turning it on and off using code.
In this instance my hidden field is called “Show Detail”. I made it a required field with a default value of “No”. The code I used in the ON CLICK function of the Total Gross Revenue formula field is this:
if ‘Show Detail’ then
‘Show Detail’ := “No”
Else
‘Show Detail’ := “Yes”
End
'Show Detail'
Jennifer welcomes feedback and can be reached at jennifer@nioxus.com.
Join us for this week’s Ninox Learning Lab on the 18th at 12:00pm EST for open Q&A!
Did you know that Nioxus has built over 140 templates which are available to all Standard, Deluxe and Premier Nioxus members?
This weeks “Just Jim” is dedicated to the Gold Star Program (GSP). February is Gold Star Redemption Month.
This was our first year for the Gold Star Program.
I personally want to thank all the members who have been attending the Learning Lab.
Last week, we welcomed 8 new members to our family which is great! We also have many members who attend faithfully every week (which I have jokingly named them the “Frequent Fliers”). A special thanks to these folks for learning Ninox as well as learning how to deal with our goofiness, too.
I have been bringing to the table a returning theme of “Humor and Laughter.”
So go out, be smart, and laugh it up…
click to view comic
– Cleaner Code Vol. 2 –
Last week we saw an example of what kinds of code can be used for creating a new record in a table. But how can I return different values from it?
In this first example I show how to return a single value – the ID number of the newly created record:
let xReturn := do as server
let xRec := 0;
(create ‘Test Table’).(
xRec := number(Id);
‘Text field’ := “New”
);
xRec
end;
popupRecord(‘Test Table’, xReturn)
Here is how I return multiple values from the new record and then retrieve one of them. I can retrieve any of the values in this way:
let xReturn := do as server
let xRec := 0;
let xName := “”;
let xAge := 0;
(create ‘Test Table’).(
xRec := number(Id);
Name := “Jon”;
xName := Name
);
{
RecordID: xRec,
Name: xName
}
end;
xReturn.Name
All logos, trademarks and names are the protected property of Nioxus Corporation or their respective owners.
“Ninox,” “Ninox Database” and the blue Ninox owl eye logo are the property of Ninox Berlin and are used with permission.
