QR Code Scanner

Link a QR code scanner to your database

Let’s consider an example on how to link a QR code scanner to a database. Say you’ve built the “QR Code Scanner” application. The main idea of this database is that users can scan QR codes via a mobile device and then search database tables for records containing QR codes.

Assume that your database keeps descriptions and photos of different computer parts. Each part has a unique QR code. There are parts of two types: Type A and Type B, so you keep these types in two separate tables:

“Type A Parts” table:

QR Code Scanner

“Type B Parts” table:

QR Code Scanner B

So, each user should be able to scan a particular part’s QR code to search the database for this component and get all the supplemental information related to this part.

For example:

QR Code Scanned

What would you have to setup in your database to build this functionality?

1.Generated Document

In this example each table contains a generated document that keeps all the data related to the part. For instance, “Document A” can be generated in the “Type A Parts” table.

DocumentA settings

“Document B” can be generated in the “Type B Parts” table.

DocumentB settings

2.Auxiliary Formula-Text column

Moreover, you need to add a “RecordId” auxiliary formula-text column to each table with the following formula: RecordId().

So, let’s create the “RecordId” formula-text column in the “Type A Part” table:

RecordID A

Also, you should create the same column in the “Type B Part” table:

RecordsID B

These columns will be used in the future adjustment.

3.New “Search Requests” table

Now you need to create a new auxiliary table. Let’s name it “Search Requests”. This table should include the following columns: QR Code, Document, RecordIdA , RecordIdB, DocumentA, DocumentB.

Columns

First, let’s create a “Barcode type”- column called “QR Code”. Select “QR Code” option in the “Type” dropdown:

QR Code

Then add a “File Attachment” – type column called “Document”. It will be used in the workflow action setup.
On the next step it is important to add two Many-to-Many relations:
– between “Type A Parts” and “Search Requests” tables
– between “Type B Parts” and “Search Requests” tables.
with the following match condition: [QR Code]=[QR Code]

As a result, you can now add “RecordIdA” and “RecordIdB” summary columns to each relation respectively. These columns pull a maximal record ID from the related “Type A Parts” or “Type B Parts” table if a scanned QR code value kept in the “Search Request” record matches the QR code value kept in the “Type A Parts” (or “Type B Parts”).

Here is an example of the “RecordIdA” summary column:

RecordIdA summary column

The “RecordIdB” summary column should be added to the second relation.

RecordIdB summary column

The most difficult step is to build formula-URL columns. For instance, the “DocumentA” formula-URL column should contain a URL that consists of the elements described below:

FormulaURL A column

URL Element Description
62922 This is a database id number that is visible in database URL, for example:
https://www.teamdesk.net/secure/db/62922/overview.aspx?t=516386
Your database will have a different number.
9302F8A771AA4C2EB98A13AA35923400 This is the REST API authorization token generated by the system. In your database you will use a different token.
Type%20A%20Part This is the table name “Type A Part”, please note that spaces are replaced by “%20”.
Document%20A This is the document name. In this example the document name is “Document A”. So, the space is replaced by “%20”.
[RecordIdA] This is the name of the summary column described above.

You need this formula-URL column to generate a URL referring to the document that belongs to a “Type A Part” record if the QR code of this record was scanned.

Also,  you should build a similar URL for the “DocumentB” formula-URL column.

FormulaURL B column

4.Form Behavior

When all the columns are added, you need to adjust the form behavior in the “Search Requests” table. First, set the “Document” column as ‘Readonly’ on the Default Form. After that add the RecordId()<>0 criteria and set the ‘Readonly’ option for the “QR Code” column in this criterion.

Form Behavior

5.Mobile Device Record Create action

Now you need to add a mobile action allowing code scanning and searching. Let’s set it up in the “Search Requests” table and name this action “Search for a record”.

Record Create Mobile

The “Search for a record” mobile device ‘record create’ action allows QR code scanning and inserting this code into the “QR Code” column. As a result, a new record is added to the “Search Requests” table.

6.Triggers with Mobile Device Record Update actions

The next step is to create triggers with mobile device record update actions. In our example you need to create two similar Record Change Triggers. Let’s name these triggers “If QRCodeA” and “If QRCodeB” respectively.

QR Code Scanner Triggers

Moreover, each trigger should include two actions:

Trigger A with Actions

The first action of the ‘Update Record’ type is called “Generate DocumentA”.
This action contains one assignment.

Assignments A

The second action of the ‘Mobile Device Update Record’ type is called “Search Result”.

Search Result

Please note that this mobile device update record action is used in both triggers.

Now you can add the “If QRCodeB” trigger to the “Search Request” table. The setup is similar to the “If QRCodeA” trigger setup, but you need to use the “RecordIdB” column in the filter:

Trigger B with actions

and the “DocumentB” column in the “Generate DocumentB” action:

Assignmetns B

After that you can add the existing “Search Result” mobile device update record action to the “If QRCodeB” trigger.

As the result, you can see the following actions created in the “Search Requests” table:

Mobile Actions

The database adjustment is done.

How to link QR scanner to database?

1.Connection of mobile device to database account

To connect your mobile device, such as smartphone, to your account, open the “Personal information” page and click on the “New” button in the “Devices” section.

Add new Device

Then you will see a QR code of your user account.

DB QR Code

As the next step you should download TeamDesk Mobile Client App and click on the “Scan QR Code” button.

Scan QR code button

As a result, you’ll see the “Inbox” screen.

Inbox

2.Mobile Device Record Create Action

To enable QR code scanning you need to activate the “Search for a record” action.
Click on the menu icon to open the menu:

Mobile Menu

Choose the “Search for a record” link in the menu to activate a mobile ‘record create’ action.

As you know, the “Search for a record” action allows to fill in the “QR Code” column value when you scan a particular part’s QR code via your mobile phone.

Mobile Search

Click on the QR code icon to begin scanning.

When the scan is complete, the text will appear in the “QR Code” field.

Use the “Send” icon to submit the record.

Submit Record

As a result, the request will be sent to the database and a new record containing QR code value will be created in the “Search Requests” table.

Search results table

You can see the sent requests in the “Sent” menu option:

Mobile Menu Sent

3.Mobile Device Record Update action

When you enter “Inbox” tab, you may review linked document containing Part A (or Part B) detailed description. This document is generated by either “Generate DocumentA” or “Generate DocumentB” record update actions and can be opened via a mobile phone with the help of the “Search Result” mobile device ‘record update’ action.

“Inbox” tab is updated every 5-10 minutes automatically. You can still update “Inbox” manually by scrolling an empty screen down to force uploading of the inbox record/notification.

Mobile Search Result record

Click on the notification to open the record and review generated “Document A”.

As you can see, the QR code you scanned is ‘Type A Part’ QR code.

Mobile Document

Click on this PDF document to open it and review the part-related details:

QR Code Scanned

Here you can review all data related to the “Type A Part” record linked to the scanned QR code.

Leave a Reply

Your email address will not be published. Required fields are marked *