Jump to content

Need help with an if statement that is not working as I think it should!


This topic is 2843 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

Hi,

 

I am sure my if statement should execute as 'true', but it is not.

 

I am on a table called SALESINVOICES. I want to check how many SALESINVOICELINES (ie, the products attached to the invoice) qualify for a special.

 

My 'If' statement is:

 

$CorS = "C" and ( SalesInvoices_SALESINVOICELINES|CompanyID|fqualifiestoptenspecial::s unique no products count customer ID = SalesInvoices_PRODUCTS|toptenproducts::z_FoundCount )

 

where SalesInvoices_SALESINVOICELINES|CompanyID|fqualifiestoptenspecial::s unique no products count customer ID is a summary field counting the total of lines that have flagged as qualifying for the special, and

SalesInvoices_PRODUCTS|toptenproducts::z_FoundCount the found count of products that qualify for the special, and

$CorS is a variable that shows whether a store is a customer or shipping store.

 

Before this If statement runs, I put a Show Custom Dialog to check whether the fields are definitely giving me what I expect of them.

 

The dialog box shows:

 

$CorS & "      " & SalesInvoices_SALESINVOICELINES|CompanyID|fqualifiestoptenspecial::s unique no products count customer ID & "       " & Count ( SalesInvoices_PRODUCTS|toptenproducts::a_c1 ) &     "      " & SalesInvoices_PRODUCTS|toptenproducts::z_FoundCount

 

and the output is:

 

C   12    12    12

 

So I believe the If statement on the following line should execute, as $CorS = "C", and the other two fields are identical.

 

Is there some other problem - will it not work out the summary field correctly in the If statement?

 

Any help would be appreciated, it is driving me crazy!

 

Link to comment
Share on other sites

Hi Steph and welcome the the FM Forums.

I moved your topic from "FileMaker Pro 12" to "Calculation Engine (Define Fields)” because the General topic areas (with the exception of version 13) are reserved for discussion about the new tools, functions and features that were introduced with their release of FileMaker.

The General Topic for Version 13, will be disbursed out to other topics when the next version of FileMaker is Release.

Link to comment
Share on other sites

Your long field names and table names are making me dizzy, but

 

$CorS = "C" and ( SalesInvoices_SALESINVOICELINES|CompanyID|fqualifiestoptenspecial::s unique no products count customer ID = SalesInvoices_PRODUCTS|toptenproducts::z_FoundCount )

 

< ...snip...>

The dialog box shows:

 

$CorS & "      " & SalesInvoices_SALESINVOICELINES|CompanyID|fqualifiestoptenspecial::s unique no products count customer ID & "       " & Count ( SalesInvoices_PRODUCTS|toptenproducts::a_c1 ) &     "      " & SalesInvoices_PRODUCTS|toptenproducts::z_FoundCount

 

 

Which are you looking for:  a logical "and" (as in your if statement) or a concatenation "&" (as in your dialog box)? I think you are expecting the concatenation, because the string "C" evaluates to a Boolean 0, which means the total expression will always be false.

Link to comment
Share on other sites

  • Newbies

Thank you -

 

I am wanting the If statement with the logical "and" to be true. The custom dialog is just for me to test whether the values are what I expect.

 

I expect the variable $CorS to be storing the letter "C" (it is set to either hold "C" or "S", hence the name), which the custom dialogue output says it is, so why would the statement $CorS = "C" return a Boolean 0? Why do you say the string "C" evaluates to a Boolean 0? 

 

Sorry about the long field / table names - they make sense to me!

Link to comment
Share on other sites

Is your If statement evaluated in the same context as your custom dialog?

 

If yes, and you're not getting the results you expect, try breaking it into smaller components, for example make your custom dialog show you the result of:

List (
$CorS = "C";
SalesInvoices_SALESINVOICELINES|CompanyID|fqualifiestoptenspecial::s unique no products count customer ID = SalesInvoices_PRODUCTS|toptenproducts::z_FoundCount
)

then you'll know which of the two is returning false and needs a closer inspection.

 

You might find the Data Viewer a more convenient tool for making these tests.

Link to comment
Share on other sites

  • Newbies

Thank you, I did your list dialogue test as you had written it and it is the second line, comparing the two fields, that is 0. The data viewer when this runs shows:

CYZEvq.png

So why isn't it executing as true? FYI, the top field is a summary of a number field and the bottom field is a calculation of number fields. 

Here is the If statement again:

yIM3Au.png

Link to comment
Share on other sites

The data viewer when this runs shows:

CYZEvq.png

 

Did you run the script in the Script Debugger and observed the values while the script was running (and especially just before the If step)?

 

You should also add the expression =

SalesInvoices_SALESINVOICELINES|CompanyID|fqualifiestoptenspecial::s unique no products count customer ID = SalesInvoices_PRODUCTS|toptenproducts::z_FoundCount

to the Data Viewer, in case one of the fields contains something else besides 12.

Link to comment
Share on other sites

  • Newbies

I just solved it! I put the Round() function around the SALESINVOICELINES field. So I presume this just means the field was very close to, but not exactly 12?

 

Thank you very much for your help with this! I have actually never used the Data Viewer before, and I am very glad I have been introduced to it!

Link to comment
Share on other sites

I just solved it! I put the Round() function around the SALESINVOICELINES field. So I presume this just means the field was very close to, but not exactly 12?

 

Maybe - but didn't you say this was a summary field, defined to count some records? Such field cannot contain any value other an exact integer.

 

OTOH, if the field is defined to total a calculation field, using the method described here, then that's very likely.

Link to comment
Share on other sites

This topic is 2843 days old. Please don't post here. Open a new topic instead.

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
 Share

×
×
  • Create New...

Important Information

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