Serial Number Analysis

[Don De Es]

Hello all

I need to be able to ascertian a make,model and date of manufacture of a product through the anlysis of a product serial number. Products for this organisation appear under different brands and each brand is identifiable by the first letter in the serial number on the unit. This is then followed by a date, then a model identity number and finally the sequential number of the production run. I need to write a function that looks at the number and auto fills the relevant information into different filemaker fields.

example serial number A030511112222 would equate to the follwoing;

A - brand/manufacturer

0305 - third month (march) fifth year (2005)

2222 - twothousandth two hundred and twentysecond unit in the production run

Can anyone tell me how to go about extrapolating this data or point me to an exsting thread?

Thanks to you all for your time and efforts making us new boys and girls look like we know what we are doing.

[MarkWilson]

Welcome to the Forums!

You can use text and number fields with auto-entered calculated values to extract the data. These formulae should get it for you.

SN_Month ==

Middle( SerialNumber ; 2 ; 2 )

SN_Year ==

Left( Year( Get( CurrentDate ) ) ; 2 ) & Middle( SerialNumber ; 4 ; 2 )

SN_Sequence =

Middle( SerialNumber ; 10 ; Length( SerialNumber) - 9 )

This one is a little trickier. This formula will get the manufacturer code letter.

KEY_SN_ManufacturerKey ==

Left( SerialNumber ; 1 )

You will need to use this in as a relationship key to lookup the value from the ManufacturerCodes table.

SN_Manufacturer ==

Lookup( RelatedTable::RelatedField ; "" )

[Don De Es]

Many thanks for this Mark

I hope it is not too much trouble to explain this a little more so I can understand how filemaker works.

Taking the example

SN_Year ==

Left( Year( Get( CurrentDate ) ) ; 2 ) & Middle( SerialNumber ; 4 ; 2 )

I understand I need to create a field (calculation)in my table called SN_Year but do I always need to use the unscore for this type of thing? This formulae would presumably go into the calculation. Why do I need ( Year( Get( CurrentDate ) ) ; 2 ) part of this. Is this to tell me how much time has elapsed since the serial number date?

I hope this is not a silly question, its just that I am so new to this.

Thanks againfor your help,

Don

Edited October 14, 2007 by Guest

[MarkWilson]

Attached is a sample file so you can see how it works.

It is recommended to use the "_" (underscore) or CamelCase for file, table, field, and relationship naming conventions. Spaces in the names will only be an issue if you start integration with other applications or use FileMaker on the web; it is part of FileMaker best practices.

Left( Year( Get( CurrentDate ) ) ; 2 ) & Middle( SerialNumber ; 4 ; 2 )

This formula is to return the year. The "CurrentDate" portion prefixes the 20xx to the fourth and fifth characters of the SerialNumber field. It would be easier to write the formula as "20" & Middle( SerialNumber ; 4 ; 2 ). The current year prefix will not be required for over ninety years.

Manufacturing.zip

[Don De Es]

Hello again Mark

I hope again I am imposing to ask further questions.

I wonder how I might check to see if the first two digits of a serial number is the member of a value list in order to then carry on and make further calucultions.I have been experimenting with a small database that I would like to include but I can't see how to do it on this post.

In field 'three' of this database I want to autoenter 'XXXX' when the first two letters of the 'serial' field are not one of the values in a value list called 'ManufacturerCodes'. The value list is itself the product of the entries in a table in the same database called 'Manufacturer Codes'. Filemaker accepts the calculation but always returns 'XXXX', even when the first two letters are a member of the 'ManufacturerCodes' value list.

I have used the calculation..

Case (Left (Serial;2) ≠ ValueListItems ( "serial number calculations" ; "ManufacturerCodes" ); "XXXX"; Middle (Serial;6;4))

I hope this problem is not an obvious one. I am sure you must tire of this in the end.

How do I attach the filemaker database by the way?

In gratitude

Don

[Fenton]

Why are you using 2 letters? You said in the first post that it was only the 1st letter was the manufacturing code, that the 2nd letter (which is a digit) is part of the month number.

The ValueListItems is the whole value list, all the values (separated by returns). So your single letter will always not be equal to it. What you want is PatternCount().

Also, if you have 2 parameters in ValueListItems(), the first must be the file's name, or the expression: Get (FileName).

I thought you already had (as Mark posted) a calculation field for "KEY_SN_ManufacturerKey", but I'll redo as you did (more or less). I'm using the Let() function to make it clearer to read.

Let ( [

man_code = Left (Serial; 1);

vli = ValueListItems ( "ManufacturerCodes" )

];

Case ( not PatternCount (vli; man_code); "XXXX"; Middle (Serial;6;4) )

)

[Don De Es]

Hi Fenton.

I am sorry for the confusion. In truth the actual serial I am using does have two letters on the manufacuter code but I do not need to use the second.

As I understood the first examples posted by Mark, I started to discover more of the possibilities and became more curious. I simply wanted to understand a bit more of the language of filemaker so I comlicated it a bit more. I thank you for your help and hope you will forgive my naivity.

I will try to get my head around your formulae and try not to post needlessly.