SeaTable: Simple Inventory Management System with QR Code workaround

When come to inventory management system, SeaTable might be able to come in handy. With the powerful REST API endpoint support, it can actually become the backend, and use mobile app to query the products’ information from front end, by using QR Code.

It is simple, let’s us show you how do built a simple inventory management system with QR Code, and REST API within 10 minutes.

At the point this article is published, there are 2 limitation we have to overcome with SeaTable

1. No reference to Row ID in formula column type, we have to create our own unique ID for this
2. No QR Code image column type, we have to use a 3rd party solution, QR Code API. At this moment, it is free.

1. Let’s start by creating a base, we call it “Inventory”

2. Then we create a table, call it “products”

3. Create the relevant fields for the “products” table.

4. QR Code image required a value to be embed, and even if we using formula column type, at this moment, we are not able to refer to Row ID for SeaTable. The workaround is, we create an additional column, called “ReferenceID”, with the column type is Auto number

We will use this as value for the QR Code.

5. Now, let’s create a column with column type formula and call it “QR Code Generator”, and we enter the formula below

concatenate('https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=', {ReferenceID})

This formula basically tell

• Create a URL from a free QR Code service
• We pass it ReferenceID (which we created as Auto number just now) as the QR Code value
• As these are actually 2 strings, we have to use SeaTable built-in formula: concatenate to join the strings.

6. Lastly, create the last column, let’s call it “QR Code”, and the column type is image.

7. To make thing works, we will have to import some sample data. As usual, we prepared the data for you here.

8. Upon data being populated, you can see the 2 columns, “ReferenceID” and “QR Code Generator” are being automatically populated as well.

9. Now, here is the strange part, you need to use keyboard hot key to copy the URL value from column “QR Code Generator – not right click and copy. For Windows, it is CTRL + C, for OS X, it is command + C.

Next, click on the image column, a pop up like below will appear, and paste the URL at the image link. You shall see the QR Code will be generated, just click “Add Image”, and it shall store in the column.

11. To print the QR Code as a list, we can make use of the Gallery plugin.

12. Once you installed that, the Gallery plugin will automatically detect the image field, and show on screen, neat?

1. To get prepare for the REST API, to serve as backend, we have some work to do for SeaTable. Let’s click on the “Team admin”

2. Click the button, “Create Base Token”, this token will enable us to get an authentication token before we query the products’ information everytime.

3. Enter some info for the token, and of course select the correct base and permission. For now, we only need “Reading only” permission.

4. Your token for “inventory” is finally created.

5. We need to get 1 more parameter, which is the base id. Go to “Bases”, click on the base we want.

6. Take note of the Base ID.

7. Alright, we have everything we need to test the REST API now. To test the SeaTable REST API, we need REST API Client software. In below example, I am using Postman.

In below example, here is what I did

• Place the base token, at the header, with the Key value is “Authorization”, and the value is “Token YOUR_TOKEN_HERE“
• The method we use is GET, and the URL is https://cloud.seatable.io/api/v2.1/dtable/app-access-token/
• This is the method to get the “access_token” before we query the products’ information

8. We got the access token from previous step, and now, we make use of that token to query product information. Please take note of what I did below

• The method is POST
• The URL is https://cloud.seatable.io/dtable-db/api/v1/query/YOUR_BASE_ID/
• The access token we get from previous step, place in the header, with Key value is “Authorization”, and the value is “Token YOUR_ACCESS_TOKEN_HERE“
• Under the body, I place the SQL statement as JSON format

{
    "sql": "SELECT * FROM products WHERE ReferenceID = '0001'"
}

The above SQL statement basically tell, get the product’s information which ReferenceID is 0001 for me.

9. If nothing wrong with the parameters and the SQL, you should see the products’ information return successfully.