Trim and copy field contents to another based on prefix

[Bill_misc_IT]

I'm creating a database to replace a warehouse receiving log. The fields are part number, quantity, supplier, etc.

Rather than navigate to the individual fields an type in data, I'd like to use a barcode scanner to enter the data into a temporary "data entry field" and have it copy to the appropriate permanent field based on the prefix (i.e. P is for part number, Q is for quantity, etc)

To test the concept, I created 3 fields: part number, quantity, & data entry. I tried to set the data entry field as a calculation to perform a case function for the various prefixes, but could not find a way to copy the contents to another field within the calculation. Plan B was to create scripts to copy field contents from the data entry to the appropriate permanent field, but couldn't figure out how to initiate a script based on a case function within a field calculation.

I know this will be an easy task, just need a little nudge in the right direction.

Thanks in advance.

[Fitch]

Set the auto-entry options for your fields:

Part

Case( Filter( barcode ; "P" ) ; barcode )

Quantity

Case( Filter( barcode ; "Q" ) ; barcode )

Plan B: The only way to initiate a script based on updating a field is with a FileMaker plugin such as zippScript.

[Bill_misc_IT]

It worked! I had to change one part of your calculation:

Part

Case( Filter( barcode ; "P" )="P" ; barcode )

Now if I enter P12345 in the barcode field, P12345 is copied to the part number field...the same is true for Q100, etc.

One more thing. I want to trim the leading character, so that I only copy 12345 and 100. How do I set of the function to remove the first character (or two) assuming that I do not know the length of the string.

For example:

One quantity may be Q100 another may be Q2500, so there is not always the same number of characters to the right.

Thanks.

Set the auto-entry options for your fields:

Part

Case( Filter( barcode ; "P" ) ; barcode )

Quantity

Case( Filter( barcode ; "Q" ) ; barcode )

Plan B: The only way to initiate a script based on updating a field is with a FileMaker plugin such as zippScript.

[Fitch]

There are several ways you could calculate that. Probably simplest is GetAsNumber:

Case( Filter( barcode ; "Q" )="Q" ; GetAsNumber(barcode) )

That's fine for quantity, if your part number starts with a zero it will strip it.

Another option is Substitute:

Case( Filter( barcode ; "P" )="P" ; Substitute(barcode; "P"; "" ) )

Yet another option would be to use Left, Right, or Middle functions -- but I think one of the above will do it.

[Bill_misc_IT]

Here is the problem with the below solutions

Case( Filter( barcode ; "Q" )="Q" ; GetAsNumber(barcode) )

will not work for alphanumeric part numbers.

Case( Filter( barcode ; "P" )="P" ; Substitute(barcode; "P"; "" ) )

will not work if there is a "P" elsewhere in the alphanumeric part number, right? i.e. PL011045P012, whereas the part number is L011045P012. The prefix "P" is added to the barcode.

The other problem is that some of the barcode fields add two digits to the prefix (such as "1V").

Here is the calculation I tried:

Case( Filter( barcode ; "P" )="P" ;

Trim(Left(barcode;1 )))

The above function trims the wrong portion. I'm left with "P" in the field not the 12345 that I want. Is there a function to trim a specific number of digits from the left? Every example I've seen for Left, Middle, Right, requires you to define the exact number of characters remaining, which is the opposite of what I want.

[comment]

some of the barcode fields add two digits to the prefix (such as "1V").

How is one supposed to know when that happens? You say that in "PL011045P012" the prefix is "P" and the part number is "L011045P012". But in "IV011045P012" the prefix will be "IV" and the part number "011045P012"?

Unless some more information about the rules is provided, there is no way to distinguish between the two.

[Bill_misc_IT]

I guess I wasn't clear as to the prefixes:

Here is a brief list of barcode prefixes (data identifier) and their description (common in automotive manufacturing):

P - Part Number

Q - Quantity

S - Serial number

1T - Lot number

V - Purchase order number

...

On a barcoded label, the part number will always start with P, quantity with Q, lot number with 1T, etc.

In my database, I'd like to scan the individual barcodes into one field named "barcode." Based on the prefix, the data will copy to the appropriate field. I've already accomplished this with the below functions:

Part number auto-enter calculation

Case( Filter( barcode ; "P" )="P" ; barcode

Lot number auto-enter calculation

Case( Filter( barcode ; "1T" )="1T" ; barcode

Quantity auto-enter calculation

Case( Filter( barcode ; "Q" )="Q" ; barcode

When I enter P12345 into the barcode field, P1234 is auto-entered into the part number field. Q100 in the barcode field enters into the quantity field, etc.

In your example below, 1V011045P012 would copy to the field assigned to the prefix 1V, not the part number field assigned to P.

The two problems that I face with my above function are:

1. I'd like to remove the data identifier prefix (i.e., P, Q, 1V, 1T, etc) from the alphanumeric data during the auto-enter.

2. If one of the data identifiers happen to be part of the encoded data (if a part number had a "Q" in it), the data would auto-enter into the part number AND quantity fields, when it should only copy to the part number field. I think I can solve this by embedding a "Left" function within the filter function to only check the first digits.

I hop that this clears any confusion. A part number barcode will ALWAYS start with P. Quantity with Q, etc.

Hope this helps explain my issue.

How is one supposed to know when that happens? You say that in "PL011045P012" the prefix is "P" and the part number is "L011045P012". But in "IV011045P012" the prefix will be "IV" and the part number "011045P012"?

Unless some more information about the rules is provided, there is no way to distinguish between the two.

[comment]

The problematic part here is the "etc.". I would like to see the full list of prefixes, before suggesting a solution.

[Fitch]

Given the new info, I wouldn't use the Filter function, but rather something like this:

Let( [ 

  prefix = "P" ; 

  len = Length(prefix) ; 

  result = Middle( barcode; len+1; 9999 ) ] ; 

  

 Case( Left(barcode; len) = prefix; result) )

All you need to modify is what's between the quotes.

[Bill_misc_IT]

:yourock:

It works like a charm! I would have never figured that one out! Thanks for everyone's help. I'll post all the prefixes, for future reference (in case there is a simpler solution and/or for other's solutions)