Ninox Connections

The Ninox connections allow data to be read from and written to a Ninox cloud database.

There are three different Ninox connections:

Ninox Create – allows a new row to be inserted into a Ninox database table from data in the Squirrel spreadsheet
Ninox Read – allows data to be read from a Ninox table to a range in the Squirrel spreadsheet
Ninox Update – allows one or more rows in a Ninox database table to have one or more of its column values updated

To add a connector to your project open the CONNECTIONS drawer in the object browser, select the appropriate entry from the dropdown box and click the plus button:

The connection will appear in the CONNECTIONS drawer of the object browser. Select it to access its properties in the property panel.

Common Ninox Properties – Credentials

Security credentials are required in order to use the Ninox connectors. Obtaining these will depend on whether you are accessing a database in the Ninox public cloud or you have a private Ninox instance.

Ninox Cloud Credentials

To access a Ninox cloud database you will need to obtain an API key for your account. Details on how to do this can be found in the Ninox documentation at:

Ninox Database – Manual / API / Get started

Private Ninox Instance Credentials

To access a private Ninox instance please contact us at support@squirrel365.io for further information

Connecting to the Ninox cloud database

The top section of the property sheet for the Ninox connectors is the common across all of them and provides an area to enter your API and to select the particular table you want to work with .

Enter your API key in the top text box and press the “Validate API” button:

This will expand the properties to show three further dropdown boxes which allow you to choose the Team, Database and Table you want the connector to work with:

Once you have made your selections the remaining options in the property sheet will depend on the specific type of connection and are documented below.

Common Ninox Properties – USAGE

The USAGE drawer of the properties panel allows control over how and when the data is refreshed.

Refresh on load – This is the default setting and means that the Squirrel project imports the data when the project starts. Unchecking it will mean that one of the other usage properties will need to be checked in order for the data to be imported.
Refresh on interval – Check this file to set a recurring interval. The Squirrel project will import the data after each interval. The interval value is measured in seconds, setting it to 30 will ensure that the Squirrel project will pull the data every 30 seconds.
Refresh on cell change – Bind to a cell that will have its content updated when the project is running. This could be caused by the user interacting with a control, input from a data mover function, or the result of a calculation etc.

Ninox Read Properties

The Ninox read connection allows data to be read from a Ninox table to a range in the Squirrel spreadsheet. Once it has been configured to access a particular database table the property sheet will expand to show the following additional properties:

Limit rows returned – limits the number of rows which can be returned to the Squirrel spreadsheet from the database. For performance this property is set at 1000 and cannot be changed (if you have a use which requires a return of more than 1000 rows please get in touch with us at support@squirrel365.io.)
Filter Rows – this property is covered later in the article.
Add column headers – selecting this option will add column headers to the returned data
Insert data here – this property should be bound to the range of cells in the spreadsheet where the data from the Ninox table will be loaded.
Sort data – this property is covered later in the article.

Tip: Ensure that the configuration of the destination cells is large enough to contain the data being imported e.g if the intention is to import 50 rows of data from two columns, ensure the destination cells cover 50 rows and two columns.

Once you have specified a range for the Ninox data to be read into, switch the designer to DEBUG mode, this will run your project and retrieve the data from the database table into the appropriate location in the spreadsheet

Tip: The data from Ninox is only read in when the project is run which is why you need to got to DEBUG more to see it. In order to make sure the data does not overwrite important spreadsheet data in your your project it disappears again when you return to design mode. If you want to see the data in a design mode then you can select it when you see it in the debug window and press Ctrl-C this will copy ito the clipboard and when you return to the design window you can use Ctrl-V to paste the data in there.

Data Filtering properties

If you check the Filter Rows property the property sheet will expand to show the following options:

Retrieve rows WHERE – this property should be bound to a two row range in the spreadsheet which gives filter values for each column you wish to filter by. For example the sample data given in the property sheet will filter the data to those rows where the column “Invoice_No” has the value “1234” and the column “Product” has the value “Shoes”.

Data Sorting properties

If you check the Sort data property the property sheet will expand to show the following options:

Select the column – use the dropdown to select the column whose data you want to use to sort the results
Direction – select the appropriate option to specify how you want the results to be sorted

Ninox Create Properties

The Ninox create connection allows a new row to be inserted into a Ninox database table. Once it has been configured to access a particular database table the property sheet will expand to show the following additional properties:

Insert – this property should be bound to a two row range in the spreadsheet which gives the values for each column to be inserted from the spreadsheet into the Ninox database table. For example the sample data given in the property sheet will insert a new where the column “Invoice_No” has the value “1234” and the column “Product” has the value “Shoes”. Any column which is not specified will be given a blank value. It is currently only possible to create one row at a time.
Insert update response here – this property should be bound to a 2×2 (two row / two column) range in the spreadsheet. When the create is attempted the result is registered in this range in the following form:

The timestamp can be converted to an Spreadsheet date using the formula of the form: <timestamp>/(1000*60*60*24)+25569, so if the timestamp was in cell H5 then the formula would be =H5/(1000*60*60*24)+25569

Ninox Update Properties

The Ninox update connection allows existing rows in the Ninox database table to be updated from data in the Squirrel spreadsheet. Once it has been configured to access a particular database table the property sheet will expand to show the following additional properties:

Find rows WHERE – is property is used to identify the rows to be updated and should be bound to a two row range in the spreadsheet which gives values for each column to be used to identify the update rows. For example the sample data given in the property sheet will update all those rows where the column “Invoice_No” has the value “1234” and the column “Product” has the value “Shoes”.
UPDATE with – this property should be bound to a two row range in the spreadsheet which gives the values for each column to be updated in the Ninox database table. For example the sample data given in the property sheet will update the column “gross” to a value of 100 and the column “net” to a value of “12”0″ for all rows which match the “Find rows WHERE” property. Column which are not specified are left with their existing values.
Insert update response here – this property should be bound to a 2×2 (two row / two column) range in the spreadsheet. When the create is attempted the result is registered in this range in the following form:

Last Updated: 2 years ago in Connections

Was this article useful?