Case oddities

[Mark Hardee]

I'm trying to work out a scripting problem that is a typical "can't see the forest for the trees", I believe.

I have a calculation field (ordertype) that looks at another text field (orderid) at the last two characters to determine how ordertype gets populated.

the specific values I am evaluating of orderid are:

L6309-00

L6390-00*

L6390-00**

L6390-00~

The last two numbers can be anything from 00 to 99.

The case statement to find the "tilde" {4 above} works, the statement to find the "**" works, but not the one to find the "0*" sequence..

Case (

( Right ( IDCode ; 2 )= "**" ) ; "M" ;

( Right ( IDCode ; 2 )= "@*" ) ; "C" ;

( Right ( IDCode ; 1 )= "~" ) ; "P" ;

"N"

)

What AM I not seeing? Must the field be numberic to find "any number - i.e "#"?

[mr_vodka]

Case (

( Right ( IDCode ; 2 )= "**" ) ; "M" ;

( Right ( IDCode ; 1 )= "*" ) ; "C" ;

( Right ( IDCode ; 1 )= "~" ) ; "P" ;

"N"

)

I am not sure what you are trying to achieve here. You stated that

"last two numbers can be anything from 00 to 99". You seem to want to do script a find but seems like you are referencing a calculation. Please explain with further details.

[Ender]

Hi Mark,

I think this is a bit confusing. You say you're having trouble with a scripting problem, and allude to finding, but I don't see any script steps listed.

If this is just about having a calc evaluate a result based on your Case statement, maybe you can list the results from each of your inputs.

But I have to ask why there are goofy characters in your ID field (if that's what you have?!).

[comment]

Wild cards work in searches, not in calculations. I THINK (based on the few examples) that this should work:

Case (

Right ( IDCode ; 2 ) = "**" ; "M" ;

Right ( IDCode ; 1 ) = "*" ; "C" ;

Right ( IDCode ; 1 ) = "~" ; "P" ;

"N"

)

EDIT:

Same as John's - except I have troubled to remove the redundant parentheses... ;)

Edited March 20, 2007 by Guest

[kjartank]

I think that

Right( IDCode ; 2)="**"

will find all the fields containing two stars at the end, but

Right( IDCode ; 1)="*"

will find all the fields containing ONE star at the end AND

all the fields containing TWO stars at the end !

Which is probably not what you want.

Maybe you should try :

Case (

Right ( IDCode ; 2 ) = "**" and Length ( IDCode )= 10; "M" ;

Right ( IDCode ; 1 ) = "*" and Length ( IDCode )= 9; "C" ;

Right ( IDCode ; 1 ) = "~" ; "P" ;

"N"

)

[mr_vodka]

Right( IDCode ; 2)="**"

will find all the fields containing two stars at the end, but

Right( IDCode ; 1)="*"

will find all the fields containing ONE star at the end AND

all the fields containing TWO stars at the end !

Hi kjar,

This becomes a moot point because since the case () function evaluates in the order that it tests for, if there are two stars '**' then it has already evaluated as true, returned "M", and stops evaluting the rest. So by the time it is checking for one '*' it has already returned a value.

This would be different if the case statement would have been like this though.

[color:red]WRONG

Case (

Right ( IDCode ; 1 ) = "*" ; "C" ;

Right ( IDCode ; 2 ) = "**" ; "M" ;

Right ( IDCode ; 1 ) = "~" ; "P" ;

"N"

)

[Mark Hardee]

Hi Mark,

I think this is a bit confusing. You say you're having trouble with a scripting problem, and allude to finding, but I don't see any script steps listed.

If this is just about having a calc evaluate a result based on your Case statement, maybe you can list the results from each of your inputs.

But I have to ask why there are goofy characters in your ID field (if that's what you have?!).

Sorry for the delay ... I had pressing issues -

The "goofy" characters are from someone elses data that I get a report from each morning - they signify a difference in a item number - that is the item number as is, is a static pick and pack item - with a single asterick it is a customizable piece and with two it is a mailing item. (these are all Print on Demand pieces)..

I took some of "comments" ideas and have gotten it to work - what I was tring to do was search for any "number" and and asterick and return a "C". but the wildcard obviously was not going to work here..

So, I extended my case statement to look at the last three characters and see if it had:

"0*"

"1*"

"2*"

and so on - that finally got it working - I guess I was just being hard-headed in getting it to work with just three lines in the Case statement.

The scripting part - well, that was probably confusing to real Filemaker folks... not an amateur like me! It was part of a script - so my mention of scripting probably was not a help to any of you - just confusion...

Long story short - I have it working - if I was at work, I'd place the calc here, but, I am ar home now..

Thanks for the help - all of you... I hope I can help in some small way one of these days...

[comment]

I still don't get your rules. If I wanted to make sure that the character before a single asterisk is a NUMERIC character, I'd probably make it:

Case (

Right ( IDCode ; 2 ) = "**" ; "M" ;

Right ( IDCode ; 1 ) = "*" and not IsEmpty ( Filter ( Right ( IDCode ; 2 ) ; "0123456789" ) ) ; "C" ;

Right ( IDCode ; 1 ) = "~" ; "P" ;

"N"

)

)

But judging from what you said ("with a single asterick it is a customizable piece and with two it is a mailing item"), it shouldn't be necessary.