Filemaker "If Function" from beginner/intermediate newbie

[kimbakat]

I have a database of 3000 entries which have a current month when a job was worked on.

So if my exising field in my database is MONTH
(contents are 01,02,03 thru 12) (01=Jan; 02=Feb)

I need to do search of Quarters. (01-03) (04-06) etc

While the Filemaker HELP defines the "IF Function" it does not logically and properly explain how to write one.

It says
------------------------------------------------
If(Country = "USA";"US Tech Support";"International Tech Support")

returns "International Tech Support", if the Country field contains France or Japan. Returns US Tech Support if the Country field contains USA.
--------------------------------------

My question is..Where does the "Then" part occur in the example above.

How can I write my function to get an output Q1 Q2 Q3 or Q4 into the new field called QUARTER?


Any help much appreciated!!

 

[kimbakat]

I tried this:
------------------------------------
If(MONTH END="01"or"02"or"03";"Q1") or
If(MONTH END="04"or"05"or"06";"Q2") or
If(MONTH END="07"or"08"or"09";"Q3") or
If(MONTH END="10"or"11"or"12";"Q4")
------------------------------------------
but it only returns an output of "1" in my QUARTER field

 

[JeanW]

The If() function will return a true or false value, which is why you have a 1 value.

Better is to look what the Case () statement can do for you.

If you have a field that calculates your monthNumber, something like:

Month (yourDate)

and you pull this value into your case statement you can have something along these lines:

Case(
monthNumber >= 10; "Q4";
monthnumber >= 7; "Q3";
monthNumber >= 4; "Q2";
"Q1"
)

To make a search for records within a given Quarter you can make a search routine script where you trigger the date and convert this to a quarter with the set field function.
It becomes something along these lines:

Case(

_Quarter = "Q1"; Date(1; 1; _Year);
_Quarter = "Q2"; Date(4; 1; _Year);
_Quarter = "Q3"; Date(7; 1; _Year);
_Quarter = "Q4"; Date(10; 1; _Year)

)

for the startdate and

Case(

_Quarter = "Q1"; Date(3; 31; _Year);
_Quarter = "Q2"; Date(6; 30; _Year);
_Quarter = "Q3"; Date(9; 30; _Year);
_Quarter = "Q4"; Date(12; 31; _Year)

)

for the end date.

If you make you montName out of 3 charcters and want to parse out the monthnumber from that field, you can use the choose function, which is a zero based function.
You could use something along these lines:

to set the startDate
Date(
Choose(Int(Position("JanFebMarAprMayJunJulAugSepOctNovDec"; Left(_Month; 3); 1; 1) / 3); 1;2;3;4;5;6;7;8;9;10;11;12); 1; _Year)

to set the endDate
Date(
Choose(Int(Position("JanFebMarAprMayJunJulAugSepOctNovDec"; Left(_Month; 3); 1; 1) / 3); 1;2;3;4;5;6;7;8;9;10;11;12) + 1; 1; _Year) - 1