Jump to content

Performing a find based only a portion of a field value

This topic is 1424 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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:


And another:

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.


Link to comment
Share on other sites

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 by LaRetta
added clarity
Link to comment
Share on other sites

  • Newbies

@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.

Link to comment
Share on other sites

This topic is 1424 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.