7 Replies Latest reply on Mar 25, 2015 8:10 AM by RobertT

# Creating a boolean calculation followed by a date function

### Title

Creating a boolean calculation followed by a date function

### Post

I'm trying to create a boolean function where I have a lists of patients who have a field for "number of medical scripts written"   these are random from 0-5 for each patient record.  I figured out how to use the boolean function to equal 1 for anything empty or "0" in that field.  I want to use a date calculation right now Im focusing on year.. to separate from 2014 and 2015 and eventually total up the amount of how many patients were written scripts during a given period   DOS= date of service random dates 2014-2015

i tried    GetAsBoolean ( NumberScripts)  &  DOS = Year ( "1/1/2014")

I figured get as a boolean would give me a count of one where each record had at least one script.  but I can't seem to separate that by year (or even a period of time)  if input that above of the date or even a date calculation.  My final goal is to create a report for this data.  Any help would be appreciated.

• ###### 1. Re: Creating a boolean calculation followed by a date function

GetAsBoolean ( NumberScripts)  &  ( Year ( DOS ) = 2014 )

But there are much more flexible options that use relationships to get counts of how many patient records have "medical scripts" written.

"1/1/2014" by the way, is text, not a date.

• ###### 2. Re: Creating a boolean calculation followed by a date function

Redid that but I'm not getting what should be expected... I'm getting a value of 10...I have a sample of 12 patients..   4 have a DOS  (date of service)  in 2014.. the rest are in 2015 only 1 of those patients had no medical scripts written in 2014.. so there the boolean should of calculated a 1 value for only 3 of the patients for 2014.

thanks for the info on the date though.

• ###### 3. Re: Creating a boolean calculation followed by a date function

I don't really see how your calculation is intended to work and it's pretty inflexible anyway as it's hardwired to a specific year. Even if it worked, you'd have to keep adding calculation fields or changing the existing ones to work for different years.

Here's how your current calculation evaluates:

If number of scripts = 3 and date of service is 2/2/2014, you'd get a result of 11.

GetAsBoolean ( NumberScripts) produces 1.

( Year ( DOS ) = 2014 ) produces 1

1 & 1 produces 11 as the & operator is the concatenation operator.

Perhaps you wanted the logical operator "and"?

• ###### 4. Re: Creating a boolean calculation followed by a date function

What I was trying to do (well what the doctor wanted)  was to calculate how many patients received prescription scripts on any given time period (day, month etc... so right now i was doing by year.  I created a sample of about 12 patients,  and a field just tallying how many scripts were writtten, randomonly from 0-5    I was using the boolean calculation instead of an If calculation so i could get 1  in Getasaboolean(Number of scripts) = produces 1   but I also wanted to produce 1 if the date (DOS)  is equal to 2014, but im getting 11

I also wanted rewrite it to seperate to find how many scripts were written in 2015 and so on.  Then I was going to create a report layout of the calculated data.  That was my expected result.   Maybe Im using the wrong calculation functions?

• ###### 5. Re: Creating a boolean calculation followed by a date function

Is it possible to do an If(function)  to include two variances???      If(DOS=year("1/1/2014") & (Numberscripts) > 0 ; 1; 0    This is my main goal but it won't calculate out

• ###### 6. Re: Creating a boolean calculation followed by a date function

I don't see the response that I posted earlier. To repeat, you appear to be using the concatenation operator (&) when you should be using the logical and operator (and).

If you want the value 1 when both the boolean function evaluates as True and the "year" comparison evaluates as true, write it like this:

GetAsBoolean ( NumberScripts)  AND  ( Year ( DOS ) = 2014 )

If you want the value 1 when either the first or the second term is true, write it as:

GetAsBoolean ( NumberScripts)  Or ( Year ( DOS ) = 2014 )

And if NumberScripts is a number field or a calculation field with a number result type, you don't need the "getasBoolean" function given the way FileMaker evaluates Boolean expressions.

• ###### 7. Re: Creating a boolean calculation followed by a date function

Thanks that is exactly what I needed!!