Jump to content

CASE statement in Calc field not working...

This topic is 6554 days old. Please don't post here. Open a new topic instead.

Recommended Posts

OK, ppl. I know I must be doing something stupid because this is just not working. I am building a ledger-like layout so that the company I am consulting for can interact with a computerized billing system. Anyway, it was moving along realy well, until I reached the weirdest situation... one I originally thought would be cake. Before getting into the situation itself, let me first explain a bit about the files I am working with:

In this one db called Ledger 2002, I have many fields, two of which must be created so that they interact with each other:

  • Date of Fee - Date field.<br><br>
  • Billing Cycle - Calc field. This field is related to another db called Billing Cycle 2002 through a matching field called Cycle Name. The relationship is called ledger to billing cycle.

Anyway, in the Billing Cycle 2002 db, 3 fields exist. The values for these fields were manually entered; no calculations were performed. It is a static db with the following fields:

  • lower - Date field. Contains the lower value of the date range that is a predetermined billing cycle. For example: 8/15/2002, 9/15/2002, etc.<br><br>
  • upper - Date field. Contains the upper value of the date range that is a predetermined billing cycle. For example: 9/14/2002, 10/14/2002, etc.<br><br>
  • Cycle Name - Text field. Contains the billing cycle's text id. Billing cycle in this case is determined from the 15th of one month to the 14th of the next. For example: a cycle with a lower value of 8/15/2002 and an upper value of 9/14/2002 would make this field "September 2002".

OK, back to my OBJECTIVEB) I want to make it so that when a user types a date in the Date of Fee field, the Billing Cycle field automatically generates. Billing Cycle must also update if the value in Date of Fee field is changed.<br><br>


In my first attempt, I used the the relationship ledger to billing cycle (of course in FM, it is altogether and not on separate lines):

Case ( IsEmpty (Date of Fee), "", (Date of Fee >= ledger to billing cycle::lower)

and (Date of Fee <= ledger to billing cycle::upper), ledger to billing cycle::Cycle Name, "" )


If I do this, FileMaker highlights ledger to billing cycle::lower and says that I can't use it here because it will cause a "circular definition". I am confused as to why... I related Billing Cycle and Cycle Name, NOT Billing Cycle and upper or lower... I really like the idea of having a separate static db to hold the ranges of the billing cycle and the cycle names... that way I only need update the db if there needs to be a revision. So how could I rework the relationship so that it would work?


In my second attempt at solving this problem, I tried entering the following CASE statement that DID NOT use the relationship ledger to billing cycle:

Case ( IsEmpty (Date of Fee), "",

(Date of Fee >= 8/15/2002) and (Date of Fee <= 9/14/2002), "September 2002",

(Date of Fee >= 9/15/2002) and (Date of Fee <= 10/14/2002), "October 2002",

(Date of Fee >= 10/15/2002) and (Date of Fee <= 11/14/2002), "November 2002",

(Date of Fee >= 11/15/2002) and (Date of Fee <= 12/14/2002), "December 2002",

(Date of Fee >= 12/15/2002) and (Date of Fee <= 1/14/2003), "January 2003",

(Date of Fee >= 1/15/2003) and (Date of Fee <= 2/14/2003), "February 2003",

(Date of Fee >= 2/15/2003) and (Date of Fee <= 3/14/2003), "March 2003",

(Date of Fee >= 3/15/2003) and (Date of Fee <= 4/14/2003), "April 2003",

(Date of Fee >= 4/15/2003) and (Date of Fee <= 5/14/2003), "May 2003",

(Date of Fee >= 5/15/2003) and (Date of Fee <= 6/14/2003), "June 2003",

(Date of Fee >= 6/15/2003) and (Date of Fee <= 7/14/2003), "July 2003",

(Date of Fee >= 7/15/2003) and (Date of Fee <= 8/14/2003), "August 2003",

(Date of Fee >= 8/15/2003) and (Date of Fee <= 9/14/2003), "September 2003", "" )


Attempt #2 didn't work either. When I place a date in the Date of Fee field, nothing comes up in the Billing Cycle field. Even when I revised Attempt #2 so that I left off the IsEmpty (Date of Fee), "", part, I simply receive a standard response of "September 2002" in the Billing Cycle field. And, more importantly, no matter what I change the Date of Fee field to be, the Billing Cycle field does NOT change accordingly. I also tried Attempt #2 with nested IF statements and didn't get any farther. So... what is wrong with my CASE statement?


Using the online Help in FileMaker 5.5, I attempted to find the problem myself. So I used the simplest Case statement I could think of, as shown here:

Case ( Date of Fee = 8/7/1974, "My Birthday", "Not My Birthday" )

I couldn't get this to work, either. I am very confused. Any help on these issues will be greatly appreciated.



Link to post
Share on other sites
  • Replies 4
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

First, relationships must be based on keys that can be indexed. A calculated key cannot be based upon a related field.

Your problem with the Case() statement is in how you are entering the date to be compared "Date of Fee <= 5/15/2002" won't work as you are comparing a date field to a text string. You must enter instead: "Date of Fee <= Date(5,15,2002)". The Date() function converts the month, day and year arguments into the proper internal representation for FM to perform the comparison.


Link to post
Share on other sites

I agree... this was extremely frustrating. You would assume that if FileMaker has a Date field specificcallyd esigned to hold dates AND it only takes the dates entered in that field in the mm/dd/yyyy format, that it would store them that way. But no. So, here's the thing then... why use a FileMaker Date field when you could use a number field with a mask? Kinda weird.

Link to post
Share on other sites

You have to think in terms of the programmer designing the parser. Should he always convert the string "7/11/2002" to a date (which internally is a number). That would create a problem when comparing to a text string. Perhaps the type of field being compared to should be considered. Ok, so what is "7//2002". Is this an invalid date or a valid string? Anyway, just a flavor of some of the problems involved. Using Date() makes the answer to the programming problem clear.


Link to post
Share on other sites

This topic is 6554 days old. Please don't post here. Open a new topic instead.

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.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

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

  • Create New...

Important Information

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