CASE statement in Calc field not working...

[clrblue]

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 OBJECTIVE 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>

ATTEMPT #1

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, "" )

---

ATTEMPT #1 - ISSUES & QUESTIONS

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?

ATTEMPT #2

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 - ISSUES & QUESTIONS

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?

TROUBLESHOOTING CASE STATEMENTS

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.

TIA!

KC

[LiveOak]

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.

-bd

[IdealData]

I think we've all been caught by this one !

May be the "Help" system could be a bit more explicit in some areas.

[clrblue]

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.

[LiveOak]

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.

-bd