Leading Zero

[PIerre Chouinard]

My database has Leading Zero on the Sku number.
How can add Leading Zero to The barcode Scanned?
Because when I try to lookup on it it does not work.

Thanks

 

[Dan Peluso]

Hi,
I can quickly think of 2 options

1. You can sometimes do this in the barcode scanner configuration itself. This will depend on the model of the scanner and also the barcode type you are scanning.

2. Alternatively, in TracerPlus, you can create a new calculated field that would use the Add function to add a static/constant '0' to the barcode field and use this calc field in your lookup instead of the actual barocode scan itself.

 

[PIerre Chouinard]

I was thinking of using Sql Custom command like this:
SELECT REPLACE('0',14-LEN(Field1))+Field1 FROM Session1 WHERE Field1='[*0*]' (Those functions are supported in SQL COmpact)

In the Select it will padd the field and do the lookup on it....(14 is the length of my SKU number field)
Make sense?

 

[Dan Peluso]

Using custom SQL is definitely an option to return some out of the ordinary type lookup result. I haven't validated the SQL above but conceptually, that could be a 3rd option.

 

[PIerre Chouinard]

Looking for Custom SQL documentation,
This SQL return the number 6 do not know what it means...

 

[Howard Heckman III]

Could you try something like this...?

SELECT Field1 FROM Session1 WHERE Field1 = ('0'+'[*0*]')

That should pad the leading zero to the value entered on the form.

 

[PIerre Chouinard]

Maybe I missing something it only show 0 (zero)

 

[Corey Hagewood]

Hi,

You can find the documentation on the TracerPlus Custom SQL here:
https://support.tracerplus.com/index.php?/Knowledgebase/Article/View/216/0/writing-sql-queries-for-tracerplus

Another option that can be used here is TracerPlus Form logic. It should be possible to do this with two logic items. First construct 2 fields, one to hold the raw barcode and another to hold the barcode with the leading zero. Now using logic, set the value of the second field to be 0[*0*]. You can trigger this logic on the Afterscan event in the raw barcode field. This will get your leading zero barcode into the second field, so now you just need a way to trigger the lookups off that value.

For your second logic item, you can try a Field value Changed event with an Afterscan action on the Leading zero Barcode field. This should Active the lookups any time a new barcode is scanned into the Raw Barcode field.

 

[PIerre Chouinard]

Corey your solution worked, but only if the raw data is always at the same length. By padding a value, this value should always keep the same length By example if we want 14 chararacters with Leading Zeros
1 is
00000000000001
and 123456 is
00000000123456

 

[PIerre Chouinard]

Let's face the problem deffirently.

Can we do a Lookup on the Database, by a value ending By.

Lets Say scanned Barcode 123456 and the database 00000000123456 it will find it...

 

[Dan Peluso]

Hi,

You cannot do a TracerPlus lookup on partial data like that but custom SQL could probably be configured to do that.
Another option similar to Corey's is to force leading zeros to a string to match a certain length using Calculated fields (ADD and RIGHT) by adding a static field of "0000000000000" with your scanned barcode and then taking the RIGHT, 14 of that result.

I have attached a sample project attempting to outline what I am describing.

EDIT: Sorry, this attachment failed. Let me know if you would like it and I can send it via email.

Thanks.

 

[PIerre Chouinard]

Dan
yes you can send you project pchouinard[at]nostratek.com

 

[Dan Peluso]

Sent. Let me know if it helps clear anything up.