Filter question

[Josh M]

Good evening,

In the table that I'm displaying data from I have a field "Salesperson" that is auto-populated when a record is created by the username of the user adding the record using "Get ( UserName )". I'd like to filter the records shown in the portal by that field based on the user that is currently logged in. In other words, when Joe Guy is logged in, he will only see the records he created in the portal.

I'd also like to filter the portal by another field which has values like Won, Lost, Pending, etc.. In this case, I'd like to filter out anything that has the value of "Won".

Pobably basic, but I'm stuck. I appreciate any help you can provide in advance.

[rwoods]

Hi Josh, thanks for the post.

In the Filter settings of the portal you need to tell FileMaker which records you would like to SHOW. So the calculation you enter should be TRUE when a record should be shown. Therefore the calculation to enter should be something like :-

( Salesperson = Get ( UserName ) ) and ( not ( quoteStatus = "Won" ) ) 

Don't forget, that although the Filter will restrict the records that are visible to the user on screen, all the records that are matched by the relationship for that portal are still being considered by FileMaker, so if you create a calculation field to show the total of the Quote Value, it will add up ALL the records matched by the relationship, not just the visible ones. You could use some SQL to add up only the relevant records like :-

TotalOutstandingQuoteValueForThisSalesperson = ExecuteSQL ( SUM (quoteValue) FROM Quotes WHERE ( ( SalesPerson = ? ) and ( QuoteStatus <> 'Won' ) ) , "" , "" , Get (UserName) )

Forgive me is this SQL syntax is slightly wrong somewhere, I don't have a solution to test it in!

 

[Josh M]

9 hours ago, rwoods said:

Hi Josh, thanks for the post.

In the Filter settings of the portal you need to tell FileMaker which records you would like to SHOW. So the calculation you enter should be TRUE when a record should be shown. Therefore the calculation to enter should be something like :-

( Salesperson = Get ( UserName ) ) and ( not ( quoteStatus = "Won" ) ) 

Don't forget, that although the Filter will restrict the records that are visible to the user on screen, all the records that are matched by the relationship for that portal are still being considered by FileMaker, so if you create a calculation field to show the total of the Quote Value, it will add up ALL the records matched by the relationship, not just the visible ones. You could use some SQL to add up only the relevant records like :-

TotalOutstandingQuoteValueForThisSalesperson = ExecuteSQL ( SUM (quoteValue) FROM Quotes WHERE ( ( SalesPerson = ? ) and ( QuoteStatus <> 'Won' ) ) , "" , "" , Get (UserName) )

Forgive me is this SQL syntax is slightly wrong somewhere, I don't have a solution to test it in!

 

Great, thanks for the help!