Substitute problem

[John Wenmeekers]

I have a problem with the Substitute() logic.

I have 3 fields:
previousMonth
month
nextMonth

The month field is a text field with a value list with a 3 letter abbrev of the
months (Jan, Feb, Mar, ..... etc)

Out of that field I calc the month number with a custom function and do ( -1)
for the previous month(number) and (+ 1) for the next month(number)

To have the previous and next month Name, I substitute the number:

Substitute ( nextMonth_cn;
[1; "Jan"];
[2; "Feb"];
[2; "Mar"];
[4; "Apr"];
[5; "May"];
[6; "Jun"];
[7; "Jul"];
[8; "Aug"];
[9; "Sep"];
[10; "Oct"];
[11; "Nov"];
[12; "Dec"]
)

The results are fine, except when I reach the month October.
The previous month returns as Sep, but the next month returns as JanJan.

November (Nov) gives Jan0 as previous and JanFeb as next month.

It is obvious that the substitute() takes the numbers 10, 11 and 12 as
separate digits.

I'm looking for a logical explanation of this substitute behavior.
We're using FM 8.5A on Win2k.

TIA

[Tim Mansour]

2009/10/13 John Wenmeekers <[hidden email]>:

> It is obvious that the substitute() takes the numbers 10, 11 and 12 as
> separate digits.
>
> I'm looking for a logical explanation of this substitute behavior.

It's simply because the Substitute command performs its replacements
in order, from first to last. Hence, any "1" is being substituted for
"Jan": including the "1" that is part of "10" or part of "11".

Try changing the order of substitutions so that the double-digit ones
are done first.


--
Tim Mansour <[hidden email]>
Neologica Print & Promotions ABN 63 904 335 408
Certified FileMaker 10 Developer

"Reminds me of my safari in Africa. Somebody forgot the corkscrew and
for several days we had to live on nothing but food and water." --
W.C. Fields

[Stan Millar-2]

John

I haven't tried this but try using double digits for all the months -  
01 for Jan, etc. I have done this for many years and it helps with  
sorting also.

Regards

Stan
Stan Millar
Director
Bromac Business Services Pty Ltd
FileMaker Database Development
Member of the FileMaker Technical Network
http://www.bromac.com.au/
Ph [phone]
Fax [phone]




On 13/10/2009, at 8:08 AM, John Wenmeekers wrote:

> I have a problem with the Substitute() logic.
>
> I have 3 fields:
> previousMonth
> month
> nextMonth
>
> The month field is a text field with a value list with a 3 letter  
> abbrev of the
> months (Jan, Feb, Mar, ..... etc)
>
> Out of that field I calc the month number with a custom function and  
> do ( -1)
> for the previous month(number) and (+ 1) for the next month(number)
>
> To have the previous and next month Name, I substitute the number:
>
> Substitute ( nextMonth_cn;
> [1; "Jan"];
> [2; "Feb"];
> [2; "Mar"];
> [4; "Apr"];
> [5; "May"];
> [6; "Jun"];
> [7; "Jul"];
> [8; "Aug"];
> [9; "Sep"];
> [10; "Oct"];
> [11; "Nov"];
> [12; "Dec"]
> )
>
> The results are fine, except when I reach the month October.
> The previous month returns as Sep, but the next month returns as  
> JanJan.
>
> November (Nov) gives Jan0 as previous and JanFeb as next month.
>
> It is obvious that the substitute() takes the numbers 10, 11 and 12 as
> separate digits.
>
> I'm looking for a logical explanation of this substitute behavior.
> We're using FM 8.5A on Win2k.
>
> TIA

[John Wenmeekers]

@ Stan: I tried that already, nope.

@Tim: bingo ¡¡

That's it.

Silly that during all those years I nearly never used the Substitute().

Seems to require somewhat the same way of thinking as the order in the Case
().

Thanks to both.
Never too old to learn something new.

JW