Jump to content
Josh M

Filter question

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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!

 

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites

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

×

Important Information

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