10 Replies Latest reply on Feb 22, 2012 4:20 PM by disabled_JustinClose

# Custom Function not being accepted....

### Title

Custom Function not being accepted....

### Post

This seems really simple, but I am obviously not finding the problem.

I get an error when i try to 'OK' this custom function:

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

Let  (   [ x = getasdate (aDate)  ];

Case(
x = ""; x = Month ( Get ( CurrentDate ) );
x = Month (x)
)

Case (
x <= 3 ; "Q1" ;
x <= 6 ; "Q2" ;
x <= 9 ; "Q3" ;
x <= 12 ; "Q4"
)
)

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

See screenshot.  Says it expects an operator.  I probably have some simple syntax error that I am not catching.  (I am trying to return a string of the quarter that is passed in as a parameter; if there is no quarter, then it uses the current date to find the quarter.)

I have tried many variations without luck.  The logic seems simple enough.

• ###### 1. Re: Custom Function not being accepted....

Your Let function cannot have both case functions like this, only one or the other.

You'd get the same error in any specify calculation dialog.

Try it this way:

Let ( [ x = Getasdate ( adate ) ;
y = case ( Isempty ( x ) ; Month ( Get ( CurrentDate ) ) ;
Month ( x )
)
] ;
Case ( y < 3 ; "Q1" ;
y < 6 ; "Q2" ; //and so forth

• ###### 2. Re: Custom Function not being accepted....

You can also use this simplified expression:

Let ( D = getasdate ( adate ) ;
"Q" & Ceiling ( Month ( D ) / 3 )
)

• ###### 3. Re: Custom Function not being accepted....

Ah!  I knew it was simple.  Makes some sense when you think about it, I suppose.  :)

Thanks Phil.

• ###### 4. Re: Custom Function not being accepted....

OK, bit of a further issue.  Here is my custom function now:

----------------------
Let ( [
x = GetAsDate ( aDate ) ;
D = Case ( IsEmpty ( x ) ; Month ( Get ( CurrentDate ) ) ;  Month ( x )  )
];

"Q" & Ceiling ( Month ( D ) / 3 )
)

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

The only result that I get appears to be "Q1".  I have tried passing in variables, fixed strings, calcs (e.g. get(currentdate) + 90), etc.  Everything returns "Q1".

Thanks,

J

• ###### 5. Re: Custom Function not being accepted....

It doesn't like the "ceiling (month(d)/3)" part.  When I break it out in steps inside my script, and watch the parts, I keep getting a one.

• ###### 6. Re: Custom Function not being accepted....

It works in my test file. Better take a look at the value of aDate and x as you'll get today's date each time x is empty and then you'd get Q1 each time.

• ###### 7. Re: Custom Function not being accepted....

Here's a screen grab of what I have tested.  It isn't just the custom function that isn't working; I have put the code in discrete statements ('Set Variable...') directly in a script and am still getting the odd results.  (The screen grab shows this, not the custom function.)  (Difference in versions, maybe?  I am using FMP11.)

Hmmm...I think I figured it out.  The code supplied above has "Month()" function using just a number (variable "D" in this case).  I think in the code in your post above should be:

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

...

"Q" & Ceiling ( D / 3 )

...

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

D is an integer at that point, not a date, so "Month(D)" was evaluating as "Month(5)" and I am guessing that Month() doesn't know what to do with that.  Trying that change appears to have fixed it for me.

Thanks,

-- J

• ###### 8. Re: Custom Function not being accepted....

D is a date in my calculation.

Month ( D ) returns the number of the month.

However, when you set a variable to date value, it gets stored as text and this breaks the Month function.

in your tests, use

Set variable [\$Result2; value: Ceiling ( Month ( Getasdate ( \$Result1 ) ) /3 )]

• ###### 9. Re: Custom Function not being accepted....

Correction. It's stored as a date. But in your example you are taking the month twice.

So \$Result1 is the month number when it should be the date.

• ###### 10. Re: Custom Function not being accepted....

Yeah, I had put the second chunk of code you provided:

Let ( D = getasdate ( adate ) ;
"Q" & Ceiling ( Month ( D ) / 3 )
)

...and wrote it into the other part so that it would handle the empty case (e.g. parameter was "").  So I was using Month() twice, as you noted.  Well, works now!  :)

Thanks,

-J