Value List breaks down Single Field Single Record

[rgold52]

I have been using a single record file to generate value lists as well as many other uses. I have never had a problem, however I was given an example where this practice breaks down. I am hoping someone knows why!

Just paste the following list into a field and create a valuelist and you will experience this anomaly:

Allow Occupancy Lock Ins

Checks

Checks - Advanced

Checks - Check Printing Options

Checks - Review Payments

Checks - Select Checks

Direct Deposit

Export or Delete Current Check Data

Export or Delete Current Check Data - Export Check Data, Delete Printed Checks and Adjustments

Export or Delete Current Check Data - Export Check Data, Export to 1099s

Export or Delete Current Check Data - Export Check Data, Export to Standard Interchange Format

Find

Help

Maintenance

Manual Payments and Adjustments

Manual Payments and Adjustments - Add Manual Payment

Manual Payments and Adjustments - Review Manual Payments or Adjustments

Prohibit Occupancy Lock Ins

Reconcile

Reports

Reports - Check Summary

Reports - Check Summary, Detailed

Reports - Port In Details

Reports - Port Out Details

Reports - Record of Payments

Reports - Unit Counts

Update Payment Data

I would appreciate any insight to this phenomenon.

Thanks,

Robert Gold

[Vaughan]

It's not an anomoly -- it's all about how FMP indexes field contents.

http://www.filemaker.com/ti/104408.html says

"[what gets indexed in a text field:] The strings as entered except for punctuation and spaces."

http://www.filemaker.com/ti/103463.html says

"Only the first 20 characters [of a single word] are indexed... Spaces and special characters (like the comma, period, number sign, and dollar sign) identify word breaks and are not included in the index. Exceptions: the ampersand (&), apostrophe('), hyphen (-) and slash (/) are included as part of an indexed word when they are embedded between two standard alphanumeric characters.

There is another reference that specifies the technical limitations of FMP indexing but I cannot find it.

I my experience, the best value lists are those where each and every item is unique. Achieving this often requires a lot of negotiation with the clients.

[Vaughan]

I found more references, though some are old.

http://www.filemaker.com/ti/102010.html says

"FileMaker does not index text strings (words) past the twentieth character, so it will not sort words longer than that correctly."

http://www.filemaker.com/ti/101496.html says

"In FileMaker Pro 3.0 and later the language for the index can be selected under Field Definition - Options - Storage Options. Selecting ASCII will index all characters except special characters used in the Find operation. For example =, ==, !, ?, @, and * are all special symbols used for special search functionality and are not part of the index."

http://www.filemaker.com/ti/101325.html says

"... only WORDS are indexed (and the / and - characters). Other punctuation marks and other non-alphabetic characters are not indexed (for example , . ;

[KirstenM]

I have had occasion to use long entries in value lists also, when creating a value list from a field. By trial and error, it appears that Filemaker will only recognize entries if they differ in the first 64 characters. If you have two or more values in the list that are longer than 64 characters, and are different only after character 64, the values will be treated as identical, and only the first instance will show up in the value list based on that field.

For example:

Source field:

This is an incredibly amazingly long value entry in a value list - 1

This is an incredibly amazingly long value entry in a value list - 2

This is an incredibly amazingly long value entry in a value list - 3

Will show a value list with the entry:

This is an incredibly amazingly long value entry in a value list - 1

I have yet to find a way around this; it has been occurring as of FileMaker Pro 3.

Kirsten M

[-Queue-]

Actually, it's 60 characters (including spaces) for a field. It's 20 characters per word, btw.

[KirstenM]

Well, isn't that interesting.

I have a value list with a four digit unique ID key, with a show-also field that has the long text entries. I had expected the four digit field to be included in the character count, but apparently, it is not!

Thanks for the clarification.

Kirsten M.

[Vaughan]

They are generated from separate indexes.

BTW there is an bug in FMP 5.0 and 5.5 Windows... see TechInfo article 107398 for more details.

"Scrolling through a value list containing values from 2 fields where one of the values in the second field is longer than 255 characters can cause FileMaker Pro 5.0 and FileMaker Pro 5.5 for Windows 98, Windows NT 4, or Windows 2000 to crash."

http://www.filemaker.com/ti/107398.html