Lookup filter when using Barcode Reader

[Zradene]

Hi Guys, I am using a lookup table based on a custom browse on a form. I am using a Barcode Reader to scan & select a product from the lookup table.
This system is used to test each individual product item within the product table every 6 months. I am trying to find the best way to filter each scanned product to identify whether or not that particular product has been already scanned and tested during each 6 month period. I need this to take place WHILST BEING SCANNED, not when trying to save record etc.
I need to display a custom message if an "already tested item" is scanned, as I can then add xbasic code to do other things after the message is displayed.
I have tried using the validation options within the field rules, but that just brings up a default message which I do not want.

Each 6 month period gets a unique Invoice Number assigned to each product tested, so I am using the code below.
I am currently using this code on the form. I have tried using the code on various events on the form and in the Field rules events, but without success.

tbl = table.current()

if key_exist("invoice_number", alltrim(tbl.INVOICE_NUMBER) + " " + alltrim(tbl.PRODUCT_ID) ) then

cancel()

ui_msg_box("Error", "This Product has already been tested for this period")

end if


Any idea's anyone ?

[Tom Cone Jr]

Re: Lookup filter when using Barcode Reader

Fred,

Please make a screencast using the free version of Jing to show us the exact sequence you're facing. Jing is available at the Techsmith website.

Next tell us how invoice numbers are re-assigned to the products each 6 months.

Next tell us if scanning a product is the same thing as testing a product. If not tell us how they're different.

It's not clear to me why this is happening while the user is doing a lookup. It sounds like you want to use the scanner to get a product id and then use the id to do a lookup. And, then, take one path if the record is already there, and another if its not. You don't need to do a field rules table lookup to do this. Or, am I missing something? If so, tell me what you mean by a lookup and a lookup filter.

Thanks.

[Zradene]

Re: Lookup filter when using Barcode Reader

Hi Tom, got a lot of client info in this so no can do mate regarding Jing.

Regarding generating the invoice numbers, I am simply looking at todays date and if it falls between 2 dates, then the invoice number is set accordingly. This all works just fine.
What I mean by scanning is using the barcode reader to identify a product. The product information relating to the scanned barcode is displayed on screen where the client can tick field boxes to indicate whether or not the item has passed the safety regulations relating to that item.
On clicking my save button a record is written to another table which is basically a store for the test results.
Everything is working just fine, but all I am trying to do is stop duplicate records being created, but I need to display my own message in xbasic when the client scans a barcode which has already been scanned. (ie. meaning the product has already been safety tested).
As the invoice_number for all products tested lets say for July will have the same invoice number, and as each product_id has a unique product ref, I thought I could use the code above to bring up a message. As I said before, I have tried using the validation options in field rules, but as I have an alternative Product Search option, when the duplicate product is scanned, I get the default alpha 5 message box appearing, and this is not what I want. I need my message box appearing on screen.

Fred

[Tom Cone Jr]

Re: Lookup filter when using Barcode Reader

Still not clear on all details.

However, if the table used as store for the test results has an index based on the invoice number and product id code couldn't your save button check to see if the current product has already been stored (i.e. in the store table)?

if so, why not have your script use one of the lookup() functions? or roll your own, by scripting an indexed find by key?

[Zradene]

Re: Lookup filter when using Barcode Reader

Hi Tom, the problem I have is that I need to STOP the user doing the test (if it had been done already) otherwise the user would not realise this until they tried to save the record. I need my message to appear as soon as the barcode has been scanned.

Fred

[Tom Cone Jr]

Re: Lookup filter when using Barcode Reader

Fred, this is why an example or screencast would assist us in helping you. data entry sequences are difficult to describe in words.

What happens when the product id is scanned? Be specific. Describe what happens on screen and what the user does with mouse or keyboard.

- tom

[Zradene]

Re: Lookup filter when using Barcode Reader

Hi Tom, the LOOKUP field is the field in focus on entering this form. The user then selects product for testing. They then swipe the barcode and the product information is then displayed from the Product database. The fields that will be modified are in another joined table.
Using a barcode scanner is exactly the same as typing in a barcode number. We are using 4 or 6 digit barcode Tags. example 123456

I will send you a PM so you can download it Tom.

[Stan Mathews]

Re: Lookup filter when using Barcode Reader

if key_exist("invoice_number", alltrim(tbl.INVOICE_NUMBER) + " " + alltrim(tbl.PRODUCT_ID) ) then

You speak of while the item is being scanned but before the record is saved. This line checks the field values in the current table record which have not changed if the record is not saved. Perhaps you want to reference the form object values rather then the table values?

if key_exist("invoice_number", alltrim(INVOICE_NUMBER_object_name.value) + " " + alltrim(PRODUCT_ID_object_name.value) ) then