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:
“Type B Parts” table:
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:
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.
“Document B” can be generated in the “Type B Parts” table.
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:
Also, you should create the same column in the “Type B Part” table:
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.
First, let’s create a “Barcode type”- column called “QR Code”. Select “QR Code” option in the “Type” dropdown:
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:
The “RecordIdB” summary column should be added to the second relation.
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:
| 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.
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.
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”.
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.
Moreover, each trigger should include two actions:
The first action of the ‘Update Record’ type is called “Generate DocumentA”.
This action contains one assignment.
The second action of the ‘Mobile Device Update Record’ type is called “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:
and the “DocumentB” column in the “Generate DocumentB” action:
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:
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.
Then you will see a QR code of your user account.
As the next step you should download TeamDesk Mobile Client App and click on the “Scan QR Code” button.
As a result, you’ll see the “Inbox” screen.
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:
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.
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.
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.
You can see the sent requests in the “Sent” menu option:
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.
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.
Click on this PDF document to open it and review the part-related details:
Here you can review all data related to the “Type A Part” record linked to the scanned QR code.
