VOLUME LXXIII               02/17/2021

Flexible and Functional Formula Fields

By Jennifer Neighbors, Senior Consultant

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>&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspCanteens</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				
			
This bit of code forces the state of “Show Detail” to switch between true and false each time the field is clicked. The fields that toggle in and out of view have this line of code in their Display Only, If function:
							
					'Show Detail'				
			
By mastering these variations of the versatile formula field, you can go far toward gaining a full understanding of how to use fields on forms.

Jennifer welcomes feedback and can be reached at jennifer@nioxus.com.

Ninox FormsPLUS is Coming Soon!

This Week in the Learning Lab

 Join us for this week’s Ninox Learning Lab on the 18th at 12:00pm EST for open Q&A! 

Check Out Our Templates

Did you know that Nioxus has built over 140 templates which are available to all Standard, Deluxe and Premier Nioxus members?

Ninox University YouTube Channel

Nioxus has created nearly 100 videos and over 200 hours of content teaching you how to use and optimize Ninox, as well as our supplementary products: CalendarPLUS, ReportsPLUS and DocumentsPLUS

Just Jim

By Jim Harris, Stargazer

This weeks “Just Jim” is dedicated to the Gold Star Program (GSP). February is Gold Star Redemption Month.  Please be sure to choose your Gold Star gift by completing the form before February comes to an end.  Remaining Gold Stars will roll over to next year.

This was our first year for the Gold Star Program.  While we are excited that it has been so well received, we are also very proud to be the only company that we are aware of in the industry, to offer this kind of client recognition 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.”  Why you ask? Humor can only make you feel better.  Laughter has no harmful side effects and packs plenty of positive punch!  Let’s all be sure to take at least a few humor breaks every single day.  It relieves tension and is a crucial component to creativity; the very creativity that is necessary for building great Ninox databases.  I would like to leave you with a quote from Mark Twain:

“Laughter without a tinge of philosophy is but a sneeze of humor. Genuine humor is replete with wisdom.”

So go out, be smart, and laugh it up…

jim@nioxus.com

Jim's Comic Strip Corner

click to view comic 

Quick Ninox Tips

By David Gyenes, Director of IT

– 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				
			

Copyright 2021 © All Rights Reserved.

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.

en_US