Build a Product Review Tool For Telegram Bot with Appsmith
29
June
2022
Tutorial

Build a Product Review Tool For Telegram Bot with Appsmith

Build a Product Review Tool For Telegram Bot with Appsmith
Paul Asalu
0
 minutes ↗
#
tutorial
Tutorial

Telegram-Business" is a Telegram bot that connects customers to small-scale businesses. Users can either sign up to use the bot as business owners and create a catalog for their products or use it as customers looking to purchase something from businesses on the bot. It works a lot like the popular 'Whatsapp Business' platform.

In this article, I will teach you how to use Appsmith to build a product review tool for a Telegram bot. Think of this as a proof of concept of how Appsmith can help small businesses by automating some aspects of their businesses. More specifically, with this app, users can add reviews on products they've used to help others make wise decisions when purchasing, and business owners can take the feedback given to make their products more consumer-friendly.

If you want to see how I built the Telegram bot you can check this article I wrote about it here.

Prerequisites

To follow along with the article steps, you should have the following setup:

  • Appsmith Account: You can create an account with Appsmith for free via this link.
  • Also, be sure to read the tutorial on how to create a telegram bot via this link.

Overview of the Tool

The process starts by creating a new application on Appsmith, and designing the interface for the product review tool using the available widgets in the design area, as shown below:

review-tool-interface

This utilizes the rating widget for the star rating, the text widget to display the product description, image widget to show an image of the product, and input widget for the comment that the user may want to leave on the product as part of their review, and of course, a button widget to submit the review.


We also want users to see comments made by other people on the product, so we'll design a second portion of the interface right beneath this one, as shown below:

review-tool-interface

This portion consists of a text widget which describes the section "what others are saying", followed by the list widget, which has been emptied and contains only a text widget to display the comments by other users. It's empty at the moment so it seems bare, however, once the data kicks in it'll come to life. You can choose to add an outline to the input field to make it more visible as shown below:

review-tool-interface


Adding Datasources

We'll start by creating an API to fetch data to display on the list widget from the bot's database; which is hosted by Fauna which is a serverless database provider, that also provides us with a graphql API through which we can send queries directly to the database.

We will be querying the database via this GraphQL API from our Appsmith application. Head over to the "Queries/JS" section and choose to create a new "Blank API", and add the following details to this new API:

Once you're done your API should look like this:

{{    JSON.stringify(        {            variables: null,            query: `                query {                    findProductByID(id: "${appsmith.URL.queryParams.product_id}")                    {                        name                        image                        description                    }                }            `        }    )}}
getProduct-api

You will observe that in the body of this API we make use of the "findProductByID" graphql query made available to us via FaunaDB.

Inside of this query we must specify a product id, namely the product id of the product that is to be reviewed, that way we can fetch the information about the product including the product image and description, which we can then display on our interface via the image widget and text widget.

id: "${appsmith.URL.queryParams.product_id}"

That is what this line does in the query, we fetch the product id from the Context object that Appsmith provides us with.

The Context object contains a number of properties of any Appsmith application that help us store and reference information about our application, for example, the famous "store" object is a part of the context object and we can use it to store information in our application's local store. Here's a list of items available in the Appsmith context object:

appsmith-context-object

You can find out more about the context object here.


Meanwhile, the item of interest for us here is the "URL" object from the context object, which holds information about our application's URL, including the hostname, and query parameters amongst other things, as shown below:

appsmith-url-object

This is important to us because we will be passing in information about the product to be reviewed and the user issuing a review as query parameters to our application url, namely the "product id" and the "user id" from the bot. This will make more sense when we integrate the application with the telegram bot.

getReviews API

Next, let's add another API that will fetch all the reviews on the given product. So head to the "Queries/JS" section again and choose to create a new API this time with the following details:

{{    JSON.stringify(        {            variables: null,            query: `                query {                    findReviewByProductID(product_id: "${appsmith.URL.queryParams.product_id}")                    {                        data {comment}                    }                }            `        }    )}}

Here we send a query to the bot's database to fetch all reviews from the "Reviews" table that are associated with the given product's id, which is also gotten from the URL query parameter as with the previous API.

send_review API

This API is what will handle the creation of new reviews which plays a vital role in our application since that's the main purpose of the application. This API unlike the rest utilizes a graphql mutation (also provided by the FaunaDB graphql API for the bot's database). Below are the details of this API:


{{    JSON.stringify(        {            variables: null,            query: `                mutation {                    createReview (data: {                        chat_id: "${appsmith.URL.queryParams.chat_id}"                        stars: "${Number(Rating1.value)}"                        comment: "${Input1.text}"                        product_id: "${appsmith.URL.queryParams.product_id}"                    })                    {                        _id                    }                }            `        }    )}}

As you can see in this mutation query, we reference the data from some of the widgets, including the rating widget, for the value of the ratings that are given. And the "input" widget, which holds the comment given by the user on the product. Also, observe that we fetch the user's telegram id (chat_id) from the URL to associate the new review with that particular user.

Connect Datasources To Widgets

Next, we must connect the APIs we've created to the interface via some of the widgets. First, off we want the user to be able to send in a review to the database once they click on the "submit review" button, so to do that, we will open up the properties of the button by clicking on the settings icon next to it, and choose an OnClick action for the button.

Head over to the submit button widget and bring up the settings pane, and under the Actions section, choose the option to Execute a query and then select thesend_review` API from the list of options shown, as shown below:

submit-button-settings

Not only do we specify an "onClick" action, but we also specify an onSuccess action, which simply brings up a modal that displays a success message, like the one shown below:

success-message-modal

This is a simple modal with just two Text widgets, so you can create one like that and link it to the onSuccess action as I have done.

Next, we must feed data to the list under the "What others are saying" section; click on the widget to bring up the settings for the list. Under the " items" option, add the following to load the data from our getReviews API:

{{getReviews.data.data.findReviewByProductID.data}}

This will load data from the query onto the list widget as a list of JSON objects. However, we must still specify what data goes where for each list item. In our case, we must specify what data from the "current object" goes to each list item's Text widget. Recall that each list item has a "text" widget in it, so for that text widget, we will specify its "Text" property to be the following:

{{currentItem.comment}}

Lastly, we must add data to the Image widget and the Text widget that holds the product's description. For the image, we'll set the "image" property to the following:

{{getProduct.data.data.findProductByID.image}}

Here, I am calling the "getProduct" API, which we created earlier on, and fetching the data from the response of the API call. For the description text widget, we'll set the "text" property to something similar:

{{getProduct.data.data.findProductByID.description}}

Now that we are done with this, we can deploy the application and fetch the URL. So I'll click on the Deploy button to deploy the application, wait for it to load up completely, then copy the URL from the browser.

deployed-application-interface

As observed, the application currently has no data in it because necessary arguments for the APIs getProduct and getReviews are not yet present, namely:

  • user_id
  • product_id

If you recall, these two parameters were taken from Appsmith's URL context object, which holds information about the current URL of our application - including any query parameters that may be present.

This is important because I will be passing these two arguments as query parameters to our Appsmith application.


Integrate With The Telegram Bot

This brings me to my next stop, which is to implement some logic from the bot to help users who want to leave reviews access my application.

For this, I will send a message containing the link to our Appsmith application, along with the query parameters, which would contain the "ID" of the product they want to review and their user id on telegram (chat_id) to whoever is interacting with the bot.


Note: The code snippets shown below do not show the full implementation of my telegram bot; if you want to see how I built it, kindly refer to this article.

Typically, when users come to the bot, they get multiple options when viewing products in a catalog, as shown below:

telegram-bot-options

As observed I have added a last option which allows them to Add Product Review, in order to handle that I wrote a handler function that will send back a message containing the Appsmith URL with the product id and their chat_id in the URL as query parameters. Observe:


appsmith_url = "https://app.appsmith.com/app/review-     product/review-6179715eb2db5a27f21af7e5"    product_id = data.split(';')[1]    # generate link to appsmith UI    bot.send_message(       chat_id=chat_id,       text="Kindly click on the following link to head over to        the review page "       f"{appsmith_url}?chat_id={chat_id}&product_id={product_id}"     )

The appsmith_url variable holds the URL for our deployed Appsmith application, and contains no parameters, so opening that would just display a "bare" version of the interface we designed. However, notice how i append the ?chat_id={chat_id} option along with the product_id={product_id} option to the URL before sending it.

That way the user gets a response that's customized to include their personal id and the id of the product they wish to review. As such, when they click on the URL our Appsmith APIs will be able to fetch the data they need from the context object and react accordingly. Here's what a typical response from the bot looks like:

reponse-from-bot

Now when they click the link, it'll take them to the Appsmith tool we created but this time with data loaded in, as shown below:

product-review-interface-alive

This way, my users can also leave comments on the products, and see reviews on the product, further increasing the trust that they have in the platform and in small-scale business ventures.


I hope you enjoyed reading that!, this article has shown you how to use the Appsmith context tool as well as how to send GraphQL queries on Appsmith, via the API editor, and ultimately how to build a simple product review tool for any use case you might have.

I invite you to use what you've learned from this article to build more interesting versions of what I have built or even different tools if you wish, rest assured everything you need to fast-track your development is available to you on Appsmith.com. If you wish to see the code base for my bot you can find it on GitHub here.

How To Display and Manage Documents on Appsmith
13
June
2022
Tutorial

How To Display and Manage Documents on Appsmith

How To Display and Manage Documents on Appsmith
Uma Chukwudinma Victor
0
 minutes ↗
#
tutorial
#
beginners
Tutorial

Today, modern websites that use document-based data have provisions to view, manage and download these documents. Such documents can range from simple files showing budgets, tutorials, or even notes. With these features, application users can upload and manage files. While such features are nice to have, building an ideal solution to handle them are not feasible and can be time-consuming. Often developers find it hard to code PDF-viewers on their web apps because of dependency issues (libraries and frameworks), and when working with frontend frameworks — React, Vue.JS, etc. — configuration can get complicated. 

When building apps on Appsmith, the in-built document widget takes care of some important functionalities related to managing documents. This widget supports multiple file extensions, such as .txt, .pdf, .docx and .xlsx among others. Documents can also be populated via APIs and URLs. 

In this tutorial, we will learn what the document viewer widget is, and its applications. At the end, you will be able to fetch and display documents in Appsmith, and also learn to configure different properties that come with the widget.

Set-up the  Document Viewer Widget

The document viewer widget on Appsmith can be used to display a wide range of documents. This document widget has support for numerous file extensions of documents. Documents to be displayed can be of type: .pdf, .docx, .xlsx, .pptx, .ppt and .txt.

To get started using this widget:

  • You will first need to have an Appsmith account. You can log in if you have a pre-existing Appsmith account, otherwise, you can create a new Appsmith account.
  • Once logged in, you are navigated to a dashboard. Here, create a new application. On the left sidebar, you will see a number of widgets offering different functionalities.
  • Find the document viewer widget and drag it into the workspace.

Adding the widget will give a result similar to the image below:

Within the widget, you will notice there is a document being displayed. This document is being generated with the link in the Document Link property of the widget. 

To display documents, you can either add links/URLs or connect a data source that can be connected to display documents. The document widget provides the option to download or print the displayed document.

Alternatively, a data source can be used to provide the documents. 

The data source could be a database or an API pointing to the file you want to display. 

The Connect Data button will take you to a page to add a data source.

Here you will see options for APIs and different databases. 

In this tutorial, we will use the MongoDB database to supply our documents. 

First, you will need to create a database on MongoDB (I will show you how to do this in the next section) and then link it to our application. 

Select MongoDB from the datasource link

Here, I’ve renamed my data source to “Book Datasource.” Take note of the IP addresses; you will need to allowlist this when creating your MongoDB cluster to allow external access via Appsmith.

Creating a MongoDB Database

To get started with MongoDB, navigate your browser to MongoDB and click on Try free to create a user account.

After logging in, select your database plan and click on Create Cluster.

Next, set up security credentials to authorize access to the database.

Finally, allowlist the domains from Appsmith, click on the Finish and Close button, and click 'Go to Database' on the popup dialogue that appears.

On the new page that opens up, you will have your database and a cluster created for you. 

On the cluster, click on the Browse Collections button.

You will get a page to manage the data stored in your database.

Select Add My Own Data to define our own database’s content. On the new page, select Insert document to add new documents to the database.

Our 'Collections' will contain two fields, namely: Name and Url, along with their corresponding values. Above I have a URL for a Javascript tutorial document. Click on the Insert button when done to add the new document.

Below, I have three documents in my database collection.

On the side pane, click on Database to navigate to the cluster. Click on the Connect button and select Connect with Mongodb Shell

Copy the selected area in the image below

Add the Newly-Created Mongodb as a Datasource

Now that we are done setting up our database, we need to add it as a datasource to Appsmith. For this, you will need to navigate back to Appsmith and fill in the required fields.

Select Replica set for the connection type, paste the copied connection string into the host address field, and enter your database name. Under authentication, enter the username and password you defined when creating the cluster.

Click on test to check the validity of the datasource, then finally, click on Save. Click on the new query to create a query for the datasource.

Enter the name of the collection in the field provided and click on the Run button. You will get a response showing the database content. Click on Add new Widget and select Table on the left window pane. This will create a table widget with the database as its data source.

You can link this new widget to your document viewer widget. 

To do this, open the document viewer widget option pane. Within the Document Link field, add the following moustache syntax

{{Table1.selectedRow.Url}}.

This will set the document to be displayed in the document viewer widget, to be the URL of any row you select from the table.

The document viewer widget has 3 properties: the Document Field, visible and Animate Loading properties. The visible field determines if the widget will be visible upon production while the latter controls the loading of the widget.

Approving and Rejecting Documents

In the Filepicker widget, criteria can be set, which the document to be uploaded has to satisfy, else it would be rejected. Select and drag the file picker widget onto the workspace to add it.

Above is the file picker option pane. The label property is the text to be displayed on the widget. With this, you can also set the number of files to be uploaded, max size of the files, and allowed file types. Documents not meeting the predefined criteria will be rejected. Documents uploaded with the file picker widget can also be linked and displayed using the document viewer widget.

Click on the widget to upload a file.

You can either drag and drop files unto the widget to upload them, or you can browse your system directories for the files to be uploaded.

Here is a file I’ve added using the widget. Note that the file picker widget stores uploaded documents in the form of an array. To view your uploaded document, add the following mustache syntax to the document viewer widget document link property: {{FilePicker1.files[0].data}}. Here, the document at position zero is the first document which I added.

Above is an image showing the document uploaded using the file picker widget.

Sending Documents as Mails

Documents can be sent to specific email addresses using the Appsmith SMTP email service. To do this, you will first need to do is to add SMTP integration. Navigate to datasources and select SMTP.

To be able to use this service, you will need to create an account with an SMTP provider. On the configuration page, add the host address, port and authentication credentials of your SMTP provider.

With SMTP setup, you can build a form to send the documents to an email address. To do this, add the form widget to your work environment.

Here is a form which will take the values of the name of the email and the file name of the document to be sent.

To send emails, create a new query using the configured SMTP service.

Here is the query screen. The from email property would take the email address you used to create your SMTP service, the To email(s) property can be bound to the input field of the form using the following moustache syntax: {{Input1.text}}. The subject would be: {{Input2.text}} and finally the attachment would be the file uploaded to the file picker widget. So use the moustache syntax: {{FilePicker1.files[0]}}.

Finally, you will need to bind this query to the submit button on the form to run it.

Here, I’ve added the query to the onClick event handler, and added a message to be displayed once the document has been sent. Clicking on the button will run the query and send the attached document to the email specified in the form.

We also covered using the file picker widget to upload files and send documents to specified email addresses. You can take a look at the tutorial here. 

Adding Social Authentication for Your Internal Applications Without Writing Any Code!
24
February
2022
Tutorial

Adding Social Authentication for Your Internal Applications Without Writing Any Code!

Adding Social Authentication for Your Internal Applications Without Writing Any Code!
Vihar Kurama
0
 minutes ↗
#
authentication
#
tutorial
#
developer-tools
Tutorial

Not every internal application is purely internal; sometimes, several organizations and teams need to have a way to automate their operations. For example, you might want to share your expense manager app with a financial advisor and other finance teams. Because there are multiple connections, having robust user authentication is crucial for such internal applications. But, adding an authentication layer to web applications is not straight forward task. The traditional approach is to have a form-based email-password authentication, then validating them with the backend using auth tokens and redirecting them onto the application. Sometimes users and developers prefer a faster way of using social authentication. This authentication allows users to log in to your app with their already set up credentials from their selected social sites.

Today, I’ll show you how you can add social authentication with Google to Appsmith applications without writing any piece of code. To do this, we will use Xano as the backend.

Before getting started, let me give you a quick introduction about Appsmith and Xano, in case you’re new here.

Appsmith is a low-code open-source framework to build internal applications, dashboards, crud apps, and many more. With Appsmith, it’s possible to create a fully functional and custom frontend in minutes. Connecting data sources with Appsmith takes a few minutes, and you can quickly build tools on top of the database of your choice.

Xano is one of the few no-code backend platforms that provide a comprehensive Database test environment, which allows you to easily switch between production and test data. Xano also provides API request history and easy ways to monitor CPU Usage and Database Record utilization.

Alright, now let’s get into it.

Getting Started: Creating an Account on Xano and Appsmith

The first step is to create accounts on Xano and Appsmith. In this tutorial, I’ll use the cloud versions on both Appsmith and Xano.

Note: Since Appsmith is open-source, you can always use it locally or self-host it on your own servers using Docker.

  • Navigate to appsmith.com and sign-up for a new account if you’re a new user or login to the existing one.
  • Navigate to xano.com and sign-up for a free account on the other tab. Ensure you select the Google authentication in the auth types during the onboarding process.

After the Xano app is created, navigate to the API page in the dashboard, we’ll see two API groups; one is Default, and the other is google-oauth.

CleanShot 2022-02-17 at 07.52.25@2x.png
  • Before we start to use them, let’s first get our OAuth credentials from Google, for this, you’ll need to open Google Console, and create your API credentials. Here’s what it would look like:
CleanShot 2022-02-17 at 07.51.26@2x.png

If you’re new to creating social auth keys on Google console, you can follow these steps from the documentation here.

  • Now you will need to navigate to the Settings tab on the Xano dashboard and hit manage on the Environment Variables. Under this, we’ll see google_oauth_client_id google_oauth_secret variables. Here, copy the credentials from the Google console.

Building the UI on Appsmith

Appsmith comes with a vast array of pre-built UI components: widgets. These can be fully customisable to help you build good-looking applications. For this application, I’ll keep the UI clean and simple, however, you can always add more to your applications based on your use-cases.

Now follow the below steps:

  • Create a new application on Appsmith, and choose “Build from scratch using drag and drop”.
  • Rename the Page name from Page1 to login by clicking on the existing page.
  • Click on the + icon next to widgets from the side bad and drag and drop a container widget onto the canvas.
  • Now, add a button widget and a text widget inside the container to create an authentication form.
  • Click on the text widget and set the Text property to Log in, similarly set the button label to Continue with Google.

Here’s a screenshot of the form that’s built on Appsmith:

CleanShot 2022-02-17 at 08.40.54@2x.png

Initializing the Authentication Flow

To initialize the authentication process, we’ll need to configure APIs on Appsmith from Xano, to do this follow the below steps:

  • Open the Xano dashboard, and navigate to the Settings tab; now click on the google-oauth API group.
  • We should see all the APIs that we can consume to finish our authentication, and yes, all these are automatically generated without writing any code, thanks to Xano.
  • Now, select the oauth/google/init API and copy the endpoint.
CleanShot 2022-02-17 at 09.47.21@2x.png
  • On Appsmith, click on the + icon next to Datasources and create a NEW API.
  • Rename the API endpoint to init, and paste the copied URL.
  • This endpoint expects an redirect_uri, this basically, redirects to another page if the authentication is successful. So, we’ll need to create a new page on Appsmith, and copy it’s URL to the redirect_uri param on the init API.
  • Now create a page on Appsmith, by clicking on the + icon next to the Pages on the sidebar. Rename the page to welcome.
  • Copy its URL, navigate back to login and paste the URL in the redirect_uri param, here’s how the API should look like:
CleanShot 2022-02-17 at 09.56.05@2x.png
  • We’ll need to add the Redirect URL onto the google console so as to redirect from Google Auth after the redirection is successful.
CleanShot 2022-02-17 at 10.06.08@2x.png

There’s one last thing, we will need to set the button onClick action to JS and copy the following:

{{init.run(() => navigateTo(init.data.authUrl, {},'SAME_WINDOW'), () => {})}}

This code snippet will navigate to the redirectURI that’s requested from the init API call, which is obviously to the welcome page.

Note: The auth endpoint from Xano we used in this section is responsible for sending the user off to a Google webpage to authenticate. Once complete, the user will be redirected to where this request was initiated and then depending on your requirements, the user will go down the login, signup, or continue path.

Next, we’ll need to configure the continue flow to get details from the user and manage if the user is a first time user or an existing user.

Configuring the continue Authentication Flow

To configure the continue workflow, navigate to the welcome page on Appsmith; this is where the user lands after the initialization is complete. First, let’s design the UI.

  • Drag and drop a container widget onto the canvas, just like we did it for the login page.
  • Add an image, text widget and a button widget for the look, here’s how I’ve designed it, you could add images and colours of your own choice:
CleanShot 2022-02-17 at 10.14.37@2x.png

Next, create a new API by clicking on the + icon next to the datasource section and follow the below steps:

  • Rename the URL to oauth_continue
  • Copy the /oauth/google/continue endpoint from Xano and paste it into the request URL.
  • This endpoint needs two parameters:
  • code: which will be queried from the URL queryParams. (accessed from the previous login page after the redirection from google is complete)
  • redirect_uri: Same as the Page URL

Now let’s test it out by clicking on the Login button from the login page, and we should successfully see the response on the continue API.

Here’s the GIF:

Awesome, we were now successfully able to finish the authentication process, now let’s bind the data onto the UI with JavaScript.

For this, set the text property on text widget to:

Welcome {{oauth_continue.data.name}}

This will show the username from the Google Account when the user is logged in.

Securing Pages on Appsmith with Xano Auth

To create a secure page on Appsmith with auth add a new page by clicking on the + icon next to the Pages. Next, drag and drop a TAB widget onto the canvas.

Now, open the property pane of the tab widget and rename the Tab names to logedin and notlogedin; under the Default Tab property, paste the following snippet:


{{appsmith.store?.user?.token? 'logedin':'notlogedin'}}

This snippet will show logedin tab when the user has logged in and the notloggedin tab when there is no user. Here's how it works:

This is how we can achieve social authentication on the internal application! To see more details about other social providers, follow the Xano documentation here.

If you’re interested in using a database that is not listed on our website as an integration, please let us know about it by raising a PR on Github and we will do our best to include it at the earliest.

Join our growing community on Discord, and follow us on Youtube and Twitter to stay up to date.

Building an Appsmith Dashboard with Cube
24
January
2022
Tutorial

Building an Appsmith Dashboard with Cube

Building an Appsmith Dashboard with Cube
Vyom Srivastava
0
 minutes ↗
#
community
#
tutorial
#
dashboard
#
javascript
Tutorial

Every project contains several moving parts. We developers call them features. They can be dashboards, performance overviews, a blog, or web pages that include forms. Features take time to develop from scratch. That can impact delivery and deadlines.

In this article, I want to create a metrics dashboard with Appsmith. I'll use API endpoints generated from Cube with a public dataset from the Museum of Modern Art (MoMA).

Here's what the end-product will look like:

dashboard-image

I want to showcase how it would be more efficient for your team to have a tool to create custom dashboards for internal metrics. A huge bonus point is that it can be used by anyone, including non-devs in your team. This is where low-code tools come into play.

Appsmith is an open-source low-code platform that lets you create web applications like dashboards and admin panels by using drag-and-drop widgets. With Appsmith, you can connect multiple data sources like MySQL, MongoDB, REST API, Google Sheets, and perform CRUD operations.

I'll be using a hosted Cube deployment on Cube Cloud to get data from the Museum of Modern Art (MoMA) dataset.

A Quick Look at Appsmith

You don't need to be a developer to create metrics dashboards using Appsmith. It comes with a set of drag-and-drop widgets for forms, charts, images, and more, that you can use in your application to enhance its functionalities.

A few of the benefits of Appsmith include:

  • The ability to create apps without development knowledge
  • Saving the developer team's time
  • Saving your company's money by saving man-hours
  • Increased productivity
  • Drag-and-drop widgets

However, keep in mind that despite all of its functionality, Appsmith can't compare to the customization level of a custom-built app.

A Quick Look at Cube

Cube is an open-source analytics API platform that lets you connect with data sources and then access the data through an API. You can connect to data sources like MySQL, AWS Athena, and Google BigQuery, among others. It's agnostic for visualization tools, meaning you can connect to any front-end charting framework and build your own customized UI.

The Cube API lets you sort, group, and filter through the dataset itself. It acts as a centralized back-end metrics layer for any dataset. The multi-staging query system allows Cube to handle trillions of data points.

Creating a Dashboard with Appsmith and Cube

You're going to display a public dataset from the MoMA on the dashboard you're creating on Appsmith. The MoMA is one of the largest and most influential museums in the world, with around 300,000 books and catalogs, and more than a thousand periodic files.

This is how your dashboard will look:

dashboard-image

You'll use db4free.net, a free database service, to upload and host the database. After uploading the data to the database, you'll connect to the database using Cube. You'll be able to integrate the API generated through Cube into the different widgets of Appsmith.

Prerequisites

  • Any public dataset (in this case we're using one from MoMA)
  • A Cube account
  • An Appsmith account
  • Any database service; this tutorial will be using db4free.net

1: Clone the Dataset and Host It on Your Database

Once you clone the dataset, use the PHPMyAdmin or the cPanel of the database hosting service and import the data via CSV.

2: Connect Cube to Your Database

Now log in to your Cube account and click Create deployment. Give the deployment a name; the images in this example will use Appsmith Dashboard.

setup-1

On the next screen, select MySQL from the data source list. If you're using some other data source like Google BigQuery or AWS Athena, select the option accordingly.

step-2

Now, just fill in your database credentials, and you're good to go!

step-3

Once you've generated the data schema of your dataset, you can create REST APIs.

3: Get API Credentials and API Endpoints from Cube

Once you have created a deployment, you'll be redirected to the overview page of the deployment.

overview-page

Copy the API endpoint and click How to connect your application to get the Authorization key.

And you're done creating Cube's API. It's time to move ahead with Appsmith to create your dashboard.

4: Set Up Appsmith

Log in to your Appsmith account, create a new application, and select Build with drag & drop.

create-app-1

5: Create the First Widget

Once you see an empty canvas, click the + button next to Datasources in the sidebar. Select Create new API.

create-app-2

You should see the following page:

create-app-3

For the URL, paste the REST API endpoint, and in the Authorization header, paste your API code. In the case of this tutorial, the REST API endpoint is https://fuchsia-shark.aws-eu-central-1.cubecloudapp.dev/cubejs-api/v1/load.

Pass the following JSON as a GET param:

{
  "dimensions": [
    "Artists.begindate",
    "Artists.displayname"
  ],
  "timeDimensions": [],
  "order": [
    [
      "Artists.begindate",
      "desc"
    ]
  ],
  "limit": 10
}

In this code block, you're asking the REST API to return the top ten begin dates and display name. The rows are in descending order.

Once you start to get the data, you'll see a list of widgets on the right side of the page. Select Table from this list:

A table will appear in the canvas.

6: Create the Second Widget

Repeat the previous step but with a different query. Just add another API data source with the following query:

{
    "dimensions": [     
        "Artists.nationality"   
    ],   
    "timeDimensions": [],   
    "order": {     
        "Artists.count": "desc"   
    },   
    "ungrouped": false,   
    "measures": [     
        "Artists.count"   
    ] 
}

This query will return the count of artists according to country.

Again, select Table from the widget list. Your canvas should look something like this:

create-app-6

7: Add Different Widgets as Per Your Requirements

In this example, I've experimented with different widgets and data customizations. I've added three more charts using this query.

Query 1

This query will return the count of all artists with their begin date.

{   
    "dimensions": [     
        "Artists.gender", 
        "Artists.begindate"   
    ], 
    "timeDimensions": [],   
    "order": {     
        "Artists.count": "desc"   
    },   
    "ungrouped": false,   
    "measures": [     
        "Artists.count"   
    ], 
    "filters": [
        {
            "member": "Artists.gender",
            "operator": "set"
        },
        {
            "member": "Artists.begindate",
            "operator": "notEquals",
            "values": [
                "0"
            ]
        }
    ]
}

Query 2

This query will return the gender breakdown of the complete dataset. I have used the data coming from this API in the pie chart.

{
  "dimensions": [
    "Artists.gender"
  ],
  "timeDimensions": [],
  "order": {
    "Artists.count": "desc"
  },
  "measures": [
    "Artists.count"
  ],
  "filters": []
}

Query 3

This query will return the count of artists from each country listed in the dataset.

{
  "dimensions": [
    "Artists.nationality"
  ],
  "timeDimensions": [],
  "order": {
    "Artists.count": "desc"
  },
  "limit": 10,
  "measures": [
    "Artists.count"
  ],
  "filters": []
}

You can use this query with any widget to represent the data efficiently.

Create Your Own Internal Apps with Appsmith

In this article, you learned how to create a metrics dashboard with Appsmith and Cube to display data from the public dataset from the Museum of Modern Art (MoMA). You now know how to use low-code tools to save time and money on development.

Check out the live example here and try adding some more widgets to your metrics dashboard with Appsmith.

I think Cube and Appsmith are a great match. With Cube, you focus on creating business value without wasting time and effort on maintaining infrastructure.

Appsmith lets you focus on creating business value with metrics dashboards with minimal effort to both create and maintain.

You can register for Cube Cloud right away, and check out Appsmith as well!

I'd love to hear your feedback about building metrics dashboards with Cube Cloud in the Cube Community Slack. Click here to join!

Until next time, stay curious, and have fun coding. Also, feel free to leave Cube a ⭐ on GitHub if you liked this article. ✌️

Build an Equipment Checkout App for the Admin Team at Your Company
17
December
2021
Tutorial

Build an Equipment Checkout App for the Admin Team at Your Company

Build an Equipment Checkout App for the Admin Team at Your Company
Meera Datey
0
 minutes ↗
#
tutorial
Tutorial

On average, a mid-sized company uses 75 apps to run its businesses! As teams grow and scale, internal apps, primarily administrative and departmental tools, help in sustaining growth. When manual processes get automated, it leaves more time for organizations to focus on their core growth-related work. Low code tools are an excellent way for businesses to solve their dilemma in allocating engineering resources. Low code platforms can help developers build custom internal applications at a fraction of the time traditional development takes. In this blog, we will build a web application using two low-code tools: Appsmith and Supabase. 

 Appsmith lets us drag-and-drop UI components into our application to develop the applications visually. It helps us connect the datasource to the UI components within a browser window. This speeds up the application building process. Supabase is a “Postgres database on the Cloud.” With Supabase, we only need to define table structure and SQL functions. Supabase takes care of generating REST APIs from the database schema. 

 As part of this tutorial, we will build an equipment management application. Creating such an application from scratch is laborious and time-consuming. We will use Appsmith and Supabase to rapidly iterate design and development and get feedback from our users early on. Both tools will significantly cut down development time, allowing us to deliver this solution in far less time.

 Let us first understand the application and its uses in more detail. 

The Use-case

 Consider a video production company. This company has an inventory containing video cameras, lenses, and tripods. Employees borrow the equipment for photoshoots and then bring it back after the photo shoot is completed. The company needs to keep track of the equipment. They also need to make sure that people are accountable for the equipment they take. The company would need to know which employee has the equipment and when it will get returned at any given time.

 Although we are using a video production company as an example in this blog, this scenario applies to other industries, for example, sports clubs that lend equipment to players and software companies that provide computers to their employees for remote work. An inventory system is needed for any organization where employees borrow the equipment to take it to a job site or work remotely. 

 We can implement this by using a simple spreadsheet or in paper form. But, spreadsheets or booklets are unmanageable once the equipment list or the number of employees grows. An equipment management system becomes a necessity.

 In this blog, we will learn the following:

  • Use Appsmith to build web pages and use Supabase as a data store.
  • Connect Supabase with Appsmith using REST APIs. This approach eliminates the need to use master database credentials in Appsmith.
  • Use Appsmith’s UI widgets to display data.
  • Build Forms in Appsmith.

You can see the finished application here.

We will switch back and forth between Supabase and Appsmith to complete this project. Keep Supabase and Appsmith open on different browser tabs to follow this tutorial. We have a lot to cover, so let’s get started! 

Appsmith and Supabase: Setting Up a Project

Get Started on Appsmith
  • Create a new account on Appsmith (it’s free!), if you are an existing user, log in to your Appsmith account.
  • In Appsmith, create a new application. You will see a new Appsmith app with an empty canvas and a sidebar with two directories: Datasources, Widgets, and JS Objects [This is a new feature, read more about it in this blog].
  • Visit Supabase.io and create an account in another browser tab if you don’t already have one.
  • Start a new project in Supabase and create a new organization name, project name, database password, and the region near your user’s location.

 

Define Database Table

The first step is to use Supabase to create database tables. Here are the steps needed to create tables.

  • Use the left-hand sidebar to visit Table Editor and create a table by clicking + New table. 
  • Create a Table ‘equipment’ and add varchar field’ name’ and varchar field ‘image’.
  • You can populate the database with sample data. Use ‘Import Data from SpreadSheet’ and this CSV file to follow this tutorial.

Create another table and name it the ‘employee’ table. This table contains three fields - id, name. 

Add another table and name it the ‘checkout’ table. This table stores checkout transactions; it contains which employee checked out which equipment.

 This table will have the following fields: 

- id, 

- created_at, 

- equipment_id, 

- employee_id, 

- due_by, 

- returned_on and notes. Fields’ equipment_id’ and ‘employee_id’ are foreign keys into table ‘equipment’ and ‘employee’, respectively.

 

We will be using this dataset

 

Connect Appsmith and Supabase

  • Go to the Appsmith window. [ See accompanied gif]
  • Click on Datasource, create a new datasource and find a new “Curl Import”.
  • Goto Supabase dashboard left sidebar, open menu ‘API’ and click on Table ‘Equipment’ and find corresponding Bash API and select a key as ‘anon key’.  
  • Copy this text into your clipboard.
  • And Paste the text into the Appsmith Curl text box.
  • Click ‘Import’.
  • Let us rename this API as ‘get_equipment’.
  • Save this as a DataSource - name it as ‘StoreRoom_DB’. From now on, we only need to modify the relative URL path (the part with /rest/v1/table_name?query_param) to read other tables, views and invoke functions. Now that is cool! 😎
  • Run the API get_equipment and see that we get a successful result.

 

Note: You can also use SQL from Supabase and write them as queries.

Appsmith: Display Table Containing Equipment
  • If you successfully followed the previous step, you will see that the API has successfully returned data. Now, we will display the equipment data in a table.
  • On the right-hand side menu under Pages, you will find the ‘Widgets’ option. 
  • Select a Table Widget and drag it to canvas.
  • Open its property pane by clicking on the cog icon on the top-right next to its name. Now to bind the data, use the moustache syntax {{ }} and enter the query name: {{ get_equipment.data }}. This will populate all the data from the query onto the table. The table widget has built-in search, pagination, and filters - without us coding a single line!
  • Column ‘image’ field is displayed as a text URL instead of an image. Update the column type for the image field to ‘image’. Now, you should see the image.
  • Now, let us add a Button for Checkout. Adding a column to the table widget.
  • Change the Column Type to ‘Button’ and change the label to ‘Checkout’.
  • We will code the checkout logic bit later.
Appsmith: Read Employee Table 

Follow these steps to read the employee table:

  • Duplicate the get_equipment API - and rename it to ‘get_employee’.
  • Change the relative endpoint to /rest/v1/employee?select=*
  • Run to make sure you can read all employees.

  

Appsmith: Build Equipment Checkout Form

We can now build the Checkout form. 

When the user clicks on the checkout button, we will open a modal containing a form. The form will allow users to select an employee and the date when equipment will be returned. 

Here is how we can do it!

  • Drag a Modal widget into the canvas.
  • Add a DatePicker to pick the date to return the equipment.

We want to set the default return date to a week from today’s date. We can calculate the default return date by adding 7 to today’s date. We can use external libraries such as moment.js inside our Appsmith application, which we will use in our application.

Replace default date with 

{{moment().add(7, 'd')}}

You can read about using external libraries in the Appsmith application here. Now, we need to Select an Employee who will check out the Equipment. For selecting an employee - drag a ‘select' Widget. 

Inside the options, we like to populate the list with employee names. We can derive the full name of an employee by concatenating last_name and first_name. 

{{get_employee.data.map(

(emp)=>{return {'label':emp.name, "value": emp.id }})}}

Appsmith Code Checkout Form Submit Button 

When user checks out, duplicate the get_equipment API - and rename it to ‘post_checkout’.

  • Change the relative endpoint to /rest/v1/checkout
  • And this code inside the body

{

"employee_id": {{SelectEmployee.selectedOptionValue}},

"equipment_id": {{TableEquipment.selectedRow.id}},

"due_by": {{DatePickerDueBy.selectedDate}}

}

This code essentially says: Construct a JSON with employee_id = Employee Id of the selected drop-down box, equipment_id = Current Row of Equipment Table, and Due_by = Value from the date picker. 

  • Connect this API when the user presses the ‘Confirm’ button.
  • Run to make sure you can successfully read the checkouts.

View Outstanding Equipment Checkouts

We must provide a way to view a list of outstanding equipment checkouts. Let us create a new page.  

First, let us build an API to read the checkout table. The process is similar to reading equipment or employee tables. 

The only twist is that our checkout table is normalized. That means we have chosen to store ids for equipment and employees' tables instead of duplicating the record. In our UI, we don’t want to show ids.

For people familiar with REST APIs, that means multiple REST API calls to retrieve complete information. To avoid multiple REST API, we would need to use GraphQL. However, we don’t have to use GraphQL.There are simpler alternatives available. 

Supabase’s REST API is based on PostgREST. PostgREST can detect foreign relationships and automatically returns embedded data. All we need to do is include the names of tables with fields. In our example, we like to retrieve all fields from the checkout table with corresponding records from employee and equipment tables.

Follow these steps to read the checkout data table:

  • Duplicate the get_equipment API - and rename it to ‘get_checkout’.
  • Change the relative endpoint to

/rest/v1/checkout?select=*,employee(*),equipment(*)

  • Run to make sure you can successfully read the checkouts. It would be best if you got nested JSON.

This will return values in JSON 

[

  {

   "employee": {

      "id": 1,

      "first_name": "Smita",

      "last_name": "Mehra",

        },

    "equipment": {

      "id": 1,

      "name": "Dell Laptop",

      "image": "url..."

    }

  }

]

  • Drag a Table widget onto the canvas.
  • Use ‘get_checkout’ as a data source for the table widget.
  • Because employee and equipment are nested fields, we need to adjust column values for ‘employee’ and ‘equipment’
  • Update the computed value for employee to {{_.get(currentRow.employee, 'name')}}
  • Update the computed value for equipment to {{_.get(currentRow.equipment, 'name')}}  

[See screenshot]

And you are done! We hope this was a great starting point for you to build the application. You can expand it further to include more features and make it as complex as you’d like. 

If you’re feeling stuck, feel free to reach out to me! Email me at meera.bavadekar@gmail.com. This article was written as part of the Appsmith Writers Program. If you wish to participate, send an email to writersprogram@appsmith.com expressing your interest. 


Meera Datey is a freelance web developer based in the San Francisco Bay Area. She specializes in helping small businesses design software solutions that deliver immediate value. This article was sent in as part of the Appsmith Writers Program.