Can't sort by calculated text fields? So...

[hillborne]

I've learned that you cannot sort reports by calculated text fields so I need to come up with some workaround. Sadly, I've been using FMP for about 1 month now and am not sure the simplest way to go about this.

I have 3 calculating fields that check for weekly stock conditions and either display a big red "YES" or a plain "No" depending on the outcome of the IF condition. Each IF also checks a flag field to see if the warnings should even be displayed. I need to be able to sort on these conditions in a report.

I'm embarrased to say that I got it to work by having the IF result in "YE5" (number 5 instead of S) or "N0" (number 0 instead of O) but such trickery seems too risky and unprofessional. I'd rather not resort to using numbers in the fields to enable a sort.

And can anyone confirm that there's no way for the results of a calculation of one field to set the field of another? That is strictly the domain of scripts, right?

It seems that maybe I have to run a script that loops through the records and sets 3 other fields to Yes or No and then sort on those since they'll be plain text? ug...

Edited March 17, 2006 by Guest

[bruceR]

I've learned that you cannot sort reports by calculated text fields...

May as well stop right there. Tain't true; what leads you to this conclusion?

[hillborne]

Hi Bruce,

Well, I tried sorting by any of the 3 calculated text fields and they just sort randomly. Below are the three calc fields so maybe I'm doing something wrong. It didn't matter if the result was a number or text, still couldn't sort by them.

Out_of_Stock_calc:

If(

// If there's none on hand but there have been some sales this year

Column_D_OnHand = 0 and Column_F_YTDSls ≥ 5

and

// And user has not already turned off warning flag

Stock_Status_Reviewed_Flag ≠ "Yes";

// If out, make bold, red

(TextStyleAdd(

(TextColor ("YES" ; RGB (255; 0; 0)));

Bold))

;

"NO")

----------------

Low_Stock_Calc:

If (

// Confirms product is not dead or brand new or sold out and that...

Column_G_WkSls ≥ 1 and Column_F_YTDSls ≥ 1

and

// Stock is low...

Days_Until_Zero_Stock < Lead_Time

and

// And user has not already turned off warning flag

Stock_Status_Reviewed_Flag ≠ "Yes";

// If low make bold, red

(TextStyleAdd(

(TextColor ("YES" ; RGB (255; 0; 0)));

Bold))

;

"NO")

---------------

Low_Stock_No_On_Order_Calc:

If (

// Checks if the stock is low and there is nothing on order...

Low_Stock_Calc = "YES" and Column_E_OnOrder = 0

and

// And user has not already turned off warning flag

Stock_Status_Reviewed_Flag ≠ "Yes";

// If yes, make bold, red

(TextStyleAdd(

(TextColor ("YES" ; RGB (255; 0; 0)));

Bold))

;

"NO")