In the previous chapter, ‘How to Connect the Xero API to Appsmith’ we created an Appsmith application which connected to your Xero account through the oAuth API and displayed a list of invoices on a table. In this chapter, we’re going to extend that application further by adding actions to enable the user to approve an invoice. The previous chapter is available here in the news and resources section of the Tick 9 website.

Requesting New Permissions

Up to now, we created a read-only Xero application, however in this chapter we’ll be writing changes back to your Xero account. So, we’ll need to request additional permissions from Xero.

Under ‘Datasources’ on the left-hand side of Appsmith, click on your Xero API connection and press the ‘Edit’ button on the top-right. In the ‘Scopes’ field, simply update the value with the below:

openid,

accounting.transactions,

profile,

offline_access,

accounting.contacts.read,

email

Then, click on the “Save and Re-Authorize” button to reauthorize with Xero with the additional permissions enabled.

Cleaning Up

If you’re following from the previous chapter, when you bind an array of objects to a table, it will by default, display every field from the objects in the array. So let’s clean this table by hiding the columns we no longer need.

In the ‘Edit’ mode, click on the table on the page, and under ‘Columns’ press the button on the far right to hide columns you don’t need.

Now you’ve tidied up the columns, do you want to display every invoice, or only the invoices which are awaiting approval? If you require the invoices only needing approval, go over to the getInvoices query, select the Params , create a new row with the name Statuses with the value DRAFT,SUBMITTED . Once you re-run the query you will find that the table will show only the invoices which require approval.

Creating an Action Menu

Whilst selecting the table, on the right hand side, go to the bottom of the ‘Columns’ list and click on ‘Add a New Column’. Then, under the ‘Column Type’ menu, select the item ‘Menu Button’.

On our menu, the user will have a choice of two actions, ‘Authorize’ which will set the status of the invoice as ‘AUTHORIzED’ in Xero, and ‘View Line Items’ which will open a dialog for the user to view the individual line items of the selected invoice.

You may choose the customise your menu item further by setting an icon, background and text colours.

Viewing Line Items

Next up, let’s create the dialog which will appear when the user clicks on the ‘View Line Items’, go back to the ‘Widgets’ section on the left hand side and drag-and-drop a ‘modal’ widget and give it a name (e.g lineItemsDialog). Then, within the same modal, drop a ‘table’ widget inside of it. Once placed comfortably within the dialog, bind the table to the following data source {{invoiceTable.selectedRow.LineItems}} . This will now display the list of line items on the table.

Now, let’s make the dialog open when we click the menu option, go back to your main table widget on the page and find the menu column you just created. Go to the ‘View Line Items’ menu option, under the onItemClick menu option, select Show modal and then click on the dialog you created (e.g lineItemsDialog).

Great, deploy your application and try out the dialog you just created.

Approving an Invoice

Let’s now connect up the ‘Authorize Invoice’ button to an API action, so when you click on the button, it will send the request to the Xero API to change the status of the invoice to the ‘AUTHORIzED‘ type.

On the left-hand side, click on ‘Add Query/JS’ and select on ‘New Xero Query’. Set the name approveInvoice and set POST as the HTTP action. In the URL field, we want to bind the selected Invoice ID to the query – so paste the following:

https://api.xero.com/api.xro/2.0/Invoices/{{invoiceTable.selectedRow.InvoiceID}}

Then we need to apply the same standard headers to inform Xero of the organization, and the type of data we’re sending/receiving.

Next, click on the ‘Body’ tab, select the ‘JSON’ switch and paste the below JSON body. This will provide Xero with the fields which we’re changing, in our case, the status of the invoice.

{ "Status": "AUTHORIZED"}

Then, click on the ‘Settings’ tab and enable ‘Request confirmation before running API’ – this will prevent accidental approval of invoices, by confirming with the user before committing to the action.

Finally we need to wire up the button on the table menu to the API action. Return to your page, select on the table and find your ‘Action’ column. In the ‘Authorize’ menu option, click on the JS button alongside the onItemClick label and paste the below JavaScript.

{    {        approveInvoice.run(            () => {                showAlert('Great, your invoice was approved!', 'success');                getInvoices.run();            },            () => {                showAlert('Oops! An error occured. Please check your API connection and try again.', 'error');            }        )    }}

Let’s break-down what this code snippet is doing. When the user clicks on the menu button, it will run the approveInvoice query. In the first argument, the code is executed when the request was successful, so we’ll display a notification to the user informing them of the successful action, and then refresh the getInvoices query. The second argument will execute on failure, in this case, display a notification to the user that an error has occurred and that they may want to try again.

Great, that’s it! If everything has gone to plan, you should be able to deploy the application, click on the menu and approve an invoice on the table.

Thank you for reading! If you’ve got any questions, or have an issue following this guide, then please don’t hesitate to reach out by emailing luke@tick9.co.uk.

‍

In this guide – we’ll walk you through connecting your Xero account to Appsmith to create an application around its data.

Setting up Appsmith

Appsmith can be downloaded and hosted within your local business network. But this guide won’t cover the installation process; if you want to try Appsmith, you can use the cloud service provided by Appsmith or speak with a member of our team for advice on installing Appsmith within your local network.

Open Appsmith, create a new application called “Xero – Invoices,” and click “Build with drag & drop.” You’ll see an empty page with “Drag and drop a widget here.” We now need to connect Appsmith to your Xero API account.

Step 2) Setting Up the Data Source

On the left-hand side of Appsmith – you’ll find a toolbar combining “Widgets,” “Queries/JavaScript,” and “Data Sources.” Let’s start by connecting to your business account on Xero through the Xero API.

Click on the plus button to the right of “Datasources” and then select “Authenticated Account”. Appsmith will now create a new REST API datasource within your application. But before using it, we need to configure the datasource to communicate with Xero’s OAuth Authentication correctly.

1. In the URL field, paste the base address of Xero’s API (https://api.xero.com/).
2. Click on the Authentication Type dropdown, and select OAuth 2.0.
3. Scroll down to Grant Type and select Authorization Code.
4. Within the Access, Token URL field, paste the followinghttps://identity.xero.com/connect/token.
5. We now need to provide the scope of our API access. In this guide, we’ll need to access our organization's contacts and accounting data. So paste the following values into the field OpenID, offline_access, accounting.transactions.read,profile,accounting.contacts.read,email.
6. Within the Authorization URL, paste the following https://login.xero.com/identity/connect/authorize.

In order to get our Client ID and Client Secret – we need to make a quick diversion to Xero. Head over to the following link and click on the blue “New App” button once logged in. Give your application a name (e.g., Northwinds Appsmith), and then paste your generated “Redirect URL” from Appsmith into the box at the bottom.

Once created, go into the registered application and select “Configuration” on the left-hand side. Take note of the Client ID and Client Secret (if you don’t have one, click on the generation button). Now return to Appsmith and paste these values into the respective fields.

We should now be ready to authorize your access with Xero. Simply click on the “Save and Authorize” button at the bottom of the page and wait a few moments until you’re redirected to Xero (as shown in the screenshot above). Select the organization you want to grant access to, and click “Allow access.” You’ll then be taken back to Appsmith.

Step 3) Selecting Your Organization

Now you’ve successfully connected Xero to Appsmith – we can retrieve data from your Xero account. Let’s begin by getting the information of organizations from your Xero account.

On the left-hand side of Appsmith – click on your data source (e.g. Xero API) and click on New API +. You’ll now be taken to Appsmith’s query editor for REST APIs. In the URL bar, type in /connections after the URL. Appsmith will automatically handle the oAuth 2.0 authentication in the background and populate the request with the required headers. Once you click the Run button, you’ll see the JSON response from your request at the bottom of the page. It should contain an array of objects listing the Xero organizations you’ve granted access to.

Let’s now create a drop-down list on your application so that your users can select the organization which they want to view data from. On the left-hand side, under “Pages” select on the first page (e.g Page1) and then click on Widgets .

Click on your widget, and within the Options code block we need to bind the values from our API response to the Select widget. Some JavaScript code may have already been populated, which creates an array of items with two values for the widget, a label value to be displayed to the user and the value which should be the ID of the organization. Paste, or enter the below code into the Options block.

{{getTenants.data.map( (obj) =>{ return  {'label': obj.tenantName, 'value': obj.tenantId } })}}

Before we move onto the next stage, make sure to provide the widget with a name (e.g tenantSelect – we will need to reference back to the widget in our next query!

Step 4) Getting Invoice Data

Let’s now retrieve the invoice data from your Xero account! On the left-hand side, click on your Xero datasource and click on New API+. Name the query getInvoices and press return.

In the URL bar, enter /api.xro/2.0/Invoices after the base address.

Under the Headers tab, we need to specify the organization in Xero which we want to pull back the invoices for. You’ll see two rows under this tab, under Key 1 enter Xero-tenant-id and then in Value 1 we need to bind to the selected value from the drop-down we previously placed on the page.

Enter the value

{{tenantList.selectedOptionValue}}

Next, we need to tell Xero that we want the API to respond with JSON, and not XML which is the default behaviour. To do this, simply enter Accept in the next row under the Headers tab, and enter application/json .

Now press “Run”, and if everything has gone to plan – the API would have responded with a list of invoices from your Xero account.

Step 5) Displaying Invoice Data

We’ve now created a query which retrieves invoice data from your Xero account, but let’s now create a table which will display the list of invoices.

On your page, drag and drop a button next to the select button. In the Label field, give the name Refresh . When we click this button, we want Appsmith to execute the getInvoices query which we created in the previous stage – so under Events on the right-hand side, click on the drop-down under onClick and select getInvoices under Execute a query . Then let’s display a useful alert to the user informing them that the data has been refreshed, under onSuccess click on Show message and enter Successfully retrieved invoices from Xero .

Next, find the Table widget and place it on your page. Give the widget a useful name, such as invoiceTable . Then the right-hand side, under Table Data bind to the array of invoices from the API response by entering the following value {{getInvoices.data.Invoices}} . After a few moments, the table will display the list of invoices from your Xero account.

However, this will only display the first page of invoices from the Xero API which is limited to 100 records. In order to display all your invoices, you will have to enable server side pagination – on the right-hand side, scroll down to Server Side Pagination and make sure this switch is enabled.

Return to the getInvoices query – under the Pagination tab select on Paginate with Table Page No . And then go to the Params tab, under Key 1 enter page , and then in the value input bind to the current page of the table with the following value {{invoiceTable.pageNo}} .

Then back on the table widget, run the getInvoices query when onPageChange is triggered. Once this has been enabled you can click on the right arrows at the top of the table to navigate through all your invoices.

Next Steps

That’s it for this article! Click on the “Deploy” button at the top-right to see the the final version of your application.

In the next article, we’ll delve further into how we can modify our application to write changes back to Xero.

Airtable is a popular choice for developers who want to manage tabular data easily. While it's easy to use Airtable as a backend, it can be challenging to build a custom UI from scratch. This is where Appsmith comes in. With Appsmith's native Airtable integration, you can create dashboards, CRUD apps, and internal tools in minutes.

In this tutorial, we'll use an Airtable base to build an issue tracker. We'll start by creating a database in Airtable and then importing our data into Appsmith and building on top of it using JavaScript. 

With this application, users can:

• Connect to their Airtable base.
• Add a new bug/issue
• Update the existing issue
• View the current bugs in the management tool.

Let's jump in!

Step 1:  Getting started

Create a new Appsmith App 

First, we need to create a new application in Appsmith so we can build it out.

• Visit https://www.appsmith.com to create a new account or login in to an existing one.
• Create a new application in your preferred organization and edit it.

Connect to Airtable

Now, we need to add your Airtable datasource to the app. In this case, we will clone an existing Airtable sample to provide our data. 

• Create a new datasource by clicking ‘+’ on the Datasources tab from the entity explorer and then select Airtable.

• Rename the datasource. Select API Key as the Authentication Type and enter your API Key in the input field. Hit Save.

• For the database, go to https://airtable.com/shr5ZklhBlptzRX4C and click "Use this data." Alternatively, you can also fork this Airtable template database.

• Choose a workspace and a base in the dialog box and hit 'Create Table.'
• Go to https://airtable.com/api and select the base titled 'All bugs and issues'
• In the 'Introduction' section of the documentation, copy the Base ID (highlighted in green in the picture below)

• Select the "Bugs and issues Table" on the left pane. Copy the table name highlighted in green in the image below.

Step 2 : Set up UI for the App

Wireframe

Here is the wireframe for what we are trying to create.

Including the modal that is used to add new bugs.

Create your widgets

Using the wireframe as a guide, create the UI for the application using the drag and drop editor. Here is a suggested process.

• Choose a preferred theme from the Theme properties option in the property pane on the right.
• Add a container widget with a text widget for your app's header.
• Three (or as many as you like) stats boxes on the canvas to display essential statistics at a glance.
• A container with a text, icon button, and list widget for showing a list of all the issues.
• A container with text, button, select and list widgets for showing details of the selected issues.
• A modal with a text, icon button, and JSON form widgets for adding a new bug entry.

Step 3 : Binding data on widgets

Listing records on the List widget

The list of bugs/issues should look something like this. In order to populate the data, create a new query and bind the results to the text.

• Create a new query from the left pane which uses the Airtable datasource you created in the first step. Rename the query to getBase and choose the Commands to be List records. We chose this command because we would like to list all the bugs and issues in our app. Enter the Base ID and Table Name you copied in the steps above. 

• Hit Run and you should see a JSON response generated which lists the records. 
• To bind this response to the list widget, we would first create a JSObject that maps the fields from the records. Create a new JSObject and paste in the following snippet.

getAirTableFields: () => {
  return getBase.data.records.map((record) => {
    let row = record.fields;
    row["id"] = record.id;
    return row;
  });
};

‍

• In this JSObject, we get the response from the GetBase query, map the fields, and get the id for every row in the table. 
• Bind the list with this data using  {{JSObject1.getAirTableFields()}}
• For getting the bug name and the source, bind the text widgets within the list with {{currentItem.Name}} and {{currentItem.Bug_source}} respectively.

Getting details of the selected bug

When we click on an item from the list, we should populate the view container with details of the selected issue

‍

• In order to get details about the selected bug on the container placed on the right, we would just use the {{List.selectedItem.attribute}} for all the details you wish to display. For example, The bug title can be displayed using {{List1.selectedItem.Name}}, for Associated features write {{List1.selectedItem.Associated_features}}, For priority write {{List1.selectedItem.Priority}}. So on and so forth. 

• For a closed/open bug field, use the ternary format to display the status. {{List1.selectedItem.Closed == '1'? "Closed": "Open"}}

• To bind the attachments for the selected bug, write {{List1.selectedItem['Attachments']}} to bind data on the list widget in the right container. 

• Just like binding the bug details, in the image widget enter {{currentItem.url}} in the property pane to display the image attached

• Use {{currentItem.filename}} and {{currentItem.type}} to display the file name and type on the text widget.

Displaying statistics on the statsboxes

These statsboxes should help display important information at a glance from this database. As the number of issues grows, this will give us a quick understanding of the status.
‍

• In order to populate the statsboxes with statistics, we would create a JSObject function that maps to fields and then to Priority within the same JSON response and check if the value is High, meaning the priority is set to high. What we get in the response is our desired statistic. 

highPriority: () => {
  const high = getBase.data.records.map((record) => record.fields.Priority);
  return high.filter((currentItem) => currentItem == "High").length;
};

• Bind this output in the text widget using {{JSObject1.highPriority()}}
• In the very same manner, write a function and bind the output for the number of bugs labeled open and critical. 

Adding a new Bug/Issue

When clicking the button to add an issue, a modal appears with a form that creates a new entry. It looks like this:

• Set the Modal to open on onClick of the icon button on the top right corner of the container on the left. 

‍

• Populate the JSON Form with source data by pasting the following.

{
"fields": { 
    "Bug Title": "",
    "Priority": "",
    "Assigned To":"",
    "Status": "",
    "Screenshots": [
      {
        "URL": ""
      }
    ],
    "Bug Description": "",
    "Bug Source": "",
    "Features Associated": "",
    "Created by": ""
}
}

‍
You can customize the field configuration as per your requirement. Here’s what the JSON Form looks like https://www.loom.com/share/1087b1e8932846feaf3dd03e8b3bb780

• To insert a new record, we’ll write a new query. 
  Create a new query and name it as InsertQuery. Choose the command to be Create Records. Add in your Base ID and Table Name. For the Records, bind the form data from the JSON form for every field. 

[
  {
    "fields": {
      "Name": "{{JSONForm1.formData.fields['Bug Title']}}",
      "Priority": "{{JSONForm1.formData.fields['Priority']}}",
      "Status": "{{JSONForm1.formData.fields['Status']}}",
      "Attachments": [
        {
          "url": "{{JSONForm1.formData.fields.Screenshots[0].URL}}"
        }
      ],
      "Assigned_to": "{{JSONForm1.formData.fields['Assigned To']}}",
      "Description": "{{JSONForm1.formData.fields['Bug Description']}}",
      "Bug_source": "{{JSONForm1.formData.fields['Bug Source']}}",
      "Associated_features": "{{JSONForm1.formData.fields['Features Associated']}}",
      "Created_by": "{{JSONForm1.formData.fields['Created by']}}"
    }
  }
]

• We’ll make a new JSObject function to run multiple queries when the Add Bug button is clicked in the form

addBug: async () => {
  InsertQuery.run();
  getBase.run();
  closeModal("Modal2");
};

• Now bind this function on onClick of the Add Bug button in the JSON Form.

Update fields of a bug

This query/button can help update the details of the bug. In this case, we update the priority and statuses. 

To update the priority and status of a selected bug, an Update Records query would be used. 

• Create a new query and rename it as updateQuery. Choose the command to be Update Records and enter your BaseID and Table Name. In the records field, paste the following to get the selectedOptionValue of the select widgets

[
    {
      "id": {{List2.selectedItem.id}},
      "fields": {  
        "Priority": {{Select1.selectedOptionValue}},
        "Status":{{Select2.selectedOptionValue}}
      }
    }
]

• Now, bind this query to run on onClick of the update button.

Final thoughts

And that’s it! You have your bug tracker application ready using Appsmith’s native Airtable integration 🎉

First, you created a new Appsmith application and connected it to Airtable. Then you created the UI for your app using the drag and drop tools in Appsmith. Finally, you tied the data from Airtable to the UI widgets. Your final app should look similar to this:

Please use this form to contact us if you have any template requests for internal tools that you need, and we will get to work! 

If you have any questions, contact us on Discord. You can also keep up with us on Twitter and YouTube.

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.

‍

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

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

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

Set-up the  Document Viewer Widget

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

To get started using this widget:

• You will first need to have an Appsmith account. You can log in if you have a pre-existing Appsmith account, otherwise, you can create a new Appsmith account.

• Once logged in, you are navigated to a dashboard. Here, create a new application. On the left sidebar, you will see a number of widgets offering different functionalities.

• Find the document viewer widget and drag it into the workspace.

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

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

‍

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

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

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

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

Here you will see options for APIs and different databases. 

‍

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

‍

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

‍

Select MongoDB from the datasource link

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

‍

Creating a MongoDB Database

‍

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

‍

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

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

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

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

On the cluster, click on the Browse Collections button.

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

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

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

‍

Below, I have three documents in my database collection.

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

Copy the selected area in the image below

Add the Newly-Created Mongodb as a Datasource

‍

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

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

‍

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

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

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

‍

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

{{Table1.selectedRow.Url}}.

‍

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

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

Approving and Rejecting Documents

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

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

Click on the widget to upload a file.

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

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

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

Sending Documents as Mails

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

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

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

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

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

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

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

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

‍

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

‍

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!🙂

‍

Different organizations rely on admin panels to manage, read and mutate data stored in their database. An admin panel is a console used to control features configurations of an application and manage content in that application. Admins in most organizations use these to help control and manage content and provide security for the application. Anyone looking to handle applications or site functions easily will need to use an admin panel.

Goals

In this tutorial, you will learn how to set up a Redshift database and create and deploy an admin panel template built on Appsmith.

Setting up our Redshift Database

To set up your Redshift database: Navigate your browser to Redshift. Click on the 'Get started with Amazon Redshift' button. Sign in if you already have an account; else, create a new user account.

Next, you need to set up a new role for RedshiftDB. Click on Services in the top navigation, then IAM. Choose Roles from the left navigation panel and click on Create role. In the AWS Service group, choose Redshift. Next, choose Redshift - Customizable, then select Next: Permissions. On the Attach permissions policies page, scroll down and select AmazonS3FullAccess. Enter the role name and description, then click on Create role.

Creating a Cluster

On the dashboard, click on ‘Create Cluster’ and enter your cluster name, cluster plan, admin username, and password in the form on the new page that opens up. Ensure to store your admin username and password as you will be using it to connect the database to the Appsmith app. You can finally click on the 'Create Cluster' button when you are done with that.

When the cluster is created, select the cluster and click on Actions; under the 'permissions' section, click on Manage IAM roles. When the management screen appears, select the IAM role you had created earlier, click on Associate IAM role, and click ‘Save Changes.’

With the newly created cluster, you can use a boiler template database. Click on the Query data button.

Testing the Database

Select the newly created cluster in the left panel to use a cluster.

By default, Redshift creates a dev database that we will use in this tutorial. You can create your table data using the following command and run it to view your output.

Click on the 'save' button to save this query, enter the name of your query and finally, click save to store it.

To access the database's credentials, click on 'security credentials' in the dropdown menu on your user name at the top navigation bar. Click on 'Create new access key,' then download the key file in the access key.

Creating the Appsmith Tool

After setting up the database, the next step is to create the panel on Appsmith.

• First, create a new application to get started.
• Next, click on the '+' icon on Datasources in the left navigation pane.
• Click 'Create new' and select Redshift from the list of databases.
• By default, our data source is named 'Untitled,' you can change the name to something of your choice.
• You can get your host address and port information from your cluster's information.

The endpoint is your hostname, copy and paste it into the required field without the port number at the end. By default, the port number is 5429, but you can find this information in your endpoint.

• Enter the name of your database. In this case, the name of my database is dev.
• Add your username and password to the authentication section
• Whitelist the IP instances in your Redshift database settings
• Finally, click on test, then save.

Creating our Table

With the Redshift data source connected, the data from the database can be displayed using a table. Navigate to the page and add a Table Component from the widgets section by dragging and dropping it onto the canvas. Next, open the table's property pane by clicking on the cogwheel icon on the table.

Before that, we need to set up a query for the data with the command:

COPY

COPY

SELECT firstname, lastname, total_quantity
FROM (SELECT buyerid, sum(qtysold) total_quantity
           FROM sales
           GROUP BY buyerid
     ORDER BY total_quantity desc limit 10) Q, users
WHERE Q.buyerid = userid
ORDER BY Q.total_quantity desc;

Then on our page, we can get this data. In Appsmith, we use the mustache syntax {{}} to add Javascript. To get our query, we use {{Query1.data}}

Setting up a Form with Update Functionality

Apart from reading the data to the table, you can use Appsmith to create and update functionalities.

To do this:

• First, we will create a form that will contain the input parameters for our table.
• This form will contain text fields for the firstname, lastname, and total_quantity.

Drag the form widget onto the canvas from the widgets section and add the fields for the firstname, lastname, and total_quantity.

You will need to link the form to the database to add the new data with the form setup. You will need to write a query to collate the data from the form when the submit button is clicked and append it to the table. To do this, click on the cogwheel icon on the submit button. Then set the onClick to execute a database query and then click on create a query:

COPY

COPY

INSERT INTO users
 ('firstname', 'lastname', 'userid')
VALUES
('{{Input1.text}}', '{{Input2.text}}', '1');

       INSERT INTO sales
       ('qtysold', 'salesid', 'listid', 'sellerid', 'buyerid', 'eventid', 'dateid')
VALUES
('{{Input3.text}}', '1', '1', '1', '1', '1', '1')

Here we entered the values of the input fields into the firstname, lastname, and qtysold. Also, I've set the id's to '1' for every entry; usually, you may use default values for this. You can now add new entries through the form to the table.

Updating and Deleting Table Data

Apart from reading and creating table entries, we can also update and delete data from our table. You must first target a particular row and set it as the default value in the form's input fields to update the data.

Above, we set the Default Text for the input field to {{Table1.selectedRow.firstname}} to always get the data from the firstname column of the selected row in the input field. We repeat this step for lastname and total_quantity by entering {{Table1.selectedRow.lastname}} and {{Table1.selectedRow.total_quantity}} as their default text respectively. We need to add new functionality via a button to update the table. Add a button from the widgets pane and name the ‘Update’ button.

Next, create a new Javascript query for the on-click handler in the button option pane. For our new query to handle updates, we have the following:

COPY

COPY

update users
set firstname='{{Input1.text}}', lastname='{{Input2.text}}'
where firstname='{{Table1.selectedRow.firstname}}';

The query above checks for the row where the firstname corresponds to the selected row's firstname column value. It then replaces the firstname and lastname columns with the value of the input fields.

Delete Functionality

We can add another button called Delete to our form to do this.

Create a new query as follows:

COPY

COPY

delete from public.users where firstname = '{{Input1.text}}'

When executed at the click of the Delete button, this query checks the user's table and deletes the corresponding row, which has a firstname with its value corresponding to the selected data.

You can try out the query and observe that the selected row is removed when the 'Delete' button is clicked.

Deploying the Redshift Template

To deploy the Redshift panel template, click on the deploy button at the top right of the navigation bar. You can then make it publicly accessible or share it with specific individuals via email. The admin panel can be further modified with added functionalities such as user authentication for restricted access and email integrations to inform the admin when a change has occurred.

‍

Proper feedback and community management is a significant part of building the organic reach of products, especially open source products. So, marketing teams might decide to build customized apps that contain valuable metrics. Products like Orbit allow us to get live data from our community forums to view them more concisely. In this tutorial, you will learn how to build an internal growth dashboard, where we’ll be able to display key metrics, including monitoring a Slack group and viewing existing members of the channel. The goal is to display specific growth metrics that enable better decision making in the marketing team.

First, we’ll be learning how to connect our Slack directly to Orbit, where we can view our information, activities, and members on their dashboard. Then, we’ll use Orbit’s REST API to view the data on our Appsmith dashboard.

This is useful in cases where we want to view our Slack data in a custom Appsmith app outside something like Orbit, mainly when the dashboard includes other external community data, not data from just the Slack channel.

Getting Started with Orbit

We’ll [signup to Orbit] (app.orbit.love) and create our Orbit’s workspace:

Once we’ve created the workspace, we can now integrate our Slack community to orbit.

You can now open the Slack workspace you just connected with Orbit.

Go into your channel and open the Channel Details menu by clicking on the top-left channel name. Then, we’ll open the integration panel and click on “add apps.”

We’ll then see a bunch of apps where you’ll select Orbit.

The Orbit integration is now added to our #general channel! This automatically imports all messages and profiles (up to one year). Plus, it will listen for any new activities, including messages, thread replies, etc.

A message will automatically appear in the channel you just added, saying, “Orbit was added to #channel by you.”

To add the Orbit integration to more channels, we need to repeat the same steps or take advantage of the message shown above: Clicking on the name or icon of “Orbit” in that message will open a menu, from which you can add the integration to more channels with a click.

We’ve successfully connected Slack to orbit and can view all activities on our Orbit dashboard.

Setting Up Our Orbit API

Orbit offers RESTful APIs that we’ll use to pull our Slack data into our dashboard. The specific data pulling is the list of members from this Slack channel.

Here’s the endpoint:

app.orbit.love/api/v1{workspace_slug}/members

Note: You can can check the docs for more endpoints and use-cases.

You can find the workspace_slug under the workspace settings. It’s the text in bold below:

Setting Up Appsmith

Let’s sign up to Appsmith and create our first app. On our first page we’ll add the container and text widgets to act as the header. Then, we’ll also add the list widget.

Now, we’ll go under QUERIES/JS dropdown on the left and create a new Datasource.

Set the API request to GET and add the endpoint. You’ll have to add your {workspace-slug} to the endpoint, as explained earlier. Once that’s done, we’ll click Edit Datasource, and it’ll take us to a page where we’ll add our header token.

Scroll down to the Authentication section of the page, down to the Bearer Token input, and add your Orbit’s API token. You can find that on the settings page. Note that you won’t add Bearer as a prefix, just the token. (that’ll be done automatically)

Let’s go back to our API datasource page and test it again

We now have everything functioning as it should, and the API provides the appropriate response. Look at the left and open a new page:

Once we select the list widget, we go to the left and see all related options. Under items, we’ll get the response preview by adding the name of the API: Orbit_Members.data.data.

{{Orbit_Members.data.data}}

Similarly, you’ll add an image widget inside the List item. On the right, we’ll use currentItem to refer to the current API in question. So it’ll be currentItem.attributes.avatar_url.

In the same vein, we add the name and email to the List item with {{currentItem.attributes.name}} and {{currentItem.attributes.email}}. If we do this correctly, we should have a similar screen to the one we have above.

Displaying Member Details

First, we’ll create a new page called Member Details

We’re now going to add a button to each user that navigates us to a new page (Member Details). While navigating, we’ll save the user’s slug as a store value, and send it as a query param to the next page.

The aim is to use that slug to make another request on the new page. This request displays that particular user’s details. Under Member Details, we’ll name that request Member_Details and get our slug value from Appsmith.value.slug. This allows us pull the data for that single member that was selected on the Dashboard page.

Let’s go back to Member Details and display our data.

First, we add an image component to display the picture and use {{Member_Details.data.data.attributes.avatar_url}} to get our image URL from the Member_Details DataSource we created.

We also use {{Member_Details.data.data.attributes.location}} and {{Member_Details.data.data.attributes.avatar_email}} to display our location and email respectively.

Next, we’ll manually add other data to our dashboard. These include our Daily Active Users (DAU) and New Community Members.

Sales Report

There’s a sales report graph that helps us display our sales data.

In this case, we’re manually adding our data as an array.

Finally, we’ll hardcode other important data like churn rate, conversions, and our MRR.

Deployment

Now that our Appsmith app is complete, what’s next? Deploying our shiny new app for other team members to use. Here’s the link to our application.

There’s also a tutorial below that shows you how to deploy with digital ocean. youtube.com/watch?v=6fitHGX2G4E&; youtu.be/6fitHGX2G4E

Conclusion

In this article, we’ve covered how to create a growth dashboard with Appsmith and Orbit‘s REST APIs. This shows us how to use low-code tools to optimize time and valuable resources when building internal tools in your company.

Orbit and Appsmith complement each other when it comes to monitoring community platforms like Slack, Discord, Discourse, etc. With Orbit, you get access to direct feedback and necessary information without spending engineering time building infrastructure from scratch. While Appsmith lets you focus on creating a visual dashboard with metrics, using minimal effort to implement and maintain.

Beyond using Orbit as a datasource, we can make use of custom REST APIs, Airtable, Supabase, Backendless, etc. Also, you can add other third-party integrations like intercom, email, or even add Zapier into your application. Appsmith can be customized into any form to fit your technical use-case.

If you have blockers or issues using Appsmith, you can always jump into our community Discord server to ask questions. Plus, you can connect with other Appsmith users.

Also, feel free to check other Appsmith tutorials using similar services like Cube, SnowflakeDB, Hasura, etc.

‍

Information is the lifeline of any business, and to keep it alive and well, businesses have to transfer information to their various stakeholders like investors, bankers, suppliers, customers, government, etc. One of the simplest and the most effective ways of transferring information is using PDF files. These PDF files can be generated in different ways, but it involves lots of coding and configuration on your frontend and backend applications. To make this process easier on Appsmith (low-code framework to build internal tools), we created a workflow on n8n that uses APITemplate and simple webhooks.

This tutorial is a step-by-step guide on generating and exporting PDF files from data on your Appsmith application. Let's dive in!

Getting Started: Setting up Appsmith and the Data to Export

The first step is to create an account on Appsmith. In this tutorial, I'll be using the cloud version of Appsmith, but you can always choose to use Appsmith locally or self-host it on your server.

• Navigate to appsmith.com and sign-up for a new account if you're a new user or login to the existing one.
• Next, create a new application under your preferred organization. You'll see an editor with everything you need to build your internal application.

Now, let's create a table from the mock database already provided on Appsmith.

• For this, click on the + icon next to the Datasources on the left sidebar under Page1.
• Now choose the users database (Postgres) under-sample databases section.

• With this, you should see users' databases under your data sources section on the left sidebar. Now create a new query by clicking on Create New button from the user's datasource.
• This will create an empty query named Query1, rename this as getUsers and paste the following SQL query:

SELECT * FROM users ORDER BY id LIMIT 10;

• Hit the Run button on the top right to execute this query; it will return details of the first ten users from the user's table on the database.

Now let's bind this on a Table widget and then create a workflow to export it into a PDF file.

• To bind the query on a table widget, click on the + icon next to the widget and search for the table under the Widgets tab.
• Drag and drop the table widget onto the canvas and open its property pane by selecting the table on the canvas. You should see all the table configurations on the side right bar.
• Next, update the Table Data property to the following:

{{ getUsers.data  }}

• Here, we use the moustache bindings to bind the data from the query onto the widget. You can write JavaScript anywhere across appsmith inside these bindings.

Following is what the application looks like now:

Excellent, our data is now ready. Now let’s create a workflow on n8n to generate PDF using APIs from APITemplate.io.

Setting up APITemplate.io

If you are new to these tools, the following is a quick intro to help you get started:

APITemplate.io: APITemplate helps auto-generate images and PDFs with Zapier, Integromat, N8n ow with a simple REST API.

Now let’s create a free account on APITemplate; you will be redirected to your dashboard after signing up. Navigate to the Manage Templates tab and create a new PDF template.

The template here is how you want your PDF to be organized this tutorial; I’ll be using the basic table template provided under the PDF template section.

Since our goal is to export the data on the Appsmith table, I’ve navigated to the HTML editor and removed all the unnecessary HTML content.

The idea is straightforward we send dynamic data from Appsmith onto the HTML template using a webhook, and APITemplate will generate a PDF file from the HTML.

Here’s how the HTML is added:

The item variable here holds all the data that will be updated from the webhook request on Appsmith.

We don't see any data here, as we did not have any workflow. So let's create on n8n!

Creating a Workflow on n8n

n8n: n8n helps you to interconnect every app with an API in the world with each other to share and manipulate its data without a single line of code. It is an easy-to-use, user-friendly, and highly customizable service, which uses an intuitive user interface for you to design your unique workflows very fast.

I'll be using the cloud version of n8n for this tutorial. Note that you can also self-host n8n on your server.

Now, let's create the workflow:

• Drag and drop a webhook node and add the following configuration:
• Set the authentication type to None.
• The HTTP method to POST.
• Set the Response method to Using 'Respond to Webhook' node

Now copy the TEST URL, and create a new API on the Appsmith app by clicking on the + icon next to Queries and pasting the TEST URL in the request URL. Rename the query to generatePDF for a better naming convention.

Now navigate to the Body tab in the API request and paste the following JSON:

{
    items:{{Table1.tableData}}
}

‍

Here, we send the items data from the Table to the webhook using the moustache bindings.

Now, we can test this webhook by starting the workflow; here’s how it works:

Next, drag and drop a new APITemplate.io node and connect it to the webhook and use the following configuration:

• First, copy the API key to the Credentials for APITemplate.io property.
• Set resource to PDF and Operation to Creation
• The template ID property can be found under the manage templates tab; this refers to the template created in the last section.
• Toggle the JSON parameters and Download to ON
• Copy the following in the Properties:

{    
    "items": {{(JSON.stringify($node["Webhook"].json["body"]["items"]))}}
}

‍

This is where n8n sends data from Appsmith to APITemplate using a webhook.

Now, connect a new Respond to webhook node and set its respond with property to First incoming item property. This is how the complete workflow looks like:

Now click on execute the workflow and RUN the API on Appsmith; we should see a response with a downloadable PDF URL:

To improve the look and feel, we could create a new button and set its onClick property to run generatePDF query and bind the response to a text widget.

Here’s the complete workflow:

Alternatively, for PDF generation on Appsmith, we could also rely on other services like Zapier / Adobe.

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.

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

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

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

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

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

Alright, now let’s get into it.

Getting Started: Creating an Account on Xano and Appsmith

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

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

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

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

• Before we start to use them, let’s first get our OAuth credentials from Google, for this, you’ll need to open Google Console, and create your API credentials. Here’s what it would look like:

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

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

Building the UI on Appsmith

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

Now follow the below steps:

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

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

Initializing the Authentication Flow

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

• Open the Xano dashboard, and navigate to the Settings tab; now click on the google-oauth API group.
• We should see all the APIs that we can consume to finish our authentication, and yes, all these are automatically generated without writing any code, thanks to Xano.
• Now, select the oauth/google/init API and copy the endpoint.

• On Appsmith, click on the + icon next to Datasources and create a NEW API.
• Rename the API endpoint to init, and paste the copied URL.
• This endpoint expects an redirect_uri, this basically, redirects to another page if the authentication is successful. So, we’ll need to create a new page on Appsmith, and copy it’s URL to the redirect_uri param on the init API.
• Now create a page on Appsmith, by clicking on the + icon next to the Pages on the sidebar. Rename the page to welcome.
• Copy its URL, navigate back to login and paste the URL in the redirect_uri param, here’s how the API should look like:

• We’ll need to add the Redirect URL onto the google console so as to redirect from Google Auth after the redirection is successful.

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

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

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

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

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

Configuring the continue Authentication Flow

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

• Drag and drop a container widget onto the canvas, just like we did it for the login page.
• Add an image, text widget and a button widget for the look, here’s how I’ve designed it, you could add images and colours of your own choice:

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

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

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

Here’s the GIF:

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

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

Welcome {{oauth_continue.data.name}}

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

Securing Pages on Appsmith with Xano Auth

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

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

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

‍

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

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

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

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

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

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

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

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

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

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

A Quick Look at Appsmith

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

A few of the benefits of Appsmith include:

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

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

A Quick Look at Cube

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

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

Creating a Dashboard with Appsmith and Cube

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

This is how your dashboard will look:

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

Prerequisites

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

1: Clone the Dataset and Host It on Your Database

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

2: Connect Cube to Your Database

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

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

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

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

3: Get API Credentials and API Endpoints from Cube

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

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

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

4: Set Up Appsmith

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

5: Create the First Widget

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

You should see the following page:

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

Pass the following JSON as a GET param:

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

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

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

A table will appear in the canvas.

6: Create the Second Widget

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

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

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

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

7: Add Different Widgets as Per Your Requirements

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

Query 1

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

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

Query 2

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

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

Query 3

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

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

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

Create Your Own Internal Apps with Appsmith

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

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

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

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

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

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

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

‍

GraphQL is a query language for APIs and a server-side runtime for executing queries using the system you define for your data. It provides a complete and understandable description of the data in your API, gives clients the power to ask for exactly what they need and nothing more, makes it easier to evolve APIs over time, and enables powerful developer tools.

Today, developers spend a lot of time building internal applications, dashboards, and admin panels with GraphQL APIs as backend. But guess what? It’s now possible for you to write customizable applications with the ability to quickly prototype and deploy in a few minutes. With Appsmith, a low code app builder, you can build entirely customizable internal tools without spending too much time or effort.

In this blog, I am going to be building an e-commerce order management dashboard.

To build this app, we will connect Appsmith with a GraphQL API and build an interface that will fetch information from a database. We will use Hasura to create, manage, and customize GraphQL APIs.

Hasura is an open-source service that gives you production-grade GraphQL & REST APIs on your data instantly. It allows you to develop and deploy GraphQL APIs in minutes. At the core, Hasura helps you build APIs with a simple to use GraphQL composer and a GraphQL server that is stateless and blazing fast.

What exactly are we building?

By the end of the tutorial, we will have a fully customizable e-commerce order management dashboard! Typically, such dashboards enable the app users to:

• Manage the entire product catalogue
• View sales, track all details of total and recent orders
• Send email to customers

Note: We will be using data from Hasura Super App, which can be found here. You can directly migrate the data here to your Hasura instance in just a few steps listed here: Moving from local development to staging and going production with Hasura.

Alternatively, you can connect to the instance we created for you to test and play with GraphQL APIs on Appsmith. These details of the instance are disclosed in the next section :)

All right, let's dive in!

Connect to Hasura on Appsmith with GraphQL

We will be connecting to the Hasura instance via the REST API datasource on Appsmith. For this, we will need the GraphQL API end-point and Admin Secret key from Hasura Dashboard. If you're using your instance, this is how your dashboard looks like, where you can copy the mentioned details.

If you want to know more about the data schema and customize the relations between tables, you can always use Hasura cloud explorer.

Now, let's quickly take a look at all the tables that are available on Hasura Super App Dashboard in brief:

• user: This table contains a list of all the customers who signed up for the Hasura super app.
• address: The address tables contain all the user delivery addresses that will be further linked to the order.
• admin: Details of all the super-admin who have access to all the dashboards
• order: The order table contains all the order information a user places
• order_product: This table contains a list of products that are included in the order
• order_status: Contains details of order delivery status
• product: Details of all the products on the e-commerce store
• product_review: Reviews of products that the customers gave

Awesome, now that we had a glance at our tables, let's build the app! First up, we'll need an account on Appsmith Cloud. You can create it here for free if you don't have one. After we log in, we will be redirected to our dashboard to make a new Appsmith; this can be under any organization of your choice.

Now, to connect Hasura APIs, follow the below steps:

• Click on the + icon next to datasources and choose Create new API under the Create New tab.
• Rename the query to getProducts by double-clicking on the existing one.
• Set the query type to POST, and paste the following URL:

https://subtle-platypus-36.hasura.app/v1/graphql

• Under the headers pane, add the following key-values:

‍

content-type: application/jso
x-hasura-admin-secret: <YOUR_SECRET_KEY>

‍

• Lastly, navigate to the Body property under the API pane, set the body type to JSON, and paste the following:

{{
JSON.stringify({
    variables: null,
    query: `
    query {
        product(limit: 300) {
            id
            brand
            category_display_name
            created_at
            description
            name
            image_urls
        }
}
`})
}}

‍

Now, we can test this GraphQL query by clicking on the RUN button to the top-right of the application; with this, we should see all the products list from the backend in the response body.

Following is the screenshot:

Here, inside the body pane, we write the GraphQL query to fetch all the products; this is similar to how we do it on a regular GraphiQL playground.

Now let's bind this data onto the table widget.

• For this, click on the + icon next to widgets and drag and drop a table widget onto the canvas.
• Select the table on the canvas; this will open the property pane on the right sidebar; now, under table data, we bind the query using the moustache syntax:

{{ getproducts.data.data.product }}

• And just like that, we should see all the data from the query binded onto the table.
• Note that all the columns are not required for us. Hence, we can hide them by clicking on the eye-icon on the column name under the columns property.

Adding a New Product to Database

Doing a simple READ operation using Hasura's GraphQL API is easy now; let's look at how we can do a WRITE operation by building a form on Appsmith. Follow the below steps:

• First, let's drag and drop a new button widget on the canvas; the idea is to open a modal and show a form to create a new product on the e-com store.
• Next, add a text widget and a few form widgets on the modal by dragging and dropping them onto the modal from the widgets section.
• We can also set the form's label by updating the label property from the form's property pane.

Following is a screenshot of how the form should look like:

Now, update the form names to the following to access them in the query:

You can update the widget names from the property pane by double-clicking on the existing one.

• Name: getName
• Description: getDescription
• Category: getCategory
• Brand: getBrand
• Price: getPrice
• ProductID: getID

Now, add a new button, update its label to Add Product, and set its onClick property to "Run a query" and choose Create a new API. This will create a new API datasource under the datasources section. Now, follow the below steps to create a new query that lets us post new data onto PostgresDB on Hasura.

• Rename the query to addProduct
• Update the URL to:

https://subtle-platypus-36.hasura.app/v1/graphql

‍

• Set the method type to POST, and copy the headers x-hasura-admin-secret, content-type from getProducts query to here.
• Update the query body to the following:

{
    "query": {{JSON.stringify(`
    mutation ($object: insert_product_insert_input!) {
        insert_product_one(object: $object) {
    id
            brand
            category_display_name
            description
            price
            name
        }
    }
    `)}},
    "variables": {
        "object": {{
          JSON.stringify({
    id: getID.text,
            brand: getBrand.text,
            category_display_name: getCategory.text,
            description: getDescription.text,
            price: getPrice.text,
            name: getName.text,
        })}}
    }
}

‍

Here, we have an object consisting of a query key that holds the GraphQL query and a variables key with all the variables that need to be posted to the database.

In the query, we have a mutation that lets us modify the server-side data. We'll send the object that references the data querying inside the mutation. The insert_product_one allows us to post one object to the database. Inside this query, we can add the attributes we need to post. Next, we've associated the variables to the object from the created UI input widgets. The .text on the input widget name helps us get the text written inside the input.

That's all, and our query is now ready! We can test this out by opening the form and creating a new product on our e-commerce store.

Add Stats on Dashboard

In this section, we will be showing some statistics on our dashboard; for this, let's drag and drop three stat box widgets onto the canvas. Following is a screenshot of how it looks like:

As we can see, these stat box widgets compute the total products, total categories, and unique brands. The data here is computed directly from the Hasura instance, using JavaScript. Following is how it works:

On the first stat box widget, select the text-widget and paste the following in the Text property:

{{getproducts.data.data.product.length}}

This small JS code will return the array's length from the getproducts query, displaying the total products.

Similarly, update the other two stat box widgets with the following JS code:

To compute Total Categories, we use an IIFE function and filter the getproducts query using its category_display_name attribute :

‍

{{
function(){
    const filterProducts = getproducts.data.data.product.map(item => item.category_display_name)
  .filter((value, index, self) => self.indexOf(value) === index)
    return filterProducts.length
}()
}}

‍

To compute Unique Brands, we use an IIFE function and filter the getproducts query using its brand attribute :

{{
function(){
    const filterProducts = getproducts.data.data.product.map(item => item.brand)
  .filter((value, index, self) => self.indexOf(value) === index)
    return filterProducts.length
}()
}}

‍

There we go; we now have our dashboard up and running! Not just that, Appsmith allows us to create charts on top of Hasura data. Let's learn more about it in the next section.

Charting on Appsmith on Hasura Data

To get started, let's rename the page as Recent Orders; the idea is to create a chart that shows daily sales. Drag and drop a new chart widget onto the page from the widgets section.

This will show you a column chart with some pre-defined data; let's update this by creating our new query that fetches all the sales data:

• Click on the + icon next to datasources and choose Create a new API.
• Set the query type to POST, and update the URL to [https://subtle-platypus-36.hasura.app/v1/graphql](https://subtle-platypus-36.hasura.app/v1/graphql)
• Under the headers pane, add the following key-values:

content-type: application/json
x-hasura-admin-secret: YOUR_SECRET_KEY

‍

• Lastly, navigate to the Body property under the API pane, set the body type to JSON, and paste the following:

‍

This query will return a bit complex JSON; we will see something like:

{ "data": { "order": [ { "order_products": [ { "product": { "order_products": [ { "quantity": 2 } ] }, "created_at": "2021-02-22T18:16:17.776779+00:00" } ] }, … }

Here, order_products are nested in an array; hence, to process this and put it on a chart widget, we create a new JS object, allowing us to write full code on Appsmith and utilize its widgets. Confused? Follow the below steps:

• Click on the + icon next to JS Objects, or use the JSObject1
• Update myFunc1 to the following:

‍

myFun1: () => {

  const data = _.compact(salesGraph.data.data.order.map((row) => 
  {
        const quantity = _.get(row, 'order_products[0].product.order_products[0].quantity');
        const created_at = moment(_.get(row, 'order_products[0].created_at')).format("LL");

        // if any of those in undefined
        if (!quantity || !created_at) return undefined;

        return {
      x: created_at,
             y: quantity,
    }
  }))
    return data.slice(1, 20)
    },

Here, we do a simple ordering using the _.compact method from lodash to aggregate the total number of sales done on a particular date. We return these in a special {x: value, y: value} format, which is accepted by the chart widget.

Now, let's head back to the chart widget and use this JS Object:

Update the series data property with the following:

{{JSObject1.myFun1()}}

Boom! There you go, we have all our sales aggregated based on the date on a beautiful chart widget. Following is how it looks like:

We also extended this app to show all the sales and orders on stat boxes and list widgets. Here's the link to the complete application!

If you liked this tutorial, and are planning to build this, let me know. I’d love to help you make it as complex as you’d like.

Write to me at vihar@appsmith.com.

I recently started investing and came across a product called Smallcase. The way it works is — experts create a diversified long-term portfolio of stocks & ETFs and manage it on a timely basis for you. I really love the idea of how stocks can be added into portfolios or baskets to reduce market volatility. Unfortunately, at the moment, this product is only available for Indian investors and that led me to create an app, where I could create a portfolio, and also create a custom index on top of it, with features to compare benchmarked indices like Dow Jones / S and P.

Building this kind of application from scratch would have taken me a lot of time; I would have had to choose a front-end framework, configure a database, and work on different visualizing frameworks to build time-series charts. With Appsmith, I was able to build this app in just a few hours. If you don’t already know, Appsmith is an open-source low code framework to build internal apps. In this blog, I will take you through the steps I followed to create the app.

First, let me share the specs of the app in a little more detail.

With this application, you can do the following:

1. Track stocks: Set filters on what stocks should be tracked, throughout the day by default or create specific lists.
2. Make your own baskets: Create customized stock portfolios as baskets.
3. Time-series charts to compare performances: Create a custom index for these portfolios and compare them with benchmark indexes using time-series charts.
4. Notify: Send notifications and triggers when there are fluctuations in the market.

‍

In this blog, I will also dive into building some nifty time-series charts and build our stock index tracker that can compare with S&P 500, Dow Jones, and NASDAQ.

‍

Here are a few screenshots of the application:

Now, let’s get started!

What All Did I Use to Build This Application?

TLDR; I’ve used Appsmith, Fusion Charts on Appsmith, APIs from MarketStack, Alphavantage, and Google Sheets as the backend to build this application.

This application primarily uses Appsmith, an open-source low-code tool that can connect to any database, API, or GraphQL to build admin panels, dashboards, and internal tools. Appsmith also supports charting options; it has an in-built extension that uses Fusion Charts to create data visualizations; this made it much easier to build my own time-series charts. To pull all the stock data and prices in real-time, I’ve utilized free APIs from MarketStack and Alphavantage.

(Please remember to generate your own API keys if you’re following this tutorial to build a similar app! To know more about generating your own API keys, read this blog)

I’ve used Google Sheets as my backend to watchlist stocks and store customized stock portfolios and baskets.

Creating Baskets to Group and Track Stocks

The first step to creating portfolios, or as I like to call them, “baskets,” is to build a user interface. Through this, a form can take in our portfolio name, time created, and a logo for easy viewing… and some vanity! 😎

It took me less than 5 minutes to create this with Appsmith and Google Sheets; here’s how I did it:

Note: I’ve used the Cloud Version (Community Edition, which is free) of Appsmith; you can build this either on a local version using Docker or a self-hosted version of Appsmith.

• Sign-up for a new free account on appsmith.com
• Click on the + icon next to Datasources and choose Google Sheets as Datasource.
• Authorize your Google Account, and create a new query by hitting the NEW API button after the data source is saved.
• Renamed the query name to assBasket, as we’ll need to access query names on the UI.
• I created a new google sheet with the following columns:

Basket Name
Basket Image URL
Basket Create On

• Copied the URL of google sheets and pasted it under the SpreadSheet URL property.
• Set the Query Type to Insert Sheet Row

With this, our query is ready. Next, to build UI on Appsmith, you can just drag and drop widgets from the library on the right side of the screen. Appsmith’s canvas is quite responsive, and you can even drag widgets between two other widgets, and it will auto-resize, saving you valuable time! I added a container widget so that our app looks unified; we even have an option to add colors to this if we want to stylize the background a little more. I then dropped a text widget to set the app’s name and a button widget.

Here’s how it looks:

Neat, yeah?

Next, since we want there to be a form to create new baskets, we should get the button to open a modal. To do this, click the settings icon on the button widget to see the property pane.

Property pane is where we can configure all the properties of the widgets. You can open this, by simply clicking on the widget, and you’ll see it docked on the right sidebar, just as shown in the above screenshot.

We need to update the onClick property of the button and set it to open a new modal. You can create it there or drag and drop a modal widget onto the canvas. Both options work. You do you.

Next, we need three types of inputs for the form, so I added three input widgets to this modal. You can set the labels as follows:

Basket Name
Basket Image URL
Basket Create On

Here’s a Loom on how it looks like:

Things look in great shape so far, in order to be able to track the index of the portfolios based on created time, I thought it was best to use a pre-installed library on Appsmith since it’s not possible to track this manually. I used moment.js to do this.

We can set the Basket Created On default value to:

{{moment().format(“MM ddd, YYYY HH:mm:ss a”)}}

This will save time when the portfolio is created.

On Appmsith, we can write JS anywhere across the platform to add customization using the moustache {{ }} bindings. It also comes with the moment.js library; hence, calling the function will save time when these portfolios are created.

Lastly, I opened the buttons property-pane and set the onClick property to run a query and choose addBasket query.

With this, my UI is all set; lastly, I went back to addBasket query and set the Row Object to the following:

{ "Basket Name":{{Input1.text}},
"Basket Image URL": {{Input2.text}},
"Basket Create On": {{Input3.text}}
}

Note that we will be collecting the values from the Input widgets and passing them to the Google Sheets via the addBasket query.

Next, to display all our baskets on this page, we need to create a new API. I created another one called getBasket, from the Google Sheets datasource. I updated the query method to Fetch sheet row list everything from the excel sheet. Here’s a screenshot:

Next, we have to bind our queries to the UI widgets. This part can be done like this:

• Open the property pane of List1 widget, and set the Items to {{getBasket.data}}
• Inside the list widget, select the image and set the image property to {{currentItem[“Basket Image URL”]}}
• Set the text widget properties to {{currentItem[“Basket Name”]}}, and Basket Created On: {{currentItem[“Basket Create On”]}}

‍

This is how it looks now:

Here’s what we’ve been able to do till now:

• Added functionalities to add a new portfolio/basket and list them on Appsmith.

Now, we will work on adding stocks inside these portfolios. For this, it would be best to create a new page to search for stocks and add them to baskets.

Let’s create a new page; you can choose the option on the left sidebar and name it as ‘Basket Page’. The idea is to redirect to this page from Page1 and filter all the stocks present in the basket.

But how do we filter stocks? Usually, we do on a regular backed to consider an id and filter based on it. Similarly, on Appsmith, while navigating to Basket Page, I stored the value of the basket page to filter and display data on the Basket page.

I can use the code snippet I mentioned below on a new button widget placed on the list widget. So, when clicked, it should save the selected item on the list, redirect to the basket page, and filter accordingly.

We need to the button’s onClick property to:

{{(function () {
    storeValue("selectedBasket", List1.selectedItem["Basket Name"]);
    navigateTo("Basket Page") })() 
}}

Here, we need to use the storeValue from Appsmith to store the basket name in a variable named selectedBasket, the List1.selectedItem copies the data from the selected list item.

Next, we need to use the navigateTo function to redirect to a new page.

Adding Stocks into Baskets

Before we add any stocks into our portfolios, we’ll have to ensure that this page filters my stocks according to the selected basked from page one. So let’s add a container and a text widget to display the Basket name.

Note that this value comes from the global store, so on the Text property from Text widget’s property pane, we will have to bind value from the Appsmith store, and this can be done with a simple line:

{{appsmith.store.selectedBasket}}

Alright, the next cool feature for us to add is a search function. This way, we can easily search for stocks and add them to baskets; for this, we need to add another button and modal with the following fields:

• An input widget
• A button labbeld as Seach
• A table widget to list out all the results
• A select widget to add them to the list of baskets I created

The table widget definitely deserves high praise. It allows us to add filters and sort results. We can add relevant stocks based on where they are listed on. For example, I could simply search NASDAQ, which filters stocks listed there. If you want to know everything that you can do with a table widget, you’ve got to check out this detailed blog.

Before I tell you how I built this, here is how it looks like:

The search feature works with a simple API from Stockdata, to integrate this, I followed the following steps:

• Click on the + icon next to the Datasources
• Select Create New API, and paste the following URL:

https://api.stockdata.org/v1/entity/search?search={{stockcode.text}}&api_token=YOUR_API_HERE

• Rename the API query to stockSearch

To get this working, make sure you add API token from Stockcode here. Please note that I use moustache bindings here. This adds the values I’m searching for from the input widget dynamically. Now, let’s head back to the modal and update the names of the widgets.

I renamed the widget names to the following, to make it easy while pushing data onto sheets:

Input Widget: stockcode

Table Widget: stockSearchTable

Now, the search API takes the value from the input widget, and on the table widget, we can bind the results from the API for the following functionalities:

• Set the buttons onClick property to run a query and choose stockSearch query.
• Bind the stockSearch query to the table widget, using {{ stockSearch.data }}

Next, we want to select which basket we want to add the searched stock to; for this, I just copied my API (getBaskets) from Page1, and bound it onto the selected widget with the following code:

{{
    getBaskets.data.map((row) =>   
    {
    return {
    		label: row["Basket Name"],
        value: row["Basket Name"]    
     }  })
 }}

Last, we need to save all this information onto a Google Sheet, so let’s create a new spreadsheet under the same Google Sheet and create a new API from the connected data source. I named this sheet Watchlisted Stocks and named the query addtoBasket.

These are the columns I have on my Google Sheet:

symbol
name
type
exchange
exchange_long
mic_code
country
watch_listed_on
basket

Now, on the query to collect all this information, we have to update the Row Object to the following:

‍

{"symbol": "{{stockSearchTable.selectedRow.symbol}}","name":"{{stockSearchTable.selectedRow.name}}","type":"{{stockSearchTable.selectedRow.type}}","exchange":"{{stockSearchTable.selectedRow.exchange}}","exchange_long": "{{stockSearchTable.selectedRow.exchange_long}}","mic_code": "{{stockSearchTable.selectedRow.mic_code}}","country":"{{stockSearchTable.selectedRow.country}}","watch_listed_on": {{moment().format('MMMM Do YYYY, h:mm:ss a')}} ,"basket": {{Select1.selectedOptionValue}}}

With this last step, our watchlist will be ready. However, in order to show it on the page, we have to add a new table widget. Wait, I still had to pull all the watchlisted stocks from the google sheets. This is pretty straightforward, I create a new query from the GSheets datasource, set the method to Fetch Sheets Row, copy my GSheets URL, and update my Sheet Name to Watchlisted Stocks.

Now, we have to bind it on the new table widget, but here, it should only filter stocks that are added to this basket or portfolio, I do this by using a simple transforming when binding using the following code snippet:

{{     
    getStocksfromBaskets.data.filter((item) => {
    return item.basket == appsmith.store.selectedBasket
    })
 }}

As you can see, we’re now filtering our stocks based on the selectedBasket value from the Appsmith store that we saved while redirecting!

We now have all the portfolios and stocks ready.
Now it’s time for data visualization!

Building Time Series Charts and Tracking Index

As mentioned, Appsmith’s chart widget comes with the Fusion Chart configuration, which makes it much easier to build time-series charts. The other thing I love about this is, I can even create candlestick charts for the stocks I watchlisted inside the application. These charts are used by traders to determine possible price movement based on past patterns.

First, let me show you how to fetch details of all the stock prices, using the MarketStack API:

• Inside the basket page, I created a new API (marketStackAPI), with the following URL:

http://api.marketstack.com/v1/eod?access_key=YOUR_API_KEY&symbols={{appsmith.store.current}}&date_from=2021-11-01&date_to=2021-12-12

Note: I’m filtering the data from 2020–11–01 to 2021–12–12, you can update this API accordingly based on your requirements.

In the API, as you can see, I have added an Appsmith store variable named current; this is to dynamically fetch the stock prices of the symbol or stock we wanted to analyze or add to our tracker.

Now, we need to write a code snippet under JS Objects, that stores all the data for all the stocks that are there inside my portfolio.

This is what I’ve used:

export default {

getBasketGraph: () => {let stocks = getStocksfromBaskets.data.filter((item) => item.basket == appsmith.store.selectedBasket)    let stockcodes = stocks.map(item => item.symbol)    let allStockData = [];    stockcodes.map((item, index)=> {    storeValue("current", item);     marketStackAPI.run(()=> {     let tempData = marketStackAPI.data;     allStockData.push(tempData.data);    }, () => {}, { allStockData });  })    storeValue("allStockData”, allStockData)    return appsmith.store.allStockData; 
}}

Here is what this code snippet does:

• Stores all the symbols that are present in the basket in the stockcodes variable
• Iterates through the stockcodes array and stores all the stock prices in a new variable allStockData
• Returns the store variable when the function is executed

To create a time series chart, we need to drag and drop a new chart widget onto the canvas, below the table widget.

This is how it looks like:

It comes with a default configuration, so we will have to change the type to a Custom Chart to configure our data onto this. We will need to follow a particular format in order to do this. Here’s the basic config for Fusion Charts:

{  "type": "",  "dataSource": {    "chart": { },    "data": [ ]  }}

Here, we need to pass in three parameters, the chart configuration, data, and the categories that need to be displayed on the x-axis. Here’s the updated code:

‍

{    "type": "MultiAxisLine",    "dataSource": {        "chart": {            "xaxisname": "Date",            "vdivlinealpha": "",            "labeldisplay": "ROTATE",            "theme": "fusion"        },        "categories": [{                "category": {                    {                        marketStackAPI.data.data.reverse().map((row, index) => {                            return {                                "label": moment(row.date).format("LL"),                            }                        })}                }            }        ],        "axis": [{                "title": "Price",                "dataset": [{                        "seriesname": "Apple",                        "data": {                            {                                marketStackAPI.data.data.map((row, index) => {                                    return {                                        "value": row.close,                                    }                                })}                        }                    },                }            ]        }    }

Finally, this is how our graph will look like with the aforementioned configurations:

Additionally, I also created a list, that tells us the index value with simple math:

Imagine having your own custom app terminal to track all your stock indexes in one place. If you’re into crypto, you can extend this app to even have a crypto tracker.

I hope that you had success following this tutorial and building your own app. If you got stuck somewhere, I’d be happy to help you out :)

More content at plainenglish.io. Sign up for our free weekly newsletter. Get exclusive access to writing opportunities and advice in our community Discord.

Statistical data and database information is often represented using table models. These models might represent large amounts of data sets; hence there is a need for customization between table sets to help distinguish them. Finding a suitable application to help develop tables that meet these requirements is often an uphill task. Appsmith is an open-source framework that enables developers to build internal tools for applications and add functionality quickly. With Appsmith, we can also create tables, link them up to an API or database and perform CRUD operations.

In this blog, we will learn how to style tables in Appsmith. We will also learn how to customize different aspects of our table to change the appearance and how the table data is displayed. We will apply different styles to other tables to help distinguish them.

Why Do We Need To Style Tables?

In situations where we are building numerous tables on Appsmith, we will need to add labels to each table to differentiate between them. Alternatively, we can also apply different styles to each table ranging from color gradients, cell colors, borders, and box shadows. Styling helps to improve the overall appearance of tables. When styles are used to identify specifically stored data, it makes it easier for viewers to pinpoint what they need to see in a table. For example, a table may have a specific cell color for table columns reflecting profit or loss in an account, making it easier to understand. 

How Styling Works in Appsmith

Appsmith tables provide an ordered form of representing data from a data source in rows and columns. This table can also be used to view large amounts of data, and the data can be arranged in the form of server-side pagination in which the amount of data displayed is limited per page. 

Here are a few examples of some tables built with Appsmith:

Appsmith provides us with a property pane. The Appsmith property pane contains all the properties where users can connect to data and customize it with different properties. Numerous styles which can be applied to produce various results with the widgets can also be found in the property pane. Select the widget you wish to style and click on the cog-wheel icon at the top right to get to this style pane. The Style section is located at the bottom of the menu that pops up.

This panel may vary based on which widget is selected. The image above is the Style panel of the Table widget. Starting from the top,  we have the following options: 

- Cell Background, 

- Text Color, 

- Font Style, 

- Text Align and 

- Vertical Alignment.

We will go over these options in the course of this tutorial and discuss the effects of each of them.
‍

In these style options, there is a JavaScript toggle button. Clicking on the JS toggle button changes the mode of input for the options and lets us enter strings of characters or JavaScript for the property we wish to apply. For instance, we can set visibility to none by toggling the JS toggle for the visible property and entering “false” in the field or entering {{false}}. The latter is Javascript enclosed in mustache syntax. We will talk more about what mustache syntax is and its usage later in this tutorial. 

Styling Tables: A Manual 

Below, I have a table widget with some data in it. If you select your table, you will get a popup with a gear icon you can use to edit your table.

Clicking on the gear icons opens up a property pane to edit the table. If you scroll down the menu, you will see a section to add styles to the table.

Cell Background and Text Color

‍In this section, we will learn about the different styles provided by Appsmith and how they can be applied to produce different results with tables. The first two options in the Styles section allow us to change the color of our table’s background and our text color.

Above, I have selected a black color for my cell’s background, and I’ve also set my text color to white. Alternatively, you can use a different color other than the ones provided here by entering the Hex code for the color in the property field. An example is a purple color with the Hex code: #ed18cd. If you are looking for a site where you can pick colors from a palette and get the Hex code, you can check out Coolors or get the extension for your browser. 

Changing Font Size

‍The next option gives us the ability to change the font size of the text in our table. There are different options to choose from, with “Heading 1” being the largest font size and “Paragraph 2” being the smallest font size. 

Here, I have increased the font size for my table by setting my “Text size” property to Heading 1.  

Font Style, Text Align and Vertical Alignment

We can customize the font style and justify our text horizontally and vertically with the remaining three options.

Note that it is possible to add both “bold” and “italics” font styles simultaneously. The text-align property provides you with options to position your text either at the left, center, or right of your cell. In other words, it lets us set our text on the horizontal axis. 

Vertical Alignment is used to make adjustments to the vertical position of our text in the table. The first option positions text at the top of their cell, the second positions the text at the center, and the last positions the text at the bottom of the cell.

Also, rather than applying these styles to the entire table, it is possible to target only a particular cell to apply these styles.

Adding Styles Based on Fulfillment of a Condition

‍Appsmith has a feature to create styles for certain table elements that meet a particular criterion. We can define this criterion using JavaScript and state what styles should be applied. To do this, open the property pane on the column you wish to apply this condition.

Above, I have selected the phone column. We will add a condition to set the text color for this column. We will check if the value of each row in the phone column is even or odd and assign it a particular color. We can add JavaScript to the property pane elements using mustache. This is primarily JavaScript code enclosed with two curly-braces {{ }}. We can add JavaScript data or even functions to our table with this syntax.

{{currentRow.phone %2 == 0? "red" : "blue"}}

Some examples of writing JavaScript functions can be found in Appsmith Documentation.

The command mentioned earlier checks if the row is even; if it is, it will be red; otherwise, it will be blue. We can add this to the Text Color option in your column. Enable the JS toggle to write Javascript in the field and add the snippet above.

Here, the fields divisible by two in the phone columns have a text color of red, and the others have a text color of blue. This way, you can apply different styles to your table if specific criteria are met.

Adding Gradient as Cell Background Color

‍Appsmith allows us to add gradients as background color as we would normally add in a CSS Stylesheet. 

Here is an example of a CSS gradient:

linear-gradient(90deg, hsla(217, 100%, 50%, 1) 0%, hsla(186, 100%, 69%, 1) 100%)

We can add this in our background property. The resulting color change will take place in the table widget.

I hope you found this article helpful and now have a better understanding of the Table Widget on Appsmith.

If you have any questions, please don’t hesitate to write to me at umavictor11@gmail.com. I will be happy to help you understand it better. 

Uma is a software developer based in Nigeria who is familiar with various web technologies and frameworks. He is also keen on finding ways to explain things in simple ways. This article was written as part of the Appsmith Writers Program.

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

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

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

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

The Use-case

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

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

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

 In this blog, we will learn the following:

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

You can see the finished application here.

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

Appsmith and Supabase: Setting Up a Project

Get Started on Appsmith

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

Define Database Table

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

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

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

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

 This table will have the following fields: 

- id, 

- created_at, 

- equipment_id, 

- employee_id, 

- due_by, 

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

We will be using this dataset. 

Connect Appsmith and Supabase

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

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

Appsmith: Display Table Containing Equipment

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

Appsmith: Read Employee Table 

Follow these steps to read the employee table:

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

Appsmith: Build Equipment Checkout Form

We can now build the Checkout form. 

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

Here is how we can do it!

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

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

Replace default date with 

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

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

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

{{get_employee.data.map(

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

Appsmith Code Checkout Form Submit Button 

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

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

{

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

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

"due_by": {{DatePickerDueBy.selectedDate}}

}

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

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

View Outstanding Equipment Checkouts

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

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

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

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

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

Follow these steps to read the checkout data table:

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

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

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

This will return values in JSON 

[

  {

   "employee": {

      "id": 1,

      "first_name": "Smita",

      "last_name": "Mehra",

        },

    "equipment": {

      "id": 1,

      "name": "Dell Laptop",

      "image": "url..."

    }

  }

]

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

[See screenshot]

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

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

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

Many companies use questionnaires to keep track of their employee’s health and general wellbeing. Because of the time and complexity involved in building one, many choose to go ahead with off-the-shelf solutions that may be quick to set up but have poor analytics tools. For example, the organization might be interested in knowing specifics about the use of the survey/questionnaires. For example (include one or two examples here) But there’s something better than off-the-shelf, and it can be built using Appsmith.

In this article, I will teach you how to build a custom employee survey dashboard to fit your specific requirements. We’ll achieve this by tying together the best open-source tools in the business to build powerful workflows.

We’re going to cover a lot, but specifically, you will learn how to:

• Use Supabase as a data source
• Use filters in DB queries
• Display data in charts
• Perform email automation with N8N

You can peek ahead to see the app we’ll be building here. Let’s get started!

Project set up

As mentioned earlier, we’ll be using Supabase as our storage DB. Signup for Supabase and create three tables; an employee, a question, and a responses table. You can seed the database with this dummy data

You should also go-ahead to set up N8N. You can signup for the cloud version or self-host it. Create a new workflow, and we’ll come back to it in a bit.

Lastly, signup for Appsmith and create a new app. Go to Datasources > Create New > PostgreSQL and fill the form to connect to your Supabase instance.

Awesome! With this, we have completed all the setup. Let’s begin writing queries.

How to use filters in DB queries

We need to write a query to get questions from the DB. Let’s call this query get_questions, and it should look like this:

SELECT * FROM public."questions";

With the returned data, we can build a question filter. Drag in a select widget and configure its Options property shown below.

The name of the widget has been updated to q_filter

{{
[{label: 'all', value: '%'}]
.concat(get_questions.data.map(q =>({label: q.question, value: q.id})))
}}

Similarly, go ahead to build a team filter. Name the select widget t_filter, and its Options property should be:

[
    {
    "label": "all",
    "value": "%"
  },
    {
    "label": "hr",
    "value": "hr"
  },
  {
    "label": "marketing",
    "value": "marketing"
  },
  {
    "label": "engineering",
    "value": "engineering"
  },
    {
    "label": "logistics",
    "value": "logistics"
  }
]

Using the filters we just built, we can write filtered queries i.e., to get employees based on the selected team and get responses based on the chosen question and team.

Create a new query for employees and update its name to get_employees. The query should look like this:

SELECT id AS employee, name, email, team FROM public."employees"
WHERE team LIKE "{{t_filter.selectedOptionValue}}";

Similarly, the get_responses query for responses should look like this:

SELECT r."completedAt", r.employee, r.response, r.comment, e.age, e.sex, e.team
FROM responses r
INNER JOIN employees e ON r.employee=e.id
WHERE
    r.question LIKE "{{q_filter.selectedOptionValue || '%'}}"
    AND e.team LIKE "{{t_filter.selectedOptionValue}}";

Using a table widget, you can display the employee completion status like so:

And, here’s the snipped used for the Table Data property:

{{
    get_employees.data.map
      (e => _.omit({
        ...e,
        ...get_responses.data.find(r => r.employee === e.employee)

      }, ['sex', 'age', 'response', 'employee', 'comment'])
      )
}}

‍

How to display data in charts

In the last section, we wrote a query to get responses for the selected question and team. Now, we’re going to focus on displaying the data in charts.

From the widgets tab, drag a chart widget into the canvas. We want this chart to display the aggregate number for responses for a particular rating level, i.e. 26 employees rated 5. So for the Series Data property on the chart widget, use the snippet below:

[
  {
    "x": "5",
    "y": {{get_responses.data.filter(r => r.response === 5).length}}
  },
  {
    "x": "4",
    "y": {{get_responses.data.filter(r => r.response === 4).length}}
  },
  {
    "x": "3",
    "y": {{get_responses.data.filter(r => r.response === 3).length}}
  },
  {
    "x": "2",
    "y": {{get_responses.data.filter(r => r.response === 2).length}}
  },
  {
    "x": "1",
    "y": {{get_responses.data.filter(r => r.response === 1).length}}
  }
]

And your chart should look like this:

We can also display responses by gender. For this, we’ll need a sex filter. You know the drill, so go and make one. Here’s mine (named f_sex):

And now, we can display the data using a chart widget set to a Pie Chart Chart Type. Here’s the snippet:

For some extra credit, use the principles above to build a filtered chart to display responses by age group. Here’s mine:

How to perform email automation with N8N

In this section, we’re going to look at how to send out emails from the dashboard using N8N.

For employees who haven’t completed a survey questionnaire, we need a way to resend it to them via email. Let’s build an N8N workflow for this.

The workflow would consist of two nodes, a webhook that accepts post requests and the second being a Gmail node. Here’s a snapshot of the workflow:

The Gmail node should be configured to send an email by setting the Resource to Message and the Operation to Send. The subject should contain a link to the questionnaire form, and the recipient email should be an expression that is evaluated the payload of the webhook as shown below:

Going back to the dashboard, we can create a new API to trigger this workflow. Go to Datasources > Create New > Create New API and enter the URL of the N8N webhook. Give the request a name i.e. send_email, set the request type to POST, and the body would be the email of the employee selected from the table:

Now, going back to the table widget, we can create a custom column of Column Type Button. Configure this column to run the send_email API when it is clicked. Thus, clicking on the button for a particular employee would resend the questionnaire to that employee via email.

Wrap up

Awesome! We’ve been able to build the employee survey dashboard. You can take a look at the app here https://app.appsmith.com/applications/61281a99695af65b46d75843/pages/61281a99695af65b46d75845

You can expand on this and add more features to the app. The floor is all yours! We’d love to see what you add next. By the way, give us a star any time you visit github.

In this article, we will be building a Shopify Dashboard that fits into your custom workflow. But before that, let’s set some context. Lots of store owners choose Shopify as their go-to online store. It’s a mature product with excellent features like the store admin panel and full API access.

That said, it’s not without its flaws.

Integrating with no-native Shopify services can be a real pain, and it’s almost impossible to build custom workflows with the Shopify admin panel. We’ve also published another post on how to make a Shopify admin panel.

In this article, I’ll be showing you how to overcome all of these hurdles so you can build a dashboard app for your Shopify store that fits into your workflow. We’re going to do this by covering the following concepts:

• How to integrate with Shopify APIs on Appsmith
• How to set a custom theme for your Shopify dashboard
• How to use dynamic parameters in API calls
• How to use the Google Sheets integration

Feel free to peek into the app we’ll be building.

Sounds good? Let’s begin!

How to integrate with Shopify APIs on Appsmith

Lots of services provide secure access to resources via APIs. This makes it possible to design complex workflow and unlock new capabilities. Shopify is not exempt from this rule as we’ll be extensively making use of their APIs.

To get started, enable Private apps in your Shopify dashboard. Create a new app and configure the permission level. You’ll be presented with the following details to interact with the API securely: Your store endpoint i.e https://your_store_name.myshopify.com/admin/api/2021-10/ A username And also a password i.e shppa_cc*******

With these details, we can begin building the dashboard. So signup for Appsmith if you’re yet to do so, and create a new app.

In the new app, click on Datasource > Create New > Authenticated API. Be sure to set Authentication Type to Basic and then fill the form. Here’s a quick snapshot of mine:

Using the Shopify data source, let’s create a new API to get all products from the store. Call this get_products and it should be a request to the products.json route.

Now we have data coming into the app. Let’s go build some styled UI components to display this.

How to set a custom theme for your Shopify dashboard

There are many widgets to choose from on Appsmith; the best part is that you can customize and style the look and feel of many. Let me show you how to do this.

From the widgets tab, drag a container widget into the canvas. In the opened configuration menu (called property pane), give it a Border Radius of 5 and set some Box Shadow. You should have a cool looking card container:

Now you can bring in a list widget into the container and bind the data from the get_products query like this:

And similarly, you can display the product image and name by accessing the data from {{currentItem}}. Here’s an example:

Cool! That’s how easy it is to style components by using the Styles section in the property pane.

How to use dynamic parameters in API calls

So far, our app can read data and display it in the UI. Now let’s add a flow that allows us to write data. To do this, let’s build a product update feature.

Using the concepts illustrated in the previous sections, build an update form UI that looks like this:

And should be configured such that all input widgets are named and have default values based on the product selected from the product_list widget i.e the tile input widget is named up_title and has a Default Text of {{product_list.selectedItem.title}}:

Cool. Do the same for all the other input widgets in the form.

Now, using the Shopify data source configured earlier, create a new update query that takes input from the form widgets as parameters. Here’s an example:

‍

{
    "product": {
        "title": {{up_title.text}},
        "body_html": {{up_body_html.text}},
        "product_type": {{up_product_type.selectedOptionValue}},
        "status": {{up_status.selectedOptionValue}}
    }
}

‍

You’ll also notice that the ID of the product to be updated is populated from the product selected on the product_list widget and used in the endpoint URL.

Awesome! To finish this up, go back to the form submit button and configure it to run the update query. We should also add a get_products query so that all data in the app is refreshed i.e:

{{update_product.run(() => get_products.run())}}

And that’s how easy it is to pass dynamic parameters to API calls!

Lastly, let’s talk about how to integrate with Google Sheets.

How to integrate with Google Sheets

This section will be quite advanced, so stick with me! We will build a bulk import functionality from Google sheet, a feature not natively supported by the Shopify API.

To get started, create a new page and under Datasource > Create New, click on Google Sheets. Authorize the datasource and create a new API that we’ll call get_products_from_sheets.

You can use this sample sheet as a reference for the Spreadsheet URL config. Here’s a sample configuration:

Now, you can build UI widgets to display the data returned as we did for the product list. In this sample, I used a table widget:

When the Sync button is clicked, we loop through the items and create each product by making a couple of API calls. Also, notice here that we have parameters passed to the API calls as the third arguments:

{{
    (() => {
      get_products_from_sheets.data.forEach((i) => {
        new_product.run(
          (res, i) => {
            new_image.run(
              () => {},
              () => {},
              { ...i, id: res.product.id }
            );

            new_price.run(
              () => {},
              () => {},
              { ...i, id: res.product.variants[0].id }
            );

            new_track_inventory.run(
              (res, i) =>
                new_inventory.run(
                  (res, i) => showAlert('Added: '+i.Title, 'success'),
                  () => {},
                  i
                ),
              () => {},
              { ...i, id: res.product.variants[0].inventory_item_id }
            );

          },
          () => {},
          i
        );
      });
    })();
}}

‍

And here’s a snapshot of each of the queries call:

You can learn more about these resources/endpoints in the official Shopify docs.

What’s next?

Congratulations, you did it!

So far, we’ve implemented the core functionalities such as viewing, updating, and bulk import of products. But you can expand on this and add additional services i.e., payment with Stripe.

The floor is all yours! We’d love to see what you add next. By the way, give us a star any time you visit github.

Every application involves some level of CRUD operations, be it reading records from a database, creating or updating records, or deleting records. All of which is put together by well-designed application UI and logic.

In this article, I will show you how to build a task manager in Appsmith using Firestore as the database. By doing this, we will cover and solidify core concepts like connecting to databases, writing queries, infusing data to widgets, and writing custom functionality using JavaScript.

In this article, you will learn how to do the following:

1. Connecting to and querying Firestore
2. Binding data to widgets on Appsmith
3. Deploying the application you built

Let’s get started with the first.

1. Connecting to and querying Firestore

Before we go in-depth into using Firestore, you need to create an app. Signup for Appsmith cloud and click on the New button with orange. Now, we can connect the created app to the database.

Click on Datasources > Create New and then Firestore. Fill in the credentials to connect to your firebase instance. To make sure all details are correct, click on the Test button, after which you can save the data source.

To get your Service Account Credentials, go to your project settings in Firebase and generate a new Nodejs private key that will be copy-pasted into your Appsmith app.

Here’s a snapshot of my setup:

Using the Firestore data source, we need to query for data. Let’s write a query to get tasks from the database. To display some data, I have a simple collection called asana that has some seeded data. A query to get tasks would look like this:

The response data in the snapshot above shows the shape of each entry in the database. This will be useful in seeding your database.

Now that we have linked to Firestore and have some data coming in, let’s display that data in the app using widgets.

2. Binding data to widgets

To display the data coming in, we’ll need a List widget. Switch to the widget tab in the sidebar and drag a List widget into the canvas. (The widgets tab is below the app name)

On the List widget, a simple binding can be written like this:

Similarly, we can display the data for each item in the List widget by bringing in Text widgets and writing their corresponding bindings.

That’s all you need to know about binding data to widgets; write {{}}, and you’re good to go.

Before we move on to deploying the app, let’s spend a few minutes building a form that will allow us to update/delete tasks.

To do this, drag in a Form widget into the canvas, and use a couple of text widgets to display the information about the task selected from the List widget, such as clicking on a new item on the list will automatically update the form. Your binding should look like this:

You should also do the same for the Datepicker, Input, and Select widgets required to build the form. For example, you should configure the selected widget this way:

![select widget configuration] (res.cloudinary.com/confidenceappsmith/image..)

You should also rename your widget to something more descriptive as I did in the Select widget shown above

Now, using the Firestore datasource configured earlier, you can write an update query like this.

And then, configure the submit button on the form to call the update_task query when clicked.

Wasn’t that was quite easy? Using the same principles, you can write a query to delete the task.

3. Deploying the application

This is by far the easiest step. To deploy an app, click on the Deploy button to the top right corner of the screen.

You may also invite others to use/develop the app or even make it publicly available. You can use the share button just beside the Deploy button. This brings up a modal:

Wrap up

You’ve made it! You’ve learned the core concepts of building apps with Appsmith, and I’m sure you’re ready to put this knowledge to use.

I’m going to leave a link to the app here with more features added to make it production ready. Click on this text to see the app.

Awesome! Go into the world and build! (Also consider giving us a star on GitHub)

If you have specific questions for me, just send me an email and I’ll be happy to help!

Many internal tools, especially administrative applications, often require file uploading and file management features. File pickers, and file selectors, therefore, are some of the most used components in internal tool development. Developers experience numerous challenges when it comes to implementing these features resulting in delayed operations. This can easily be mitigated by using Appsmith to build and deploy applications quickly. Appsmith has a dedicated filepicker widget that can be used to upload files to any of the developers' preferred cloud storage services, such as Cloudinary, S3, among others. 

In this short blog, we’re going to focus on one such widget, the filepicker. With this, you can upload files from your local machines to any cloud storage via API. You can upload files by implementing a POST API and reference the base64 or binary version in the post body.‍

Features of the Filepicker Widget‍

1. The Allowed File Types properties allow us to restrict the types of files that a user (of our application) can upload.

2. The Maximum File Size attribute allows us to specify the total file size that a user can upload.

3. When the user selects files to be uploaded, we can immediately use an API or utilize the S3 plugin to upload the file's base64 to our cloud storage.

4. The visibility property controls the widget's visibility on the page. When the widget is switched off, the widget will not be displayed when the app is published. 

5. There is a parameter that allows us to specify the maximum number of files that a user is allowed to upload.

How Does the Filepicker Widget Work?‍

In this section, we'll look at how the filepicker works. First, let’s set up a new application to get started!

• Create a new account on Appsmith (it’s free!), if you are an existing user, log in to your Appsmith account.
• Create a new application by clicking on the Create New button under the Appsmith dashboard.
• We’ll now see a new Appsmith app with an empty canvas and a sidebar with two directories: Datasources and Widgets. This is where we can connect to data sources and find all the widgets.
  

Now, we’re all set to explore how the filepicker widget works in Appsmith.

Drag and drop the filepicker widget (as shown in the image below). 

Now that we have our filepicker widget on the canvas, we can customize the properties as desired. 

After selecting our file(s), we get the following result:

How to manage files on Cloudinary using Appsmith

‍

Filepicker configuration

We'll go over each of the configurations accessible in the Appsmith filepicker one by one.

Label: This is used to set the label of the Filepicker from the default “Select Files” to any label of our choice.

Maximum No. of files: This allows us to specify the maximum number of files that a user is authorized to upload. 

Maximum File Size: This property allows us to specify the maximum file size that a user can upload.

Data Type: This determines the data format of the files uploaded. 

Allowed File Types: Allows us to restrict the types of files that a user can upload. 

Accepts an array of wildcardsimage/*, exact mime types image/jpeg, or file extensions .jpg:['image/*', '.jpg', '.jpeg', '.png', '.gif']

Required: When turned on, it marks user input as a required field and disables form submission until the input is made.

Visible: Controls widget's visibility on the page. When turned off, the widget will not be visible when the app is published.

See the documentation to learn more.‍

‍

Uploading single and multiple images to cloud storage via Filepicker using a Cloudinary API

Let's now explore how to upload single or multiple files using filepicker to cloud storage using the Cloudinary API.

Setting up a data source

We'll need to connect to our data source by clicking on the Plus icon next to the data sources link in the sidebar, then selecting "Create New" from the menu, as shown below:

After this, we should now choose the "Create New API" option. This will take us to the screen shown below:

We renamed the datasource from the default "Api1" to "Cloudinary API" in the image above to have a clear description. 

We will be making use of the Cloudinary API: `https://api.cloudinary.com/v1_1/{cloud_name}/image/upload` where {cloud_name} is our cloudinary username.

The API requires our Cloudinary cloud_name and upload_preset.

To get our cloud_name and upload_preset, we follow the steps listed in the following sections. 

The cloud name is obtained from our Cloudinary dashboard, as shown below.

An upload preset can be found in the “Upload” tab of our Cloudinary settings page, which we access by clicking on the gear icon in the top right corner of the dashboard page.

We then click on the Upload tab on the settings page:

We scroll down to the bottom of the page to the upload presets section, where we see our upload preset or the option to create one if we don't have any.

Adding the Cloudinary API URL and setting the header should look similar to what we have below:

Let's go to the "Body" tab and configure it, as shown below.

We specified the file(s) to be uploaded as well as our upload preset in the image above, and we configured our request body in a multipart structure.

Now that we've finished configuring the API, let's put it to the test before using it in our filepicker.

We will click on the FIlePicker Widget as shown below and select a file to be uploaded from our system.

Now let’s head over to the API section; the following is the test result:

Yay! That works! We’ve successfully tested our API and got a valid response.

Upload images using Filepicker

Let's upload a single image directly from our filepicker to cloud storage by connecting to the API Datasource we just created. We will head over to the filepicker configuration and update the "onFilesSelected" property.

As seen below, we can add a success alert to show when the image is successfully uploaded and when there is an issue after selecting our query to be conducted.

The response below shows a success alert after uploading our image to cloud storage utilizing the Cloudinary API directly from our filepicker widget.

We hope that you found this short guide helpful. If you want to know how to upload or download files from S3, you can look at this document and follow this discussion. To learn more about how to upload a file to a local server, take a look at this discussion on our community forum. 

Appsmith has more than 35 UI widgets, and these can cut your development time in half! To know more, head to our docs! 

Did you get stuck somewhere? We’re happy to help! Join us on Discord today! 

Olubisi Idris Ayinde is a software engineer, technical writer, and community builder. This article was written as part of the Appsmith Writers Program. If you’d like to write for Appsmith, send an email to writersprogram@appsmith.com expressing your interest.

Automation has become the ‘it’ thing in the world of business. The need for organizations to cut costs has forced them to improve internal processes to provide improved service while also conserving time. Unfortunately, it's not always easy for teams to streamline everything internally because they often seem like complicated processes - but there are ways around this. Using Appsmith, developing internal tools can cut costs, time and even improve your workflow by connecting services across specialized platforms with Zapier.

Zapier has thousands of apps and services that can meet almost all your business automation needs. Integrating Appsmith into the mix opens up a whole new dimension. In this way, you'll have a more efficient system in which you can connect anything from, say, a MailChimp email campaign to a Google form with data from your ERP or CRM systems delivered within the app!

In this blog, we will be looking at adding automation to Appsmith applications using Zapier.

Let’s get started!

Building Internal Tools with Appsmith

Appsmith is a powerful platform to build admin panels, dashboards, crud apps, and internal tools. It allows us to create these applications with little to no coding required. We can build apps that integrate with just about any service you can think of. Today, Appsmith has more than 15+ data source integrations; a few popular ones include Postgres, MongoDB, Google Sheets. That's one of the best things about Appsmith -- it's completely customizable. But if you want to build apps with Appsmith, you will likely be doing some coding in JavaScript. Yes, we can use JS anywhere across the Appsmith applications to bind, transform and create actions to the data. In terms of UI, Appsmith provides more than 25 widgets with a wide range of styling options. While it's not hard to use Appsmith to build apps, you will need to start building them to get to the next level with Appsmith.

Now, let’s build a simple newsletter application that can help collect contacts from Appsmith and send Emails using Sendinblue via Zapier.

Building UI on Appsmith

The idea of our newsletter is to collect emails from users and to maintain campaigns on Appsmith. Let’s get started by following the below steps:

1. Create a new account on Appsmith (it’s free!), if you are an existing user, log in to your Appsmith account.
2. Create a new application by clicking on the Create New button under the Appsmith dashboard.
3. We’ll now see a new Appsmith app with an empty canvas and a sidebar with two directories: Datasources and Widgets.
4. Click on the + icon next to the widgets section and drag and drop a container widget onto the canvas.
5. Next, bring in a form widget inside the container and two input widgets inside the form.
6. We can configure widgets and their properties by clicking on the cog icon on the top right. This will open a property pane where we can update all the widgets settings.
7. Now set the form labels to name and email.
8. Additionally, you can add a text widget to add aesthetics to our application; the following is how the UI looks like:

Excellent, our UI is now ready; let’s create a new Zapier workflow to connect to SendInBlue and manage our newsletter.

Set Up Zapier with Appsmith

Excellent, our UI is now ready; let’s create a new Zapier workflow that can connect to SendInBlue and manage our newsletter.

• First, create a free account on Zapier and select create custom workflow option from the dashboard.
• On Zapier, every action on the workflow is named on Zap; to establish a connection between Zapier and Appsmith, let’s create a Webhook.

Webhooks are essentially a way to create a communication channel. It allows you to listen to an event and run a command or take any action you want. Here, the event will be triggered by an API call from Appsmith.

• Now open the Appsmith app, click on the + icon next to the Datasource section from the sidebar, and choose Create New API.
• Copy the webhook URL from Zapier and set the request type to POST. For ease, let’s also rename the query to addContact.
• Here, we also need to send the data from the Form widget we had created earlier; now, let’s use their names to collect data and pass them in the Webhook.
• Navigate to the Body tab on the API and paste the following code snippet:

{
"name":"{{Input1.text}}",
"email":"{{Input2.text}}"
}

• This code snippet will send the information from the Input widgets to Appsmith.
• Lastly, set the button onClick property to execute a query and set it to addContact.
• Now, on Zapier, hit the test button and send a request by filling the form on Appsmith and hitting the API. We should see the successful response on Zapier.

• Next, click on the + icon beneath the zap to add a new workflow. Select the SendInBlue Zap from the list.
• We will need to authenticate with SendInBlue from Zapier to access the workflow; for this, you can either create a new account or log in with your existing credentials.
• Next, set the Action type to Add or Update in Sendinblue and fill in the details related to Email; here, we can also use the data sent from the Webhook. In the following screenshot, we select the EMail field data to be the email received from the webhook.

• Similarly, you can add a Send Transactional Email in Sendinblue action to the workflow using the Seninblue Zap.

With this, whenever a new user subscribes to the Newsletter, he receives a confirmation Email and will be added to the contact list on Sendinblue.

And just like that, we could configure and build an entire Newsletter manager without writing code. This application can be extended in multiple ways, such as creating new email campaigns, filtering contacts, blocking users, and many more! The entire demo app of this tutorial can be found here. This is just one example of how you can automate your apps on Appsmith!

We hope that you will have a better idea of how to leverage automation in your Appsmith applications. For more information on automating your Appsmith applications, please contact me anytime at vihar@appsmith.com.

Every job has a set of critical and essential tasks that can feel mundane to execute. As a developer advocate, I love talking to developers and new users and helping them out on their journey with Appsmith. While I am busy doing this work, some tasks often slip into the basket of “I’ll get to it soon.” These tasks often include pushing out information about our new content in specific channels on Discord. This is crucial work and helps us, but it gets deprioritized when there are many other things to do. For social media, a tool like Hootsuite has been a game-changer for us. We also built our custom social media scheduler with Appsmith and automated it using n8n. We also wanted to solve this for our community on Discord. And that’s when we came up with our comprehensive push messaging system, which automatically pulls up content from various channels like our blogs, Twitter handle, and Luma events. We can just share it with users on our Discord server in just one click!

The Discord Message Pusher has been an excellent internal tool for us; the entire content and marketing team uses it, and guess what? No more pending tasks on our to-do lists! In this blog, I am going to discuss the steps involved in building this application on Appsmith. And hopefully, like me, you can make a customizable internal tool for your content team.

Let’s start! 

Connecting Data Sources (APIs) on Appsmith

In the first step, we will be creating a new application on Appsmith and integrating it with different data sources like Twitter, Youtube (YT), and Webflow. We’ll be using APIs to fetch all the information and render them on the Appsmith widgets. First, let’s set up a new application to get started!

• Create a new account on Appsmith (it’s free!), if you are an existing user, log in to your Appsmith account.
• Create a new application by clicking on the Create New button under the Appsmith dashboard.
• We’ll now see a new Appsmith app with an empty canvas and a sidebar with two directories: Datasources and Widgets. This is where we can connect to data sources and find all the widgets.
  

Fantastic, the app is now set up, now let’s fetch all the information from a particular YT channel and render them onto a list widget.

Setting up Youtube <> Discord Push Messaging System

In this step, we’ll be connecting to YouTube using APIs. Appsmith provides an easy-to-use IDE-based environment to connect to APIs and configure them. Now follow the below steps to fetch all the Youtube video links and their information.

• Click on the + icon next to the data sources section and choose the Create New under the APIs section.
• Paste the following URL:
  

https://www.googleapis.com/youtube/v3/search?key=APIKEYHERE&channelId=UCMYwzPG2txS8nR5ZbNY6T5g&part=snippet,id&order=date&maxResults=20

• This API will allow you to fetch all the videos from a particular channel using its ID. We’ll need to create an API key from the Google Console account to put it in use.
• Rename the API to getYTData by double-clicking on the existing one. 
• Additionally, Appsmith automatically parses the URL and adds them into the parameters tab, where you can update the ID and the API key. Following is the screenshot of how it looks like: 

• Here, we can configure all the settings for this API. Additionally, we can turn on pagination settings if we’re working with massive sets of data.
  
  

We now have all the videos on Appsmith Youtube Channel (Appsmith YT ID: UCMYwzPG2txS8nR5ZbNY6T5g). Now, let’s use this query to render all the information on the application by following the below steps:

• Click on the + icon next to the Widgets section, here we’ll find the list of all the widgets that we can use for building the application.
• Now find the Tabs widget, drag, and drop it onto the Canvas.
  

> The tabs widget allows us to create different navigation tabs. We’re using this as it’s more suitable for adding navigation between social platforms.

• Next, open the property pane of the tabs widget by clicking on the cog icon on the top-right corner of the device. This is where we can configure the entire widget.
  

• Here, under Tabs property, we’ve added found different tabs to navigate between our social contents. Also, the `Default Tab` property is set to Youtube.
  
  

Listing all the Youtube Information to List Widget

We have our tabs ready. We’ll display all the videos from the Appsmith channel to the List Widget under the Youtube tab. Follow the below steps:

• Drag and drop a new list widget inside the Youtube tab, adjust the width to half so that the other half can be used to write the messages to push them on Discord. 
• Open the List widget property pane and set the List Items property to:
  

{{getYTData.data.items}}

• Initially, we see the list widget already populated with pokemon data. To make changes to them, we just need to edit the first item, and the others automatically get populated. 
  

• Delete all the widgets on the first item on the list widget, and add one image widget and three text widgets. Now set the following properties to these widgets:
  
  

Image Widget Value : {{currentItem.snippet.thumbnails.high.url}}

Text Widget 1 Value: {{currentItem.snippet.title}}

Text Widget 2 Value: {{moment(currentItem.snippet.publishedAt).format("LL")}}

Text Widget 3 Value:

https://www.youtube.com/watch?v={{currentItem.id.videoId}}

Here, we’re accessing the currentItem, which evaluates the element in the list values. On the first text widget, we’re displaying the Title of the Youtube Video; the second widget corresponds to the time it was published. On Appsmith, we can use moment to display the DateTime field in the necessary format. Cool right? Lastly, we display the YT video URL and add the id to the end of the URL.

Here's how the list widget looks like:

Excellent, next. Let’s learn how to push messages from Appsmith to Discord. Follow these steps:

• Drag and drop the container widget next to the list widget. Add Input widget and a button widget. Additionally, we can also add some Text widgets to customize and style the UI of the application accordingly. 
• Now set the default value of the Input widget to the following: 
  
  

Here, in the code snippet, we have a default message; this will help us push it directly to Discord. Also, we’re accessing the title and youtube video id from the list widget. Therefore whenever a new list item is clicked; the message automatically gets an update for you.

Let’s now use a Webhook to push these messages on a discord channel. For this, you’ll need a discord server and a dedicated channel.

Now follow these steps:

• Navigate to the channel on Discord and click the cog icon next to it. 
• Click on Integration and choose webhooks, and copy the given URL.
  

• Click on the + icon next to data sources and click create new. Add the copied URL and set the request method to post. Now open the `Body` tab under the API configuration and paste the following:
  

{"content":"{{Input1.text}}"}

• Here, we’re sending the contents from the Input widget to this particular request. Now, as soon the API is called, the data is to the Webhook, and the contents will be posted on this specific Discord channel.
• Rename this API to pushYTVideo
• Lastly, let’s go back to the page and configure the button onClick to call this API. Open the button widget property pane and set the onClick property to call a query, and choose pushYTVideo
  
  

Awesome! When you choose or select a video from the list widget, the message automatically updates from the default value and will be pushed to the discord channel. 

Setting up Twitter <> Push Messaging System

This part is almost similar to how we’ve listed all the videos from Youtube and drafted a custom message; the only thing that changes is the data source. Instead of YouTube, we’ll be using the Twitter API.

Now, let’s copy-paste all the widgets from the YouTube tab to the Twitter tab; for this, select the YouTube tab, hit CTRL/CMD + A, CTRL/CMD + C, open the Twitter tab, and hit CTRL/CMD + V. This will copy the list widget, the buttons, and the inputs.

Just like that, all our widgets will be copied to the tab. Now, let’s add the functionality to fetch all the tweets and push them to the Discord channel by following the below steps:

• Click on the + icon next to Datasources and hit Create New, rename the API to getTwitterData, and add the following URL:

https://api.twitter.com/2/users//tweets

• For example, let’s update the id with: https://api.twitter.com/2/users/1151016626235437056/tweets. To setup your own Twitter profile, you’ll need to copy the ID.
  

• We’ll need to add the Authorisation key to the API. For this, we’ll need to have a developer account on Twitter and create the API keys. 
  

• We can configure this under the API settings.

Following is the screenshot:

Now let’s update the widget properties. 

To show all the tweets on a list widget, open the property pane and set the Items value to {{getTwitterData.data.data}}. Next, add two text widgets to the list widget and set the Text Value to the following:

Text Widget One: Tweet ID {{currentItem.id}}

Text Widget Two: {{currentItem.text}}

On the right, in the Discord Push Message Input placeholder to following:

Checkout our latest tweet here: https://twitter.com/theappsmith/status/{{List1Copy1.selectedItem.id}}

This is how the application looks like now:

With this, we should be able to schedule/push our social posts from Appsmith to Discord. In case if you’re using other services like Webflow, you can utilize APIs to fetch objects from CMS and use the same template. Building this app from scratch, including writing snippets of code, is likely to take 30 minutes! Isn’t that fast?

If you liked this tutorial, and are planning to build this, let me know. I’d love to help you make it as complex as you’d like.

Write to me at vihar@appsmith.com.

Catalogue management is hard. But building a catalogue management app for your company is harder. Not only does the application have to connect to multiple tables/API, and pull data on inventory, price and even offers, but the application also needs a robust UI that lets the user manage this catalogue easily.

Not only does this take a lot of time to build and maintain, but direct access to the database or an API endpoint that could return private information could also prove to be a problem. GraphQL helps solve these problems inherently in the way it returns only what it is queried for.

Because of these benefits, GraphQL is a great interface to develop an internal application on and to make development and maintenance easier, a low-code tool like appsmith can be very useful and will help speed up the process of development.

In this tutorial, we'll learn to create an application to manage a games store's catalogue running a GraphQL backend. We'll be using appsmith to build the application and implement CRUD operations on the database. Here's the link to the final application.

Appsmith is an open-source framework that lets developers build dashboards, workflows, and CRUD apps with only the necessary code. You can connect to any API or databases like MongoDB, PostgreSQL, or MYSQL and get access to multiple widgets including charts, tables and forms for building a UI really fast.

Initial Setup

To build this application, first, we’ve collected a dataset from Kaggle that has a list of games, their prices, rating, average playtime and genre they’ve belonged to. We’ve created a mock database out of this, hosted it on Hasura and exported them as GraphQL APIs. Below is an image showing some of the attributes of the dataset.

Here, as we can see, the response consists of data of games in a JSON format. Next, we’ll use Appsmith to utilise this database and build a Store Manager by writing different GraphQL queries.

Getting started with Appsmith

To begin, we'll need to sign up for Appsmith (it's free!), if we don't already have an account. We can also deploy a Docker image on a server. For this tutorial, we're going to create an application on Appsmith cloud.

1. Sign in to Appsmith Account
2. Click Create New to create a new application and name it GraphQL Store Manager. This takes you to an empty workspace with a sidebar.
3. On the sidebar, you should see items like Pages, API, and DB Queries in a folder-like structure. Under Pages, a new page, Page1, already exists. Rename it as Store Dashboard.

Below is a screenshot of the application:

Connecting to the Database and Creating the first UI View

To work with Appsmith, you need a database. This can be in the form of an API or a database. Appsmith supports MongoDB, MySQL, and PostgreSQL, among others. In this case, we’ll be connecting to Hasura, as it'll be our GraphQL backend server. To establish a new connection, follow these steps:

1. Make a connection to a database by clicking the plus icon near APIs on the sidebar under a page.
2. Now, rename your API to FetchGames
3. We’ll write a POST request and use GraphQL query to list down all the games in the database.
4. Next, use the following URL as the data source to access the mock database hosted on GraphQL: https://mock-as.hasura.app/v1/graphql
5. We’ll have to set headers in order to access the database. Set content-type to application/json and x-hasura-admin-secret to qpN7KbsgIoKcuuC1sL3xiaIN2eegukvPPweg6MW1RkM8KUkaHAi85U3FOVj0Zkxm
6. Next, we’ll have to write a GraphQL query in the request body to list all the database games. This will require an object with query as key and the GraphQL query value associated with it to fetch the games.

{
  "query": {{JSON.stringify(`
    query {
        steam_games(limit: 100, order_by: {appid: asc}) {
            name
            appid
            average_playtime
            genres
            price
        }
    }
  `)}}
}

Here, we have the query stringified with moustache syntax to ignore all the line breaks. Inside the query, we have the table name we want to query steam_games with a limit parameter set to 100 and an ordey_by parameter to order all the games by the appid ascending order using the asc keyword. Inside the query, we’ll have to use the fields we want to fetch. Now hit Run to run the query. You should see a list of games in a JSON field in the response tab.

Awesome! You now have your store catalogue with all the games and their prices. Let’s now build a UI to display all these games on a beautiful table.

1. Navigate to Page1 and click on the plus icon next to the widgets.
2. Now drag and drop a table widget onto the canvas; we'll see a new table with some pre-populated data.
3. Next, we’ll have to open the table’s property pane to update the table data by clicking on the cog icon.
4. Inside the property pane, we’ll find a Table Data property, and here we’ll have to bind the FetchGames API. Use the moustache syntax and add the below code snippet:

{
{FetchGames.data.data.steam_games}
}

This will pull data from the FetchGames API we’ve set up in the previous section.

Beautiful! We now have our catalogue displayed on a table. Next, we’ll create a form where we can add new games to the database.

Implementing Create Operation on Database

In this section, let’s look at how we can add new values to the database by building a simple UI. For this, let’s create a new Form on Appsmith that takes in all necessary fields to post in our game store database.

1. Now, go back to the page, and drag and drop a form widget. Inside the form widget, we’ll add some Input widgets that accept necessary fields to add a new game to the database.
2. We’ll also have to name the input fields so that we’ll have to reference them while posting values from the GraphQL query.
3. Additionally, we’ll also have some text widgets that’ll look like labels for the Input widgets. Below is how we’ll have to name the text widgets and associated input widgets.

Name : nameInput

AppId : appIdInput

Average Play Time : avgPlayTimeInput

Genres : genresInput

Price : priceInput

You can simply do this by going to the input widget’s property pane and double-clicking on the existing widget name.

Below is a screenshot how the form should look like,

Perfect, we now have our UI ready; let’s create a GraphQl API to post data into the Database.

1. Create a new API by clicking on the plus icon next to the APIs, rename the API to InsertGame.
2. Set the content-type andx-hasura-admin-secretheader to access the database on Hasura from the previousFetchGames` API.
3. Now we’ll have to write a GraphQL query inside the request body, paste the below code snippet inside the Body:

{
    "query": {{JSON.stringify(`
    mutation ($object: steam_games_insert_input!) {
        insert_steam_games_one(object: $object) {
            appid
            name
            average_playtime
            price
            genres
        }
    }
    `)}},
    "variables": {
        "object": {{JSON.stringify({
            appid: appIdInput.text,
            name: nameInput.text,
            average_playtime: avgPlayTimeInput.text,
            price: priceInput.text,
            genres: genresInput.text,
        })}}
    }
}

Here, we have an object consisting of a query key that holds the GraphQL query and a variables key with all the variables that need to be posted to the database.

Now, in the query, we have a mutation that lets us modify the server-side data. Inside the mutation, we’ll send the object that references the data we will be querying. The insert_steam_games_one allows us to post one object to the database. Inside this query, we can add the attributes we need to post. Next, we’ve associated the variables to the object from the created UI input widgets. The .text on the input widget name helps us get the text written inside the input.

One last thing now that you’re API is ready, we’ll have to call the API when we submit values from the Form. Now go back to the form, open the property pane of the submit button and set the onclick action to call an API. You’ll now find the list of APIs we’ve created, select the InsertGame API. Below is the GIF explaining the same.

Awesome, now that we have our POST request ready, we can try adding a new game to the store using the form we’ve created.

Updating the Store Item on Database

In this section, let’s look at how we can update the database’s values by building a simple Modal. For this, let’s create a new column consisting of a button in the table; once the button is clicked, we should see the modal with existing values and modify it after submitting it.

1. Open the Table Property Pane, and click on the Add New Column option.
2. Now set the column type to Button and the label to Edit.
3. We’ll have to open a new modal when the Edit button is clicked, hence under the actions, select Open a New Modal and create a new modal and name it to editItemModal.
4. Now add necessary inputs widgets and text widgets to edit the store item.
5. Name the input widgets and their default text to the following:

Name Input: nameEditInput

Default Text: {{Table1.selectedRow.name}}

Average Playtime Input : avgPlayTimeEditInput

Default Text: {{Table1.selectedRow.average_playtime}}

Genre Input : genresEditInput

Default Text: {{Table1.selectedRow.genres}}

Price Input: priceEditInput

Default Text: {{Table1.selectedRow.price}}

The default text will allow us to display the selected row data on the table.

Now, let’s create an API that performs update operations on the data.

1. Create a new API by clicking on the plus icon next to the APIs, rename the API to EditGame.
2. Set the content-type andx-hasura-admin-secretheader to access the database on Hasura from the previousFetchGames` API.
3. Now we’ll have to write a GraphQL query inside the request body, paste the below code snippet inside the Body:

{
 "query": {{JSON.stringify(`
     mutation ($object: steam_games_set_input!) {
         update_steam_games(where: {appid: {_eq: "${Table1.selectedRow.appid}"} }, _set: $object) {
             affected_rows
         }
     }
 `)}},
 "variables": {
     "object": {{JSON.stringify({
         name: nameEditInput.text,
         average_playtime: avgPlayTimeEditInput.text,
         price: priceEditInput.text,
         genres: genresEditInput.text,
     })}}
 }
}

Here, we have an object consisting of a query key that holds the GraphQL query and a variables key with all the variables that need to be posted to the database.

Now, in the query, we have a mutation that lets us modify the server-side data. Inside the mutation, we’ll send the object that references the data we will be querying. The steam_games_set_input allows us to update an object in the database. Next, we’ve associated the variables to the object from the created UI input widgets from the Edit Modal. The .text on the input widget name helps us get the text written inside the input.

Now we’ll have to call the EditGame API after the modal is submitted, hence open the button’s property pane and add the following code snippet under the onclick action:

{{
    EditGame.run(() => {
        FetchGames.run();
        closeModal('EditModal');
    })
}}

This will update the item on the store and refresh the table, by re-running the FetchGames query.

Deleting the Store Item on Database

This section looks at how we can delete the database’s values by building a simple button on the table. For this, let’s create a new column consisting of a button in the table; if clicked, the selected item should be deleted.

Open the Table Property Pane, and click on the Add New Column option. Now set the column type to Button and the label to Delete.

Now let’s write a Delete API and link it to the created button.

1. Create a new API by clicking on the plus icon next to the APIs, rename the API to DeleteGame.
2. Set the content-type andx-hasura-admin-secretheader to access the database on Hasura from the previousFetchGames` API.
3. Now we’ll have to write a GraphQL query inside the request body, paste the below code snippet inside the Body:

{
    "query": {{JSON.stringify(`
    mutation {
        delete_steam_games(where: {appid: {_eq: "${this.params.appid}"} }) {
            affected_rows
        }
    }
    `)}}
}

Now, in the query, we have a mutation that lets us modify the server-side data. The delete_steam_games allows us to delete an object in the database based on the selected appid. The appid from the table is referenced using this.params.appid.

Lastly, let’s call this API, after we click the delete button, go to the delete button property pane and select the Call an API action in the actions and select the DeleteGame API.

To refresh the table, after deleting an object, you can add custom JS in the button's onclick property:

{{
DeleteGame.run(() => FetchGames.run(), () => {}, {appid: currentRow.appid})
}}

This code snippet will first, delete the Game and then fetches the data again and updated it on the table.

Conclusion

You've seen how easy it is to build an application on Appsmith, specifically a catalogue dashboard. This guide covered how to create an application and connect it to a GraphQL, as well as how to create, read, update and delete data. You learned how to build interactive pages, work with widgets, and customize them for your purposes.

Building a dashboard with Appsmith is a fast way to build admin tools. Check it out if you’d like to save your developers time and get internal tools launched faster.

Cover Image: Photo by Charisse Kenion on Unsplash

by Kayode Alade

The daily standup has become a norm in the schedule of most developers around the world. A standup is a daily team meeting, at a specific time for a specific duration, that asks team members to answer three major questions:

1. What did I work on yesterday?
2. What am I working on today?
3. What issues are blocking me?

The daily standup answers these questions but does not resolve them. When put to good use, daily standups increase team productivity and also enhance cohesion between all the parties involved.

Tutorial Overview with Appsmith

In this tutorial, you’ll learn how to build a daily standup application using Appsmith, an open-source framework for building internal tools, admin panels, dashboards, and workflows. You’ll be using Appsmith to forward a summary of daily standups to Slack. Using a web framework like Appsmith is a much quicker way to add this feature to your workspace than building a completely new internal tool.

Appsmith comes out-of-the-box with prebuilt widgets like forms, charts, and maps that you can easily configure to your team’s needs. It also supports APIs and different types of databases. For more details about its capability, visit their official GitHub page.

Setting Up the Application and Data Model

First things first: head over to Appsmith to get a free account. After you sign up, it’s time to set up the user interface of your standup app.

• Click Create New on the dashboard to create a new app. You will be taken to an empty canvas as shown below where you can start creating your app. The explorer sidebar on the left is used to add widgets, create pages, and connect to APIs and data sources such as Firestore.

• To build all the features needed for this app, you’ll need to create two pages in Appsmith. Double-click Page1 to rename it as First Page.
• On the Pages bar, click the + icon to add a page, then double-click to rename the new page as Second Page.

Now that you’ve created your two pages, it’s time to start adding widgets. Your app’s first page will contain:

• A personalized welcome message
• A paragraph showing yesterday's standup
• A text area where the user can enter what was done the previous day
• A text area to write out what they plan to do today
• An option field to show their blockers
• A table to show users who completed yesterday’s tasks
• Submit and reset buttons

Let’s create the custom welcome message next:

• Navigate to the First Page and click the + icon beside Widgets to add a new widget.
• Drag the text widget and drop it on the canvas.
• Type in a custom welcome message as shown below.

Next, let’s display yesterday’s standup to-do on top so that you can see at a glance what you planned to do yesterday and then make plans based on that for today.

1. Add two text widgets side by side on your canvas.
2. Label the first Last Standup todo. The second widget will hold the value, or what was on the last standup to-do list. This will eventually be drawn from the database, but for now, you can pre-populate it with filler text.
3. Style the widget’s text as you prefer via the Settings gear at the top right of each widget.

As mentioned earlier, the goal of a standup is to provide information about the previous day’s tasks, tasks that need to be done today, and anything standing in the way of accomplishing those tasks. Obviously, you’ll need a form to input all that information.

To create a form:

• Drag the form widget from the sidebar onto the canvas.
• Label the inputs or dropdowns appropriately (eg, User, Yesterday’s todos, Yesterday completed, and so on). Note that the form widget comes out-of-the-box with Reset and Submit buttons.

• Rename the form by double-clicking on the default name and editing it. Naming this particular form seemed unnecessary, so that the title widget in the form was deleted.
• To delete a widget, hover over it, then right-click the widget name at the top right corner. In the dropdown menu, you’ll see a Delete option. Click to delete the widget.

To finalize your first page’s UI, let’s add a table to display the users who’ve submitted their standup for the day:

• Drag the table widget onto the canvas. Note that the Table Data option in this widget already contains an array of objects. Later, you’ll change this to a query response from your database.

• Navigate to your Second Page, where you’ll add your table.
• Drag the table widget onto the canvas.
• Open the table options and add a new column called Actions.
• Click the Settings gear above the Actions column and set the following properties:
• Column Type: Button
• Label: Edit
• onClick: OpenModal
• Modal Name: New Modal

• In the Actions column you just created, click the button that now reads Edit. A new modal will popup, which you’ll use to edit the table’s data.
• Change the title text widget to Edit Table.
• Drag a text widget into the modal and set the following properties:
• Text value: Username
• Text align: Left
• Text style: Label
• Add a dropdown widget beside the label you just created. In the Settings for that widget, set Selection type to Single Select. This dropdown, which ought to display all users of your app, will read data from your database after connecting the database to Appsmith later in this tutorial.
• To add a field for blockers, drop in a text widget, name it Blocker, and add a dropdown widget as you’ve done previously.
• Add one field each for today’s to-do and yesterday’s to-do. These will take a text widget and an input widget each.
• Finally, add a field to confirm if yesterday’s to-do is complete. Drag over a text widget and a dropdown widget with the values Yes or No.

Connecting Your Database

Appsmith allows you to link data from several databases. For this tutorial, you’ll make use of Firestore.

• In Appsmith, click Second Page on the sidebar, then click the + icon beside DB Queries.
• Select Add a new data source.
• Select Firestore.

• Create a Firestore database to get the project ID.
• From your Firebase console, click the Settings gear on the sidebar.
• Copy your project ID and paste it into Appsmith. Your database URL is https://_your-project-id_.firebaseio.com.

• Back in your Firebase console, click the Service accounts tab.
• Click Create service account. The JSON file containing your service account's credentials will download.
• Copy the contents of the file and paste it into the Service Account Credentials field.
• Click Test so that Appsmith can verify everything is correct, then click Save.
• Back in Firestore, click Start Collection to create a collection, or database table. Set the Collection ID to User and add fields for name and email, both as string type. Sample user values will work for each, eg Chris for the name value and chris@email.com for the email.

• To add a collection named StandUps, add fields for date (in seconds), today's to-dos, yesterday's to-dos, completed, and blocker in Firestore.

Note that since you’re building an internal app, you can create more users and standups in their respective collections.

Creating Standup Queries

Mustache syntax ({{...}}) allows you to write JavaScript in Appsmith to read data from elements defined on a particular page. Let’s take advantage of this to pull information from queries or other widgets. First, let’s create the queries:

1. Click the + icon on the DB Queries menu. You should see your database as an option.
2. Click New query on the top right corner of your database option.
3. Rename it to createStandUp.
4. In the Method dropdown of the createStandUp window, select Add Document to Collection.
5. Set the database to the name of your database in Firestore. Fill in the body with the following code:

{
    "yesterday": "{{Input3.value}}",
    "user": "{{Input2.value}}",
    "blocker": "{{Input5.value}}",
    "todos": "{{Input4.value}}",
    "prev_completed": "{{Dropdown2.value}}"
    "date": {{Date.now()}}
}

Note that widgets in Appsmith are global objects, so you can access their values simply by calling widget_name.value.

Continue to round out your app’s queries:

• For fetchUsers, set the following properties:
• Method: Get Documents in Collection
• Document/Collection Path: users

• For fetchStandUps, set the following properties:
• Method: Get Documents in Collection
• Document/Collection Path: standUps
• Order By: ["date"]

• For updateStandUps, set the following properties:
• Method: Update Document
• Document/Collection Path: standUps/{{Table1.selectedRow._ref.id}}
• Body: paste in the following JSON

{
    {
    "yesterday": "{{Input3.value}}",
    "user": "{{Dropdown3.value}}",
    "blocker": "{{Dropdown4.value}}",
    "todos": "{{Input4.value}}",
    "prev_completed": "{{Dropdown2.value}}"
}

Note that queries can only be referenced on the page where they’re defined. If you need the same query on another page, you need to copy and rename it on the other page.

Connecting Widgets to Queries

Now let’s connect these queries to the widgets in your Appsmith app.

• On the First Page of your Appsmith app, replace the text in the widget next to Last Standup todo with:

{
{{fetchUserStandUps.data[0].todos}}

• For the User and Blockers dropdowns, replace the options with this:

{
{{fetchUsers.data.map((e,i) => {return {label: e.name, value: e.name}}) }}

• Fo the Yesterday completed dropdown, replace its options with this:
  

{
[{"label": "Yes", "value": "true" }, { "label": "No", "value": "false" }]

• To configure the First Page’s Submit button, select Execute DB query under onClick, then select the createStandUp query.

• To configure the Second Page’s Refresh button, select Execute DB query under onClick, then select the fetchStandUps query.

• To configure the Second Page’s Search button, select Execute DB query under onClick, then select the StandUpsByName query. Set onSucess to store value, key to data, then set value to {{StandUpsByName.data}}.

Integrating with Slack

To send the summary of your standup to Slack, integrate your Appsmith app with Slack using incoming webhooks.

“Incoming Webhooks are a simple way to post messages from apps into Slack. Creating an Incoming Webhook gives you a unique URL to which you send a JSON payload with the message text and some options. You can use all the usual formatting and layout blocks with Incoming Webhooks to make the messages stand out.” - Slack

Let’s dive in with the integration:

• Head to Slack to create an account if you don’t have one.
• Open the Create an App page. The Create a Slack App window appears automatically. If it doesn’t, click *Create New App.
• Give your app a name and choose the Slack workspace you’re building it for. Click Create App. The Building Apps for Slack page opens.

• Click Incoming Webhooks to open the feature, and toggle the switch to On to activate it. Scroll to the bottom of the page to copy the webhook URL.

• Back in Appsmith, under First Page, click the + icon beside APIs, then select Create new.
• Paste the webhook in the first input field and change the request type to POST.
• Click the Body tab and fill in the message as a JSON object as shown:

{
{
    "text": "New Standup added by {{Dropdown1.value}}, Yesterdays todo: {{Input1.value}}, Completed: {{Dropdown3.value}}, Todays todo: {{Input2.value}}, Blockers: {{Dropdown2.value}}, link: https://app.appsmith.com/applications/6043f3a5faf5de39951a897e/pages/6043f3a5faf5de39951a8980  "
}

Let’s go back to your First Page in your app and configure the Submit button so that it sends a Slack message on submit.

Click the Settings gear for the Submit button. Below onClick, find the onSuccess field and from the Call An API option, select your Slack API.

Viewing the Completed Daily Standup Application

At this point, your Appsmith app should look like this:

And as a result, your Slack channel should look like this:

You can check out this tutorial’s completed app on Appsmith.

Summary

In this tutorial, you learned how to build a daily standup app using Appsmith, including widgets that enable users to detail their accomplished tasks, their daily to-do lists, and any blockers keeping them from their goals. You then integrated your app with Slack, so you can send summarized standup reports to a specific Slack channel via incoming webhooks.

Have an idea for another app you’d like to build without reinventing the wheel? Check out Appsmith’s Getting Started documentation, or jump right in by signing up for a free account.

Author Bio: Kayode is a tech enthusiast specializing in embedded systems and system design and modelling. His programming languages of choice include C, C++, JavaScript, and Python. In his free time, he loves adding value to people's lives with technology.

Admin panels are an important part of every organization. They are necessary to serve customers better and run business processes smoothly. They help reduce management and people overhead significantly. But on the other hand, they take a lot of time to build and are troublesome and expensive to maintain. As a result, it is not the choice of project for most developers.

Thankfully, there are a lot of options available to developers today to build complex admin panels and dashboards fairly easily while making maintenance easier. An example of such a framework that we have explored, React-admin. We’ve written an article on how to use it. It’s better than building an admin panel from scratch, but it is still cumbersome to move from a simple admin panel to an admin panel that behaves like an internal app.

And that’s one of the reasons why we built Appsmith. Building Admin panels and internal apps should be easy and, more importantly, fun. Appsmith offers a drag and drop interface for creating UI elements and provides the option to write code wherever you may need to. You can build dashboards a hundred times faster on Appsmith, and that’s not an exaggeration.

For this article, I’d like you to imagine it's 2005. Netflix doesn’t stream video yet. You run a local DVD store, and you need software to manage your rentals. You’re the only developer in the organization. How do you go about building one that is fast, scalable and easily maintainable? In this article, I’m going to show you how to build an admin panel using Appsmith to manage DVD rentals for your store.

The app would have two pages. The first page would have a table listing all registered members of the store. The second page will have a table that holds all rental records. From here, new rental entries can be created, and existing rentals can be updated i.e from borrowed to returned. We would also be able to click on a customer from the first page and then taken them to the rentals page to see his rental history.

Before we begin, I’d like to give you a quick overview of the dataset we’ll be making use of. We’ll be using a modified version of the Sakila dataset. The database of choice is MongoDB on MongoDB Atlas and here’s the schema for each collection

// Customer collection
{
  "store_id": String,
  "first_name": String,
  "last_name": String,
  "email": String,
  "address_id": String,
  "activebool": Boolean,
  "create_date": Date,
  "last_update": Date,
  "active": Number
}
// Film collection
{
  "title": String,
  "description": String,
  "release_year": Number,
  "language_id": String,
  "rental_duration": Number,
  "rental_rate": Number,
  "length": Number,
  "replacement_cost": Number,
  "rating": String,
  "last_update": Date,
  "special_features": String,
  "fulltext": String
}
// Rental collection
{
  "status": String,
  "rental_date": Date,
  "film_title": String,
  "customer_email": String,
  "return_date": Date,
  "staff_id": String,
  "last_update": Date
}

That’s the overview of everything. Now let us get started!

Oh, snap! I almost forgot. Here are some of the core Appsmith concepts we’re going to cover:

• How to connect Appsmith directly to your database (eliminates the need for an API server)
• How to create data-driven widgets
• How to perform CURD operations
• How to securely share your application (no auth required)
• How to deploy your application

We can get started now!

Connecting to your data source

It’s quite important that our application is linked to a persisted data source because admin panels are almost always data-driven applications. This data can come from an API, a database, or a Google sheets table as we have the freedom to use any of these on Appsmith. We’ll be with the database route.

Create an Appsmith account if you do not have one yet and let’s begin to smith our application 😁

As highlighted earlier, our application would be based on a MongoDB database. Don’t despair if you would like to follow along and do not have a ready-to-go database. Here’s a database connection you can make use of. Mind you, it’s read-only (don’t forget to say thank you)

Username: article_reader

Password: 0HkfXpxVpvvSvUOx

Connection url: cloud.r4vra.mongodb.net

Database name: movie_store

Alright, we’ve removed all hurdles from the track, now let’s start racing!

Go ahead to create a new application from your Appsmith dashboard and give it a fancy name. Next, let’s connect to the mongo database. Click on the DB Queries button on the left panel on the page and select MongoDB from the list of supported databases. Now go in and supply the database credentials to the from, also give this data source a descriptive name i.e. movie_store or movie_store_db. I think I’ll go with movie_store. And lastly, hit the big green save button.

Awesome! We’ve connected the application to the database. We can now do some querying and build data-driven widgets. Let’s move on to the next section.

Creating data-driven widgets

We now have a database connected to the application. The next thing we need to do is to create a widget that can be used to create, update and display data from the database. To achieve this, we need a way to fetch data from the database. This can be done on Appsmith by writing database queries.

Rolle up your sleeves because we’re going to be writing our first DB query! Click on the DB Queries section again, and you’ll see a button to create a new query on the MongoDB movie_store data source. Give the query a name i.e. get_customers, and select a read template from the list because we’re going to be reading documents from the Customer collection.

The get_customer query should find all customers in the Customer collection, sort them by email and return a fixed limit of customers. Your query should look like this.

// Customer collection
{
  "find": "Customer",
  "sort": {
    "email": 1
  },
  "limit": 10
}

Configure your query and hit the run button, and you’ll get a list of 10 customers. Sweet!

It would be much readable to display this data in a table. So let’s do exactly that. Click on the Add widget button just below the query editor, and you’ll get a new table widget on the canvas that has been automatically linked to the get_customers query. An alternative way would be to click on the Widgets section and drag a table widget to the canvas. Then, data from the query can be bound to the widget via the Table Data property on the widget’s configuration menu using moustache syntax.

// Customer collection
{{get_customers.data}}

It’s important you give the table a descriptive name. Consider changing the name from Table1 to customers_table

Go ahead to customize the table by re-ordering the columns and hiding columns you don’t like. We’ll also need to paginate the table, so on the table’s config menu, turn on Server Side Pagination and configure the onPageChange action (in the Actions subsection of the menu) to execute a DB query and then select the get_customers query.

We’ll need to update the query and add a skip property that calculates a skip based on the current table page number to simulate pagination. The moustache syntax would be very handy in this case to perform quick evaluations, so let’s make use of it. Your updated get_customer query should look like mine.

{
  "find": "Customer",
  "sort": {
    "email": 1
  },
  "skip": {{(customers_table.pageNo - 1) * 10}},
  "limit": 10
}

As you may have noticed, we are accessing the table’s page number from within the {{}} through the object bearing the widget’s name (customers_table in this case). You can learn more about writing Javascript in Appsmith here.

Click on the page number on the table widget now fetches new records. We’ve been able to query the database, bind data to a widget and perform customization on the widget. Good job!

Now, we’re going to create a new page to manage rentals. Click on the plus button on the Pages section of the left-hand panel and create a new page. Call this the Rentals page and update the name of the home page to Customers. On this new page, you’re going to be building a table to display rentals like we just did for the Customers page. You’ll need a query (should be named get_rentals) and a table widget (should be named rentals_table) for this. Your query to get rentals should look like this.

{
  "find": "Rental",
  "sort": {
    "_id": 1
  },
  "skip": {{(rentals_table.pageNo - 1)*10}},
  "limit": 10
}

Where rentals_table is the name of your table widget on the Rentals page.

Good luck!

Creating new rentals

After completing the table on the Rentals page, the next feature we need to build in is the ability to create new rental records.

Here’s the game plan: We’re going to build a form that takes as input the status, rental data, film title, customer email, and return date. This form would be housed within a modal widget that would be triggered by clicking on a ‘Create new rental’ button. Now, you have a good overview, let’s get started!

On the Rentals page, head to the widget’s section and drag in a modal widget. In the modal’s config menu, update the name to create_rental_modal and set the Modal type to Form Modal. Now you see we have more space to work with.

With create_rental_modal open, drag in three dropdown widgets and two date picker widgets. The name of the first dropdown widget should be updated to customer_dropdown. Following this convention, go ahead and rename the other two dropdowns to film_dropdown and status_dropdown. For the two date pickers, their names should similarly be updated to rental_datepicker and return_datepicker. You should drag in a few text widgets to label these inputs.

We need a button on the UI to trigger the opening up of the modal. Let’s drag a button from the widgets section to the canvas. Give this button a new label i.e Create New Rental, and set the onClick action to Open Modal, then select create_rental_modal. Clicking on the button now opens up the modal.

Nice! We’ve completed the UI for the crate form. Now let’s wire it up with some queries.

The first query we’ll be writing for the create form would get all registered customers from the DB. We would use the returned data to build the options for the customer_dropdown. This query would be similar to the get_customers query from the Customers page.

Go ahead and create a new query named all_customers. It should be configured as shown below

{
  "find": "Customer",
  "sort": {
    "email": 1
  }
}

Now, we can use the data returned from this query to build the options of the customer_dropdown. Open up the create_new_rental modal and head to the config menu of the customer_dropdown. Now replace the content of the Options section with the snippet shown below.

{{all_customers.data.map(c => ({label: c.email, value: c.email}))}}

This returns an array of objects whose label and value are the email addresses of the customers from the all_customers query. You can play around with the dropdown and see that the options generated from the query. Sweet 🤩

Now you’re going to do the same thing for the film_dropdown. Create an all_films query and use the title field of each document to build the options for the dropdown. Your query should look like this.

{
  "find": "Film",
  "sort": {
    "title": 1
  }
}

Cool! For the status_dropdown, we can simply hard code the Options with this simple JSON array.

[
  { "label": "borrowed", "value": "borrowed" },
  { "label": "delayed", "value": "delayed" },
  { "label": "lost", "value": "lost" },
  { "label": "returned", "value": "returned" }
]

Don’t forget to set a default option for each dropdown widget The rental_datepicker and return_datepicker widgets do not require further configuration, and they are good to go. The last thing we need to do is to write a query that would create a new document on the ‘Rental’ collection using the data from the input widgets. Alright, go ahead and create a new query called create_rental and configure it this way.

{
  "insert": "Rental",
  "documents": [
    {
      "staff_id": "{{appsmith.user.email}}",
      "status": "{{status_dropdown.selectedOptionValue}}",
      "customer_email": "{{ customer_dropdown.selectedOptionValue }}",
      "film_title": "{{ film_dropdown.selectedOptionValue }}",
      "rental_date": "{{ rental_datepicker.selectedDate }}",
      "return_date": "{{ return_datepicker.selectedDate }}",
      "last_update": "{{moment().format()}}"
    }
  ]
}

Now we can go back to configure the big green Confirm button on the modal to run this query when it is clicked. We also want to close the modal and refresh the rental table when the button is clicked. Because what we want to do is a complex chain of actions, we can write some JavaScript to achieve this. Click on the JS button just beside the onClick button of the Confirm button and supply the bellow code snippet.

{
{create_rental.run(
  ()=> {
      closeModal("create_rental_modal");
    get_rentals.run();
  }
)}
}

Great Job! Go ahead, give it a spin and create a few new rentals.

Updating a rental

The flow for updating a rental is quite similar to that of creating a rental. We’ll need a modal that would be opened by clicking on a button. The modal would only contain form elements for updating the status and return date of the rental.

The only difference here is that we want the rental in question to be the one selected from the table. To do this, we need to head back to the config menu for the rentals_table and create a new column by clicking on the Add a new column button. Click on the gear icon of the newly created column and set the column name to Update. Also, set the Column type to button and set its Lebel property to Update. For its onClick action, it should be configured to open up the update_rental_modal which you’re going to build on your own.

And that all you need. Oh, you’ll also need to write a update_rental query too. It should look somewhat like this.

{
  "update": "Rental",
  "updates": [
    {
      "q": {
        "_id": "{{rentals_table.selectedRow._id}}"
      },
      "u": { 
        "$set": {
      "status": "{{ update_status_dropdown.selectedOptionValue }}", 
          "return_date": "{{update_return_date_datepicker.selectedDate}}",
          "last_update": "{{moment().format()}}"
     } 
      }
    }
  ]
}

Now you have everything you need to know. Go ahead and build the update flow.

Putting it all together

You’ve done a good job making it this far. I’m so proud of you that I’m going to leave the gif below. 🤗

We’ve been able to accomplish most of what we set out to do. We have a table on the Customers page to view all customers. On the Rentals page, we can view all rentals, create a new rental or update existing rentals. It was no small feat we accomplished.

I’ll like us to add one more feature. Let’s make it such that clicking on a customer on the customers_table takes us to the Rentals page, and then we can view all rentals made only by that customer.

We’ll need column buttons for each row like we did for the update flow. In this case, the onClick action of the custom column buttons would perform a navigation to the Rentals page and pass the selected customers’ email from the table as query params. Use the below snippet in the Query Params configuration for the navigation.

{{{customer_email: customers_table.selectedRow.email}}}

And lastly, we need to update the get_rentals query in the Rentals page to filter by customer_email coming in as a query param. We also add a fallback just in case no email is passed.

{
  "find": "Rental",
  "sort": {
    "customer_email": 1
  },
    "filter": {
    "customer_email": {
      "$regex": "{{appsmith.URL.queryParams.customer_email || ""}}"
      }
    },
 "skip": {{(rentals_table.pageNo - 1) * 10}},
  "limit": 10
}

And that’s it! You’ve completed the app. 🥳️🎉🎉

Sharing and deploying the app

It’s quite easy to hand off your application to the team that would make use of it i.e customer support. You don’t have to worry about an authentication/authorization flow; Appsmith does all the work for you.

There’s a SHARE button on the top right side of the page. You can easily use it to invite users to the application and set roles i.e app viewer or developer. No hassle is required!

Clicking on the ‘DEPLOY’ button to the right of the share button will publish all changes made in the editor to the hosted version of the application. Isn’t that cool?

Wrapping up

We set out to build an admin panel, and we’ve seen how we can do it very easily using Appsmith. Going this route will drastically reduce the development time and resources put into maintaining the app.

If you found this tutorial helpful, please consider giving us a star on Github.

Cover Photo Credits: Photo by Serpstat from Pexels

Most founders talk to 10s, if not 100s of investors. Keeping track of the conversations, files shared, and specifics of each investor is hard. Most of the time, we find ourselves rummaging through our inbox for that last email received, the previous file sent or the following action steps.

Many founders rely on spreadsheets (Google Sheets, usually) to list down the bare minimum of next steps, notion or google docs for the notes, and everything else on email to “streamline” the fundraising process. We know that’s not efficient from our experience, and we thought why not take a leaf out of our sales team and use a CRM to track our investor conversations.

So we went ahead and built an investor CRM that we’ve been using for our fundraising. We used Appsmith and the Google Sheets Integration to build this. Feel free to develop or fork this application and extend it to however you want.

In this tutorial, we’ll build an essential Investor CRM, which will let you:

• Build unique profiles for the fund and investor
• Filter saved investors by fund specifications and interests levels
• Track conversations, see the most recent interaction
• Set deadlines and reminders for action items
• Manage file sharing by keeping track of files shared with each investor

Here's a screenshot of how the app looks like:

Appsmith is an open-source framework that lets developers build dashboards, workflows, and CRUD apps with only the necessary code. You can connect to any API or databases like MongoDB, PostgreSQL, or MYSQL and get access to multiple widgets, including charts, tables and forms, for building a UI really fast.

Let’s dive right in!

Setting Up Google Sheets and Appsmith

To build this application, we’ll be using Appsmith’s Google Sheet Plugin. This will allow us to use our Google Sheet as a data source and help us build a custom CRM with a beautiful UI on Appsmith. Follow the below steps to integrate Google Sheets with Appsmith:

• Create a new account on Appsmith (it’s free!), if you are already an existing login to your Appsmith account.
• Create a new application by clicking on the “Create New” button under the Appsmith dashboard.
• We’ll now see a new Appsmith app with an empty canvas and a sidebar with Widgets, APIs and DB Queries.
• Click on the + icon next to the APIs section and choose the Google Sheets option. Next, click on the New Datasource button and set the scope to Read and Write and click Authorise.
• This will ask us to log in from our Google Account, choose the account we want to access Google Sheets with and log in. After successful authorisation, this will redirect back to your Appsmith account.
• Now, you’ll find your Google Sheets Datasource under your APIs, and you can create the necessary queries by choosing this data source.

Awesome! Now that our Google Sheets Plugin is set up, let’s create a new Google Sheet and add the necessary fields required for Investor CRM.

Let’s divide our Google Sheet into two sheets; in the first Sheet named “Investor Details”, let’s add all the information regarding the inventors and their specifics. Following are the fields we’ll be considering:

• Investment Fund
• Location
• Name of Investor
• Designation
• Interesting Portfolio Companies
• Size of Fund
• Ref Check Names
• Ref Check Details
• Website
• Notes
• Interest Levels

In the second Sheet, “Notes”, let’s add all the notes/conversations related to these investors; we’ll use an identifier named id to filter our discussions based on the Investors. Additionally, we will also save the links to media/pitch decks that need to be shared with the Investors. Following are the columns we would need in the second Sheet.

• id
• Notes
• Date
• Author
• Files Shared Link

To make this more precise, we’ve made a sample Google Sheet with some mock data here. We’ll be using the same Sheet throughout this tutorial, and you can either follow with this or create your own based on our requirements.

In the next section, let’s fetch all the investor information and display it on a beautiful table.

Fetch Data from Google Sheet to Appsmith

Now that we are connected to our Google Sheets data source, let’s connect to our Google Sheet and query all the data onto a table widget in Appsmith. To do this, navigate to the created data source under the APIs section and click on the New API button on the top right. Next, follow the below steps:

• After clicking the New API button, you’ll be redirected to a new query tab, name your API to getInvestorDetails by double-clicking on the existing one.
• Now set the method to Fetch Sheets Row, this method will query the data that’s present in the given Google Sheet.
• In the SpreadSheet URL property, paste the URL of your Google Sheet, in this tutorial we’ll be following with the following URL:

https://docs.google.com/spreadsheets/d/19ewbxuiNwfD5etHpb__jMzFYjVdYaeLSviC951htlsI/edit#gid=333192

• Next, in the Sheet name, add the name of our first sheet which is Investor Details
• The Table Heading Row Index property takes in the row number that basically has our column heading, we’ll set this to 1.
• Next, we’ll set the Row Offset to 1 and Row limit to 100, this will query only the first 100 rows of our Google Sheet.
• Lastly, click on the Run button on the top right, we should see the sheet information in JSON format in the response pane at the bottom. Below is the GIF showing the same:

Awesome, now that we have our data from the Google Sheet, let’s put this in a table; follow the below steps:

• Make sure the API is saved as getInvestorDetails
• Next, click on the + icon next to the widgets and drag and drop a table widget onto the canvas.
• Open the Table property pane by clicking on the cog icon on the top right of the table.
• Now under the Table Data property, paste the following code snippet:

{{ getInvestorDetails.data }}

• Here, we’re using the moustache syntax to bind the data from the API to the table widget.
• With this, we should see the Table data with the investor details from the Google Sheet. Below is a GIF showing the same:

Perfect! We now binded our Investor Data into a table widget; you could play with it by opening the table’s property pane and displaying only required values or adding custom columns. Next, let’s add a feature to add new Investor Details to the Google Sheet from Appsmith.

Insert new data/rows to Google Sheet from Appsmith

In this section, let’s learn to add a new row from Appsmith UI to Google Sheet. With this, we should be able to add new investor details for our Investor CRM. Let’s start by adding a button and showing a modal that has a form to add all the details of the new investors. Follow the below steps:

• Drag and drop a button widget on to the canvas and next open the property pane of the button by clicking on the cog icon on the top right of the button when selected.
• Open the onClick property and select the Open Modal option and click on New Modal, this should open a new modal.
• Now add the necessary form fields by dragging the Input widgets onto the Modal. In our case of Investor CRM, we will add the following fields:

- Investment Fund
- Location
- Name of Investor
- Designation
- Interesting Portfolio Companies
- Size of Fund    
- Website
- Notes
- Interest Levels

Name the input widgets to follow to easily use them for other operations for our CRM.

• Investment Fund: addFund
• Location: addLocation
• Name of Investor: addInvestorInput
• Designation: addDesignation
• Interesting Portfolio Companies: addPortifolio
• Size of Fund: addFundSize
• Website: addWebsite
• Notes: addNotes
• Interest Levels: addIntrests

Below is a screenshot of how our form should look like:

Now that we have our form ready, let's write the logic to push the values from the form to the Google Sheet whenever submitted. Follow the below steps:

• Create a new API from the existing Google Sheets data source and set the query method to Insert Sheet Row. This query method helps us insert new data to the Google Sheet from Appsmith.
• Name the query as addNewInvestor
• Next, in the SpreadSheet URL property, add the link to our Google Sheet.
• The Sheet Name will be Investor Details as named in our Google Sheet.
• The Table Heading Row Index will be the row where the names of the columns are listed; in our case, this will be 1,
• Lastly, the Row Object takes a JSON object with keys set to the column names associated with the desired values. In our case, below is how the Row Object should look like:

{
"Investment Fund": "{{addFund.text}}",
"Location": "{{addLocation.text}}",
"Name of Investor": "{{addInvestorInput.text}}",
"Designation": "{{addDesignation.text}}",
"Interesting Portfolio Companies": "{{addPortifolio.text}}",
"Size of Fund": "{{addFundSize.text}}",
"Website": "{{addWebsite.text}}",
"Notes": "{{addNotes.text}}",
"Interest Levels": "{{addIntrests.text}}"
}

Here, the key's are the column names in the Google Sheet, and the values associated with them are the names of the input widgets. The .text method is used to pick the text that's written in the input widgets.

Lastly, in the form below for the submit button, set the on click property to Call an API and call the addNewInvestor API from the options.

Our query is now complete, go ahead and try adding a new Investor Detail from the created UI. We should be able to see the updates on the Google Sheet automatically. Magical, isn't it? In this way, we could add new data using the Insert Sheet Row query. In the next section, let's see how we can edit existing row data from Appsmith.

Displaying and Updating Investor Details

Displaying on the table is cumbersome, hence let’s shrink our table and show all the details in a different container. Additionally, let’s give the functionality to edit the details when displaying them. With Appsmith, all these are pretty straightforward. Now, first, let’s reduce the width of the table and only show the Investor Name and Company, we can do this by opening the property pane and clicking on the eye icon to make the other fields invisible. Next, follow the below steps:

Drag and drop a container and add necessary input widgets to display the Investor Details. In this case, we’ll be adding the following:

• Investment Fund: editFund
• Location: editLocation
• Name of Investor: editInvestorInput
• Designation: editDesignation
• Interesting Portfolio Companies: editPortifolio
• Size of Fund: editFundSize
• Website: editWebsite
• Notes: editNotes
• Interest Levels: editInterests

Below is a screenshot of how our app should look like:

As we see in the image we have the Investor Table on the left, and the details on the right. Notice an Update Details button at the end? This button should help us the investor details wherever updated. Now in each of these inputs widgets, let’s use the selectedRow property from the table and display data.

Set the following to the Default text of input widgets in the investor details container:

• Investment Fund Input Widget: {{Table1.selectedRow.Investment_Fund}}
• Name of Investor Widget: {{Table1.selectedRow.Name_of_Investor}}
• Location Input Widget: {{Table1.selectedRow.Location}}

Similarly, add the default text to the other widgets. Now, when a row is selected in the table you should have all the details of the selected investor in the investor detail container. Below is a screenshot of how it should look like:

Awesome! Our CRM is almost ready, but we missed one more thing in here; whenever we made changes in the input widgets and hit update details, the associated investor details should be updated in the Google Sheet. For this, let’s write a new update API that’ll help us update the values in the selected row of the Google Sheet. Follow the below steps:

• Create a new API from the existing Google Sheets data source and set the query method to Update Sheet Row. This query method helps us update row data in the Google Sheet from Appsmith.
• Name the API to editInvestorDetail
• Next, in the SpreadSheet URL property, add the link to our Google Sheet.
• The Sheet Name will be Investor Details as named in our Google Sheet.
• The Table Heading Row Index will be the row where the names of the columns are listed; in our case, this will be 1,
• Lastly, the Row Object takes a JSON object with keys set to the column names associated with the desired values. In our case, below is how the Row Object should look like:

{
    "rowIndex":{{Table1.selectedRow.rowIndex}},
    "Investment Fund": "{{editFund.text}}",
    "Location": "{{editLocation.text}}",
    "Name of Investor": "{{editInvestorInput.text}}",
    "Designation": "{{editDesignation.text}}",
    "Interesting Portfolio Companies": "{{editPortifolio.text}}",
    "Size of Fund": "{{editFundSize.text}}",
    "Website": "{{editWebsite.text}}",
    "Notes": "{{editNotes.text}}",
    "Interest Levels": "{{editInterests.text}}"
}

Lastly, for the Update Details button, set the onclick property to Call an API and call the editInvestorDetail API. We should now be able to update the investor details by editing the necessary input widgets and clicking on the update button.

Awesome, now we have a fully functional app that allows us to manage all our investor contacts. Now let’s extend this application by adding a new page to save all the conversations, media files related to each investor.

Creating new conversations page to save notes and files

A fully functioning CRM should also have all the details of conversations, files with the associated investor. For this, let’s create a new page where we display all the notes and conversations from the Investors. Follow the below steps:

• Create a new page and name it “Investor Conversations”
• On this page, add a query to the URL to show only details of the selected investor. Now to the Appsmith URL, you see on the top add ?id=1. With this, we will filter all the investor details related to row one.
• Now your data source will also be copied to these pages as well, click on the create New API from the existing data source to fetch the data from the second sheet which is Notes
• Name the API as fetchInvestorDetails, next add the SpreadSheet URL and set the sheet name as Investor Details
• Set the row offset to {{appsmith.URL.queryParams.id}}, this will query the row index given from the id param.
• Lastly, set the row limit to 1, if you run the query you should see the details of investor details in the first row of the Google Sheet (as id is hard-coded to 1).

Below is the screenshot showing the same.

Awesome, let’s add some details regarding the investor to the conversation page before we list down the conversations.

• Now, on the conversation page, let’s add some Text Widgets to display the details of the Investors. Set the default text property to {{fetchInvestorDetails.data[0].Company}}
• This will display the investor name based on the filtered id from the URL; later, we’ll have to pass this id dynamically from the investor table on the first page.
• Now create one more API that fetches all the conversations from Sheet 2, which is notes.
• Name the API to getConversationDetails, and set the query method to Fetch Sheets Row and add the link to Google Sheets URL under the Spreadsheet URL property.
• Next, set the sheet name to Notes, and this will fetch all the information that’s listed in the Notes Sheet.
• The Table Row Heading Index, Row Offset will be 1, and the Row limit will be 100; you can increase this based on the data on your Google Sheets.
• Run the query. You should see all the notes listed down in the response pane in JSON format.

Next, drag and drop a table on the Canvas and in the table property pane under the Table Data, paste the following JS code snippet:

{{getConversationDetails.data.filter((note)=>note.id === appsmith.URL.queryParams.id)}}

Here, we’re filtering the notes only based on the query parameter that’s passed in the URL. Remember, we set the id to 1. Hence, we should see the conversations only from the first investor.

Dynamically Passing the Query Params and Adding Notes

Now on our conversation page, we’ve hardcoded the id parameter; that’s why we were able to see only notes from the first investor. Now let’s add a new column in the table that’ll redirect to the conversation page of the selected investor. We should pass the ID dynamically based on the rowIndex. Follow the below steps:

• First, open the table property pane in the Investor Details Page and click on Add A New Column.
• Set the Column Type to Button and Label to View Conversations
• Now when the button is clicked, it should navigate to the conversation page with the particular investor row id as a query parameter
• Now set the onClick property to Navigate to Page, enter the Page name as Investor Conversations
• Under the page name, you’ll find the Query Parameters property; add the following JS code snippet to pass the id as a query parameter:

{{ {id:Table1.selectedRow.rowIndex} }}

Awesome, this will send the id variable as the query parameter when navigated to the Conversation page, thereby filtering the notes based on the investor.

Now let’s add a new API that will add new conversations to the investor's details.

• Firstly, add a new Rich Text Editor to the Investor Conversation page and a button to add these notes to the list. Set the RTE input name to addConversation.
• Now create one more API that adds new conversations to Sheet 2, which is notes.
• Name the API to addNote, and set the query method to Insert Sheets Row and add the link to Google Sheets URL under the Spreadsheet URL property.
• Next, set the sheet name to Notes, and Table Heading Row Index to 1.
• In the Row Object paste the following JS code:

{
    "id":"{{appsmith.URL.queryParams.id}}",
    "Notes": "{{addConversation.text}}",
    "Author": "{{appsmith.user.name}}",
    "rowIndex":"0"
}

Awesome, this will add new notes to the Notes sheet in the Google Sheet. Also, make sure you call the addNote API when the “Add Notes” button is clicked.

Wrapping Up

Deploy your application on the cloud and share it with others, and that's it. We're done!

You've seen how easy it is to build an application on Appsmith, specifically a CRM with Google Sheets as a backend. This guide covered how to create an Investor CRM and connect it to a Google Sheets, as well as how to create, read, update and delete data. You learned how to build interactive pages, work with widgets, and customize them for your purposes.

We have made a slightly more robust application public here; try it out and let us know what you think. You can also check the live demo of our app here.

Cover Image Credits: Photo by Daria Nepriakhina on Unsplash

Businesses love loyalty programs. Loyalty programs are a proven and effective way to keep customers coming back to your business. The most simple loyalty program is offering discounts to your top quartile customers, so they keep coming back and get the best prices.

But if you're a small yet digitally savvy business, how do you set up and manage such a program to drive customer loyalty?

In this article, we will be building an admin dashboard to manage giving out discounts to loyal customers. Discounts will be assigned to customers based on their purchase history and will be available for use on their next purchase.

In this dashboard, we'll have a list of customers from where we can choose a customer we want to give a discount. On selecting a customer from the list, we can see all orders that have been made by that customer. We would also have a form that would allow us to give a percentage discount to the customer based on the total amount spent in the store by the user and set an expiration date for the discount.

You can check out the completed dashboard here 👈.

By the end of this article, you’d have learned how to build a Postgres admin panel using Appsmith. We’re going to cover core concepts such as

• Connecting to the Postgres DB
• Writing queries to perform CRUD operations on the database
• Binding query data to widgets
• Writing Javascript in Appsmith
• Sharing your application and managing permissions

Now you’ve got a good overview of what we’re going to be building, so let’s get started!

Creating the Application

As I mentioned earlier, we’re going to be building the discount management dashboard using Appsmith. Appsmith is an open-source platform that lets you create powerful apps/dashboards/tools quickly using a simple drag and drop interface.

Head over to appsmith.com and sign in. We’re going to be creating a new application, so click on the create new button. Please do well to give this app a creative name. The best I could come up with is Discount Dashboard.

Connecting to Postgres

The bread and butter of Appsmith is that we can connect to various sources of data by using APIs or talking directly to the database. We’re going to be connecting the application directly to our Postgres database, eliminating the need for a middle-man API.

To connect to the database, click on the + icon to the right of DB Queries then + New Datasource and select PostgreSQL. On the next page give the datasource a name and fill in the connection details.

A little side note here: This application was built using the mock Postgres DB connected to every application by default. Please feel free to use this if you do not have a database of your own

Querying the Database

Since the application has been connected to the database, we can write queries to fetch data. The first query we’ll be writing would be to fetch users from the database.

Click on the + New Query button close to the datasource card to create a new query. Go ahead and give this query a name, let’s call it get_customers and it should be a select query:

SELECT * FROM public."users";

Clicking on the Run button will fetch us a table containing all customers we have on our database. Now we build a list from which we can choose a customer using the data returned from this query.

Binding data to widgets

We need to create a list of customers. Each item on the list will display relevant information about the customer such as the customers’ name, email, and picture. We’ll be using the brand new List widget to make this happen.

Head over to the widgets section, click on the + icon, and drag a list widget into the canvas. In the property pane of the list widget, rename the widget from List1 to customers_list. In the items section, delete the dummy data that was prefilled and bind in data from the get_customers query by referencing it using mustache template syntax shown below:

{{get_customers.data}}

Now we’ve connected the data from the customers’ query into the List widget. All we need to do is to use that data. To do so, we can reference the currentItem object within other widgets in the list item and use its data to build up the list item. Here’s a gif illustrating how to do it.

Awesome! We now have a list showing all customers.

Triggering Actions

Continuing with the requirement for the application, we’ll want to see a list of orders for the customer clicked on from the list. To do this we’ll need to run an action whenever an item is clicked on the list.

Under the actions section of the List widgets’ property pane, let’s go and add an action for the onListItemClick event. From the dropdown list, select Execute a DB Query and click on + Create Query.

The new query we’ll be creating will be fetching all orders made by the customer. So call this query get_orders and set the query to:

SELECT * FROM public."orders" WHERE "userId" = {{customers_list.selectedItem.id}};

Or we can write a slightly more complicated query that gets the product name for each order from the products table using the orderProductMap table:

SELECT o.*, p.* FROM public."orders" o
    left join "orderProductMap" op on o.id = op."orderId"
    left join products p on op."productId" = p.id
WHERE o."userId" = {{customers_list.selectedItem.id}};

Clicking on the run button will get the orders for the customer selected from the List widget. Now going back to the onListItemClick event, when the get_orders query is successful, we want to execute another database query to get discounts that have been given to the customer.

Go ahead and set up the query to get the customers’ discount. Your query should look like this:

SELECT * FROM public."discounts" WHERE "userId" = {{customers_list.selectedItem.id}};

Visualizing Data

At this point, we have a list of all customers, and clicking on a customer gets all orders made by that customer as well as the customers’ discount. It will be nice if we can neatly display the orders made by the customer.

We can neatly display the orders using a Table widget. Like we did with the List widget, go on and drag a Table widget into the canvas. You’ll also need to bind the data from the get_orders query into it and give it a nice name i.e orders_table. Your binding should look like this:

{{get_orders.data}}

Also, you can hide some columns from the Table widget so that we only have the relevant ones showing.

We also need to display the discount amount and expiration time from the get_discount query. A couple of Text widgets will be sufficient for this. I’ll leave you to figure it out but here’s my solution:

Writing JavaScript in Appsmith

Finishing up with the dashboards’ requirement, we’ll need a form that will allow us to give a percentage discount to a customer based on the total amount they’ve spent on the store. We will also be able to set an expiration date for this discount.

To build this form, we’ll need a few new widgets. We will be making use of a Dropdown, Input, and Datepicker widget. We’ll also need a Button widget to submit the form and a Text widget to label the Datepicker widget.

I have mine arranged as shown below.

Alright, let’s move on to configuring the form.

To configure the Dropdown widget, give it a new i.e discount_dd and supply the JSON array shown below as its Options:

[
  {
    "label": "Clear Discount",
    "value": "0"
  },
  {
    "label": "10% Discount",
    "value": "10"
  },
  {
    "label": "20% Discount",
    "value": "20"
  },
  {
    "label": "30% Discount",
    "value": "30"
  },
  {
    "label": "40% Discount",
    "value": "40"
  },
  {
    "label": "50% Discount",
    "value": "50"
  }
]

The Input widget is where the magic happens. In this widget, we’ll need to sum all the amounts from the get_orders query and apply a discount based on the percentage selected in the Dropdown widget.

To do this we need to write multiline Js and this can be done with an IIFY(Immediately Invoked Function Expression). Set the Default Text of the input widget to:

{
{
  function(){
    const totalAmount = get_orders.data.reduce((acc, order) => (acc + order.orderAmount),0);
    return totalAmount * discount_dd.selectedOptionValue/100;
  }();
}
}

Thus, the value of this widget will be the calculated discount. It’s a good idea to make the Input widget disabled to prevent manually updating its value. Give this widget a name i.e calc_discount. Also, the Datepicker widget should be named expire_dp.

All we have to do now is to write a query that will save the discount when the Button widget is clicked on. To do this create a new query called save_discount having the below body:

INSERT INTO public."discounts" ("userId", "amount", "expiresAt")
VALUES ({{customers_list.selectedItem.id}}, {{calc_discount.text}}, '{{expire_dp.selectedDate}}')
ON CONFLICT ("userId") 
DO 
  UPDATE SET "amount" = {{calc_discount.text}}, "expiresAt" = '{{expire_dp.selectedDate}}'

The above query will insert or update the discount of the selected customer.

Lastly, we’ll need to run this query when the Button widget is clicked on. We’ll also need to re-fetch the get_discount query and show a message on the UI to let the admin know that the discount has been successfully applied.

To do this enable JavaScript on the onClick action of the Button widget by clicking on the JS button close to it. Then set its content to the code below:

{
{
  save_discount.run(() => {
    get_discount.run();
    showAlert('Discount Applied!','success');
  })
}
}

That’s it! We have a fully working discount management dashboard!

Sharing the application

Sharing the dashboard we just built is an easy thing to do on Appsmith. You can easily hand off the application to the management team or invite developers to work with you by using the share feature.

Click on the SHARE button at the top right of the page and you’ll be presented with a dialog to invite users to the app and set their roles or permissions.

And lastly, you can publish all changes we made to the application by clicking on the DEPLOY button. Sweet!

Wrapping off

That’s a wrap! If you found this article helpful, please do leave a Like ❤️. Feel free to share any concerns or questions you may have in the comments section. Also, we’d love you to check out our Github page here https://github.com/appsmithorg/appsmith.

Credits: Photo by Karolina Grabowska from Pexels

At Appsmith, we use Notion to manage our content calendar. We also work with a few external agencies and freelancers for some of our content. It is impossible to create granular access control and develop a workflow on Notion to run the process smoothly and thus, as soon as Notion released their API, we decided to build an application that helps us manage our entire content management in one place while giving our collaborators the necessary access only.

Our application uses our Notion (mock) Table as a data source and lets you plant, submit and edit articles on the application, while at the same time has a provision for integrating with an email service of your choice (we use SendGrid here) to send reminder emails or updates to people in the project.

In this tutorial, we’ll build an essential Content Management System, which will let you:

• Submit new content idea submissions from authors
• Set deadlines for submissions
• Follow up with authors directly with their Emails
• Organise all data on a Notion page

Check out the live demo here. Here's a screenshot of how the app looks like:

Appsmith is an open-source framework that lets developers build dashboards, workflows, and CRUD apps with only the necessary code. You can connect to any API or databases like MongoDB, PostgreSQL, or MYSQL and get access to multiple widgets, including charts, tables and forms, for building a UI fast.

Let’s dive right in!

Configuring the Notion API

Notion API lets us connect to Notion Pages and Databases. Now, let's look at how we can set up the API, gather all the required keys and connect to Appsmith.

• Firstly, choose the workspace you want to work with on your Notion. If you want to test it, feel free to create a new workspace.
• Now, open the workspace setting by clicking on the Settings and Members option on the left navigation bar. We should see a new modal with all the settings.
• Find the select the Integrations option under Workspace options in the settings modal. This will redirect to the integrations tab.
• Next, choose the Develop your integration option. Now we'll be redirected to our integration page.

• On the integrations page, select the New integration option and add a name and associated workspace you want to work with and hit submit. This will create us a new secret key!

• Now head back to our workspace, choose a specific page and click on the share option (we can find this on the top-right corner)
• Next, hit the invite button, a new modal opens, here we can find our created integration. Select the integration and confirm the invite.
• With this, we should be able to add an integration to a Notion page; also, make sure to save your integration key; we’ll have to use it for using the Notion API

In Notion, integration has a secret key; when added to a workspace, we could perform all the actions (create, read, update). We'll use the secret key of the integration to connect with third-party applications.

Setting up a Notion Page

To work with the Notion API, firstly, let’s create a database for our content calendar.

• On your page, add a new table with the following fields that are required for our content manager:

Title: Text Field
Description: Text Field
Author: Text Field
Category: Text Field
Status: Dropdown Field
Notes: Text Field
Deadline: Date Field
Author Email: EMail Field

You can also use this Notion page of ours, which can be used as a mock database. You can click on this link duplicate to one of your workspaces. Also, make sure to add the integration to this page to use the Notion API.

Now that we have our page and integration-ready, we’ll start building an App on Appsmith.

Querying Data from Notion API

In this section, we’ll be querying data from Notion API. Follow the below steps:

• Firstly, we should have an Appsmith account; if not, sign-up here (it's free)! Next, create a new application under an organisation by clicking on the Create New button.
• We’ll now find Widgets, APIs, and DB Queries on the left navigation; we can use these features to build our application. We’ll start by creating a Notion API.
• Click on the + icon next to the APIs and click on Create new and name the API as query_data_from_database
• Now, add the following in the URL for the API:

https://api.notion.com/v1/databases//query

• Here, db-id is the database id for a notion page. To find this, we’ll have to open our Notion Table as a page and pick the database id from the URL.
• Following is an example URL showing the id of a Notion page.

https://www.notion.so/myworkspace/a8aec43384f447ed84390e8e42c2e089?v=...
                                  |--------- Database ID --------|

• Next, add the following keys under the Headers tab.

	Authorization: 
	Notion-Version: 2021-05-13
	Content-type: application/json

Below is a screenshot of how the configuration looks like:

• Lastly, hit the RUN button, and we can find all the data from our page in the Response Body pane.

Awesome, we now have the response from the Notion API; let’s bind this onto a List Widget.

Binding Notion APIs onto Appsmith

Now that we have an API containing data from the Notion Table let’s bind this onto a list widget to display all the details. Follow the below steps:

• Select Page1 on the left navigation and click on the + icon next to the widgets section. Now we should see different UI widgets that we can use to build UI.
• *Based on your preference, you can either choose a table widget or a list widget to bind the data from Notion API. In this example, we’ll be using a List widget.
• Now drag and drop the list widget onto the canvas; we should see list items filled with Pokemon information. To inspect these, we can click on the cog icon on the top of the list widget and check out the data in the Items property. You can find more information from the docs here.
• Now, we’ll be using the moustache syntax to write JS in Appsmith, replace the Items property on the list widget with the following code snippet:

In Appsmith, you can access the API’s anywhere inside the moustache syntax using the API name.

{
{
	query_data_from_database.data.results.map(
    	(item) => {
        	return (item.properties)
    	})
}
}

Here, we’re using the query_data_from_database and mapping it to return item properties. This how the response looks like:

Awesome, now let’s add some widgets onto the list widget and show the details from the Notion API. Drag and drop six text widgets and set its Text property to the following:

- Title
- Description
- Status
- Author
- Email
- Deadline

Add six more text widgets next to them, where we’ll be binding their corresponding values from the Notion API.

Now in the text widget next to the Title text widget, set the Text property to the following:

{{ currentItem.Title.title[0].text.content }}

Here the currentItem corresponds to a unique item in the list widget, and next, we access the Title property from the Items on our list and parse the JSON. Below is the screenshot of how the response looks like:

Great, we can now see our Titles from the Notion API on items of our list widget; similarly, let’s set the Text property of other text widgets to show the contents from the API:

• Description text widget set the Text property to:

{{currentItem.Description.rich_text[0].text.content}}

• Status text widget, set the Text property to:
  

{{currentItem.Description.rich_text[0].text.content}}

• Author text widget, set the Text property to:
  

{{currentItem.Author.rich_text[0].text.content}}

• EMail text widget set, the Text property to:
  

{{currentItem["Author Email"].email || "Not Added"}}

• Deadline text widget set, the Text property to:
  

{{currentItem.Deadline.date.start}}

• Category text widget set the Text property to:
  

{{currentItem.Category.rich_text.map(row => {return row.plain_text})[0]}}

The code for parsing through items changes based on the API response from the Notion API.

We’ve added some additional styles and added a button widget at the end so that admins can send Emails to the authors directly from Appsmith. Below is how the App looks like after we parse all the data from the Notion Table onto the Appsmith List widget:

We can also add a refresh button to the query_data_from_database query. For this, drag and drop the Button widget onto the canvas, set the Label property to Refresh. Next, open the onClick property, click the Call an API option, and select the query_data_from_database API. Now, whenever we click the refresh button, we should see all the new data updated on the Notion page.

Adding an Item into Notion Database

For our content calendar, we can list all the details from the Notion Table. Now let’s add a feature to add new items on the table from Appsmith. Follow the below steps.

• First, let’s create a new page named “Author Submission” now drag and drop a form widget onto the canvas.
• Inside the Form widget, let’s add some text widgets and input widgets from which the authors should submit their new content ideas.
• This is how our form should look like:

Following are the fields we’ve created for reference:

Now, let’s name these widgets (so that we can use them to refer in the POST API method) to the following:

Title - titleInput
Description - descriptionInput
Author Name - authorInput
Email - emailInput
Category - categoryInput
Submission Date - dateInput

Let’s create a new API that’ll add a new value to the Notion page when making submissions from the Appsmith form. Follow the below steps:

• First, create a new API under the Author Submission page and name it as add_an_item_to_database
• Next, add the following keys under the Headers tab.

Authorization: 
Notion-Version: 2021-05-13
Content-type: application/json

• Now in the body tab, paste the following code:

	"parent": { "database_id": "" },
 "properties": {
           "Author": {
          "type": "rich_text",
          "rich_text": [
            {
              "type": "text",
              "text": {
                "content": "{{authorInput.text}}",
                "link": null
              }
            }
          ]
        },
        "Author Email": {
          "type": "email",
          "email": "{{emailInput.text}}"
        },
        "Category": {
          "type": "rich_text",
          "rich_text": [
            {
              "type": "text",
              "text": {
                "content": "{{categoryInput.text}}",
                "link": null
              }
            }
          ]
        },
        "Status": {
          "type": "select",
          "select": {
            "name": "Not Started",
    "color": "blue"
          }
        },
        "Description": {
          "type": "rich_text",
          "rich_text": [
            {
              "type": "text",
              "text": {
                "content": "{{descriptionInput.text}}",
                "link": null
              }
            }
          ]
        },
        "Deadline": {
          "type": "date",
          "date": {
            "start": "{{dateInput.selectedDate}}",
            "end": null
          }
        },
        "Title": {
          "type": "title",
          "title": [
            {
              "type": "text",
              "text": {
                "content": "{{titleInput.text}}",
                "link": null
              }
            }
          ]
        }
      }
  }

This is how Notion API allows us to add new items to a database. Here we'll also have to add the database id in the place of <db-id>. If we observe the body inside each property's content field, we added a moustache operation from which we'll take the input from the Appsmith input forms.

Now open the Submit button’s property pane, update the onClick property to Call an API, and choose add_an_item_to_database API. With this, we should add new data to the Notion table using the Appsmith form!

Send Grid Email Integration

Our content manager now shows all the data and also has a feature to add new content ideas. Now let’s add an email integration to communicate with Authors via Appsmith. We’ll be using Sendgrid to achieve this.

Creating a Modal for sending emails:

• First, create a new modal when the Send Mail button is clicked on the list item. We can do this by setting the onClick property to Open Modal and selecting Create New.
• Set the modal type to Form modal by toggle the option in the property pane.
• Inside the Modal widget, let’s add some text widgets and input widgets from which we should send Emails.
• Following are the fields we’ve created for reference:

Sending To: Input Widget
Email subject: Input Widget
Email content: Rich Text Editor Widget

• Now, let’s name these widgets (so that we can use them to refer in the POST API method) to the following:

Sending To: emailInput
Email subject: subjectInput
Email content: contentInput

Below is a screenshot of how the Modal should look like:

Configuring the SendGrid API:

• Firstly, we should have a SendGrid account, and if you don’t have one, you can create a free account here.
• Next, navigate to the Integration Guide by clicking on the Email API on the left navigation pane.
• Choose the Web API and choose the cURL language. Now you’ll be redirected to the verification tab.
• On the verification, tab click on the Create API by giving it a name. With this, we should have our SendGrid API key.
• Next, open Appsmith and create a new API under Page1, name it as send_email. Set the request type to POST and add the following under the URL form:

https://api.sendgrid.com/v3/mail/send

Authorization : Bearer 
Content-Type : application/json

• Lastly, set the body to the following:
  

{"personalizations": [{"to": [{"email": "{{emailInput.text}}"}]}],"from": {"email": "test@example.com"},"subject": "{{subjectInput.text}}","content": [{"type": "text/plain", "value": "{{contentInput.text}}"}]}

This is the default configuration from SendGrid that lets us send Emails, but here we are dynamically passing the to-email, from-email, subject and content field from the widget’s we’ve created on the Modal. Now, set the onClick property of the form to Call an API and choose the send_email query. With this, we should be able to send Emails from Appsmith successfully!

Conclusion

Deploy your application on the cloud and share it with others, and that's it. We're done!

We've seen how easy it is to build an application on Appsmith, specifically a CMS with Notion as a backend. This guide covered how to create a CRM and connect it to a Notion API and how to create and read data. You learned how to build interactive pages, work with widgets, and customize them for your purposes.

We have made a slightly more robust application public here; try it out and let us know what you think. You can also check the live demo of our app here.

By: Veronica Stork

What Are Web Tables?

Tables are an important component of HTML that allows us to display data in rows and columns. There are several different ways to create tables.

If you don’t have many values to display, you can create your table using pure HTML. This is fairly straightforward but quite cumbersome if you have many values or if your data may change.

Alternatively, a table can be used to display information from a database programmatically or an API. Just fetch your data, then use JavaScript to append table rows for each datapoint retrieved.

If you need more interactivity, you can write JavaScript functions that implement more advanced features such as search and filter. This adds more functionality to your app but also more code and complexity.

When you build your table using these methods, you will also need to style and structure it in a readable way for the user. This is not difficult, but it can be tedious. Ultimately, the minutiae of correctly structuring and displaying your data will eat up time that could be better spent on the underlying logic of your app.

Appsmith to the Rescue

Appsmith is an open-source framework that lets developers build dashboards, workflows, and CRUD apps with only the necessary code. You can connect to any API or databases like MongoDB, PostgreSQL, or MYSQL and get access to multiple widgets, including charts, tables and forms, for building a UI fast.

Using Appsmith allows you to focus on the logic behind your tables instead of fiddling around creating the UI, connecting to the API, and reinventing the wheel in the form of filter and search functions. You can also see your table working in real-time as you build, a feature that is super handy when you’re trying to develop something quickly.

Creating a Table in the Appsmith

• Firstly, you should have an Appsmith account; if not, sign-up here (it's free)!
• Next, create a new application under an organisation by clicking on the Create New button.
• Now, to create a table, first, click the + icon next to the widgets directory in the sidebar.
• Then, drag the table widget to the canvas. Once it is on the canvas, the table will be pre-populated with dummy data, and the property pane will be open to the right side of the widget.

• You can rename the table to something descriptive by clicking on the default name in the property pane (Table1 in the above example), then entering your data.

Your data should be an array of objects, with each object representing a row in the table. The object keys represent column names. You can populate your table with data in a few different ways: by typing the data directly into the property pane manually, by importing it from a data source using queries, or by connecting to an API.

Connecting to an API

To connect to an API, hover over the table data field of the property pane, and click on the lightning bolt icon. You will be presented with several options. Choose Use data from an API, then create new API. This will bring up a screen where you can paste the API URL in if you want to know more about creating a new API check out the docs.

Below is a screenshot showing the Table Data property and how we can connect to APIs:

Features

Appsmith tables include features relevant to some of the most common use cases, including pagination, filters, downloading, searching, actions, and drilling into individual rows of data. You can use JavaScript to customise these features based on different use-cases.

Pagination

Pagination is often required to display large amounts of data. If you have more data than rows in your table, you will need some way of splitting the data into separate pages. This can be accomplished using either offset-based or key-based pagination.

Offset pagination is based on the table’s page number and page size. For example, if you have a table with ten rows, your app will use ten and the current page number to determine which data to retrieve next. Let’s walk through how to set up offset pagination.

Enable server-side pagination in the property pane of your table. Make a query or API call that utilizes the pageSize and pageNo variables.

Run that query or API call from the table onPageChange.

Key-based pagination uses a value returned by the API to make the next page’s API call. If the API’s response includes URLs for the next and previous pages, you can enter those on the API settings.

Filters

Client-side filtering is included in Appsmith out of the box, but implementing server-side filtering requires a few steps. First, create a dropdown widget and fill it with filter values. Then call that API or Query onOptionChange from the dropdown.

In the above example, we are filtering based on the user status.

Searching

As with filtering, client-side searching is included in Appsmith out of the box. To implement server-side searching, you must call the API or query onSearchTextChange from the table and pass in the search box contents, represented by the searchText property.

Downloads

Downloading also works with no additional setup: users can click the included download button on a table to download all of its data. You can also bind the download function to an action of your choice and set it to download whatever portion of the data you want.

In the above example, you choose the option to download the currently selected row’s data, but you can specify which portion of the data you want and give the file whatever name you want.

Actions

Actions in Appsmith represent ways in which your users can interact with the app and describe what you want to happen when they do. You can set the app to download data (as you saw above), call an API, open a modal, and more. All of the available options are listed when you modify action in the property pane.

Actions include the following:

• onRowSelected describes what happens when a user selects a row.
• onPageChange describes what happens when a user navigates to the next page of data.
• onSearchTextChange describes what happens when a user types in the search bar.

A table is set to display Modal1 when onRowSelected is triggered in the example below.

Drilling into a single row of data

Drilling into a single row of data is as simple as referencing the currently selected row using the selectedRow property. For example, {{Table1.selectedRow}} targets the currently selected row of our table. You can add a column name to narrow down the targeted data further. In the following example, {{Table1.selectedRow.name}} targets the name of the user represented by the currently selected row.

Conclusion

Making tables can be time-consuming and finicky, but with Appsmith’s rich table creation tool, you can focus on bigger structural and logical issues while letting it take care of all the nuts and bolts. Writing less code might seem strange at first, but you’ll quickly realize the benefits of working smarter, not harder.

I hope you enjoyed this introduction to Appsmith’s table features, which is just a taste of the whole suite of tools and features Appsmith can provide.

You can check some of the example app's that use the Table widget here. If you found this tutorial helpful, please consider giving us a star on Github.

Since its release back in 2012, Firebase has received lots of love from the developer community because it aims to remove the burdens many engineers have to deal with during development, including database, storage, authentication, hosting, and more.

One of its core products, the Firestore database, has been a game-changer in the database world because it allows highly flexible schemaless database access. But sometimes, it can be a bit challenging to work with, especially for new developers trying to build CRUD apps with Firestore.

Firestore and, by extension, Firestore is a great product, but it's only one side of the coin. However, to build production-grade applications, an interface is required for users to interact with and services provided by Firebase. Appsmith is the open-source UI framework that fills this gap if you're making something for your colleagues. Appsmith saves you the time of building UI and routers, focusing on the logic and the architecture of your app.

In this article, I will show you how to build CRUD applications using Firestore with Appsmith. We're going to cover the core concepts of Firestore, such as connecting to the database, reading data, and writing to the database by building a Todo app.

Here's what the finished application will look like:

Awesome! Let’s start hacking away.

Connecting to Firestore

Firstly, we need to create an app on Appsmith. Go ahead and sign in to your Appsmith account and click on the + create new app button. You should then be taken to a new app where we can begin building our Todo app. You might also want to rename the app from Untitled Application to something like Todo app.

Now that we have a new app setup, we can go on to connect to Firestore. Click on the + button to the right of the DB Queries section, and then the new data source button. We’ll be connecting to Firestore, so select Firestore from the list of supported databases.

On the opened form, fill in your Firebase connection credentials. Note that your Project Id can be gotten from your project settings in the Firebase console. Also your Database URL is <your_project_id>.firebaseio.com.

Lastly, you’ll need your Service Account Credentials. Go to the Service accounts tab in your project settings on Firebase. Click on the Generate new private key button. It’s going to download a JSON file, so copy its content and paste it in the Service Account Credentials input.

Save your connection and we’re good to go!

Reading data from Firestore

Now we have a connection to the database and we can begin to make use of it. Assume that we have a bunch of todos from the database with the following structure:

{
  "due": Date,
  "task": String,
  "created": Date
}

We can easily fetch todos from the database by writing a query. Click on the New Query button on the Firestore card, and let us write a query to get todos.

Give this query a nice name i.e _gettodos. Its Method will be set to Get Documents in Collection. For the collection path, fill in your collection path. And we can go ahead to set the Order By to ["-created"], doing this will sort the documents by their created date. Here’s my query:

Click on the Run button and you’ll get an array of todos from your Firestore database.

Now we can go back to the canvas and display this data using a List widget. Head to the widgets section and drag in a List widget into the canvas. On its configuration menu (called Property pane), replace the content of the Items field with the bellow binding that pulls in data from the get_todos query into the widget:

{
{get_todos.data}
}

You wouldn’t notice any change but rest assured that we’re now feeding data from the query into the widget. Now we can go on to display the todos on the List widget.

You can delete the Image widget in the list as we do not have images in our todos

Drag in a few Text widgets to display the todos text and due dates. For each of the respective Text widgets, use the following bindings in their Text property to display data: For the task field

{
{currentItem.task}
}

For the due date field. We’re using the built in moment library to format the due date

{
{moment(currentItem.due).fromNow()}
}

Great work! The app is coming to life!

Creating new Todos

Wouldn’t it be cool if we could create new todos? Yeah, let’s do that.

We’ll need a form for this. So, drag in a Modal widget into the canvas. You can close up the modal because will need a button on the UI to open it up. To do this, drag a Button widget into the canvas. On its property pane, set the onClick Action to showModal and the Modal Name to the modal we just created, that is Modal1.

Now clicking on the button should automatically open the modal. Sweet!

On the modal, we’ll need a few new widgets to capture the todo information. So go ahead and drag in an Input widget to collect the task info, and a Datepicker widget to collect the due date info. A screenshot of mine is shown below:

It is also important to rename the widgets so that we can easily access them later on. Here’s a gif showing how you can do that:

Awesome! Now we can go ahead to write the query to create a todo.

Like we did previously, go ahead and create a new query using the Firestore datasource we earlier configured. You can call your query _createtodo and set’s its Document Path to:

todo_app/{{Math.random().toString(36).substring(7)}}

What we have above generates a random string ID for the document. Then set its Body to grab data from the Input widgets we configured on the modal:

{
    "task": "{{task_input.text}}",
    "created": "{{moment().format()}}",
    "due": "{{due_datepicker.selectedDate}}"
}

Awesome! Now we can go back to write an onClick Action for the confirm button on the Modal widget. Enable the Javascript mode by click on the small JS button close to it and set it’s content to:

{
{create_todo.run(() => {
    get_todos.run();
    closeModal('Modal1');
})}
}

The script above will run the _createtodo query, after which it will update the list by running the _gettodos query and the close form modal.

Go ahead and give it a try. Fill in a task and set a due date, click on the button to see the magic happen!

Deleting a Todo

The last feature we’ll like to add to our app is the ability to delete a to-do that has been completed. We’ll need a few new tricks to do this.

Let’s go-ahead to write a delete query. Call this _deletetodo. Set this query’s Method to Delete Document then it’s “Document Path* to the following binding which will read the todo_path from the local store API on appsmith:

{{appsmith.store.todo_path}}

Now, let’s go and drag a Button widget into the list. For the button’s onClick Action, we need to store the current todos path in the local store and then run the delete_todo query. We’ll also need to update the list by running the get_todos query. Here’s what you need:

{{
    (() => {
      storeValue('todo_path', currentItem._ref.path);
      delete_todo.run(() => get_todos.run());
    })();
}}

Paste that in and give it a test run. Cool right?

Conclusion

We’ve been able to create a full blow CRUD todo Firebase app using Appsmith. Here a gif of the app in action:

Awesome! Now you can click on the DEPLOY button to deploy it and share the app with friends (be sure to update sharing permissions by clicking on the SHARE button).

If you found this helpful, show some love and leave a star on our GitHub repo https://github.com/appsmithorg/appsmith.

by Adeyinka Adegbenro

A content management system (CMS) is software used to manage the creation and editing of content, usually formatted articles containing images and videos. A very popular example of a CMS is the WordPress CMS. CMSs are very important tools for managing content because they make it easy to create, update, and delete data without any coding knowledge.

Appsmith is an open-source framework that helps developers build dashboards, workflows, pages, and CRUD apps very quickly. Appsmith allows you to build tools with little or no code, with the ability to connect to APIs or databases such as MongoDB, PostGreSQL, or MYSQL, as the case may be. You also get access to charts, widgets, and other customization tools for building a UI. Appsmith has more flexibility than off-the-shelf options, and it’s fast to build and easy to maintain.

In this article, you will learn how to set up Appsmith locally, create a new application, connect an API to it, and build a content management system using widgets and pages.

Setting Up the Application and Database

This section will focus on how to create a CMS as an Appsmith application, as well as how to set up and connect a SQL database for use in Appsmith.

Create an Appsmith App

For the purpose of this tutorial, you’re going to create a local instance of Appsmith using Docker. Read Appsmith’s documentation to set this up for your operating system.

After going through those steps, visit http://localhost/ in your browser to see the local version of Appsmith. Create a local account to log in. Next, click the Create New button to create a new application and name it CMS. This takes you to an empty workspace with a sidebar. On the sidebar, you should see items such as Pages, API, and DB Queries.

Connect to a Database

To work with Appsmith, you need a data source. This can be in the form of an API or a database. Appsmith supports MongoDB, MySQL, and PostgreSQL among others.

In this tutorial, you’ll connect to a local PostgreSQL database. To create the data models required for the CMS, go into the PostgreSQL command line, and create the database:

CREATE DATABASE appsmith_cms;

Connect to the database and create a DB table called Author:

CREATE TABLE AUTHOR(
   AUTHOR_ID SERIAL PRIMARY KEY NOT NULL,
   NAME TEXT NOT NULL,
   PROFILE_PICTURE TEXT,
   ABOUT_ME TEXT,
   LINKEDIN TEXT,
   GITHUB TEXT
);

Create a table, Article:

CREATE TABLE ARTICLE(
   ARTICLE_ID SERIAL PRIMARY KEY NOT NULL,
   TITLE TEXT NOT NULL,
   SUBTITLE TEXT,
   CONTENT TEXT,
   IMAGE TEXT,
   AUTHOR_ID INTEGER NOT NULL REFERENCES AUTHOR(AUTHOR_ID),
   DATE_UPDATED DATE NOT NULL DEFAULT CURRENT_DATE
);

Create a table, Tag:

CREATE TABLE TAG (
    TAG_ID SERIAL PRIMARY KEY NOT NULL,
    NAME TEXT NOT NULL
);

Create a table, Article_Tag:

CREATE TABLE ARTICLE_TAG(
    ARTICLE_ID INTEGER REFERENCES ARTICLE(ARTICLE_ID),
    TAG_ID INTEGER REFERENCES TAG(TAG_ID),
    PRIMARY KEY (ARTICLE_ID, TAG_ID)
);

By now, you should have a standard database with the tables Author, Article, Tag, and Article_Tag on it. Next, insert some data into the database tables by running the following queries, so you can view it when you build your user interface:

INSERT into author (name, profile_picture, about_me, linkedin, github) VALUES ( 'Achebe', 'https://en.wikipedia.org/wiki/Chinua_Achebe', 'i am cool', 'https://www.linkedin.com/', 'https://github.com/');

INSERT into author (name, profile_picture, about_me, linkedin, github) VALUES ( 'Chimamanda', 'https://en.wikipedia.org/wiki/Chimamanda_Ngozi_Adichie', 'i am cool', 'https://www.linkedin.com/', 'https://github.com/');


INSERT INTO "article" ( "title", "subtitle", "content", "image", "author_id") VALUES
( 'Hello', 'hi', 'Hello world', 'img.me', '1');


INSERT INTO "tag" ("name") VALUES
('food');

INSERT INTO "tag" ("name") VALUES
('tech');

INSERT INTO "tag" ("name") VALUES
('fashion');

INSERT INTO "tag" ("name") VALUES
('beauty');

To connect to the appsmith_cms database from within Appsmith, go to the application sidebar. Click the plus icon near DB Queries, then click New Datasource, select PostgreSQL, and enter the connection details for your database.

Since you are on localhost, set the host address as host.docker.internal and the database name as appsmith_cms. Add your PostgreSQL username and password to the Authentication section and click Connect. Once connected, rename the new datasource as CMS.

Your App’s First Page: Create an Article

In this section, you’ll create the first UI view of your CMS application. It will be a form with several fields, for title, content, cover image, and a URL. These form fields will take in all the necessary data for creating an article.

On the sidebar, under Pages, a new page called Page1 already exists. Rename it as create_article_page.

Prepare the Data

Before building the form, prepare the queries necessary for creating an article.

Click the plus icon near DB Queries and click the New Query button near the CMS Datasource to create a new query. Rename it fetch_authors from the default name Query1. This will be useful in displaying all the available authors in your database. Paste the following code in the query box and run it:

SELECT * FROM author;

Create a new query called fetch_tags. This will be useful for displaying all available tags in your database. Paste the following code in the box and run it:

SELECT * FROM tag;

Create a new query called submit_article. This will be used to create a new article entry in the database.

INSERT INTO "article" ( "title", "subtitle", "content", "image", "author_id") 
VALUES
('{{create_article_form.data.title}}', 
'{{create_article_form.data.subtitle}}', 
'{{create_article_form.data.content}}', 
'{{create_article_form.data.cover_image}}', 
{{create_article_form.data.author}}
) returning "article_id"
;

Create a new query called create_article_tag. This will be used to create a new article_tag entry that associates a tag with an article.

INSERT INTO article_tag
  ("article_id", "tag_id")
VALUES
  (
    '{{submit_article.data[0].article_id}}', 
    '{{create_article_form.data.tags}}'
  );

Build the Page

Go to create_article_page > Widgets, and add a form to the page by clicking the plus icon. Drag and drop a Form widget to the page’s workspace. Click the settings gear to configure the form. Change the form name from form1 to create_article_form.

Next, let’s add a few fields along with labels.

To begin, add a Text widget and label it Title:, then drag the Input widget in front of it and change its name from input1 to title.

Below that, add a Text widget and label it Subtitle:, then drag the Input widget in front of it and change its name from input1 to subtitle.

Add a Text widget and label it Content:, then drag the Rich Text Editor widget in front of it and change its name from RichTextEditor1 to content. Delete the default text.

Add a Text widget and label it Cover Image:, then drag the Input widget in front of it and change its name from input1 to cover_image.

Add a Text widget and label it Author, then drag the Dropdown widget in front of it and change its name from Dropdown1 to author. In the options settings for this dropdown, add the following code:

{{
fetch_authors.data.map(
  (row)=>{

    return {label: row.name, value: row.author_id}
  }
)
}}

Add a Text widget and label it Tags:, then drag the Dropdown widget in front of it and change its name from Dropdown1 to tags. In the options settings for this dropdown, add the following code:

  {{
fetch_tags.data.map(
  (row)=>{

    return {label: row.name, value: row.tag_id}
  }
)
}}

Go to the onClick section of the Submit button’s settings, and select Execute a DB Query, choose the submit_article query, then in the onSuccess section, select Execute a DB Query > create_article_tag query. This creates an article entry in the database, and then creates the article_tag entry that links the tag and article together.

To test that it works, fill in the form with some test data and submit it. In the Article table in your database, you should see the new article row with data you just filled in.

Your App’s Second Page: View Articles

Create a new page called all_articles_page. This page will be used to display all the articles available in the database.

Prepare the Query

Add a new query called fetch_articles and run it. This query will fetch all the articles in the database, as well the authors who wrote them.

SELECT art.*, aut.name as author FROM article art 
join author aut on aut.author_id = art.author_id;

Build the UI

On the all_articles_page, drag a table widget to the page and rename it articles_table. In the Table Data section of the table settings, set the value to {{fetch_articles.data}}. This will use data from the fetch_articles query defined above. By now, the table should be populated with a list of all the articles. The table automatically comes with search, pagination, and filter features.

Next, add a new column to the table and call it View. In the settings for View, set the Column Type as a button, label it View, and set the value for onClick as {{navigateTo('view_article_page', {"key": currentRow.article_id})}} after setting it to JS mode.

This means that when View is clicked, a user will go to the view_article_page.

Create a new page and name it view_article_page. Create a new query, get_article, and fill it as:

select art.*, tag.name as tag_name, tag.tag_id, auth.name as author from article art join
author auth on auth.author_id = art.author_id
left join article_tag at on at.article_id = art.article_id
left join tag on tag.tag_id = at.tag_id
where art.article_id = {{ appsmith.URL.queryParams.key }};

Go back to the view_article_page and add the following widgets and settings:

1. Add a Text widget for the title and set the value of Text to {{get_article.data[0].title}}.
2. Add another Text widget for the subtitle and set the value of Text to {{get_article.data[0].subtitle}}.
3. Add an Image widget for the cover image and set the value of Image to {{get_article.data[0].image}}.
4. Add a Text widget for the article content and set the value of Text to {{get_article.data[0].content}}.
5. Add a Text widget for the article’s author and set the value of Text to Written By {{get_article.data[0].author}}
6. To test that the configuration works, go to the all_articles_page and click View on any of the rows. This should take you to the view_articles_page with the actual article loaded.

Your App’s Third Page: Edit an Article

On the all_articles_page, add a new column to the table. Then add a button called Edit to the column. In the settings for Edit, set the onClick section to JS and add {{navigateTo('edit_article_page',{"key": currentRow.article_id})}} as its value.

Create a new page and name it edit_article_page. On this page, you’ll add a form to edit existing articles. You'll also make sure that the form is prefilled with the details of the article you want to edit.

Prepare the Data

Go to view_article_page > DB Queries > get_article and copy the get_article query to edit_article_page.

Go to the queries under the create_articles_page, click the three dots near fetch_authors, and select Copy to Page and then edit_article_page. Copy the query fetch_tags to the edit_article_page as well. Under the edit_article_page, you should see all the copied queries. Make sure that the getArticlesCopy ends with { appsmith.URL.queryParams.key }}; instead of {{ }};.

Create a new query edit_article_query and add:

 update article 
SET title = '{{edit_article_form.data.title}}',
    subtitle = '{{edit_article_form.data.subtitle}}',
    content = '{{edit_article_form.data.content}}',
    image = '{{edit_article_form.data.cover_image}}',
    author_id = {{edit_article_form.data.author}}
  WHERE article_id = {{ get_articleCopy.data[0].article_id }};

Create a new query, new_article_tag_query, and add:

INSERT INTO article_tag
  (article_id, tag_id)
VALUES
  (
    '{{ get_articleCopy.data[0].article_id }}',
    '{{ edit_article_form.data.tag }}'
  );

Create a new query, delete_old_article_tags, and add:

DELETE FROM article_tag 
WHERE article_id = {{get_articleCopy.data[0].article_id}};

Build the Page

To build the edit_article_page page, add a Form widget to the page, click the settings gear, and rename the form edit_article_form. Change the form label to Edit an Article.

Add six text widgets to the form and label them Title, Subtitle, Content, Cover Image, Author, and Tag respectively, just like you did on the create_article_page.

Drag the Input widget in front of Title and Subtitle and change their names from input1 to title and subtitle respectively. Open the title widget and set default text as {{get_articleCopy.data[0].title}}. Also set default text for Subtitle as {{get_articleCopy.data[0].subtitle}}.

Drag the Rich Text Editor widget in front of the Content label and change its name from input1 to content. Set the widget's default text as {{get_articleCopy.data[0].content}}.

Drag the Input widget in front of the Cover Image label. Rename the widget cover_image. Set the default text as {{get_articleCopy.data[0].image}}.

Drag a dropdown widget in front of both the Author and Tag labels. For Author, change its name from Dropdown1 to author and set the default text as the following:

{{
  fetch_authorsCopy.data.map(
  (row)=>{

    return {label: row.name, value: row.author_id.toString()}
  }
  )
}}

And the Default Option as {{get_articleCopy.data[0].author_id}}.

For the Tag field, change its name from Dropdown2 to tag and set the options as:

  {{
    fetch_tagsCopy.data.map(
    (row)=>{

      return {label: row.name, value: row.tag_id.toString()}
    }
    )
  }}

And the Default Option as {{get_articleCopy.data[0].tag_id}}.

In the Submit button's settings, go to the onClick section and click JS. Set the value as:

 {{edit_article_query.run(() => delete_old_article_tags.run(() => new_article_tag_query.run(() => get_articleCopy.run()) ), () => {})}}

To test that the configuration works, go to the all_articles_page and click the Edit button on one of the rows. This should bring you to the edit_article_page, with the form prefilled with the selected row's data.

From here you can edit the article and then submit. Confirm that the article got edited successfully from the all_articles_page by looking up the row to see if the data changed.

Your App’s Fourth Page: Manage Tags and Authors

Create a new page called authors_and_tags_page. On this page, you'll be able to view all the Authors and Tags. You'll also add the ability to remove and add tags.

Prepare the Queries

Go to the create_article_page > DB Queries and copy fetch_authors and fetch_tag to authors_and_tags_page.

Create a new query, create_tag_query, and paste in the following:

INSERT INTO tag
  (name)
VALUES
  (
    '{{create_tag_modal.data.tag_name}}'
  );

Create a new query, delete_article_tag_query, and paste in the following:

INSERT INTO tag
 DELETE FROM article_tag 
WHERE tag_id = {{tags_table.selectedRow.tag_id}};

Create another query, delete_tag, and add the following code:

DELETE FROM tag WHERE tag_id= {{tags_table.selectedRow.tag_id}}

Build the Page UI

To start building the page UI, add a text widget and name it Authors. Underneath, add a table widget and rename it author_table. Set the Table Data as {{fetch_authorsCopy.data}}. Reload the page.