Value List Based on a Search

[opencirrus]

I am happy with being able to create a dynamic list from relationships.

 

What I trying to solve a away to be able to search through (or filter) a Value List of SerialNumbers from a user input of text. Both the search input of text and the calculated value list (calculated SerialNumber field) have a variable number of characters.

A user would therefore enter a text string into a field (which can be a related global storage field), then I need the value list to filter based on that string. I can get this working from a exact match (ab=ab etc), but need to know if there is a way of creating a value list based on an index of SerialNumbers?

 

To simply this to explain another way, if I have items:

 

a, ab, abc, e

 

The user would type 'a' into a field and the value list would return all items with an a (a, ab, abc)

 

 

[comment]

See if this recent thread helps:

http://fmforums.com/forum/topic/92727-related-searches-global-fields-partial-matches/

[opencirrus]

Thanks comment, I may need that later. In my case I can get what I need if write my search string to a global storage field and use that as the match to a related value list (calc) with a fixed character length in the source of the value list. What I need to figure out now is how to dynamically set the character length in the value list to match with my variable global storage field. I cannot use length(field), as this is related to a global storage field which means the value list will not work (no indexing for related or global storage).

 

To summarise:

 

If my search string is MTR-S1

 

I need my value list to match all serial numbers that start with MTR-S1 (in this case create a value list that matches the first 6 characters of the string).

 

I may to re-think this, as it seems I'm asking the table to index a value list based on a dynamic variable - which cannot work.

 

Or maybe I'm missing something obvious here, so i'll keep searching through the forums for some inspiration.

[comment]

I am afraid I don't understand your description that well.  A value list does not match anything. A value list shows the values in a field - either the values from all records, or the values from related records only. So this is really a question of which records are related.

 

If you want the relationship to include all records where the serial number starts with "MTR-S1", then you must either have a stored field that contains the first 6 characters of the serial number or use a comparative operator in your relationship. This is exactly what the other thread is about.

[opencirrus]

Thanks comment - the problem is that the character length (e.g.6) will vary. I may wish to only include records where the first  6 or X numbers of characters match. I don't know the number until the user types in a search string  - then I want the value list to return only records based on that search string. I'll study your other thread some more.

 

The field is stored of course, but I need to filter the value list based on a search string that the user will type in (there are 1000's of records), and not how do to do this.

[comment]

the problem is that the character length (e.g.6) will vary.

 

Yes, well, then you need to have all possible variations of Left ( SerialNumber ; x ) ready and stored - that's what the Explode() custom function does. Or use a < relational operator. Really, we are repeating the other thread here almost verbatim.