Optimized Full Name calculated field

[Dan Pouliot]

I just read about optimizing calculations in FMP 9 bible and I thought I'd give it a try. I have a Full Name calculated field, and my boss wants the text of that field to be red if the contact is a current member (we are a private club). Doing this slowed Finds made within that field considerably. I tried to optimize the field, and to my surprise searches slowed even more. Here's the before and after code. What can I do to speed up this field?

Before:

Let(



fullName=TextFormatRemove(

If(Contacts::Last Name  ≠ "";Contacts::Last Name & ",")

&If(Contacts::Salutation  ≠ "";" "& "("&Contacts::Salutation&")")

& " " & Contacts::First Name 

& If(Contacts::middle initial  ≠ "";" "& "("&Contacts::middle initial&")")

& If(Contacts::nickname  ≠ "";" "& "("&Contacts::nickname&")")

)









;



//if valid member, make red

If(

Contacts::Expiration Date>Get(CurrentDate);

TextColor ( fullName ; RGB ( 183 ; 0 ; 41 ) );

fullName

)



)

After

Let(

[

$Salutation=Contacts::Salutation;

$FName=Contacts::First Name;

$LName=Contacts::Last Name;

$MI=Contacts::middle initial;

$NN=Contacts::nickname;

$fullName=

TextFormatRemove(

If($LName  ≠ "";$LName & ",") &

If($Salutation  ≠ "";" "& "("&$Salutation&")")& " " & 

$FName &

If($MI ≠ "";" "& "("&$MI&")") & 

If($NN  ≠ "";" "& "("&$NN&")")

)

];



//if valid member, make red

If(

Contacts::Expiration Date>Get(CurrentDate);

TextColor ( $fullName ; RGB ( 183 ; 0 ; 41 ) );

$fullName

)



)

[Josh Ormond]

I will assume that you are using an unstored calculation. Correct me if I am wrong.

Doing finds on unstored calculations will always be slower than normal text fields (or stored calculations). Unstored calcs are not indexed and FM has to evaluate every record in order to return a found set. The larger the data set, the longer it takes. No matter how efficient the calculation is.

I would recommend using a text field with an Auto-enter calculation. It will be a stored value, making finds faster, but will still update when the First Name or Last Name is changed (just make sure the "Do Not Replace existing value..." option is unchecked.

As for the color, if you are using FM 10, use the conditional formatting to display it as red instead of formating the text in the calculation.

Edited June 23, 2009 by Guest

[Dan Pouliot]

Yes, unstored. Thanks for the tips. I'll try making it into a text field with an auto-enter calc.

Yeah, I'm still on 8.5. some day 10.

[Josh Ormond]

Then you will have to stick with the formating calc. Should still work though.

Unless, you could get your boss to go for an icon/graphic to indicate members. Then you just need to store the graphic in a global container field and use a calculation to result in the graphic if they are a current member.

[David Jondreau]

An auto enter calc is not going to work. Your calc depends on Get(CurrentDate) which must be part of an unstored calc to evaluate on a continual basis.

I see two options both requiring additional fields.

One, you could use one field to search on and another to display data. The search could be a regular old stored calc or an auto-enter calc. The display field would be unstored. The former is enterable in Find mode, the other not at all (or in Browse only).

The other option is to use a background fill of red instead of making the text red. That's easy enough with an unstored calc field with a container result. The result can reference a global container that has a pixel of the fill color you want.

If you don't mind some comments on your script:

-It's unnecessary and bad practice to use $ vars in a Let() statement. Just use plain text instead.

-not IsEmpty(field) is better than field=/="".

- You don't need to concatenate spaces and other characters. " (" works as well as " " & "(".

[Josh Ormond]

Thanks DJ.

I should have clarified that. The things you overlook when spoiled by Conditional Formatting...embarrassing I tell you. :idot: