'If' with 'and' and multiple 'or' in a statement

[Quito]

Hi FMForums Community,

When searching for Table::Type1 = "ABL" in my (1000 record) database I get 300 records.  I've added a Portal and within the "Filter Portal records" option I wrote the following:

If ( Table::Type1 = "ABL" and ( Table::Type2 = "XA" or Table::Type2 = "YA" or Table::Type2 = "ZA") ; Table::ID ; 0 )

I should be retrieving only 50 out of the initial 300 records but I'm still retrieving all 300 records where Type1 = ABL. The 50 records that fulfill criteria are visible and the other 250 are appearing blank.

When I write:

If ( Table::Type1 = "ABL" ; Table::ID ; 0 )

I retrieve just the 300 expected records. I don't understand why the filter is displaying blank records if the script seems to be filtering properly.

Any suggestions for making the "Filter Portal records" show just the 50 records?

Best,

Daniel

 

[comment]

Can you post a simple file showing the problem (and hopefully only the problem)?

--
Note that your filtering expressions are unnecessarily convoluted. The result of a filtering expression needs to be either True or False. Your expressions return Table::ID instead of True. We don't see the contents of the Table::ID field, so we don't know what it will return when converted to a Boolean. This is probably not the cause of the problem you raise, but instead of:

If ( Table::Type1 = "ABL" ; Table::ID ; 0 )

you should be using:

Table::Type1 = "ABL" 

and likewise:

If ( Table::Type1 = "ABL" and ( Table::Type2 = "XA" or Table::Type2 = "YA" or Table::Type2 = "ZA") ; Table::ID ; 0 )

should be:

Table::Type1 = "ABL" and ( Table::Type2 = "XA" or Table::Type2 = "YA" or Table::Type2 = "ZA" ) 

 

[Quito]

As always Comment, you're absolutely right. I removed the IF and left just the name of the first field I want to filter:

Table::Type1 = "ABL"

and it worked nicely.

Yet, the second part:

Table::Type1 = "ABL" and ( Table::Type2 = "XA" or Table::Type2 = "YA" or Table::Type2 = "ZA" ) 

still retrieves blank records mixed with the expected records.  Probably that's why I got muddied up with the If statement.

Best,

Daniel

[comment]

1 hour ago, Quito said:

still retrieves blank records mixed with the expected records.

As I said, this is not the likely cause. I don't know what the real cause is, and I am having trouble visualizing the situation you describe. That's why I suggested you post a demo file. 

 

[Quito]

Thank you Comment,

After your last post I decided to make a copy of the file and remove some things that weren't necessary for the demo and the problem went away.

- Still scratching my head

Update: I think one of the tables in the Portal was extending beyond the Portal layout

Edited January 9, 2020 by Quito