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)

 

 

See if this recent thread helps:

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

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.

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.

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.

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.