Help with Case Calc

[steigrafx]

I have a script which uses a calculation to make a voucher number.  It works fine for a calendar year, but our fiscal year begins on April 1 and I can't figure out how to modify it.

 

Here's my calculation:

 

Right ( Year ( Get ( CurrentDate ) ) ; 2 ) & "KS" & Right ( "000" & ( $Last_Voucher + 1 ) ; 3 )

 

I would appreciate your help.

 

Thanks,

Kevin

[comment]

This is a bit out of context, but try =

Let ( [
today = Get ( CurrentDate ) ;
y = Year ( today ) - ( Month ( today ) < 4 )
] ;
SerialIncrement ( Right ( y ; 2 ) & "KS000" ;  $Last_Voucher + 1  )
)

[steigrafx]

Thanks, Comment.  I'll give it a try tomorrow morning when I'm back in my office.

 

As always, I appreciate your input.

 

Kevin

[steigrafx]

I have a case calculation in a script to determine whether the current date is within January, February or March, in which case I need the prior year's two-digit number returned.  Otherwise, from April through December, I need the current year's two-digit number returned.  This calculation returns the correct year (I changed my system date to test it):

 

Case (
Month ( Get ( CurrentDate ) ) < 4 ; Right ( Year ( Get ( CurrentDate ) ) ; 2 )  -1 ;
Month ( Get ( CurrentDate ) ) > 3 ; Right ( Year ( Get ( CurrentDate ) ) ; 2 )
)

 

But, I need to use this to create a "Voucher Number" which follows the two-digit year, so I appended to the calculation:

 

Case (
Month ( Get ( CurrentDate ) < 4 ) ; Right ( Year ( Get ( CurrentDate ) ) ; 2 ) -1 & "KS" & Right ( "000" & ( $Last_Voucher + 1 ) ; 3 ) ;
Month ( Get ( CurrentDate ) > 3 ) ; Right ( Year ( Get ( CurrentDate ) ) ; 2 ) & "KS" & Right ( "000" & ( $Last_Voucher + 1 ) ; 3 )
)

 

Yet it doesn't work.  It always returns the prior year's two-digit number even when I change my system date to a month greater than March, such as August.

 

I don't understand why the first calculation works correctly but the second doesn't.  I would think since the case function itself is identical, that it should work in both examples.  Obviously I'm doing something wrong, but I can't figure it out.

 

I'd appreciate your input.  Thanks.

[Rick Whitelaw]

I'm not at a computer now, but I'm guessing the problem is enclosing an expression in the Right() function at the end of each of your cases. I'll check.

Not positive, but the Help on the Right() function specifies the parameter as being text;number of characters where text is either text or a text expression.

[eos]

You can vastly simplify this by using Let() and factoring out the things you need unconditionally:

Let (
cd = Get ( CurrentDate )
;
Right ( Year ( cd ) ; 2 ) - Case ( Month ( cd ) < 4 ; 1 ) & "KS" & Right ( "000" & ( $Last_Voucher + 1 ) ; 3 )
)

Yet it doesn't work.  It always returns the prior year's two-digit number even when I change my system date to a month greater than March, such as August.

 

Since you have Advanced, you can (and should) use the Data Viewer, where you can replace Get ( CurrentDate ) with a date of your choice (and, using Let(), declare a sample value for $Last_Voucher).

[comment]

How is this different from your other question:

http://fmforums.com/forum/topic/91325-setting-a-variable-from-part-of-field-contents

[Lee Smith]

Please do not double post, you will get caught

 

I merged the two topics.

[steigrafx]

Thanks, Comment.  Your calc works except for one small glitch.  The result I need would be 13KS063, whereas your calc returns 13KS13063.  There's an extra "13" in there.  How can i get rid of that?

 

I was also wondering why my Case calculation failed.  It looked straightforward to me, but I'm certainly no Filemaker expert!

 

Thanks again.

[eos]

Thanks, Comment.  

 

Sure, just ignore my input, caused by your double-post! 

 

I was also wondering why my Case calculation failed.  It looked straightforward to me, but I'm certainly no Filemaker expert!

 

This

Month ( Get ( CurrentDate ) < 4 )

is wrong; you had it correct in your first version:

Month ( Get ( CurrentDate ) ) < 4

[steigrafx]

Sorry, EOS.  I didn't mean to ignore you.  I appreciate your help.  It's funny how you look at something over and over and still don't see the mistake.

 

You're absolutely right.  I made the correction and the case calc works as it should.  And sorry for the double-post -- please don't string me up for it!

 

Thanks again, and have a great day.

[eos]

Don't sweat it, I was just jerking your chain. Good to hear you got it working.

 

And sorry for the double-post -- please don't string me up for it!

 

I won't – the dirty work around here is done by Lee, so watch out for him! 

[LaRetta]

Hi Kevin,

 

It sure makes for a confusing thread when its been double-posted and then merged.  Truly, in your original post, if a calculation produces wrong result, responding on that thread is the best.  It would have saved quite a bit of work for everyone, no?  

[steigrafx]

Yes, of course you're right.  It won't happen again.

 

And please, Lee, have mercy on me!

[Lee Smith]

You have already beat yourself up more than anything I can do or say.

[comment]

Just in case you haven't had enough beating for one day:

 

Your calc works except for one small glitch.  The result I need would be 13KS063, whereas your calc returns 13KS13063.  There's an extra "13" in there. 

 

The only way this can happen is if your $Last_Voucher variable contains a value of "13062" or some other value that returns "13062" when converted to a number, for example "13KS062".

 

That's what I meant when I said "this is a bit out of context": you didn't explain how this process starts and how it ends. We all assumed that $Last_Voucher contained a serial number. Otherwise why are you pushing ""000" in front of it before taking the last 3 characters of the result? That makes no sense, since:

Right ( "13KS062" ; 3 )

is "062" and so is:

Right ( "000" & " 13KS062" ; 3 )

[steigrafx]

Of course you are right, comment.  My variable was to blame.

 

Thanks,

Kevin