Filtered Portal Calculation

[Matthew R White]

Please help me with my calculation below, what I'm trying to say is:

Show records in the portal (filtered) if the following conditions are met:

LSP::ACCT CODE 1 = "q"

LSP::QUOTED STATUS = 0 or " "

and get user name, if user name is Matt then show records with LSP::PRODUCER CODE = 3

if user name is Kim, then show records with LSP::PRODUCER CODE = 2

etc etc.

 

Here is what I have so far:

LSP::ACCT CODE 1 = "q" and (LSP::QUOTED STATUS = 0 or " ") and

Case (

Get(UserName) = "Matt"; 3;

Get(UserName) = "Kim"; 2;

Get(UserName) = "Bill"; 9;

) LSP::PRODUCER CODE = 3 )

 

Thoughts?

[David Jondreau]

Comparative expressions ( using say, the = sign) need to be compared individually. So it should be:

 

LSP::QUOTED STATUS = 0 or LSP::QUOTED STATUS = " "

 

And I don't know what you're doing in that Case() statement.

 

I like using Let() when I can. And others here disagree, but I like parens for clarity:

I would write it:

 

Let([

acct.code.true = ( LSP::ACCT CODE 1 = "q" ) ;

status.true = ( LSP::QUOTED STATUS = 0 ) OR  ( LSP::QUOTED STATUS = " " ) ;

user = Get ( UserName ) ;

user.code =  Case ( user = "Matt"; 3 ; user = "Kim"; 2; user  = "Bill"; 9 ) ;

producer.code.true =  ( LSP::PRODUCER CODE = user.code ) ;

 

 

result = acct.code.true AND status.true AND producer.code.true

];

result

)

Edited July 24, 2014 by David Jondreau

[eos]

what I'm trying to say is:

 

… probably this:

 

Let ( [

  ~user = Get ( UserName ) ;

  ~status = LSP::QUOTED STATUS ;

  ~code = Case ( ~user = "Kim" ; 2 ; ~user = "Bill" ; 9 ; 3 ) // unclear about the trailing 3; if this is the default, you don't need to check on "Matt"

  ] ;

  LSP::ACCT CODE 1 = "q" 

  and 

  ( ~status = 0 or ~status = " " ) 

  and

  LSP::PRODUCER CODE = ~code

)

 

Thoughts?

 

Learn FileMaker calculation syntax 

[comment]

Case (

Get(UserName) = "Matt"; 3;

Get(UserName) = "Kim"; 2;

Get(UserName) = "Bill"; 9;

 

Perhaps you should have a Users table where each user has a corresponding PRODUCER CODE, instead of hard-coding names and codes into calculation formulas like that.