Jump to content
Sign in to follow this  
PC Infoman

If / Then

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by Guest

Share this post


Link to post
Share on other sites

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"

)

Share this post


Link to post
Share on other sites

not GetAsBoolean ( number ) = not number

Except when is empty!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 ≠ ""

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

IJTIOAIWSES!

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

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 by Guest

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

gives NO match :B

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 ?

Share this post


Link to post
Share on other sites

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 :B

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Okay, I think FileMaker is stupid...

Well inconsistent at least.

Share this post


Link to post
Share on other sites

Ahahahahhahahahahaha.. Ahahahahahahaha... Ahahahaha...

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

Share this post


Link to post
Share on other sites

So what does "IJTIOAIWSES" mean?

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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).

Share this post


Link to post
Share on other sites

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 by Guest

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Edited by Guest

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

: : : :

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

I'm glad I've never used GaB.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.