Use a bar code reader inside a Windows document

In this example we will try to fill in the rows of a document automatically using a USB barcode reader. This example cannot be used on mobile devices for obvious reasons.

Script Group

Form

Event

Value is being modified

A barcode reader is nothing more than a keyboard emulator. In practice, it reads the code, transforms it into text and inserts it at the point where the cursor is at that moment, as if it were written with the keyboard.

Having recovered our reader, we will create a field inside the document that we will use as a container to retrieve the reading. This field will not save anything in reality, but it is necessary to be able to launch the procedure.

The script will be linked to the event that intercepts the modification of the field. Let's create it and start inserting the following rows.

if datafield.name() ~= "barcode" then

do return end

end

if string.sub(datafield.getvalue(),-1) ~= "£" then

do return end

end

The first rows of the script are used to understand if the field being edited is the one we will use for reading the barcode. This is because the script runs on changing all the fields on the tab.

The first condition checks that the field is the correct one, while the second serves to verify that the insertion has been done through the barcode reader with a small trick.

Since the script is launched with every single modification, it is necessary to avoid searching for items for each written character. Since the reader is like a keyboard, it will literally write our code character by character.

A feature of the readers was therefore exploited, that of being able to add a special character at the end of the reading. This is possible by programming the reader thanks to its specific procedures that can vary from one to the other. In doing so, until the script reads the special character as the last character, it will not continue with the processing.

s = string.sub(datafield.getvalue(),1,string.len(datafield.getvalue())-1)

datafield.setvalue("")

Once confirmed that the field is the right one and that it has a complete reading given by the special character, we create the string s to have the codice by removing the last character, the one at the end of the reading. We then delete what is currently written in the field in order to proceed to the next reading.

table_rows = dataview.getatable("invoice_rows")

nrows = table_rows.countrows()

rows = table_rows.getrows()

Since it is certainly possible to read the same code several times before inserting it on the invoice, it is necessary to check that one does not already exist. If it exists we will increase the quantity, otherwise we will create the new row. We then retrieve the table, the rows and the number of rows.

for i = 1,nrows do

code = rows[i].getvalue("code")

if code == s then

--Increase the quantity

q = rows[i].getvalue("qty")

q = q + 1

rows[i].setvalue("qty",q)

table_rows.update()

do return end

end

end

In this cycle we turn the rows to see if there is one with the same code read. If we find it, we increase the required quantity and block the execution of the script because it has finished its task.

table_item = database.getsql("SELECT * FROM items WHERE code='" .. s .. "'")

If we have reached this point (correct field, code read, code not present in the rows) we begin to see if the code read corresponds to one of our item present in the database, so I proceed to create the table by querying the database.

if table_item.countrows() > 0 then

rows = table_item.getrows()

row = table_rows.adddatarow()

row.setvalue("code",s)

row.setvalue("gguid_code",rows[1].getvalue("gguid"))

row.setvalue("description",rows[1].getvalue("description"))

row.setvalue("um",rows[1].getvalue("um"))

row.setvalue("initial_price",rows[1].getvalue("price"))

row.setvalue("qty",1)

table_rows.update()

else

--if I have to manage the creation

end

If I find the items with the code read, I proceed to create the new row and insert the essential data (the code and its gguid) and all those that I believe are useful for completing the row.

Finally, we force the update with an update to inform the program to recalculate any expressions.