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!

 

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.

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.

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!

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.

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:

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:

The data viewer when this runs shows:

 

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.

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!

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.

That's exactly the method I used. I should have realised! Thanks again for your help.