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:

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:

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:

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:

• Name: getProduct
• URL: graphql.fauna.com/graphql
• method: POST
• Headers:
• Authorization: Bearer
• body‍
  

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                    }                }            `        }    )}}

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:

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:

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:

• Name: getReviews
• URL: graphql.fauna.com/graphql
• method: POST
• Headers:
• Authorization: Bearer
• body

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

• Name: send_review
• URL: graphql.fauna.com/graphql
• method: POST
• Headers:
• Authorization: Bearer
• body

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

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:

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.

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:

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:

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:

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.

‍

‍TL;DR: We just raised $41m in our Series B, led by new investor Insight Partners, alongside Canaan Partners, Accel, and angel investors.

Frontend and backend development for simple applications have become incredibly complicated. It feels like we are building the same things repeatedly but just using different programming languages. The world has about 20 million software developers; most have spent hours building admin panels, forms-based workflows, dashboards, or some version of a CRUD app. That's billions of hours spent building internal apps and doing a lot of repetitive and mundane work. 

My co-founders, Arpit and Nikhil, had spent many years building CRUD apps and were tired of dealing with the same problem at every company. There had to be a better way to build these apps. We decided to release an MVP to a small community that embraced Appsmith with wide-open arms. 

We started Appsmith with a simple goal: To help engineers build the best apps they can to help their teams while avoiding repetition and monotony.

‍

I am happy to announce that we've raised our Series B to deepen our commitment to that goal. 

First, the lowdown

The lead investor for Appsmith's Series B is Insight Partners, joined by existing investors Canaan Partners, Accel India, and angel investors like Abhinav Asthana (Co-founder, Postman) and Jeff Hammerbacher (Co-founder, Cloudera). Our goal was to bring investors that understood the technology landscape well and were impressed with Insight's track record of backing companies like Docker, Postman, and JFrog, many of which are also open source. As we spent more time with the Insight team, we realized they would be the right partners for us as we scale to the next level. 

And of course, a big mention to our users and community. We're only here because you decided to take your valuable time in using, giving feedback, and contributing to the core product.

‍What have we done so far? 

‍

Today, Appsmith is a robust open-source, low-code platform for building, shipping, and maintaining internal tools. Users can connect to any datasource, create UI with pre-built widgets, and deploy business-critical applications. 

‍

You can see it in action: 

‍

Our focus last year was on improving the quality and complexity of the frontend that can be built using Appsmith, a couple of examples of which is our latest theming feature or our in-built JS editor is an excellent way to code.  These are a few among the 150+ features and enhancements we shipped in 2021. 

Where do we go from here?

Over 100,000s developers have used Appsmith for personal and professional projects so far. We see over 7,000 active teams daily relying on apps built quickly on Appsmith. We developed a 3000-member strong Discord community and helped our users build over 100,000 apps. From providing rich support to writing detailed tutorials to contribute valuable integrations, our community and team are the heart and soul of Appsmith. We have loudly and proudly built Appsmith in public and honestly believe transparency and community involvement help us in the short and long term. 

Now, it's time we make it happen for dynamic apps starting with internal apps as our focus.

With this round of funding, Appsmith will build on the progress from 2021. 

Here's a five-point plan for the coming year: 

• Add more UI widgets and Data Integrations
• Simplify the coding and debugging experience
• Investing more in nurturing the community
• Build out a robust set of offerings for advanced or mid and large-sized teams 
• Improve performance of Appsmith apps

‍

Final words

As I mentioned before, this accomplishment wouldn't have been possible without the love, care and support from our team, community, families, and friends.

I want to thank you all again for your continuous support and assure you we don't take it for granted. 

‍

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

‍

‍

This guide explains how to quickly build an Inventory Management tool to manage movie rentals by connecting a HarperDB backend to an Appsmith frontend. It also implements authentication using HarperDB’s role-based access control feature.

‍

HarperDB is a distributed database that supports key-value, document store, and traditional SQL. As a flexible platform for distributed applications and data, HarperDB is read/write optimized and globally replicates data in under 100ms.

You will need a HarperDB account, which can be created by signing in here.

Appsmith is an open-source framework for building internal tools. It lets you drag-and-drop UI components to build pages, connect to any API, database, or GraphQL source and write logic with JavaScript objects

If you don't have an Appsmith account, create one here.

Let’s get started!

Step 1: Set up your Backend on HarperDB

We’ll be using the HarperDB cloud instance free - tier. Click on Create New HarperDB Cloud Instance → Create AWS or Verizon Wavelength Instance

Select Choose HarperDB Cloud on AWS and then click on Instance Info

Fill in the details about your instance and click on Instance Details button

Keep the default settings in the Instance Specs modal and click on Confirm Instance Details

Click on the Add Instance button

This will begin creating your database cloud instance on HarperDB. Your instance will be up and running in no time.

Step 2: Create the Database (Schema)

• Create a schema called Movies
• Next, create tables for Customer, Film and Rentals with id as the hash attr. The hash attr in HarperDB is equivalent to a primary key.

Use the Upload CSV feature of HarperDB to populate data into the three tables. You can find sample data here.

Step 3: Create Roles and Users on HarperDB

Click on the roles tab and create two users named admin_user and rental_user under Standard Roles. admin_user will have all privileges on the three tables so update all the permissions to true

rental_user will have all permissions on the Rental table and only the read permission on the Customer and Film tables

Under the users tab, create a user named assistant with the role of rental_user.

Similarly, create a user named manager with the role of admin_user

And just like that, the backend setup is complete.

Step 4: Build UI on Appsmith

• On Appsmith, on the Entity Explorer pane, go to PAGES→ Page1 . Rename the page to Login. Drag and drop a Form widget onto the canvas. Add input widgets for Username and Password as shown below. For the Password input widget, choose Password as the Data Type so that the characters entered are masked.

• Create a second page called Rental Inventory which will be the page that the user will be redirected to after login.
• Drag and drop a Tab widget onto the canvas and add three tabs called Rental, Film, and Customer

• On the Rental tab, add a Table widget and a Form widget to its right which will populate the details of the selected rental record that can be updated. On the Update Rental form, Customer and Movie are Text widgets, Status is a Select Widget, and Returned is a Datepicker Widget.

• Add a modal widget onto the canvas to create a form for adding new rentals. Customer (rentalCustEmail) and Film (filmTitle) are select widgets

• Set this modal to open on the onClick event of the New button

• Create the same layout for the Film Tab. The title is Text widget, Rental Duration, Rental Rate, and Replacement Cost are all Input widgets of Number Datatype.

• Drop a Modal widget on the canvas and create a form to add new movies to the collection. Title and Description are Input widgets of Data Type property set to Text and and the remaining are input widgets of Data Type property set to Number. Set this modal to open on the onClick event of the New button on the Film tab

• Lastly, add widgets to the Customer tab as follows. Search is an input widget (Use the search icon under Icon Options and add “Type to search” under Placeholder in the property pane) and customerList is a List widget with two input widgets for Name and Email.

• Drop a Modal widget on the canvas and create a form to add new customers to the database. All are input widgets. Set this modal to open on the onClick event of the New button on the Customer tab

Step 5: Configure the Login page

• The next step is to create a query to retrieve the JWT tokens when the correct credentials are entered in the login form.
• On the Login page, click on the + icon next to Queries/JS. Select New Blank API

• On the API pane, rename Api1 to get_token and select the POST method. For the URL endpoint, enter the url of the HarperDB instance. You can copy paste this from your instance card on the HarperDB dashboard.

Under Headers, ensure that the content-type is application/json

• Under Body tab of the API pane, enter the following in Raw format. Notice here you are passing the username and password entered in the two input widgets on the form.

{
    "operation": "create_authentication_tokens",
    "username": "{{Input1.text}}",
    "password": "{{Input2.text}}"
}

‍

You can enter the username and password on the Login form and then test run the query to view the response. This query returns two tokens - The operation_token which is used to authenticate all HarperDB operations in the Bearer Token Authorization Header and the refresh_token which is used to generate a new operation_token upon expiry.

• Next, we are going to create a JavaScript function that will redirect the user to the main page (Rental Inventory) of the app based on credentials and their roles.
• Click on the + icon next to Queries/JS. Select New JS Object. Rename JSObject1 to utils and paste the code snippet below in the JavaScript Editor.

‍

export default {
    login: async () => {
        return get_token.run()
        .then(res => storeValue('user', {username: Input1.text, token: res.operation_token}))
        .then(() => navigateTo('App'))
        .catch(e => showAlert(e.message, 'error'))
    }
}

‍

Note: storeValue is an Appsmith function that is used to store data in key-value pairs that can be accessed later anywhere in the application.

• Bind the JS Object to the onClick event of the Submit button on the login form.

Step 6: Write queries and bind data to widgets

• Create query to fetch data from Rental table in HarperDB and display it in the Table widget on Appsmith (rentalTable)
• On the Rental Inventory page, click on the + icon next to Queries/JS. Select New Blank API
• On the API pane, rename Api1 to get_rental and select the POST method. For the URL endpoint, enter the url of the HarperDB instance as shown in Step 5.
• Under Headers, add the following key-value pairs. We have to pass the Bearer token generated on successful login under Authorization.

• Under the Body tab of the API pane, enter the following in Raw format. This is an SQL query to fetch the data from the rental table and we are using server-side pagination to display data in the table widget (rentalTable), hence the limit and offset are set in the query.

{
    "operation": "sql",
    "sql": "SELECT * FROM movies.Rental LIMIT {{rentalTable.pageSize}} OFFSET {{(rentalTable.pageNo - 1) * rentalTable.pageSize}}"
}

• Bind the data from this query under Table Data property of the rentalTable table using the following snippet

‍

{{get_rental.data}}

• You will notice the table widget populates with results from the query.
• Next, we will bind data to the widgets on the Update form to the right of the table. When a user click on the table row, the corresponding details should be displayed on the form.
• For the Rental Update form, set the widgets in the following manner. Notice that we are manually adding the values in the Status select widget so that the status of the rental can be updated on the form.

• Create a query to update the Rental status and Returned date on this form.
• On the Rental Inventory page, click on the + icon next to Queries/JS. Select New Blank API
• On the API pane, rename Api1 to update_rental and select the POST method. Repeat the same steps as above for the URL endpoint and the Headers
• In the Body tab of the API pane, enter the following under Raw tab

{
    "operation": "update",
    "schema": "movies",
    "table": "Rental",
    "records": [
        {
            "id": {{rentalTable.selectedRow.id}},
            "status": {{status.selectedOptionValue}},
                        "return_date": {{returnedDate.selectedDate}}
        }
    ]
}

‍

• Hook this query on the onClick event of the Submit button on the Update Rental form. On success of this query also run the get_rental query to repopulate the table widget with updated data.

• Create API query named search_customer with headers same as before with the following code snippet under Body tab of the API pane in Raw format

‍

  {
      "operation": "search_by_value",
      "schema": "movies",
      "table": "Customer",
      "search_attribute": "email",
          "limit": 10,
      "search_value": {{`*${rentalCustEmail.filterText}*`}},
      "get_attributes": [
          "id", "email"
      ]
  }

‍

• Create another API query named search_film with the following code snippet under Body tab of the API pane in Raw format.

  {
      "operation": "search_by_value",
      "schema": "movies",
      "table": "Film",
      "search_attribute": "title",
          "limit": 10,
      "search_value": {{`*${filmTitle.filterText}*`}},
      "get_attributes": [
          "id", "title"
      ]
  }

‍

• Create a third query name new_rental to add new rental information with the following code snippet under Body tab of the API pane in Raw format.

  {
      "operation": "upsert",
      "schema": "movies",
      "table": "Rental",
      "records": [
          {
                          "film_title": {{filmTitle.selectedOptionValue}},
                          "rental_date": {{moment().utc()}},
                          "staff_id": {{appsmith.store.user.username == 'assistant'? 2: 1}},
                          "customer_email": {{rentalCustEmail.selectedOptionValue}},
                          "status": "borrowed"
          }
      ]
  }

‍

• Open the modal by clicking the New button on the Rental tab. On Customer select widget write the following code snippet to populate the customer email ids from the search_customer query.
•  {{search_customer.data.map(c => ({label: c.email, value: c.email}))}}
  

• Similarly, populate the Film select widget from the search_film with below code snippet.

{{search_film.data.map(m => ({label: m.title, value: m.title}))}}

• Set the new_rental query to run on the onClick event of the Confirm button on the modal.

• Repeat the above steps for the Film Tab.
• The Update Form on the Film tab has the following data bindings

• Create an API query named update_film with the following code snippet under Body tab of the API pane in Raw format. Bind this query to run on the onClick action of the onClick event of the Submit button on the Update Film form.

‍

{
    "operation": "update",
    "schema": "movies",
    "table": "Film",
    "records": [
        {
                    "id": {{filmTable.selectedRow.id}},
                    "rental_duration": {{ufDuration.text}},
                    "rental_rate": {{ufRate.text}},
                    "replacement_cost": {{ufCost.text}}
        }
    ]
}

‍

• Let’s configure the queries to run to add new film data. Create API query named create_film with headers same as before. Add the following code snippet under Body tab of the API pane, enter the following in Raw

‍

• Open the modal by clicking the New button on the Film tab. Set the create_film query to run on the onClick event of the Confirm button on the modal. Close the modal and rerun the get_film query to refresh the Film table widget on success of the query.

• Finally on the Customer page, write queries for the search input widget (customerSearchInput) and for displaying data in the list(customerList) below.
• Create query called get_customers with the following under Body tab of the API pane.

{
    "operation": "search_by_value",
    "schema": "movies",
    "table": "Customer",
    "search_attribute": "email",
        "limit": 10,
    "search_value": "{{`*${customerSearchInput.text}*`}}",
    "get_attributes": [
        "id", "email", "first_name", "last_name"
    ]
}

‍

Bind this query to the onTextChanged action of the customerSearchInput box

‍

{{get_customers.run()}}

• Bind the results of this query {{get_customers.data}} to the Items property of the customerList box

• Let’s configure the queries to run to add new Customer data. Create API query named new_customer with headers same as before. Add the following code snippet under Body tab of the API pane, and enter the following in Raw

  {
      "operation": "upsert",
      "schema": "movie",
      "table": "customer",
      "records": [
          {
                          "first_name": {{ncFNameInput.text}},
                          "last_name": {{ncLNameInput.text}},
                          "email": {{ncEmailInput.text}}
          }
      ]
  }

‍

• Open the modal by clicking the New button on the Film tab. Set the new_customer query to run on the onClick event of the Confirm button on the modal. Close the modal on success.

‍

Step 7: Control access to information based on user roles

• On the Rental Inventory page, set the Visibility of the Tab widget to show only if the login process returns tokens on success. Toggle the JS button next to the Visible property to enter the following code snippet

{{!!appsmith.store.user.token}}

• The Rental tab is visible to both the Manager and Assistant roles so we leave its visibility as is
• The Film and Customer tab should only be visible to the Manager
• The Visible property of the individual tabs can be accessed by clicking on the gear icon next to the tab name

• Add the following snippet of code in the Visible property of both the tabs

{{appsmith.store.user.username !== 'assistant'}}

Step 8: Configure the Logout button

When the user clicks on the Logout button, we will clear the username and token information in the store variable and redirect the user to the Login page. Add the following snippet of code on the onClick event of the Logout button.

{{storeValue('user', {}); navigateTo('Login')}}

So far, we've done a great job. The last part of this project will deploy our application. On the dashboard's top right section, click on the 'Deploy' button. You can also deploy this application by connecting to a Git repository. Our application is now deployed!

If you're interested in using a database 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.

Today we're excited to announce the launch of Version Control with Git, so that developers can collaborate and version control their applications. This has been one of the most requested features from our community.

While low-code frameworks like Appsmith save a developer's time via easier application management, faster plugging in of data sources, and effortless creation of UI, there are still aspects of a developer's usual workflow that aren't native to low-code platforms.

Version Control with Git, enables us to take a big step towards that.

Here's a short video on how you can use Version Control with Git:

‍

Version Control with Git Highlights

Version Control with Git allows multiple developers to add their work in a git branch, raise a pull request for code reviews, integrate with CI/CD pipelines so that their changes go live when their pull requests are approved, and provide a commit history to go back to a previous version if something were to go wrong. You can now also test a new addition to your app on a separate branch without breaking your deployed version.

You can connect to any popular version control tool of your liking: Github, Gitlab, Bitbucket, AWS Code Commit and more.

Furthermore, if you're a large organization using isolated environments for your production and test apps, you can now ensure that your applications can only be promoted to production via your CI/CD processes.

Version Control For All

Appsmith's Community Edition users will be able to connect unlimited public repos and up to 3 private repos.

In contrast, low-code frameworks in the market today either don't offer this functionality as part of their open source or free edition (and instead offer a simple auto-save), or push it deep into their most expensive pricing plans (like Retool).

Head over to our Version Control with Git documentation on how to set up version control with your favorite version control tools and get the most out of it.

If you’ve followed Appsmith for a while, you know that we can do a lot in 30 days! I am here to give you all the deets; follow along!

Application Theming [Beta]

One of our most awaited features — App Theming — is now out! Style your pages and widgets using global controls. We've also made it easy to change the visual layout with a single click.

Note that while all apps support theming, changing the theme or customizing the theme may not update the app entirely for older apps. We’ve done this to prevent overriding any changes you may have already made while styling your widgets. (Note that, this is a beta feature at the moment, and we are going to be improving it in the coming weeks).

Theming automatically updates multiple properties, including fonts, colors, borders, shadows, etc. All these properties can be configured from the property pane globally. Read the complete documentation here.

Discard Changes/ Delete Branch on Version Control

Version Control with Git is well-loved by Appsmith users, especially when working with teams. We’ve enhanced this feature by adding another option other than ‘commit’ to make it more flexible. Now you can discard changes on the current branch and reload the earlier, committed application. Additionally, you can also delete the stale or unused branches.

Allowing Camera and Microphone on iFrame

Are you communicating to other websites from Appsmith? Our latest improvements will help you get the most out of it. All you have to do is use allow="camera; microphone" on the attributes to be able to access the camera/microphone on the iframe.

Making it easy for you on Windows using WSL

Have trouble setting up the Appsmith server on Windows? Don’t worry. We’ve worked on some documentation to help you contribute to Appsmith on Windows operating systems that use WSL; check it out here. Prerequisite MongoDB is included, along with detailed notes on building/running the code and how to set up your code editor.

Improvements

New Copy Paste Experience

Duplicating widgets on Appsmith is now easier with the new-copy-paste experience. Here’s a quick summary of how it works:

When the layout widget is selected:

• If the mouse pointer is not on the layout widget, the widget is pasted inside the layout widget at the bottom of the canvas.
• If the mouse pointer is on the layout widget, the pasted widget will be aligned top-left of the mouse pointer inside the layout widget.

If the non-layout widget is selected, no matter where the mouse pointer is, It is pasted below the non-layout widget, and the widgets are aligned horizontally.

If the mouse pointer is on a widget and no widgets are selected, widgets will be posted below and are horizontally aligned.

‍

More Pages? View them all at once!

We’ve added a new option to resize the entity explorer page to view more pages at once. Just find the expander and extend it based on your comfort :)

Total Records and Page Count on Table Header

More love to the table widget; we’ve just added total record count and page count on the table header.

Better Way of Running JS Objects

We’ve revamped the way we run JS Objects from the JS Editor. Just put the cursor inside the function and hit the RUN button. The editor automatically identifies which function you’re working on; you can always use the shortcuts to make this easier!

Additionally, the page load functions can be configured from the Settings tab, just like how we do it from queries!

Added top-level isValid property to JSONForm

Currently, the JSONForm widget has an isValid property inside the fieldState for each field. However, there is no top-level property to check all fields at once. With this update, we added an isValid property which can be used to check if the current form is valid (passes all validation) or not.

Enhanced Map Widget

We’ve added a new property to the Map widget that lets us enable search location from Map without entering the coordinates. You can search directly on the map and display searched fields on other widgets without clicking on the Map widget.

Collaborations, Technical Content, Videos & Tutorials

Last month was hectic! We’ve published a new video on how you can generate PDFs from Appsmith using APITemplate; not just this, we’ve successfully hosted a couple of live sessions.

• In this Video, Confidence, our Developer Advocate, show us how you can build a workflow to generate PDFs from Appsmith by consuming APIs from APITemplte.io.
• Shams Mosowi from Rowy.io collaborated with us to demonstrate how we can build an expense management tool that employees can use to submit expenses for approval using Rowy as the backend.
• Getting started with Version Control with Git on Appsmith? We’ve created a guide on how you can connect to a Git provider to version control Appsmith apps. Watch it here.

If you're curious about what we were up to, look no further and follow this link.

If you’re interested in using a database 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 online store with WooCommerce is incredibly easy. It allows you to upload your products, set up stock and downloadable products, and track orders and customer data.

And it is all free.

It makes sense that a lot of people use it and love it. 

But getting reports out of it is hard sometimes. They have their default reporting tools, but they often don’t show all the information you need. And customizing it is incredibly complex.

A faster approach is to build a reporting dashboard using Appsmith. This allows you to create fully customized reports, and it’s easy to add new elements or edit the current ones.

Therefore, our goal for today is to make the WooCommerce and WordPress analytics even better. We will use Appsmith to read your database and build a sales reporting dashboard.

Along with the dashboard, we will explore many aspects of Appsmith and how to read external databases with it.

‍

These are the things we are going to learn today:

• Reading external databases
• Preprocessing your data
• Building your Appsmith dashboard
• Working with different time spans in your widget's data
• Reading WooCommerce data
• Creating line charts, bar charts, map charts
• Displaying stats box with our main KPIs
• Tables and lists with products and customers rankings

‍

Let’s get started!

‍

You can check out the final app here. You can fork it and build your app using your database. 

‍

Before we start building, let’s walk through the main features of the app.

‍

The time selector

This button group allows you to select different timeframes in your queries. It allows you to check partial periods as well as completed periods quickly.

In addition, it is created in a way that you can easily extend it and add custom time selectors if you want (from X date to Y date).

‍

The KPIS

‍

The KPIs show you a quick overview of your primary metrics. You can use the ones we’ve created or create your metrics. 

Overview charts

These charts allow you to quickly see orders for the selected period and how they compare to previous periods. This is the hardest part to implement in coding, but we have some excellent tips to make it much more manageable.

‍

The Map Chart

This map allows you to see your top performing countries, with labels on hover. It is quite an interesting element. It has some technical challenges as well, but it creates a useful visual element for online stores.

Products Ranking

The product ranking allows you to see which are your top-performing products quickly. This is vital information for store owners, and you can get many insights from it.

Customers Ranking

Likewise, the top customers list shows your best customers and how much they are spending. You can use this information to reward them or find other customers with the same profile.

‍

Now let's get to putting the UI together, but for that we need to plan what our app will look like.

Create a Wireframe

‍

Before coding your dashboard, you need a plan. There are many tools, from low-tech and reliable pen and paper to fancy design tools.

Another tool that might work well for you is Appsmith itself. You can use it as a prototyping tool. In this phase, you drag and drop the components you want to use on your page. Then you order them according to what makes the most sense for your decisions.

The wireframe is just like the final design in our demo, but with no data connections.

In addition to getting a feel of how your dashboard looks like, you can check two other points with your wireframe:

• What kind of interactivity and functions do you need to create
• What data source and in what format do you need

The first point is solved with some JS functions. The second one is solved with the DB connections.

‍

WordPress and WooCommerce Data Structures

Now that you know what your app looks like, it's time to check your data structures. Since we are going to read data from WordPress directly, you have three options:

• A direct database connection
• REST API connections
• Using a database that mirrors your main DB

The first option is the simplest. That's what we are going to do today. You can add your DB connection to Appsmith, and you are good to go.

The second option might work better depending on the data you want to load. If it is public data (which isn't the case for orders), this can be a quick solution, in particular, if you don't own the data source.

There are options to create custom API endpoints and authenticated requests. But this can get complex quite quickly. So make sure that this is the best option if you want to go for it.

The third option is an exciting one. In it, you can copy your database to another place. This allows you to pre-process your data if you want, reducing the stress on your main server. This might be a good option if you have too much data.

The downside is that you are always working with past data. Since you need to schedule this "mirroring" process, you are usually limited to doing it a couple of times per day.

Look-up Tables

You know the drill if you've worked with WordPress and WooCommerce before.

The data structures in WordPress are pretty hard to read, and WooCommerce makes it all the more complex.

This is where 'lookup tables' feature comes in.

They have been introduced in WooCommerce to solve performance issues. They are pre-processed versions of the most common WooCommerce queries you run, making it much, much faster. So, instead of checking the postmeta table, finding the post, then finding the user, you can look at the customer_lookup table, and it's all there.

There are many tables on your WordPress site, but we are going to use these in our demo:

You might have noticed that we have two additional tables in addition to the lookup tables. Let’s talk about them now.

Auxiliary Tables: Calendar and Times

It is common to plot all dates, even if you have no data in it when it comes to reporting. After all, you don’t want a chart that skips dates, giving you the false impression that there are only good days.

For this reason, we need to calculate all possible dates and times. You can do it with JS functions, but it can get messy. Therefore, it’s much easier to create a calendar table with all possible dates using a SQL function.

‍

This allows you to select all dates and then join your desired data (revenue, for example). The same goes for the hous of the day. Instead of manually calculating them, get the table with times and LEFT JOIN your desired data.

Data Manipulation

Since we are dealing with pre-made tables, which we don’t control, we must double-check if all the data we need is there.

For example, if you have a KPI for sales goals, do you have this stored on your site? If not, you need to find a way to store it.

In our case, all our data is there. It’s just a matter of finding which tables to load where.

There are many approaches to this, but one that works well is to go through each component and describe what kind of data you need to populate it. From there, you can even list the tables involved.

Here is how you could do it for the demo dashboard:

KPIS

Revenue - Sum of total sales from wp_wc_order_stats

Orders - Count of rows from wp_wc_order_stats

Average Ticket - Revenue / Orders

Orders completed chart

Get dates from the calendar or times

Left Join sum of total sales grouped by order date or time from wp_wc_order_stats

Order Status Chart

Get the sum of total sales from wp_wc_order_stats grouped by status

Orders Map

Get sum of total sales from wp_wc_order_stats

Left join country wp_wc_customer_lookup

Group by country

Products ranking

Get the sum of product revenue and count of orders from wp_wc_order_product_lookup grouped by product_id

Customers ranking

Get sum of order total from wp_wc_order_stats

Left join name from wp_wc_customer_lookup 

Group by customer ID

As you can see, everything we need is there. Now it’s time to get our hands dirty.

Connecting the timeframe switchers

Let’s make the first component interactive. Drag and drop a button group to your page if you haven't already.

Next, add all options. You can create regular buttons (the main buttons) using the button type as simple, and you can create the “more” button using the menu type:

‍

The menu type allows you to create sub-items for each menu item.

To mark a button as selected we are going to use the “disabled” property. You can set it as “true”, “false” or as the result of some JS code. This allows you to assign dynamic values to your buttons.

You can create a new JS object, then add this code in it:

  
currentButton: ( slug = "" ) => {   	    	 var ret = false;   	    	 if ( slug == appsmith.store.button ) {   		 ret = true;   	 }   	    	 return ret;    }, 

‍

This code just checks if the “slug” passed is the current button (stored in the appsmith.store.button variable). If it is the current button, it returns true, so the button is disabled. If it isn’t the current button, it returns false.

While you are at it, you can use this code to assign values to this “button” variable:

setButton: ( slug ) => {   	 // possible values   	 // today, (X to date) wtd, mtd, ytd, yesterday, (last X) lw, lm, ly   	 storeValue( 'button', slug );   	    	 get_country_orders.run();   	 get_customers_ranking.run();   	 get_kpis.run();   	 get_linechart.run();   	 get_linechart_past.run();   	 get_orders_by_status.run();   	 get_product_ranking.run();    }

In this case, you could simply  use the storeValue function directly. But since we need to re-run all our queries, it’s better to pass the button action to a function, and in it we can do it all. Here we can save the button value and run each of our queries again, to update data when the value has changed.

Now you just need to edit each of your buttons and add the disabled value you want, along with the “onclick” function to save the button value:

‍

Once you have this, you can play around and click your buttons; they should turn on and off automatically.

You need one more adjustment. When the page loads, there is no value assigned to this button. But we need it in all our actions. You can create a function to run on page load to assign a default value to it:

onLoad: async () => {   	    	 if ( appsmith.store.button == null ) {   		 storeValue('button', 'mtd');   	 }   	    	 return appsmith.store.button;    },

Since this is an async function, you can click on the gear button next to it and enable “Run function on page load”:

‍

The time limit is ready; now we need to use it. In this case, we use this function to generate the “where” part of all our queries. This is quite handy since you need to call this function whenever you have a time limit.

timeframe: ( type = "date_created", date_convert = false ) => {   	    	 var time = appsmith.store.button, ret = [], dateCurrent = new Date(), dateMiddle = new Date(), datePast = new Date(), compare = "", offset = 0;   	    	    	 dateMiddle.setUTCHours(0, 0, 0, 0);   	 datePast.setUTCHours(0, 0, 0, 0);   	    	 //default time = mtd   	 time = ( time == null ) ? "mtd" : time;   	    	    	 // if time is one of the "incomplete" types   	 compare = ["today", "wtd", "mtd", "ytd"];   	    	 if ( compare.includes( time ) ) {   		 //current date (most recent records) is today (tomorrow just to load all items no exceptions)   		 dateCurrent.setDate( dateCurrent.getDate() + 1 );   	 } else {   		 // current date is the begining of the period (start of the day (yesterday), week, month, year)   		 dateCurrent.setUTCHours(0, 0, 0, 0);   		    		 //increase by one all further calculations   		 offset = 1;   		    		 //set day, week, month, year   		 switch (time) {   			 case 'lw':   				 dateCurrent.setDate( dateCurrent.getDate() - dateCurrent.getDay() - 1 );   				 break;   			 case 'lm':   				 dateCurrent.setDate( 0 );   				 break;   			 case 'ly':   				 dateCurrent.setMonth( 0 );   				 dateCurrent.setDate( 0 );   				 break;   		 }   	 }   	    	 if ( time == "today" || time == "yesterday" ) {   		 dateMiddle.setDate( dateMiddle.getDate() - offset );   		 datePast.setDate( datePast.getDate() - 1 - offset );   	 }   	    	 if ( time == "wtd" || time == "lw" ) {   		 dateMiddle.setDate( dateMiddle.getDate() - ( offset ) * 7 - dateMiddle.getDay() - 1 );   		 datePast.setDate( datePast.getDate() - ( 1 + offset ) * 7 - datePast.getDay() - 1  );   	 }   	    	 if ( time == "mtd" || time == "lm" ) {   		 dateMiddle.setDate( 0 );   		 dateMiddle.setMonth( dateMiddle.getMonth() - offset );   		    		 datePast.setMonth( datePast.getMonth() - 1 - offset );   		 datePast.setDate( 0 );   	 }   	    	 if ( time == "ytd" || time == "ly" ) {   		 dateMiddle.setYear( dateMiddle.getFullYear() - offset );   		 dateMiddle.setMonth( 0 );   		 dateMiddle.setDate( 0 );   		    		 datePast.setYear( datePast.getFullYear() - 1 - offset );   		 datePast.setMonth( 0 );   		 datePast.setDate( 0 );   	 }   	    	    	 type = ' `' + type + '` ';   	    	 dateCurrent = ' "' + dateCurrent.toISOString().replace('T', ' ') + '" ';   	 dateMiddle = ' "' + dateMiddle.toISOString().replace('T', ' ') + '" ';   	 datePast = ' "' + datePast.toISOString().replace('T', ' ') + '" ';   	    	 if ( date_convert == true ) {   		 dateCurrent = " DATE(" + dateCurrent + ") ";   		 dateMiddle = " DATE(" + dateMiddle + ") ";   		 datePast = " DATE(" + datePast + ") ";   	 }   	    	 ret.push( type + ' < ' + dateCurrent + ' AND  ' + type + ' >= ' + dateMiddle   );   	 ret.push( type + ' < ' + dateMiddle + ' AND  ' + type + ' >= ' + datePast   );   	    	 return ret;    },

Now you are ready to load dynamic data! Let’s go through each of your components.

Loading data for the KPIs

As discussed earlier, the KPIs load the sum of all sales in the current period. You can do it with this SQL query:

SELECT SUM(total_sales) as "revenue", COUNT(order_id) as "orders" FROM `wp_wc_order_stats` WHERE {{utils.timeframe()[0]}}

Notice how we use the timeframe function with the current period as an argument. 

This is the output for that query:

[  {	"revenue": 14539,	"orders": 200  }]

‍

The revenue returns just a number though. Since we are using currency formatting in many different places, you can use this function in your JS object:

currencyFormat: (n) => {   	 n = ( n || 0 );   	    	 return Intl.NumberFormat('en-US' , {   			 style: "currency",   			 currency: "USD",   			    	 }).format(n);    }

Then, you need to replace the current value in your KPI boxes.

This is the revenue:

{{ utils.currencyFormat( get_kpis.data[0]["revenue"] ) }}This is the orders count:{{ get_kpis.data[0]["orders"] }}And this is the average ticket size:{{utils.currencyFormat((    get_kpis.data[0]["revenue"] / get_kpis.data[0]["orders"])    )}}

‍

That’s it! Your KPIs are ready.

‍

Create Orders Completed and Order Status Charts

‍

Appsmith comes with some preset charts. But if you need custom charts, you can use any fusion chart you want.

You need to select the “custom chart” option and then pass the arguments as defined by fusion charts.

If you need multi-series data in a line format, you can use the msline chart. That’s what we use for the main sales chart.

This is what we use in the custom fusion chart field for that component:

{  "type": "msline",  "dataSource": {	"chart": {    	"caption": "",    	"subCaption": "",    	"xAxisName": "",    	"yAxisName": "",    	"numberPrefix": "$",   	 "labelStep": {{ (get_linechart_past.data.length / 7) }},    	"plotFillAlpha": "100",    	"theme": "fusion",   	 "lineColor": "03B365",   	 "anchorBgColor": "03B365"	},	"categories": [    	{        	"category":   		 {{ get_linechart_past.data }}    	}	],	"dataset": [    	{        	"seriesname": "Previous",   		 "color": "#000",   		 "anchorBgColor": "#000",‍        	"data": {{ get_linechart_past.data }}       	     	},    	{        	"seriesname": "Current",        	"data": {{ get_linechart.data }}    	}	]}}

‍

The central aspect of this chart is that you need a setup, then your categories, and then your data series as arrays. In this case, we use the past data as the labels (since they have a complete series at all times), and we use current and past data in the datasets.

These datasets are created with 2 queries, but these queries just load a JS function. For the current data:

{{utils.queryBuild()[0]}}And for past data:{{utils.queryBuild()[1]}}In this function we make use of the time limits set in our functions and build our queries.queryBuild: () => {   	 // builds queries for the line chart   	 // return [current, past]   	 // utils.timeframe( type = "date_created", date_convert = false  );   	 var time = appsmith.store.button, ret = [], labels_table = "", labels_time_format = "", orders_time_format = "", labels_where = [ " 1 ", " 1 "], orders_where = utils.timeframe( ), query = "";   	    	 //default time = mtd   	 time = ( time == null ) ? "mtd" : time;   	    	 // select if we are plotting data from calendar_table (dates, weeks, months) or time_table (hours)   	 if ( time == "today" || time == "yesterday" ) {   		 labels_table = "time_table";   		    	 } else {   		 labels_table = "calendar_table";   		 labels_where = utils.timeframe( "dt", true  );   	 }   	 labels_table = ' `' + labels_table + '` ';   	    	 // select the column that is the time_format for the labels and orders   	 switch (time) {   			 case 'today':   			 case 'yesterday':   				 labels_time_format = ' value as "time_format", `24h` as "label" ';   				 orders_time_format = ' HOUR(date_created) as "time_format" ';   				 break;   			    			 case 'wtd':   			 case 'lw':   				 labels_time_format = ' `dt` as "time_format", `dayName` as "label"  ';   				 orders_time_format = ' DATE(date_created) as "time_format"  ';   		    				 break;   			    			 case 'mtd':   			 case 'lm':       				 labels_time_format = ' `dt` as "time_format", CONCAT(`d`, " ") as "label"  ';   				 orders_time_format = ' DATE(date_created) as "time_format"  ';   	    				 break;   			    			 case 'ytd':   			 case 'ly':   				 labels_time_format = ' `m` as "time_format", `monthName` as "label"  ';   				 orders_time_format = ' MONTH(date_created) as "time_format"  ';   			    				 break;   	 }   	    	 //build main query   	 query += " SELECT `tm`.`label`, `orders`.`value` FROM ";   	 // calendar select   	 query += "(";   		 query += ' SELECT *, ' + labels_time_format + ' FROM ' + labels_table + ' WHERE ' + labels_where[0] + ' GROUP BY `time_format`  ';   	 query += ") as tm ";   	 // orders select   	 query += " LEFT JOIN (";   		 query += 'SELECT *, ' + orders_time_format + ' , SUM(total_sales) as "value" FROM `wp_wc_order_stats` WHERE ' + orders_where[0] + ' GROUP BY `time_format`';   	 query += ") as orders ";   	 // end query   	 query += " ON `tm`.`time_format` = `orders`.`time_format` ";   	    	 ret.push(query);   	 //build past query   	 query = "";   	 query += " SELECT `tm`.`label`, `orders`.`value` FROM ";   	 // calendar select   	 query += "(";   		 query += ' SELECT *, ' + labels_time_format + ' FROM ' + labels_table + ' WHERE ' + labels_where[1] + ' GROUP BY `time_format`  ';   	 query += ") as tm ";   	 // orders select   	 query += " LEFT JOIN (";   		 query += 'SELECT *, ' + orders_time_format + ' , SUM(total_sales) as "value" FROM `wp_wc_order_stats` WHERE ' + orders_where[1] + ' GROUP BY `time_format`';   	 query += ") as orders ";   	 // end query   	 query += " ON `tm`.`time_format` = `orders`.`time_format` ";   	    	 ret.push(query);   	    	 return ret;    }

The entire function is quite long, but the entire logic can be summarized as follows:

Check if we load data from the calendar (dates) or the timetable

LEFT JOIN data from orders, grouped by the time restriction

Apply the time restriction in both queries

Return 2 queries (an array), one for the current selection and one for the past selection.

The orders by a status chart are created using a columns2d chart. 

‍

You can define your options such as axis names, palette colors, and captions in it.

Then, you need to pass the charts data. This is the query to load orders by status:

SELECT status as "label", COUNT(order_id) as "value" FROM `wp_wc_order_stats` WHERE {{utils.timeframe()[0]}} GROUP BY status

‍

And this is the result:

[  {	"label": "wc-completed",	"value": 174  },  {	"label": "wc-failed",	"value": 12  },  {	"label": "wc-processing",	"value": 14  }]

You can see that the query is crafted with the chart in mind. Therefore, we need to return the label and value comuns, as per the widget.

Sales map chart

The map charts have options that significantly affect how your data is presented. Check it out:

‍

You can pick the map type from a list of values. Some maps expect country IDs (from the fusion charts table), and some expect continents labels. 

‍

We call a function that does this mapping process for us to make things easier.

‍

This is the SQL query to load data for the map charts:

SELECT SUM(`rev`.`revenue`) as "c_revenue", SUM(`rev`.`orders`) as "c_orders", `nm`.`country` as "country" FROM

( SELECT *, SUM(total_sales) as "revenue", count(order_id) as "orders"  FROM `wp_wc_order_stats` WHERE {{utils.timeframe()[0]}} GROUP BY customer_id ORDER BY `revenue` DESC LIMIT 20) as rev
LEFT JOIN
( SELECT * FROM `wp_wc_customer_lookup`  ) as nm

ON `rev`.`customer_id` = `nm`.`customer_id` GROUP BY `nm`.`country`

‍

This might seem complex, but this is what we are doing:

SELECT SUM of all revenue, SUM of all orders count, Country codes FROM

Wp_wc_order_stats (where you get all orders, sum of revenue, orders count)

LEFT JOIN wp_wc_customer_lookup (where you get the country code for your customers/orders)

GROUP BY Country code

‍

Therefore, we get data from Wp_wc_order_stats (revenue) then we load the wp_wc_customer_lookup to know which country that order is from. Next, we group the result by country.

‍

But this query generates a result that isn’t what the fusion charts component expects. Here is an example:

[  {	"c_revenue": 759,	"c_orders": 5,	"country": "CA"  },  {	"c_revenue": 168,	"c_orders": 1,	"country": "GL"  },  {	"c_revenue": 7595,	"c_orders": 103,	"country": "MX"  },  {	"c_revenue": 1488,	"c_orders": 10,	"country": "US"  }]

‍

Therefore, we need to translate this data into their format. We can do it with array mapping. We use these 2 functions for that:

mappedCountries: () => {   	 var countries = get_country_orders.data;   	    	 countries = countries.map(({ c_revenue, country}) => ({ "id": utils.mapCountry(country), "value": c_revenue }));   	    	 return countries;    },    mapCountry: ( code = "" ) => {   	 var ret = "001";   	    	 switch (code) {   		 case 'CA':   			 ret = '005'   			 break;   		 case 'MX':   			 ret = '016'   			 break;   		 case 'GL':   			 ret = '024'   			 break;   		 case 'US':   			 ret = "023";   			 break;   		 default:   			 ret = '001';   	 }   	    	 return ret;   	     },

You can modify these functions to add more options (countries, continents) and manipulate data in other ways (for example, plot order count instead of revenue).

Either way, now, when you call utils.mappedCountries, you get all countries in the exact format you need.

A final touch is just creating chart labels in your desired format. Here is an example:

[  {	"minValue": 1,	"maxValue": 200,	"code": "ECB365"  },  {	"minValue": 100,	"maxValue": 1000,	"code": "#61A4BC"  },  {	"minValue": 1000,	"maxValue": 10000,	"code": "#03B365"  }]

Products Ranking

The product ranking is just a query that loads all orders within the timeframe selected. Then it groups all these orders by product ID, summing the order values and counting the number of orders.

It seems simple enough, but what about the ranking?

You can do it with some JS code, but you can include it in your SQL query if you want.

Here is how you can do it:

SET @rownum = 0;SELECT @rownum:=(@rownum+1) as "rank", `rev`.`revenue`, `rev`.`orders`, `nm`.`title` FROM‍( SELECT *, SUM(product_net_revenue) as "revenue", count(order_id) as "orders" FROM `wp_wc_order_product_lookup` WHERE {{utils.timeframe()[0]}} GROUP BY product_id  ORDER BY `revenue`  DESC ) as revLEFT JOIN( SELECT post_title as "title", ID FROM `wp_posts` ) as nm‍ON `rev`.`product_id` = `nm`.`ID`

‍

In this query, we set a variable (rownum), and for each row, your database adds one to it ( @rownum:=(@rownum+1) ).

The selection itself is similar to what we did before; the only adjustment is that we load the product name from the wp_posts table.

Then you can add this query to your table:

‍

‍

You can adjust the column names, and adjust the revenue to be set as:

{{utils.currencyFormat(currentRow.revenue)}}

This function converts the revenue value to currency.

Customers Ranking

The customers' ranking is very similar to the products ranking. The only difference is that we are using a list this time.

In this case, you can load the customer gravatar if you want (loading the gravatar image for their email). In our demo dashboard, we load placeholder images.

This is the query to load the customers ranking:

SET @rownum = 0;SELECT @rownum:=(@rownum+1) as "rank", `rev`.`revenue`, `rev`.`orders`, `nm`.`first_name` as "name" FROM‍( SELECT *, SUM(total_sales) as "revenue", count(order_id) as "orders"  FROM `wp_wc_order_stats` WHERE {{utils.timeframe()[0]}} GROUP BY customer_id ORDER BY `revenue` DESC LIMIT 20) as revLEFT JOIN( SELECT * FROM `wp_wc_customer_lookup`  ) as nm‍ON `rev`.`customer_id` = `nm`.`customer_id`

And you need to load its data in your list like this:

‍

‍

Similar to the table, you can reference the currentItem in your components to load data. For example, the revenue and orders field is set up like this:

Revenue: {{ utils.currencyFormat(currentItem.revenue) }} <br />Orders: {{ currentItem.orders }}

And that’s it! Your dashboard is ready.

‍

Today we learned how to create a sales dashboard for WooCommerce sites. We discovered how to read data from WordPress and add it to different Appsmith widgets.

By the end of the day, you should be able to connect your WordPress site to Appsmith, creating your widgets. We didn’t cover some aspects of applications (such as security) for educational purposes, but this should be a great starting point.

We hope you’ve enjoyed it, and see you again next time!

‍

Some things are fantastic together. 🍔🥤

In particular, REST APIs and Low code dovetail nicely.

Although APIs simplify development, they also demand a level of programming skill and technical ability. Low code frameworks like Appsmith can provide a visual layer on top of the API that empowers non-technical audiences and citizen developers to build client applications.

There is much value in simplifying the development process where we can. Low code works best for development projects where the problem domain is well understood and there are very few use cases. REST API clients are well suited to that extent - the API contract itself serves as a kind of blueprint for the client application. Take, for instance, the Brex APIs.

Brex offers several APIs that range from Onboarding and Payments to Teams and Transactions. Brex admins can plug into these APIs to build powerful workflows and custom automations. To demonstrate, we'll build an Appsmith dashboard that will connect to your Brex account.

Key Takeaways

• Appsmith has a REST interface that allows us to create and modify query objects. A query object is a portable, reusable construct that defines parameters needed for a single API call. We'll use several query objects to engage the Brex APIs.
• Building a multi-page interface with Appsmith is incredibly simple and well-documented. The platform provides a visual way to build pages using widgets and drag/drop functionality. However, unlike the global Appsmith store, page resources are page-scoped and cannot be referenced across different pages. Despite that, it is still possible to move or copy resources from one page to another.
• We'll use JavaScript Promises to simplify asynchronous workflows. This will enable us to launch multiple API calls concurrently and then handle the responses elegantly. Normally, this is a challenge because API calls rely on the network and conclude at inexact points in time. To overcome this, we used to apply callback functions, but JavaScript Promises are an improvement over callbacks.

What are we building?

In this tutorial, we'll create an Appsmith tool that leverages the Brex APIs to perform bulk operations on your Brex account. By uploading a CSV file, you'll be able to create credit cards, users, and vendors. These changes will be reflected in your Brex dashboard immediately. Our tool will also support terminating cards and deleting vendors.

As it is not intended for production use, this application doesn't attempt to be either complete or systematic. It is more like a demo project. Rather than serve as a proper software solution, the final application is meant to demonstrate what is possible using Appsmith and the Brex APIs.

For context, this tutorial depicts the reader as a fictional Brex customer in order to provide a frame of reference and bring meaning to the sample CSV files. Pretend that you are an outer space company that issues Brex credit cards to your colleagues. The cardholders have personal cards as well as other cards designated for expenses such as Equipment Repairs, Launch Services, and Telecom. You will also define fictional Vendors: General Atomics, Orbital Transport Services, Space Food Systems. Let's suppose you are building this application to automate a few back office operations. You have chosen to build it using Appsmith.

What do I need?

First, you'll want to sign into Appsmith and create a new application. Next, sign in to your Brex account and generate a user token with Read/Write permissions for Cards, Referrals, Users, and Vendors. Appsmith will use this token to authorize API calls.

To confirm that your token is working, you can perform a quick sanity check with curl:

COPYcurl --request GET 'https://platform.brexapis.com/v2/users/me' \
--header 'Authorization: Bearer <your-token>'

The response will resemble the following:

COPY{
   "id": "cuuser...",
   "first_name": "Michael",
   "last_name": "Collins",
   "email": "mcollins@example.com",
   "status": "ACTIVE"
}

Postman

Alternatively, you can use Postman to test API calls. In fact, the Brex Developer workspace allows you to experiment with every endpoint documented in the Brex Developer Portal.

Appsmith Echo API

Consider using the Appsmith Echo API to spare production API calls.

COPY[POST] https://mock-api.appsmith.com/echo/post

While testing your Appsmith tool, sometimes you will need to invoke non-idempotent API endpoints such as Create card and Invite user. Instead of exercising the live endpoints, you can create an API datasource that relies on the Appsmith Echo API:

COPYfor(let i = 0; i < 5; i++) {
  echo_post.run(onSuccess, onError, params);
}

To mock HTTP status codes and errors, you might try httpstat.us.

Disposable Inbox

For testing user invites, I recommend a disposable email system like Mailinator or Temp Mail.

Git Repo

There's a companion repo that contains sample CSV files and supporting JavaScript code for this tutorial. You can also use Appsmith's Import-Export feature to migrate the demo application to your own account.

Brex APIs

The base URL for all Brex APIs is https://platform.brexapis.com.

1. Use this URL to create an authenticated API datasource called brex-prod.
2. Select Bearer Token for the Authentication Type.
3. Supply your Brex user token.
4. Click Save.

You should now have a datasource that we can use to create Queries.

New query objects will show in the left side panel, under QUERIES/JS. For example, here I've created a query for the Get current user endpoint:

Now, you can create new query objects from the brex-prod datasource. The following search box will appear when you click the + icon next to the QUERIES/JS menu option:

Going forward, we'll create several queries to support our application.

Onboarding API

The Onboarding API allows you to refer your customers and personal contacts to Brex. You might use the Create referral endpoint as part of the Brex Referral Program.

Unlike the Get current user endpoint, Create referral is a POST endpoint that requires a JSON body.

Later on, we'll discuss this.params. For now, understand that it is possible to run the create_referral query object and pass parameters into it, like so:

COPYconst params = { referral_code, email, first_name, last_name };
return create_referral.run(params); // always return the promise

Team API

The Team API lets you manage users, departments, locations, and cards. For this example, we'll manage users and cards, starting with the Invite user endpoint, which allows you to invite a new team member as an employee. Through the dashboard, Brex admins can assign various role types to the employee, but team members created through this endpoint will bear the Employee role type by default.

Your invite_user query object will POST to /v2/users. The request body should match the following:

COPY{
   "first_name": {{this.params.first_name}},
   "last_name": {{this.params.last_name}},
   "email": {{this.params.email}}
}

Idempotency

Under the Headers tab, be sure to generate a random string value for the Idempotency-Key:

Idempotency is well beyond the scope of this tutorial. But, in short, the idempotency key ensures that the API server will only process a non-idempotent request once.

For a moment, consider network interruptions that may cause downstream services (or even the application itself) to perform a retry. Without an idempotency, the server would attempt to process the same request again. The idempotency key helps the API server keep track of whether the operation was already performed or not.

Notice that the code to generate the Idempotency-Key includes a reference to Lodash: _.random(1000). Lodash, among other libraries, is built into the Appsmith platform, which is super handy.

Create Card

The create_card query object also requires an Idempotency-Key, and will POST to /v2/cards. The request body is:

COPY{
   "owner": {
       "type": "USER",
       "user_id": {{this.params.user_id}}
   },
   "card_name": {{this.params.card_name}},
   "card_type": "VIRTUAL",
   "limit_type": "USER"
}

The Create card endpoint also accepts other parameters, card types, and limit types, so check out the developer docs. There's also a spend_controls parameter that allows you to define a spend limit for the card. This limit can be set to refresh each month, quarter, year, or never (for one-time use).

Terminate Card

We can also reference {{this.params...}} in the query object URL. For example, the terminate_card query object will POST to the Terminate card endpoint, which has a URI parameter, {{this.params.card_id}}:

COPY/v2/cards/{{this.params.card_id}}/terminate

The request body for terminate_card:

COPY{
 "description": "demo",
 "reason": "OTHER"
}

There are also other reason codes such as CARD_LOST, CARD_NOT_RECEIVED and FRAUD, but for this example, we'll stick with OTHER.

Note that the Idempotency-Key is not required, because card termination is idempotent by definition. In other words, terminating a card multiple times has the same effect as terminating a card once.

Payments API

The Payments API allows you to initiate and manage payments and vendors from your Brex Cash account. We'll use the Create vendor endpoint as an example. In a production application, you would also include vendor payment information to enable ACH, wire, or cheque payments from your Brex Cash account. But, for this example, we'll omit the vendor's payment type. You can also add vendor payment information from the dashboard - How do I manage my vendors on Brex Cash?

Create Vendor

The create_vendor query object will POST to /v1/vendors. This endpoint requires an Idempotency-Key and the request body is:

COPY{
  "company_name": {{this.params.company_name}}
}

Delete Vendor

The delete_vendor query object will send DELETE requests to /v1/vendors/{{this.params.vendor_id}}. There is no request body.

Initial Data Loading

The following query objects will enable us to populate tables on page load. To ensure that Appsmith pulls the initial dataset each time, you can configure each query object to run on page load:

QueryEndpointget_cardsList cardsget_usersList usersget_vendorsList vendors

This option is located under the Settings tab inside the query object:

Appsmith Pages

The goal of our app is to provide a user-friendly way to create Brex credit cards, vendors, invites, and referrals. In this section, we'll envision the page layout and breifly touch on a few key points. You'll use a drag/drop interface to build the UI. But, before we get started:

1. Rename Page1 to Cards.
2. Add a page for Users and another page for Vendors.
3. Move get_users, create_referral and invite_user from the Cards page to the Users page.
4. Move get_vendors, create_vendor and delete_vendor to the Vendors page.

Now that our work is cut out, we'll begin creating each page.

Cards

Use the Appsmith widgets to create the following page to your ability. Note that the green Upload widget is a file picker, not a button.

Make sure the Data Format property is set to Text for each file picker:

Users

The Users page has a Tabs widget with two tabs, one for invites and one for referrals. You can drag each Table widget into the appropriate tab.

The Referrals tab:

Vendors

The Vendors page is almost identical to the Cards page. In fact, you can select every widget on the Cards page and copy/paste them onto the Vendors page. Of course, you'll also need to adjust the widget properties and table data.

Application Logic

Our application is starting to take shape. We now have an end-to-end skeleton system. Next, we'll incrementally add functionality to each page, incorporating the query objects we created earlier. We'll also learn about JS Objects, which allow us to define handler functions that we can bind to the page widgets.

Page Load

Earlier, we created a few query objects to run on page load. Now, we'll use them to populate our table widgets. As stated in Running APIs on Page Load, "If we connect an API response to a widget, Appsmith automatically runs that API on page load..."

In effect, this means that binding {{get_cards.data.items}} to the Table Data property for tbl_cards will populate the table. However, for this tutorial, we only need the card id and card_name fields from each card. So, we'll write some additional code to remove the unwanted fields:

COPY// bind this to tbl_cards - Table Data property
get_cards.data.items
 .filter(card => card.status == "ACTIVE") // only get ACTIVE cards
 .map(card => ({ id: card.id, card_name: card.card_name }))

Likewise, we initialize tbl_invites and tbl_vendors:

COPY// bind this to tbl_invites - Table Data property
get_users.data.items
 .filter(item => item.status == "INVITED") // only get INVITED users
 .map(item => ({ email: item.email, name: `${item.first_name} ${item.last_name}` }))

// bind this to tbl_vendors - Table Data property
get_vendors.data.items.map(vendor => ({ id: vendor.id, company_name: vendor.company_name }))

JS Objects

In earlier versions of Appsmith, there was only support for writing small code snippets to data bind page widgets. But, Appsmith now supports JS Objects, which enable multi-line JavaScript code and reusable functions.

To create a new JS Object, click the + icon next to the QUERIES/JS menu option, and find the New JS Object option.

Create an object for each page: cards_object, users_object, and vendors_object.

Cards

Replace the contents of cards_object with the following code:

COPYexport default {
   upload: () => {
       showAlert('Cards created!', 'success');
   }
}

We'll revisit this code shortly. At the moment, let's go back to the page editor and bind {{cards_object.upload()}} to the file picker's onFilesSelected event:

If you are curious, deploy the application and upload a file to the Cards page. You should receive a Cards created! notification.

The cards_object.upload method will leverage the create_card query to issue virtual cards. To follow along here, you'll need your Brex cuuser ID, which you can obtain by running the get_current_user query. Place your ID into a CSV file, formatted like this:

COPYcard_name,user_id
Michael Collins,cuuser...

Later on, we'll upload this file to the Cards page.

Reading CSV Files

The following code will read lines from a CSV file, assuming that your file picker is called fp_cards:

COPY// get the first uploaded file
const csv_file = fp_cards.files[0];

// collect each line from the csv file
const csv_lines = csv_file.data.split("\n");

// omit csv header and blank lines
const rows = csv_lines.slice(1).filter(row => row.length > 0);

Simplified:

COPYconst rows = fp_cards.files[0].data.split("\n")
                    .slice(1).filter(row => row.length > 0);

Iterating API Calls

To smoothen the discussion around asynchronous code and JavaScript promises, we'll be using a helper function called loop to iterate API calls. For reference, the full loop implementation is here, but we'll strictly focus on understanding its usage:

COPY// visit each row
return cards_object.loop({
 collection: rows, // assign rows as the collection
 onReadLine: () => {
   // do this on every row
 },
 onEnd: () => {
   // do this after the loop ends
 }
})

The loop helper calls onReadLine once per row. On each loop iteration, we can pass card_name and user_id into create_card.run():

COPYonReadLine: (card_name, user_id) => {
 // be sure to always return the promise,
 // otherwise this will not behave as expected
 return create_card.run({ card_name, user_id });
}

When the loop ends, we report the number of cards created successfully, and then call get_cards.run so that the new items appear in tbl_cards:

COPYonEnd: () => {
 const count = appsmith.store.success_count;
 if (count > 0) {
   showAlert(`${count} cards created!`, "success");
 }
 get_cards.run();
}

Once you re-deploy the application and upload a valid CSV file, your card will show in the Brex dashboard, under Card > Manage cards:

Last, but not least, we have cards_object.terminate. Bind this method to btn_terminate_cards - onClick event:

COPYterminate: () => {
   // clear variables
   cards_object.reset();

   // get comma-separated values from the textbox
   const rows = txt_card_ids.text.split(",").map((id) => id.trim());

   // visit each row, terminate card
   return cards_object.loop({
     collection: rows,
     onReadLine: (card_id) => {
       return terminate_card.run({ card_id });
     },
     onEnd: () => {
       const count = appsmith.store.success_count;
       if (count > 0) {
         showAlert(`${count} cards terminated!`, "success");
       }
       get_cards.run();
     },
   });
 }

Users

The Users page involves many of the same techniques and concepts discussed earlier. However, there are two tables involved here. We'll need to refresh one of the tables, depending on whether the CSV file is intended for referrals or invites.

I should mention that an invite asks someone to join your Brex account as a team member, whereas a referral prompts someone to sign up for their own Brex account.

Invites vs Referrals

Creating an invite sends an email to the recipient:

On the other hand, creating a referral generates a personalized invite link to an onboarding flow. You can find your referral code at dashboard.brex.com/referrals. When the prospect visits their personalized link, they will see a pre-filled form containing data values sent through the Create referral API call:

When the CSV file is uploaded, we can inspect the header row to determine if the file contains referrals or not - referrals have a referral_code column while invites do not:

COPY// invites csv header: "first_name,last_name,email"
// referrals csv header: "referral_code,first_name,last_name,email"
let is_referral = rows[0].indexOf("referral_code,") == 0;

// ...

return is_referral
       ? users_object.createReferrals(rows)
       : users_object.createInvites(rows)

For brevity, the full implementation code is shown here.

Referral Data Store

Something to note about the Create referral endpoint is that it returns the referral id and the referral_signup_url, but it does not return the applicant details (first name, last name, and email). Instead, the API consumer is expected to store and handle their own Brex referrals securely, as they contain sensitive information about the applicant.

Why do we need a data store?

At first glance, this might seem trivial. Perhaps, we don't need a referral data store. Maybe we could just create the referrals and then use the List referrals endpoint to load the recent items. However, the List referrals endpoint does not return any applicant information either.

So, by design, we need the data store to establish a link between the applicant details (passed into create_referral) and the referral id (returned from create_referral).

Without a data store, it's also not possible to seed tbl_referrals on page load. The List referrals endpoint doesn't return any applicant information, so we need to query the data store instead and then use that response data to populate tbl_referrals.

Implementing the data store

I'm using a serverless database (Fauna) to host the data store for this tutorial, but the type of database you use is unimportant. It is only important that the data store is accessible via a REST API, so that we can easily connect to the data source and create query objects:

QueryEndpointadd_referralgraphql.us.fauna.com/graphqlget_referralsgraphql.us.fauna.com/graphql

Fauna exposes a GraphQL API. Please see the the wiki for more details about this as it relates to add_referral and get_referrals.

Storing the referral data

Now that the data store is in place, we can persist the referral id and referral_signup_url along with the applicant information:

COPY// get response data and csv row
onSuccess: ({ id, referral_signup_url }, row) => {
 // get applicant info from the row
 const [referral_code, first_name, last_name, email] = row;
 // store the referral data
 add_referral.run({
   id,
   name: `${first_name} ${last_name}`,
   email,
   referral_signup_url,
 });
},

Get referrals on page load

Also, remember to configure get_referrals to run on page load:

COPY// bind this to tbl_referrals - Table Data property
get_referrals.data &&
get_referrals.data.data &&
get_referrals.data.data.allReferrals.data

Vendors

Implementation of the Vendors page is left as an exercise for the reader. Here are some things to keep in mind:

1. The fp_vendors file picker needs an event handler for onFilesSelected.
2. The create_vendor query object has a company_name parameter.
3. The delete_vendor query object has a vendor_id parameter.
4. Bind {{appsmith.store.vendors_arr}} to tbl_vendors.
5. Uploading vendors.csv should create three new vendors in your Brex dashboard.

The full vendors_object implementation is here. You can also create multiple vendors by using the bulk upload tool in your Brex dashboard.

Error Handling

Thus far, we've only explored the happy path. If we return to the Cards page and upload cards.invalid-user-id.csv the create_card query will fail. Thankfully, Appsmith will handle this exception and display an error message.

But, imagine if you were to upload the following CSV file:

COPYcard_name,user_id
Space Mountain,invalid-user-id
Cargo Dragon,cuuser...
Planet Express,invalid-user-id

In this case, the API call would fail on Space Mountain and Planet Express, but the default error handling would not be able to detect which rows failed. To correct this, we'll need to implement the error handling ourselves.

During cards_object.loop(), whenever onReadLine fails, the onError function will trigger. We can implement onError to display a message:

COPYonError: ({ row }) => {
 const [card_name] = row;
 const message = `failed to create card "${card_name}"`;
 showAlert(message, "error");
 return message; // return message to the loop helper
}

The loop helper also keeps track of errors. There's a has_errors flag, which is a boolean that indicates when any errors have occurred. There is also an errors_arr collection where we store each error message for later display in tbl_errors.

The has_errors flag will determine if the red Icon Button is visible. Set its Visible property to {{appsmith.store.has_errors}}. Also bind its onClick handler to {{showModal("error_modal")}}. This button will launch a Modal that contains a table bound to {{appsmith.store.errors_arr}}:

Conclusion

The beauty of Appsmith is that you can migrate this application to your own workspace and repurpose it for your own needs. Here, we've touched on a few Brex API endpoints. We've also learned how to create an Appsmith dashboard that allows Brex admins to create and terminate Brex cards and refer and invite Brex users.

I encourage Brex admins to build and share custom Appsmith workflows that tap into the Brex APIs. There is much value in simplifying the development process where we can. Low code tools like Appsmith can provide practical advantages to Brex admins who need them, but they can also change the perspectives and attitudes of those who do not.

‍

‍

The views expressed in this post are my own and have not been reviewed or approved by my employer (Brex, Inc.).

‍

A contact application is a database that collects and stores user contact data. The contact app is a widely used application, similar to the contact application on your mobile phone, that stores data such as user contact names, phone numbers, email addresses, etc. Businesses can use this application to manage their customers' information and store personal contact data.

This tutorial primarily focuses on integrating a contact API in Python using the Django Rest Framework. This application will have functionalities that allow users to create, edit, and delete contact data. While this goes on the backend of the application, we will create the interface of our application using Appsmith's pre-built UI widgets.

Appsmith is a powerful open-source low-code framework for creating internal tools with features such as pre-built UI widgets that connect to multiple data sources: APIs, databases, GraphQL, etc., with support for OAuth 2.0 and CURL.

Appsmith allows you to create web application interfaces with little or no code. You don't need to be a developer to create an application dashboard using Appsmith. Using Appsmith's drag-and-drop UI widgets, we will create a dashboard for our application.

Prerequisites

• Basic knowledge of Python.
• Good understanding of Django
• An Appsmith account

Project setup: Building a contact book API

Creating a virtual environment

We will begin by creating a virtual environment for our project. The essence of a virtual environment is to create a separate environment for all installed packages and dependencies.

The command below shows how to create a virtual environment in your terminal:

pip install virtualenvvirtualenv env

‍

Activate our virtual environment

For Windows:

env\Scripts\activate

For Mac/Linux:‍

source env/bin/activate

Virtual environment activated!

Cloning a repository

Next, we will be cloning an already existing repository built with the Django Rest Framework. This repo contains a contact application with functionalities that allow users to create, edit, and delete contact data.

To clone the repo, run these lines of code in your terminal:

git clone https://github.com/Anitaachu/contactAPI.gitcd contactAPI

‍

Dependencies installation

Once this is completed, we will be installing a couple of dependencies and packages required for this project using the pip These packages are already contained in your cloned requirements.txt file.

Run this command to create a requirements.txt file and install the packages:

pip install -r requirements.txt

Next, migrate the data to the database and create a superuser. To this by running the following commands:

python manage.py migratepython manage.py createsuperuser

Testing the application

Next, let's test this application locally, using the following command: python manage.py runserver

In your browser hit the endpoint http://127.0.0.1:8000/. You can also access the admin page using this URL http://127.0.0.1:8000/admin/

Great! Our application is running! 🎉

Getting started with Appsmith

With our API up and running, the next phase of this project is creating an interface using Appsmith pre-built widgets and connecting these widgets to our data source (API). We will be doing this by writing some queries that will be implemented with the widgets.

Let’s dive in!

Connecting to a Datasource

It takes a few minutes to connect to a datasource, and you can develop tools on the database of your choosing. You can either connect to a database, an API, or both. For this project, we will connect Appsmith to a deployed API using the API we earlier created. The deployed API can be accessed using this URL.

• Ensure the API you intend to connect is already deployed. You can use Heroku or any other deployment service.

Add the URL of your deployed API and click on ‘run'.

Datasource successfully connected!

Now let’s get to work!

Creating a dashboard with the Appsmith widget

With Appsmith, you can build a fully customized interface with no code. Appsmith has many pre-built UI widgets such as tables, forms, buttons, boxes, images, and more, all available to help you create exciting applications.

Let’s begin! Firstly, let's create a table that will display our contact data.

Follow these steps:

• From the widget section, drag and drop the table widget onto the canvas in this manner:

• Edit the default data from the property pane and add the column names as contained in our database. As contained in our database, the column names include "_first_name", "last_name", "phonenumber", "email", "address", and "birthday."

• Next, edit the Table Data property in Table Widget’s property pane to {{Contact_API.data}} in other access data from the query.

Next, we will create a button widget that will be used to create contacts. When a user clicks this button, it navigates to another page that contains a form widget.

Adding a button and form widget

Drag and drop the button widget into the canvas.

In the label section, change the ‘submit’ label to 'create contact'. Next, edit the button widget, in the Events > onClick > Navigate to.

Under "Page Name or URL" type in 'Page 2'. By doing this, when we click on the "Create contact" button, it navigates us to a new page that will take us to the contact form.

Lastly, click on the "+" icon at the top left to add a new page.

Form widget

This new page will contain a contact form that will be used to add a new contact, using input fields where users can enter the contact data they wish to create.

Let’s create a form using a form widget with these few steps:

• From the widget section, drag a form widget into the canvas.

• Change the "Form" label to "Create contact".
• Next, drag and drop text widgets into the form widget we just created. Edit the label name to first name, last name, etc. In this manner:

• Add the input widget to the canvas. This widget will allow the user to enter their contact name, phone number, address, etc.

• Add functionality to the submit button. To do this, create a new query on the right side of the page.

• Create a new datasource and enter the URL of the datasource: https://contactapp-api.herokuapp.com/. You can also change the name of this datasource to be able to identify it.

• Add connect the query to the submit button.

• Lastly, set the body property of your datasource to:

{"first_name": "{{Input1.text}}","last_name": "{{Input2.text}}","phone_number": "{{Input3.text}}","email":"{{Input4.text}}","address":"{{Input5.text}}","birthday": "{{Input6.text}}"}

‍

By doing this, a connection will be created between the widget and the datasource.

Now let’s test out our widget to ensure that it's functioning. Firstly, on the contact dashboard, click on the "Create Contact" button. The button would redirect to another page that contains a contact form.

‍

Fill out this form and hit the submit button. You will notice that contact details that are added through the form are displayed on our application dashboard.

‍

‍

So far, we have been able to create a form widget where users can create contacts, and these contacts will be displayed on our dashboard.

Great!

Asides creating contacts, contact applications also have features where users can edit contacts or delete contacts that are no longer needed. Let’s add the edit and delete contact buttons and write queries that will connect these buttons to our datasource.

Edit contact button

In our application dashboard, add an edit button. This button will also be connected to our datasource. Let’s work on our edit button.

• Step 1: In the application dashboard, create a new column named "edit contact".
• Step 2: Next, click on the Settings symbol in the “Edit Contact”* column to add some functions to it.
• Step 3: Replace the following:
• Column Type: Choose ‘Button’.
• Label: Replace ‘Action’ with ‘Edit’.
• onClick: Choose ‘Open modal’
• Lastly, under 'Modal Name', create a new modal.

When you click on the edit button, it spins up a modal. We will edit this modal and add some queries to it to enhance its functionality. To do this:

• First, create a text and input widget similar to that of the form widget in the modal. This way:

• Add a new datasource and enter the URL of the datasource.

Now you can edit a contact by clicking on the edit button and adding new data to replace the former contact data.

For the delete function, we simply repeat the same process as that of the edit button. However, the modal of the delete may differ. The Delete modal should appear like this:

Just as we did for the first modal, create a query that will connect the delete button widget to a data source.

‍

‍

Set the body property of your datasource to: {{Table1.selectedRow.id}}

Lastly, execute a query under Events > onClick. This button will query data from the deleted datasource you have created.

‍

‍

Our widgets and datasources are well implemented and functioning properly.

Deployment

So far, we have done a great job. The last phase of this project will deploy our application. On the right section of the dashboard, click on 'Deploy'. You can also deploy this application by connecting to a Git repository.

Our application is deployed! 🎉

Finally, to test the work we have done so far. Try creating a new contact:

‍

‍

We will also test the delete button to ensure it's working as well:

‍

‍

Successful!

In this tutorial, we created an amazing user interface for our contact application with low code. I hope you had fun building!

Happy coding!🙂

‍