Number to Alpha

[LaRetta]

I need some ideas from the Forum ...

1) When customer name is 1st Choice, it needs to appear in portal when F is selected so I need to convert number to alpha (we have hundreds of customers that begin with number) and 2) If Customer name begins with certain words, they need to be ignored and the second word (first letter) entered (considered?) instead. Users and Management have decided to IGNORE numbers and enter their alpha equiv instead, so 11th Street would be E. And they want to ignore "The" etc also.

Since I do not see how a calculation alone can cover all possible conversion/exceptions, I plan to use another table to store the 'Rules.' These Rules must be available in Browse so they can be expanded as needed. I then planed to use a Convert text field (in Customers) with Auto-Enter calculation. I've attached a small sample of data (showing desired result in Convert). Convert is used as a final User filter in a complex match for filling a portal. I originally set the fields as global but then changed it to a record (multiline) but I wonder if records would be better. The Auto-Enter text would need to do the following:

1) Find first word in Customer name that is NOT in Rules::Ignore.

2) If Left(Name;3) are numbers, match Left(name; 1) to Rules::Exception. (We only have one store which begins with 3 numbers (101 Delights). I believe it the number is 3-digit (hundreds) they should match on 1,2,3 etc. So if 3 numbers, only consider first.

3) If Left(Name; 2) is number and match in Rules::Exception (Left 2), display Rules::Exception letter

4) If Left(Name; 1) and match in Rules::Exception, use that.

5) If not number, display first letter of Customer.

Have others faced a similar need? Would you suggest records or fields or global? This is multi-user. I've attached my sample data (and how the Convert field needs to end up) but, as you can see, I'm stuck on the best way to proceed. Tweaking a rule after the fact (in another table) won't refresh either. Ideas appreciated!

LaRetta

ConvertNum.zip

[Vaughan]

A custom function might do the trick better than storing the rules in a table. Not too hard to modify either. Alternatively, put the calculation in a text field in the prefs file and use the Evaluate() function to make it work in the auto-enter calculation. That would be very easy to modify.

Hmmmm.... would "101 Delights" parse out as

one zero one delights

one hundred and one delights

hundred and one delights

Getting rig of leading "The..." and "A..." words shouldn't be too hard, and the list of possibilities is finite.

However, my preferred method for solving intractible problems is to redefine them. : Most systems I have seen (albeit not many) list items starting with numbers under a "symbols" category.

[LaRetta]

Hi Vaughan

Good points. Management voted then threw it to staff for vote. 'O' or ONE hundred would be their answer - it translates just as spoken. But this is why I would prefer it be handled in Browse. I believe there will be much debate and change over this requirement; and I don't want them coming to me to change it. My thought was that a good calculation or Custom Function (in combination with Rules) can handle it. I just don't want to be adjusting it for those Ignore phrases. It is my job to provide a solution if possible. It should be a simple process to implement if I can determine the proper logic of attack.

The problem is that when pulling a list, they don't know how all names are spelled. If they want First Avenue, do they click a 1 or an F? They don't know if they have all stores beginning with F. I have advanced filtering (permutations) in other areas of the program but it is overkill here. The relationship is already filtered by several criteria. The User only refines the display to groups; they are assigned alpha groups to work in batches.

LaRetta

Edited November 22, 2005 by Guest

[comment]

So many issues here, one doesn't know where to start.

Basically, you are looking for longest string match:

1 matches 1;

10 matches 1 and 10 - you want 10;

100 matches 1, 10 and 100 - you want 100.

I think about the only way Filemaker can do this is by a relationship sorted by the length of the matched key. You need an exploded key string on the parent side, and on the child side, each "rule" is a record.

Next, you want the result to be stored - so this should be a lookup. A lookup - unlike auto-enter - can be "relookupped", when the rules are changed (although you could force auto-enter to refresh too, by re-entering the string).

If you use a lookup, you must use a custom function to calculate the exploded key. You cannot use a repeating field for this, because a repeating field key looks up each repetition separately.

You also cannot replace the lookup by a calculation using the Lookup() function - since then you will have no Relookup.

Now to the practical side:

In my Contacts table, I have a SortOverride field. So if I have a record of say "First National Bank", I can enter "Bank" into the override field, and the record will be listed under B, grouped with the other banks.

It seems to me the time it would take to fill this field, even MANUALLY, for several hundred contacts is still less than the time needed to develop an elaborate automatic mechanism. Which will then have to be changed. And changed again.

[LaRetta]

Hi Michael

Then so be it ... I will use a calc to pre-fill upon record creation (as best I can). It will then be up to staff to correct further if needed (I'll prime existing customers also). A good calc would eliminate the Exception (converting numbers) entirely and would only leave the Ignore field which is easy to check against (and can be included in the calc as per file). I want the calc to pre-fill 'close to perfect' if not perfect. Make sense? And if I can perfect the calc, I can still do it ALL for them, I hope.

I believe a pattern exists in the numbering. I also admit to becoming obsessed with the number-to-text pattern I'm observing and my desire to give them their request (it was a big TA-DO in the office). I think the numbers are predictable and can extend out. Understanding this pattern will be powerful in future needs (with similar num-to-alpha conversions). Since all we care about is the pronunciation START sound and not the entire number, it is much simpler. Other than a few ONE exceptions and the teens, it's predictable no matter how large the number, I think.

This started out as a work assignment. It has become a desire to pin down that pattern for myself. I'm hoping the conversion parameter (handling the numbers) holds up because that will provide me with a win-win here. I've attached the file with my calc. cSelectWord is only to view the prior chosen word (and 3 letters) before converting it. I admit to one fudge ... assuming there won't be 3 words starting which are on Ignore list. But I can't imagine a store name of "The A For ...".

Update: I see I need to add another exception (geez, is THIS the real pattern here - HA!). I corrected the file also:

num = "2" and Middle ( word ; 2 ; 1) = " " ; "2" ... otherwise 2 for Tea produces "S" instead of "T"

Update of Update: Another small thinking error in the num. If number wasn't first in word, it was counting it anyway instead of the text. The num sub-calc has been corrected. And I change the field name - it was the same as a Let() parameter and it messed me up.

LaRetta

ConvertNumRev.zip

Edited November 22, 2005 by Guest