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.
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
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:
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