Performing a find based only a portion of a field value

[feakes 0]

Hello, still fairly new to FM and am sure there's a simple solution to this...

I have a table of records that are organised into group by their id prefix

One group of record ids may be for example:

SV1001
SV1002
SV1003

And another:

PT1001
PT1002 etc...

When I'm looking at a single record, I'd like to then provide a button that will perform a find that returns all records that contain that prefix. E.g. if I'm viewing record SV1001, I'd like to then be able to return all records with the SV prefix.

I hope this was clear. Happy to provide further explanation.

Thanks in advanced.

[Ocean West 172]

If the Field is a Number you won't be able to search on the prefix. If the field is text you could perform a find based on the prefix.

In a script set a variable to Left ( table::id ; 2 ) assuming the prefix is only two characters. Then enter find mode set the ID field the value of the variable & perform find.

[comment 1,775]

A side note:

If the prefix has a significance of its own - and your question indicates it does - you might benefit from splitting it off to a field of its own. This is known colloquially as the "one fact per field" rule. Among other advantages it would allow you to simply find matching records to isolate a found set having the same prefix.

 

[LaRetta 512]

If the prefix might be a variety of character lengths, you can use this to pull only the text values:

Filter ( PrimaryKey ; KanjiNumeral ( PrimaryKey ) )

gleaned from Daniele Raybaudi years ago.  I suspect now would be a good time, as Comment suggests, to split the prefix aside or at least use a new indexed calculation for all future requests such as sub-summarizing etc.

This would only work if there are no other text characters in the serial or at the end (which I would guess is true in this instance).

Edited August 28, 2020 by LaRetta
added clarity

[feakes 0]

@Ocean West Thank you. the 'Left' command was the exact thing I needed. Knew it would be a simple solution but it was escaping me.

Thanks all for the comments. I have split the prefix into a separate field and am calling on that to sort the records. This seems to work exactly as needed for now.