Ninox Connections
This article describes the Ninox Connections and their properties.
Who can use this feature?
Ninox (Read) - All Squirrel subscriptions have access to the Ninox Read connector.
Ninox (Create, Update) - only Builder plans have access to Ninox Create and Update connectors.
For more details, see our pricing comparison matrix.
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 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 common across all of them and provides an area to enter your API and 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 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 that 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 that 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.
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
The data from Ninox is only read in when the project is run which is why you need to go to DEBUG more to see it. To make sure the data does not overwrite important spreadsheet data in 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 it to 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 one where the column “Invoice_No” has the value “1234” and the column “Product” has the value “Shoes”. Any column that 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 columns) range in the spreadsheet. When the creation is attempted the result is registered in this range in the following form:
The timestamp can be converted to a 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 – This 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 updated 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 that match the “Find rows WHERE” property. Columns that 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 creation is attempted the result is registered in this range in the following form:
The timestamp can be converted to a 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
Last updated
