Jump to content
Sign in to follow this  
Bekah

Inconsistent Custom Function

Recommended Posts

Okay. I want to wrap a fiscal custom function around any date field and it tell me what fiscal it falls within. should display just the year. Our start is 10/1. so I wrote this:

Name: fiscal

Parameter: datefield

Let ([D=datefield];

If(Month(D) > 9; Year(D); Year(D) - 1))

then I add calculation in different tables with simple fiscal ( AnyDateField )

It doesn't always work. Sometimes date is 10/1/2003 and it says its 2002. Wrong. And at other times it doesn't work right but I can't pin it down. I made sure the date fields are all dates too. Does anyone know what I might have wrong or is this custom function itself the problem? Does the calc need to be stored and must it be number or text; or can't it be used with related fields in the calc?; or what matters here to be sure it works right. confused.gif

Share this post


Link to post
Share on other sites

I made the calculated result number, unstored. I cannot duplicate your problem. If I put in 10/1/2003, I get 2003. Can you give us any other information?

Share this post


Link to post
Share on other sites

If you want to trim your calculation,

Year(datefield) - (Month(datefield) < 10)

The Let doesn't add much here because it doesn't refer to a calculation or calculated field. So datefield is just as efficient as D.

Share this post


Link to post
Share on other sites

I think my file was corrupting. I imported into fresh copy and evertyhing seems fine. It took 2 hours to import the data but Im glad it happened or I never would found out about this new calculations.

I tried your calculation Queue and it worked perfectly. It took a minute to figure out. You wrapped () around the month < 10 part to make a boolean, right? which if true gives a 1 and if false gives a 0. So FM doesn't need to evaluate it - it becomes straight math.

So

9/30/2004 would be: 2004 less true(1). Or 2004 minus 1 = 2003

10/1/2004 would be: 2004 less false(0). Or 2004 minus 0 = 2004

Is this the thinking here?

Regarding Let. yeah. well, im just learning Let and I was anxious to use it. I'll drop it for this but I'm excited to find something else I can use it with since I finly sorta understand it. smile.gif

One final question maybe? Can Custom Functions be used in both stored and unstored calculations. I don't quite understand what they are. Are they like Get functions? Can they be used in keys? And does it matter if fiscal is number or text if used in a key? Thank you! Thank you for helping me. grin.gif

Share this post


Link to post
Share on other sites

Yes, it is a boolean test.

I believe CFs can be used in unstored calculations. You would want to be careful that a function isn't too processor intensive, though, since it will be re-evaluating after exiting every field. They can be used as keys, but it might not be a good idea to make both keys CFs. It doesn't matter if fiscal is number or text since there are no leading zeroes to account for. If it is a key, you would just need to make sure that any related keys are of the same type.

They are not like Get functions, although you can use Get functions within them. They are functions that you create, which don't already exist in FileMaker, and can greatly enhance your UI and behind-the-scenes calculations. Their most powerful aspect is that they can be recursive. Instead of writing a repetitive calculation such as Left( field; 1 ) &

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.