upc code confusion

[randini]

I have a report that shows case sales of multiple items, each with a unique upc code. By unique I mean, some codes have only the second half of the upc code and not the first part. Others have a alternate 3-5 digit code altogether, and are much shorter numbers than the upc codes. And some of the codes are formatted just right.

I created another table that has the alternate code numbers corresponding to their appropriate and properly formatted upc code numbers. What I want to do is to have all the upc code numbers properly formatted for the report and also to show total number of cases sold by upc code.

So I figure I have to compare the upc codes in the original report against both the alternate code and the properly formatted code in my table. I don't think matching the alt codes up with the proper upc codes would be difficult (famous last words) but I'm not sure how to go about dealing with the fragmented code in the report. I think I have to come up with a calculation that would allow a match based on a partial text string in the report. But I am pretty much a newbie and getting in over my head, so any help would be appreciated.

Thanks

Randy

[coconutt2000]

I think I know what you're saying. Make sure your fields are text type.

I'm thinking an exploded key could work. An exploded key solution would increase the size of your indices considerably, but it would work.

An exploded key, for your use, would take the real code and break it down into short, incremented strings.

Example UPC: ABC12345678901

Exploded key:

ABC12345678901

ABC1234567890

ABC123456789

ABC12345678

ABC1234567

ABC123456

ABC12345

ABC1234

ABC123

ABC12

ABC1

ABC

AB

A

Essentially. This could get cumbersome, and it takes a custom function to make these easily with a recursive function. Naturally, the match field would be a calculating field.

Another idea would be to use the logical operators in the relationship panel. And you're probably going to ask me why I didn't mention this idea first. -)

Mimic type-ahead feature in FileMaker Pro fields

by John Mark Osborne, Database Pros

Type-ahead has always been on the wish list of FileMaker developers. The FileMaker technique that most closely mimics type-ahead lets you make partial entries in a field to filter a portal. This requires you to exit the field after the partial entry, move the mouse, and click on a portal row. In this tip, you'll learn a new technique that lets you complete an entry upon exiting a field using the LookupNext function in an auto-enter calculation.

Start by creating a table with a field called Value and enter a record for every value you want to auto-complete. Relate the Value field to a Text field in the table where data entry will occur using an equijoin operator. Finally, type a calculation with the following formula into an auto-enter calculation attached to the Text field:

LookupNext(VALUES::Value; Higher)

When you exit the calculation dialog, be sure to deselect the "Do not replace existing value in field (if any)" option. This lets the auto-enter calculation auto-enter every time a user modifies the Text field.

When you enter a partial value into the Text field, the LookupNext option won't find an exact match but will look for the next highest value based on the second parameter. The next highest value should be the completed word. Two words could be close enough to require you to enter a longer portion of the word. For example, entering "ala" will auto-enter Alabama. For FileMaker to auto-enter Alaska, you have to enter one more letter of the value -- "alas".

Grabbed that from: http://www.filemaker.com/company/newsroom/news/Newsletter/newsletter_032005.html

I suspect you can probably adapt that hint to your needs.