Create a Stitch
Stitch Query Types
SQL Stitch.
SQL is by far the most common type of Stitch used in Sheetloom. It comprises an SQL query, a predefined database connection, and optional parameters.
Form Stitch
Used when a user needs to select one or multiple parameter values at runtime from a pre supplied list. When Form type is selected the corresponding parameter must be supplied before the stitch can be saved.
SuiteQL Stitch
For clients with a NetSuite installation. SuiteQL code is used to query NetSuite via a preconfigured connection established during Sheetloom´s onboarding process.
Create A Stitch
All Stitches are configured in the same way.
Navigate to the Patterns page and click Create Stitch.
The Create Stitch panel displays
Stitch Elements
| Field | Description |
|---|---|
| Pattern Name | The name of the Pattern to save the Stitch to. Select from a dropdown list of all patterns. |
| Stitch Name | The name given to the Stitch. |
| Type | The query type: SQL, SuiteQL or Form. |
| Connection | The connection to use when running this query. Choose the required connection from a pre-populated dropdown list. |
| Query | The query to be to executed at runtime. |
| Query Builder | A graphical interface to enable non-technical users to build a simple SQL query using a CSV data source. The inteerface is accessed by this button and the generated query is saved to the stitch configuration panel. |
| Description | Optional description box. |
| Parameters | Used if the query is parameterized. Use the +/- buttons to attach/remove an existing parameter to the Stitch. |
Input the Stitch details. Click Preview to view and review the data. The first ten rows display.
Click Close, then Addto add to save the Stitch to the Pattern.
The Stitch can now be mapped to the Excel template.
Adding Parameters to a Stitch.
To enable dynamic filtering at runtime the query can be parameterized using “?” in the query´s WHERE clause. The column name used for the parameter must match an existing parameter separately created in Sheetloom and which is assigned from the Parameters dropdown box. See how to create a parameter here.
In Create or Edit Stitch, add the required parameter into the WHERE clause of the SQL Query:
WHERE parameter name =?
Click on the + sign to open up a list of Sheetloom parameters and add the corresponding one for the query.
Multiple parameters can be added by adjusting the query and adding in further parameters.
Click Preview to open and review the query. A parameter prompt displays. Enter the test value and click submit. The first ten rows of the results are returned.
Click Closeto shut down the preview then Save
In the example a parameter called Office was created in Sheetloom and added during Stitch configuration to match the parameter name in the SQL query. At runtime the User is presented with the dialogue box as shown above in the Preview, and prompted to enter a filter value.
Edit a Stitch
Navigate to Patterns and click the Pattern box then the Stitch. From the Stitches page select the ellipsis, or click on the Stitch name and edit stitch button that displays.
The Edit Stitch panel box opens. Make necessary changes and select Submit to save the Stitch. Edits can be Previewed before submitting.
Changing a Stitch name will break any existing mapping it has with Excel templates.
Copy a Stitch
Navigate to Patterns and click the Pattern box then the Stitch. From the Stitches page select the ellipsis, or click on the Stitch name and Edit Stitch button that displays. The Edit Stitch panel box will open. Click Copy. The Stitch is copied to the same Pattern, appended with “_copy”.
Delete a Stitch
Navigate to Stitches. Check the box beside the Stitch name to be deleted and click Delete Stitch. A Stitch can also be deleted by selecting the ellipsis button.