Case statement.. using months.

[Himitsu]

I have a calc that uses a case statement. In my view it looks good but it didn't work right. Here's what it is about:

I have 3 fields, called A, B, and C, and of course the calc field called D. Now field C, can have a value of 1 or 2. Now I want it to return a value based on the month range and also the value of C. So I wrote

Case(

month (get(currentDate)) >=4 and month(get(currentDate)) <=9 and field C =/ "2"; field A;

field C =/ "2"; field B;

""

)

Now today is October 1 and my calc is still showing field A results. Is there something wrong? I need field C to be a not equal statement because there might be more values than just "1" and "2" in the future, maybe "3" or "4", but I just don't want "2" to show.

Any help would be great.

[-Queue-]

Is field D unstored? If not, Get(CurrentDate) will not update unless you modify field C.

[Himitsu]

Oh, I guess it was on min store, oops... but then why would it work if I went the other way:

Case(

month (get(currentDate)) >=4 and month(get(currentDate)) <=9 and field C =/ "2"; field A;

field C = "2"; "";

field B

)

this way works even if it is stored, but I didn't want to go that direction due to the fact that only 5% of the records have a "2" value in field C...

Hmm... little things I guess... hahaha

Thanks a lot.

Ryan

[Himitsu]

Queue, I now have a problem, if I unstore it, then the field that refers to it won't connect. So how do I get around this? I thought about making a script that sets a date field, but will that be the same thing in I store it? Is there a better calc that checks for teh currentdate? I also use this calc to determine the school year too. I wonder now if, when I get to next April, if it will change from 2005-2006 school year to 2006-2007 school year... probably not if it is stored right?

[-Queue-]

I am not sure what you mean by 'the field that refers to it won't connect'. Do you mean it is the child key in a relationship? If so, then it will have to be indexed. You will then need a script to update the value in all records each day, in which case it may be better left as a normal text field, with the calculation to set it included in the script.

[Himitsu]

that is what I was thinking would be the way to go. But now I am getting lots of opening scripts steps... after I got a half dozen or so, I put them seperate and run them in order... but it is getting to be a whopper. What I have been doing lately is using a date field then when the script opens, I set an if statement to read if the datefield =/ currentDate, then to continue running it else exit. But in a script, is there any problems with that get(currentDate) function in a script? I wouldn't think so since it is not stored at all.

[Himitsu]

so even if it is in the same file but different table it won't work as a child key? I know it has to be stored so that leaves a global out of the solution... but to have is loop through 1000 records everyday, that will take a long time... I have made a type of flag field that refers to 2 calculation fields and if they don't equal, then it places a "1" in that flag field. Then, when the file is opened, it goes into a found script and looks for all the records containing 1's and updates those records. That is how I have gone around some problems. I can do it here too, but I had hoped there would be some other way to update the currentDate setting... I will start with what I know, but if anyone has any other ways they have done this without having to update thousands of records, it would be greatly appreciated. Thanks again.

[Søren Dyhr]

You have to think Get(CurrentDate) on the other side (primary key) of the relations def. so it makes a multicriteria relationship theta join matching the actual date as foreignkey.

--sd