SQL Connector Applications

[Grant]

Hi folks,

As we try and grow the Tulip Community, I wanted to start committing to regular content posts that help explain certain app concepts, new features, or apps that we on the Customer Team may be testing out. The plan to start will be posts every two weeks, which will hopefully grow more frequent if these seem helpful. I’d like to also open this up in the future and structure posts as a response to specific asks from the prior week. Please leave any and all feedback to help structure this in the most useful manner.

For the first post, I wanted to cover an application structure that I’ve been working on closely with several customers. I want to try and distill this down to it’s most generic format, explain how the application functions, and then give a little context into how it’s being used today. I’m a little split on how much detail to go into so please if there are any follow up questions, I’m happy to respond to any questions.

NOTE: This does require more advanced knowledge of Tulip including SQL connectors, although provides the basic queries needed to run.

Application Goal:

Provide a dynamic, interactive view into a database to manage states. For this article, I’m going to build a quick app to manage tasks.

Use Cases:

Work order management, audit task tracking, material requests and replenishment, parts picker, etc.

Application Overview:

Before we go any further, I want to show what this app will enable you to do.

SQL Database

Currently, this application does require a SQL (Structured Query Language) database to function. In the Tulip Knowledge Base there’s a few different instructions on how you can spin up your own database and connect to Tulip, so I won’t expand much further on that, but feel free to reach out with questions. One thing to note about SQL - while it’s a standard language, it has different dialects per provider (MySQL, MS SQL, PostgreSQL, etc.).

Once you have a database created, you can use SQL clients to easily visualize the underlying structure. Personally, I’ve used Postico (PostgresQL - Mac) and DBeaver (universal).

Table Structure

The columns in the table represent the following. As previously mentioned, this can be structured in many different ways with different pieces of information.

id - A primary key (unique identifier) for a task

Task - The task to be completed

Status - Defaults to requested, while move to in progress before final completion

Creator - Name of the person creating the request

Assigned - Name of the person the task is assigned to

Request Date - Date the request is made

Completion Date - Date the request is completed

Tulip Connector Functions

Once your database is connected to Tulip and the the table above has been created, we’re ready to start building functions to be used. There’s many tools to help learn the basics of SQL, so I don’t want to focus too much on this.

NOTE: This development typically happens in parallel with the application, building additional functions when the need arises.

This application will use three types of functions:

1. Display Open Tasks

A few notes about the above query. SELECT allows us to map individual columns from our database to outputs of this function. We’re mostly mapping one-to-one, with two exceptions. Request date (stored as a timestamp) is being split into a date and time output using the substring keyword - essentially focusing on a set characters from the timestamp.

The FROM identifies the name of your table. As the name suggests, this function should only return the open work orders, so we add a WHERE clause to exclude any work orders with a complete or cancel status. Lastly, we order the tasks by the date and time so oldest requests will show higher up in the list.

Ensure the Return Multiple Rows checkbox is selected as we will be managing several tasks.

2. Create New Task

This function will serve as our input into the table. When creating a task, the user will provide the task name, the name of the task creator, and the name of the individual the task is assigned to. These serve as inputs into the function, but we will also pass in the current time stamp as the request time using the NOW() keyword which returns the UTC time, which we adjust for EST.

When running a test, we can see that the information is appropriately populated into the database, with the id and status being appropriately assigned.

3. Status Changes

We’ll build status change functions for in progress, complete, and cancel. This can be expanded to any number of states but the general function is the same.

Use the UPDATE keyword and the id (primary key) of the record to change it’s status accordingly. The only difference between these status changes is we most likely want to record a completion time for the complete/cancel status changes. When testing the function with our first task (id = 1).

Tulip Application Structure

The Tulip application is effectively two pages: the main view of the task list and the form to create a request. The app starts with a step open trigger that performs a few steps:

1. Runs the Display Open Table connector function and saves to a variable TaskList, which contains a list of varying tasks
2. Stores the static value 0 into a variable called Index, which will keep track of where we are when navigating through open orders
3. Stores the length of TaskList into a variable called Length
4. Gets the first task from the TaskList and stores into a CurrentTask variable

These triggers are replicated in the refresh button.

The TaskList is then shown in the top portion as a variable, which allows you to pick and choose the columns shown and order they’re shown in. The individual variables from CurrentTask are broken down in the bottom section to show which task the action taken will record against.

The arrow icons navigate the TaskList, repopulating the CurrentTask with the next or previous option. This has a small amount of logic to make sure it loops appropriately (i.e. pressing the next button when on the last element brings you back to the first element).

The Create New Task button just navigates to the next step of the application, which has two form variables: the task itself and the user the task will be assigned to. Upon pressing the Submit button on this page, a trigger that runs the Create New Task connector function executes and the app completes and returns to the main page. The current user of the application is automatically filled in as the creator.

Lastly, the status buttons at the bottom all perform a similar function, where they run their associated connector functions using the id field of the CurrentTask and complete the app.

Results

From the recorded video above, we can see now the database keeps a record of the work orders, which can provide value from a historical perspective.

There’s also several ways for us to begin creating analytics around these applications. For example, when the app is complete, we could first perform a SQL calculation on the duration between completion and request times. This would allow us to trend average times tasks stay open, as well as keeping track of the number of tasks that are completed on a daily basis. I’d be happy to expand upon this further upon request.

This ended up being much longer than I originally anticipated, but hopefully it was helpful to provide an idea on how you could go about building your own application following this structure. The beautiful thing about this app concept is you could have many people interacting with this single application. A variation of this application is currently being used by ~15 people for material replenishment purposes, with plans to scale upwards of 80 people working from this same infrastructure.

If you’re interested in implementing an application of this nature, please reach out and I’d be happy to provide guidance. Let me know of any feedback or if there are any requests for a style of application you may like to see featured for the next post!

Best,
Grant

grant.levy@tulip.co

[Dave]

Hey Grant,

I have a bit of an SQL question,
-Which hopefully is not too much of a lift .

I have a current SQL that returns the current date.

However, I am looking enhance this query by also returning two additional dates:

1. First date of the week (Monday)
2. and the last date of the week (Friday)

I am wondering if you have a sense of how to navigate this puzzle?
-Or perhaps you have created a similar query to this before?

Any insight or support is appreciated!
-Thank you so much.

[Grant]

Hey @Dave, I think you could actually do this without a SQL connector now if you wanted to! Take a look below at some triggers that take the current date and convert them using the DATE_TRUNC_TZ expression and the ability to add to datetime stamps.

This takes the current date-time, stores it to a variable, which we then use the DATE_TRUNC_TZ function on to return the “week” timestamp. This ultimately gives us Sunday at midnight in the timezone you specify. From there, we can add one day’s worth of time (in seconds) to get Monday at midnight or 4 additional days of time to get Friday at midnight.

I could move this example app to your instance if you want to send me the destination location?

[Dave]

Awesome!
-Thanks for turning that around so quickly Grant.

I think I’ll rebuild this in my instance just to nail the concept/functionality down.
-(I appreciate this solution over utilizing the SQL connector)

Very much appreciated!

[gtewksbury]

Is it possible to get the return value from a DB function call into a Tulip variable?

If I call a built in DB function called woclockin($wo_num$) and the return value from that DB function is an integer that contains the work order ID from the DB table, or an error code if it fails. how can I capture that? Right now I can see the function call works (i.e. the DB table is getting updated) but the return is always NULL (using a postgres DB)

[Grant]

Hi @gtewksbury!

You absolutely can! So your SQL function will have some return that you’ll need to map to a corresponding output to the connector function. If you look at the example Dave shared above, he returns a SQL statement using an “AS” statement and defines it as current_date, which is mapped to the outputs in the bottom left.

Once you have your outputs defined and returning in the function, you can then save and call this function from a Tulip app, which will store the output of the function in accessible variables you can build logic around.

Does that make sense?

[gtewksbury]

Sweet !! Thank you
Was that in some documentation that I missed?

[Grant]

We do have an article around writing SQL connector functions that mention configuring the inputs and outputs in step 3. Glad to see it working though!!