Finding Simialr But not the same Values

[Mark Appleby]

I have a feature request to put into my solution. I know that I can set a field to be unique. But I have a situation where invoices are coming in in an inconsistant format. I have talked to the suppliers about this problem but there is nothing that they can do at the moment. The problem is a stock reference might come in, in two different formats. While both are similar when we enter the stock we have found duplications in stock references. is there any way to be able to find validate a field perhaps by 6 charachters within it, please note that these 6 charachters can be anywhere within it at the start of the string the middle or the end?

EG one reference is j12hoo9456 the next delivery is h009456

or 3456789 next delivery being 3456789hap

Any advice is welcome.

Mark

[Ugo DI LUCA]

Hi Mark,

From your example, it appears that the numeric part would be the way to catch "duplicates". But I see another record with a num at first. Are there other formatting.

If not TextToNum(Right(Id, 6) could retrieve the numeric part...I think.

[Ugo DI LUCA]

Mark,

I was about to make some tests.

There is a num with 7 characters in your post !

In my opinion, you'll end with a nested Case statement and some replace steps as "oo" to "00", but I think it can be debugged....then indexed and if valid (relationship) use the numeric part which I hope is Unique.

[Mark Appleby]

Hi Ugo,

I am sorry if I didn't make myself clear. Some of the styles I get from my suppliers have just 3 charachters, but if that is the case it is unlikely that I would have this duplication problem. The codes above are actual situations where where we have had duplications. The big problem is that a lot of my suppliers are artisans they don't always follow procedures. I can give you other situations where these problems happened.

How about this!

hnh4557i37 on invoice 1 and 34557i on invoice 2?!?!?!?

I thought of one way around this would be to have a drop down list based on a relationship with the supplier but some of these would be far too long for someone to scroll through.

At the moment I am thinking that the way through might be to change the way I add line items from invoices. This would be done by entering a find using a global then transposing the supplier code into the supplier ref field with wildcards.

eg in find screen set global field as 4557 and then when performing the find setting the field supplier ref as *4557* at least then I would have a similar value. The only problem is that this is a clunky way of getting an invoice entered. At the moment I am trying to streamline the whole process.

Best regards,

Mark

[Ugo DI LUCA]

Mark,

I'd bet that even the numeric part is not unique as it comes from different suppliers...

I had the problem with some listed products from my suppliers (45 suppliers, 84,000 codes in catalog) too...

Some would supply an alphanumeric code of za856PKl where others would only have 856.

Well it was such a mess to find out the good products, that I just made my own Unique code and attached theirs to the Record.

I'm now using my Unique_ID, and their codes comes through a lookup from a relationship to the Unique_ID, either when invoicing a customer or placing a replenishment order.

But this was problematical only to find out the products for a Quote.

Because, if you are invoiced by your supplier, this invoice is usually based upon some orders you've made.

Therefore, even if the Item_Code doesn't exists in your Product db, you should be able to filter the Line Item by Orders to retrieve the correct code.

I think you already know this, but having an indexed status field in the Line Item, showing if this particular line was invoiced or not is essential.

Therefore, filtering the Line Item by a concanation Supplier_ID & "-" & Status Field at right side and another "global concanation" for the left side gSupplier_ID & "-" & g_Status (where g_Status is populated with your "Not Invoiced Yet" value) should give you a list of all items not invoiced yet.

This could even be filtered more if you're invoiced upon each orders as a single relationship g_Orde#::Order# would be necessary to get this list.

These items could be listed in a value list or, better in a portal.

eg in find screen set global field as 4557 and then when performing the find setting the field supplier ref as *4557* at least then I would have a similar value.

Yes, but following your last example, how would you know that you may not have to search for *3455* instead of *4557* ?

[cjaeger]

i do not know how many suppliers you have, and how many orders you process per day. it it's a relatively small number per supplier, try another approach:

make a portal that lists only line items from open purchase orders for this particular supplier, sorted by date, so you can quickly select them by simply clicking the right portal row. The items status will then be set to received, so it disappears from list.

this implies that you know the supplier id beforehand, but that should not be a problem ...

[Mark Appleby]

Just so that you understand. We would not always issue a computer generated order. We are a jewellery company that works on many unique items. The problem is the buyer goes into a meeting with the Artisan. They may make an order of items that we have used the styles before, but very often we will create a new style while in the meeting. It will then be up to the artisan to create a reference to this item, for his records. Also so that if something similar is ordered in the future the artisan will have a better idea of exactly what is wanted.

I should also explain how we enter the invoices. I have a Delivery note file. This can be used for Invoices or delivery notes depending when they arrive. On entering the Invoice header, the administrator goes to the style file and finds the delivered line items. If this is a repeatable stock line more stock will be delivered in a movements file for each line, or if it is a unique item we can deliver a new item.

While we have a order file with order lines this is mainly for repeatable stock lines rather than unique lines. So that if we are ordering something in quatity it can be easily handled here.

Mark

[Mark Appleby]

We have about 100 suppliers with aprox 12,000 stocklines this is the number of styles, not the number of items there would be aprox 50,000 items within the next couple of years. So there are plenty of stock codes. While we do not necessarily get a lot of deliveries from each supplier some deliveries could have 200 lines. Each line would have a lot of information to be entered at the begining, including photography. The entry of stock is say the administrators the easiest they have come accross in this business. But it would still take 1 to 2 days to deliver 200 new lines. Some suppliers are delivering every day. With these they know all the reference numbers that they have used before with only a few exceptions.

[Mark Appleby]

Christian,

Have you seen my system? you have hit on exactly the way I deliver items that are on back order!

[Ugo DI LUCA]

I see Christian had some good ideas

Computer against Handmade Orders...Hmm...

No clue for this apart from changing certain job procedures between the Artisan and the Buyer. If he don't care about computers, then he doesn't cae about Ids !

Until you fix the Unique Id yourself, you'll never fix the problem

[Ugo DI LUCA]

Mark,

How you set it is just the way we quite all do it. Once delivered, it's not active in line item anymore...

Because, if you are invoiced by your supplier, this invoice is usually based upon some orders you've made.

Therefore, even if the Item_Code doesn't exists in your Product db, you should be able to filter the Line Item by Orders to retrieve the correct code.

About the uniqueness....

A product is usially part of a collection, or as you said a style. May be you should consider to filter the list and define with your artisan some serials according to styles "names".

It would be easier to filter the product file for some other quite similar products, if they were somehow classified by "collection/style".

May be also its component. When you deal with this number of item, it is highly impossible to know the codes by heart.

You need some keys to scrowl down the list to the minimu requested items according to search keys.

Example : Manufacturer - Collection - Size - Format - Component - Category - Description - ID !!

That's how I filter the Product db.

[CobaltSky]

Hello Mark,

A technique I sometimes use for the kind of situation you've outlined is one which locates all values that have any of the same characters as the search string that the user enters, then ranks them in order of similarity and displays the resulting list in a portal.

The technique is illustrated in a demo file which is available for download from:

http://www.nightwing.com.au/FileMaker/demos/FindSimilar.zip

The demo is set up to deal with single-word key values (ie of a length no greater than 20 characters), and the ranking for similarity combines common character and common string (sequence) weightings to provide an approximate measure of similarity. The formulae can be extended or adjusted to reflect the requirements of other implementations, but the principle of the technique would be broadly suitable for what you are trying to do.

[Lee Smith]

Hi Ray,

The link isn't working. I think you're missing ghe demos (page #).

Lee

[CobaltSky]

Hello Lee,

The issue was that I'd put an .fp5 extension on the url instead of a '.zip'. Must be getting late!

I've corrected it now, so if you'd care to try a 'refresh page' it should be a different story.

Thankyou for pointing out the problem.

[Lee Smith]

Hi Ray,

That worked. Thanks for sharing. I like the file, I'll have to investigate it further after my company leaves.

Lee

[Mark Appleby]

Hi Ray,

That file is exactly what I think that I need. It is a very sophinsticated calculation. It is going to take me a little time to wotk it out. I think that I might find myself using this technique on a on a regular basis.

Thanks for the input from all.

Mark

[cjaeger]

Hello Cobalt Sky,

this file is similar to a solution I did for "automatic" classification of product data, only based on words in product description, not characters. There is a slight flaw in your ranking, as an exact match will not rank first.

You should include an if(Exact(g_searchstring_text,SimilarData::DataField);100; .....) at the beginning of the calculation...

btw, this plugin might help when finished:

http://www.suitesync.com/software/fmpro.html

[CobaltSky]

Hi Christian,

Thanks. You are right, with short search strings an exact match is not always ranked first.

The formula was originally devised for ID strings which were all the same length and for that purpose it did rank exact matches first. But it is a different story with multiple length strings, so the weightings needed to be 'tweaked'.

To correct that, I've added:

((DataField = gSearchString_txt) * 80)

at the start, which does the trick!

Cheers,

[Lee Smith]

Hi Ray,

I think I did this right, as it seems to work still

In the field icRelevance, I pasted at the very start of the calculation followed by "+" sign?

TIA

Lee

[CobaltSky]

Hi Lee,

Yes that was correct. However I subsequently noted a minor issue with the operation of repetitive strings and PatternCount, and have made a few more adjustments to compensate for that.

I've updated the online copy, if you'd like to download it afresh.

The weighting calc could be tuned further for specific circumstances, but the present version should suffice as a 'demo' of the technique...