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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Who Viewed the Topic

    1 member has viewed this topic:
    arni 
×
×
  • Create New...

Important Information

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