Barcodes and custom functions

[adolphe]

Hi, I am a new user at this forum. I have used FileMaker for many years, but never really delved in to its possibilities. But when I started reading about custom functions I became very interested.

I work with travelling exhibitions which involves a lot of logistic planning of packing and shipping. I usually make pretty basic FileMaker-databases for all the crates and objects in the exhibition, but this time I would like to make it a bit more sophisticated by using a barcode scanner.

Here’s what I want to do:

For each item in the exhibition I want to create a custom barcode consisting of 13 numbers. The first two numbers tell which part of the exhibition the item belongs to, no:s three and four tell what category the item belongs to, no:s five and six, what type of item, no:s seven and eight what subgroup within the type, no:s nine and ten which code the object has within the subgroup and the last three digits give the crate number i.e. in which crate the item should be packed in.

The barcodes will be easy to create with the proper application and printer. It is also easy to scan them and get the information in to a field in FileMaker with a barcode scanner. The challenge is to configure the database so that it translates the data correctly and puts information in the correct fields.

For example, when I enter the 13 digit code: ”0123456789 123” (the space is intentional to separate the crate number from the rest of the digits) in the barcode field in my database I want it to automatically fill in information in the following fields:

”Exhibition part”

”Object category”

”Object type”

”Object type subgroup”

”Object code”

”Crate number”

Furthermore I want to define what information the codes will generate in the fields.

”01” will for example in the code above example generate the text ”Saint Petersburg” in the ”Exhibition part”-field, ”23” generate ”Technical equipment” in the ”Object category”-field, ”45” generate ”Screen” in the ”Object type”-field, ”67” generate ”17 inch” in the ”Object type subgroup”-field, ”89” generate ”4a” in the ”Object code”-field and ”123” generate ”123” in the ”Crate number”-field.

I apologize for the long and winding explanation, but I hope someone found the matter interesting enough to read it through. What I want to know is, is this this possible to do in FileMaker? Is custom functions the way to go, maybe even the only way? Is it enough to create a custom function where I define all the information above for one field (the ”Barcode”-field”) or is it better to define value lists in the corresponding fields that you refer to in the custom function? I would be grateful if someone could point me in the right direction for more information!

Thanks!/ Adolphe

[Mikhail Edoshin]

I don't quite understand why you want first to encode the data in a barcode and then decode it back. Why not to keep the data in FileMaker records and only print the unique ID of the record in barcoded form? Then as you scan the barcode, a script gets the ID, enters find mode, finds the record and displays all the other data.

[adolphe]

I see what you mean. That would work as well, but maybe take a bit more time.

My planned work process would be to generate all the barcodes i need for the exhibition, print them with a dymo and then paste the codes on the different items. Then, when I pack the items in crates, I add them to the database by scanning them.

If I do it the other way round, i.e. start by adding all the items to the database and let FMP generate the barcodes I have to do the organizing work twice: first go through all the items and add them through the database, then print the barcodes, go through them again to paste all the labels and then mark them in the database as packed when I pack them.

My way might be a bit more complicated, but I think that if I keep my concentration it should all work out well. But maybe I’m not thinking it through…

[comment]

I was thinking the same thing as Mikhail, when I read your first post. The missing link here is how are you going "to generate all the barcodes i need for the exhibition"? It would seem easiest to do this in Filemaker - otherwise you'll have to keep a duplicate database of codes in another application.

Anyway, to answer your question, it would be very easy to decipher the barcode in Filemaker. No custom function is required for this. All you need is a translation table for each type of code (for example, Object Categories with fields for Code and Category). You would use the Middle() function to extract each barcode part to a separate field, and a relationship to get the meaning of the code from the relevant translation table.

[Colin Keefe]

One reason to encode metadata in the barcode is so other vendors/venues/clients/lenders/shippers that receive or release (or destroy, or repair, or pack) the crates can scan them and interpret the contents meaningfully into their own systems.

That assumes all the parties involved - venues, lenders, craters, shippers and so on - actually cooperate with each other to come up with a barcode standard.

Which, given how the art world operates, I rather doubt. There are opportunities for that kind of integration between vendors and long-term clients, but with a traveling exhibition you're probably going to be the only one scanning these things. If that's the case, then using the record ID seems simplest.

[Colin Keefe]

Also...just food for thought...

what happens when you flat pack 20 framed artworks into a single crate? Or mixed object types? How well does the encoding model you've come up with handle that?

Put another way, if you can put more than one object in a crate, how well can you really describe a crate's contents with a short string of numbers?

If the barcode metadata really must represent the contents, and they really will be scanned into other systems, I'd probably create a barcode representing the crate itself, and a packing list with individual barcodes next to each object, which the recipient could scan line by line into their system as they pull the objects out of the crates. They're going to want a packing list anyway, to do condition reports from.

[adolphe]

comment: Right now I have all the items in a word file that a colleague of mine has put together, sorted first into exhibition part and then into categories. It will be rather easy to assign each item a code according to the system above. If I then feed the codes into an application like Barcode X I will get barcode labels that I can put on the items.

Colin K: Yes, I or one of my colleagues will always be the ones scanning the crates and using the database. We will also always be there when the crates are packed or unpacked and when they are loaded to or unloaded from the containers. The shipping companies usually only need a long list in Word with all the crates and their content.

The crates will have their own barcodes, but very simple ones. Probably only with their number and what exhibition part they belong to. I will have two databases, one called ”crates” and one called ”items”. When I scan a crate I will get a post with all its contents in the ”crate” database. The Barcode X software can also be bought as a plugin to FileMaker, so each post will have it’s individual barcode in the database that I can print and use in different ways.

I am also hoping I can make this system mobile by syncing the database to my palm which I then can use to scan the crates and items with the help of a SDIO In-Hand Scan Card I have bought FileMaker Mobile 8 (which is now discontinued) that I hope will work. I was never able to get version 7 to work though. It kept crashing all the time.

Thank you for your thoughts, tips and help!

[Bailey Kessing]

I have a similar system but in a different field, biology. We store sample Tubes in Boxes and Freezers. We track millions of samples and their locations with barcodes and scanning tubes. Our tube barcodes contain info about the Samples (eg. ID, type, vial copy, etc). Sounds very similar to what you do with your codes. I parse the scanned barcode in to calculated fields based on the barcode characters to make human readable and also decide what to do with them. I can send an example, but not sure if this would be of use to you. Good luck.

[Bikeman17]

In order to generate a barcode that includes all the codes you want to assign to a product, you have to create a basic calculation field. Once done, you need to assign a barcode font to that field. If the Arial font is assigned to your fields by default, you must change that font for the barcode font you have in your font folder. This way your printer will printer barcodes.