There are a number of text functions for working with text in FileMaker. The first catch that I see is that you will need to distinguish, for example between
ADMIN 01-13 and ADMIN 02-01
Try this expression to extract the number to the left of the hyphen:
Let ( t = YourTable::YourField ;
GetasNumber ( Left ( t ; Position ( t ; "-" ; 1 ;1 ) ) )
There is no simple expression that uses asterisks... if text contains "*01*" type of filtering?
Only because the database already filters out previous years records....so I really need to find the "01" in the field.
No such built in text function works with wild card symbols, but the expression I suggested will work regardless of whether oro not you are already filtering out records from previous years.
Ok..but what do i do to the expression for February? "ADMIN 02-13" is the data in the ID field.
If I plug the expression in..they are all now red on each month.
Let ( t = aPROJECTS::PROJ ID ;
GetAsNumber ( Left ( t ; Position ( t ; "-" ; 1 ;1 ) ) )
I guess I need to understand how it's working.
The expression extracts the number for the month.
if the value in aPROJECTS::PROJ ID is "Admin 02-13", the expression returns 2, the month number.
If the value in this field is "Admin 01-13", the expression returns 1
Position returns the position of the hyphen.
Left returns all text to the left of the hyphen as well as that hyphen
getAsNumber then filters out all the nonnumeric characters and evaluates the data as a number to produce the month number.
You wanted to use a conditional format expression that checked for the month value in this field did you not?
On a simlar yet other function....I modified a script so that when I generate a new record it modifies the record ID to be a similar format as above.
I have Set Field - in the calculation - "ADMIN 03" & "-" & Right ( Year ( aPROJECTS::CURRENT YEAR ) ; 2 )
So far it works..but returns this
What is the value in aPROJECTS::CURRENT YEAR when you get that result?
Hope you aren't using these fields as match fields in relationships---that's asking for trouble...