process scanned value before using it as a ref key

[MyPill]

Hi,

I am working on an app to record transfers of pills. I use scanner to read barcodes from pill's packages to identify them. I have table PILL with unique codes of the types of pills (one type = one code) and table BATCH where one type of pill can have more different production batches with different expiration dates (one batch = one code; one pill type can have multiple batch codes).

Now - some packages have EAN13, but some others have 2d matrix code that strores more information about the package: type (the same value as what used to be in EAN13), batch and expiration date. I need to scan any type of package and be able to identify its type and find it in the PILL table. If I scan EAN13 it immediatelly finds the correct type, that's OK. But if I scan 2d matrix code I need to extract the type code first and then find that code in the PILL table. But how?

The solution is not to have all 2d matric codes in the PILL table, as the PILL table should have only one record for different type of pill, not one row for different batch of the same type of pill.

So I am looking for a way to

1. scan the code
2. try to find it in PILL table
3. if the code is in the PILL table, then use it as a REF key
4. if not then then try to extract a type code from the scanned string (maybe it is a 2d matrix code) that matches any code in the PILL table; if it succeeds then use it as a REF key
5. if the previous false then return nothing/null

Any idea? Thank you in advance!

[WillowMobileSys]

---

@MyPill wrote:

The solution is not to have all 2d matric codes in the PILL table, as the PILL table should have only one record for different type of pill, not one row for different batch of the same type o

---

How you handle this depends on how many different ways you might have to lookup details to identify the correct Pill row. 

1. If there are only a couple ways you would ever need to perform this lookup, then I would just add the necessary columns into the Pill table to facilitate the lookup functions.
2. However, if you have many different ways or unknown number of ways, then I would create a parent/child setup where the Pill table is the parent and then a Scan Codes table as a child table.  In this parent/child method, when you scan a bottle, the app performs the lookup in the Scan Codes table - not the Pill table - and if found then you have the Pill reference on the Scan Codes table row to assign the correct Pill row.

As for the 2D matrix code, I assume you are referring to a QR Code?  If so, then scanning them is very similar to scanning a barcode just that you get a string of text that can be used in various ways.  In your case, you need to strip out the details to identify the Pill.   How depends on the format of the string (or strings) which will determine the necessary APpSheet string functions to use.

Do you have any examples of the QR Code strings we can see as examples?

[MyPill]

Hm, interesting solution... Thanks for it.

I have PILL table with (bar)code as a primary key/ref key and pill's name as a label. So when you search for the pill you have one field with scan button. And the scan is immediatelly transformed to the pill's name. Or, using a browser version without scanner, you can choose pill's name from drop down menu. And I wanted to maintain such solution so the user have only necessary fields.

If I understand you right you suggest to have parent table PILL with causual ID as a primary key (one record per one pill) and then a child SCANCODE table with all possible QR codes and reference to the pill. Would it be possible to have one field with scan button and be able to choose pill's name from drop down menu as well as scan for pill's code in child table?

My goal is to show as little field as possible. User is not intersted to see the code, they just want to pick some readable name.

Anyway, my question reather focused to a possibility to parse the scanned string before it is validated. Let's say something like LEFT(scanned string, 10) and such a parsed string use as a final value in a field.