# 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.

##### 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 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 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.

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.

##### 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 on other sites

Remember this now:

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

• ### Who Viewed the Topic

×
×
• Create New...