If / Then

[PC Infoman]

In my FM database, I have a field for "quantity". It is only a number field. However, if that field is set to "0", I would like to have the fields named "price1" and "price2" set to "Call" and field "price3" set to "N/A".

How can I do this?

[mr_vodka]

Try an auto-entry text field as calculated value with the 'Do not replace' unchecked.

price1 and price2

Case ( not qty; "Call" )

price3

Case ( not qty; "N/A" )

[PC Infoman]

I am sorry John. I am new to this filemaker stuff. Can you explain to me what exactly I need to do to make your suggestion happen?

I would have thought I needed to attach some kind of script to the price1, price2 and price3 fields to have it look at the quantity field and then act appropriately. Am I wrong?

My I should explain more clearly:

This is a catalog database. My fields are as follows

InStockRental - Taking a product that is in stock and renting it.

InStockSale - Taking a product that is in stock and selling it.

NewBuildRental - Making a new product and renting it.

NewBuildSale - Making a new product and selling it.

Quantity - How many that are in stock.

Clearance - Selling off old stock at a cheaper price.

The only editable fields are "Quantity" and "NewBuildRental".

When somebody puts a dollar value in "NewBuildRental", it automatically calculates the value of "InStockRental", "InStockSale", "NewBuildSale" and "Clearance".

What I would like is for the calculation for "NewBuildrental", "NewBuildSale" and "Clearance" to be usurped with the text of my choice if the "Quantity" is 0 or blank while still doing the calculation for "NewBuildSale". If the value of "Quanity" is anything other than 0 or blank, I would like it to calculate all fields normally.

Edited April 27, 2007 by Guest

[Raybaudi]

not GetAsBoolean ( Quantity )

gives true ( 1 ) when quantity is 0 or blank, so you can make a calculation with Case function.

for example:

Case(

not GetAsBoolean ( Quantity );"the text of your choise";

"the normal calc"

)

[comment]

not GetAsBoolean ( number ) = not number

[Oldfogey]

not GetAsBoolean ( number ) = not number

Except when is empty!

[comment]

AFAIK:

not GetAsBoolean ( "" ) = 1

not "" = 1

1 = 1

If the intent is to force evaluation when all referenced fields are empty, then check the box designated specially for this purpose.

[Raybaudi]

not GetAsBoolean ( number ) = not number

not GetAsBoolean ( number ) = not number or IsEmpty ( number )

The [not] flag into the checkbox makes your assertion right, but only then, not always.

While boolean algebra has only two elements, 0 and 1, a given number field has 3 levels:

1) a value

2) a zero

3) an empty value

the not operator gives:

1) zero

2) one

3) empty

AFAIK:

not GetAsBoolean ( "" ) = 1

not "" = 1

1 = 1

AFAIK:

not GetAsBoolean ( "" ) = 1

not "" = "" ( for FileMaker calculation engine )

1 ≠ ""

[Raybaudi]

BTW: scripting a set field [ number ; not number ] gives 1 even if number is empty

[Genx]

What's up with all this "AFAIK" business...

IJTIOAIWSES!

I just tested it out and it works so everyone shhh!

not "" = 1 for the FileMaker calculation engine.

not nothing = something... seems fairly logical reasoning to me. How booleans work:

0

Empty Value

= False or 0

Positive or Negative Number

= True or 1

Not reverses either False or True...

Edited April 29, 2007 by Guest

[comment]

not "" = "" ( for FileMaker calculation engine )

Hmm. But the same FileMaker calculation engine says not "" = "" is FALSE.

I believe you are confusing two separate issues. The VALUE of the expression not "" is always TRUE, never empty. In fact, if you put this expression into a calculation field, it will return TRUE.

However, in order to get the value of an expression, the expression must be evaluated. This is true for any expression, not just the one here. When the calculation engine is too lazy to evaluate a calculation field, the FIELD is empty - but that does not mean the VALUE of the formula in the field is "".

Filemaker confuses the issue even more, by returning TRUE to the question IsEmpty ( calcField ). But now we are discussing the economics of evaluation, not Boolean algebra.

By wrapping the field reference in GetAsBoolean( Numberfield ), you have merely forced the expression to evaluate. I don't know why it evaluates when Numberfield is empty (I would expect it not to), but it does. But you could have also forced it to evaluate in many other ways, for example by:

Lookup ( ; not Numberfield )

Would that be a good solution? IMHO, the recommended practice to get a calculation field to evaluate when all referenced fields are empty is to check the box at the bottom of the formula - instead of adding redundant operations.

[Raybaudi]

http://www.acronymfinder.com/af-query.asp?acronym=IJTIOAIWSES

gives NO match

BTW:

1) make a number field ( number )

2) make a calculation field with this calc:

not number

What do you see into the calc field when number is empty ?

[Raybaudi]

I don't know why it evaluates when Numberfield is empty (I would expect it not to), but it does.

Hi comment

it evaluates as expected

From Help On Line:

------------------------------

GetAsBoolean function

Format

GetAsBoolean(data)

Parameter

data - any text, number, date, time, timestamp or container expression, or a field containing text, a number, date, time, timestamp or container.

Data type returned

number

Description

[color:red]Returns 0 if data has a value of 0 or is empty, all other values return 1.

[Genx]

What do you see into the calc field when number is empty ?

That's because it's not evaluating... When creating your calc field, down the bottom where it says "do not evaluate if all referenced fields are empty" - untick that.

To remove all doubt, try it in the data viewer.

[Raybaudi]

Ok

Now re-tick that box and change the calc to:

not GetAsBoolean ( number )

What do you see into the calc field when number is empty ?

BTW: go to read my first post... I didn't say that someone had given a wrong answer, I only say a way to solve the problem... and that calc works as expected w/o thinking for that box.

[Genx]

Okay, I think FileMaker is stupid...

Well inconsistent at least.

[Genx]

Ahahahahhahahahahaha.. Ahahahahahahaha... Ahahahaha...

Not isn't even a function, it's an operator, that's why it doesn't work.

[Vaughan]

So what does "IJTIOAIWSES" mean?

[Genx]

Lol, guys it was just a joke... I think most acronyms are ridiculous (except for lol hehehe)

IJTIOAIWSES!

I just tested it out and it works so everyone shhh!

[PC Infoman]

OK, What have I started. All this talk is well and good, but I am lost. What exact programming do I add? How exactly do I add it? I can follow instructions, but I cannot figure this out on my own.

~FM Newbie

[David Jondreau]

What I would like is for the calculation for "NewBuildrental", "NewBuildSale" and "Clearance" to be usurped with the text of my choice if the "Quantity" is 0 or blank while still doing the calculation for "NewBuildSale".

Little confusing here. What do you mean by "still doing the calculation"? That might affect what I'm about to say.

You already have a working calculation. Take that calculation and wrap it in a Case statement.

Case(isEmpty(quantity) or Quantity = 0); "Text of Choice"; YourCalc).

[PC Infoman]

Well, like I said before, a calculation is automatically done to determine the value of "NewBuildSale", "InStockRental", "InstockSale" anfd "Clearance" when a numerical value is entered into "NewBuildRental".

When "Quantity" is set to "0" or it has no value in it at all, I want the values of "InStockRental". "InstockSale" and "Clearance" to be set to "Call". However, when "Quantity" is set to "1" or any number greater that "1", I want the original calculations to be done instead.

Edited April 30, 2007 by Guest

[Oldfogey]

Sorry, PC Infoman. I know this doesn't help you but having started the off-topic argument, I thought I'd better summarise it.

GetAsBoolean.zip

[comment]

Try it this way?

GatAsBoolean.fp7.zip

[Oldfogey]

OK, comment, I give up. I much prefer your result but I'm hanged if I can find the difference.

Edited May 8, 2007 by Guest

[comment]

The difference is that my calculation is set to evaluate even when all referenced fields are empty (that was my point all along).

[Oldfogey]

: : : :

thanks comment. I have written out 200 times "Please put brain gear before mouth/keyboard."

I'm glad I've never used GaB.

[comment]

LOL. GetAsBoolean() has its uses - though it wasn't very high on my priority list.