7 Replies Latest reply on Feb 6, 2013 3:15 PM by philmodjunk

# Conditional Fomatting - Using Calculation Functions

### Title

Conditional Fomatting - Using Calculation Functions

### Post

I'm having difficulty understanding how to use the Calculation areas for Text. Especially, finding "partial text" in a field. Can anyone recommend a good place that clearly explains with examples how to use them?

For instance, I have a Unique ID field that has the text in it "ADMIN 01-13" which means its a record specifically for the month of January 2013.

I'm looking for a way to conditional  change a Text button to RED if the current record is selected here. I already have a conditional that makes it red if the current date equals the current month's record:

MonthName ( aPROJECTS::TODAYS DATE ) = "January"

Which works great!

But would like to have a conditional above that that basically says...if the unique ID has the text "01" in it..then make it red.

I just find it difficult to figure out which of the calculation functions would be the one to choose to use....It's just not clear to know which one to start with on finding a partial text within a field.

• ###### 1. Re: Conditional Fomatting - Using Calculation Functions

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

Try this expression to extract the number to the left of the hyphen:

Let ( t =  YourTable::YourField ;
GetasNumber ( Left ( t ; Position ( t ; "-" ; 1 ;1 ) ) )
)

• ###### 2. Re: Conditional Fomatting - Using Calculation Functions

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.

• ###### 3. Re: Conditional Fomatting - Using Calculation Functions

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.

• ###### 4. Re: Conditional Fomatting - Using Calculation Functions

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.

• ###### 5. Re: Conditional Fomatting - Using Calculation Functions

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?

• ###### 6. Re: Conditional Fomatting - Using Calculation Functions

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