Fast Data Search, Slice and Dice

Oftentimes, you want to segment and drill down into parts of your data stores. Followed by searching and filtering your data by a certain property, you needed to perform actions on it, such as sending selected data to an API or downloading it as a CSV.

In this tutorial, we will build an admin panel that allows you to quickly filter a database by a column and download selected rows, all from the UI. Our end application will look like the following:

👍

Enable search on scattered data sources

Combine this tutorial with the Sql on Any Datasource tutorial to quickly build a search, slice and dice dashboard on scattered data sources.

Start by creating a new application in Datasiv. Then, drag a table into the builder.

Make sure to set the Select Row property to multi.

Next, click Add Data Query and choose Sql Api. Datasiv allows you to call and display data queries to a variety of sources from your application.

👍

DataSiv provides integrations and read/write capabilities across multiple sources

Although we use a SQL in this tutorial, you can swap these out with other supported integrations, such as a Rest Api Endpoint, GraphQL, Salesforce, Stripe, etc.

We will be using our dummy database for this task.
Set the Resource to Chinook DB (builitin) (readonly), and for the SQL Query to Select * from Customers.

Save the query as sql0.

Each customer comes from a state. We want to be able to filter and view customers only in a specific state. To do so, let's create create another database query with the same database, and set the SQL Query to Select Distinct State from Customers.

Save the query as sql1.

Next, go back to the main editor, and click Add transform. We will process the data from sql1 so we can input it into a Select component. Select Transform for your transform.

📘

Transforms allow you to preprocess and postprocess your data

Transforms, such as the one we're about to create, allow you an easy way to mix and merge data sources together, filter them, or write code to get it into the right format before putting it into another frontend component. You can write raw javascript, client-side SQL, or other transforms to get started quickly.

For the data source, choose queries.sql1.data. This will run the transform on the data property of the sql1 query.

For the transform, write (element, index) => {return {key: element.State, value: element.State}}. Save the transform as transform0.

Add a Select component to the frontend.

In the right hand menu, click Edit Data, and select data.transform0.result.

Congratulations! Now the select in the frontend allows you to choose from States in the database.

📘

How to reference DataSiv components (such as selected items from a select view)

All component properties are stored in a global key value store that other components in the app are able to access. You are able to reference other components using javascript dictionary terminology.

In this instance, we would like to reference the key value under the selectedItem* property from the select0 Select component. All data components are stored under the global data** key, hence, we type in data.select0.selectedItem.key.

Finally, we want to only display customer data in the table that comes from the selected state. Modify the SQL Query for data query query0 to be Select * from Customers WHERE State = {{data.select0.selectedItem.key}}

Now the customer data in the table only includes customers from the selected state.

Finally, we'll add a download button so that you can download selected rows.
Select several rows from the table.

Add a Download Button to the frontend.

In the righthand sidebar menu, click Edit Data, and choose data.table0.selectedRow as your data source.

Congratulations! You can now filter customer data by a database column (state), select and download rows, all without writing SQL queries.