Filter Portal by month/year and global variable

[Rob Ross]

I know this type of question has been posted several times and I have read every thread I can find on how to accomplish this.  I think my confusion comes from not understanding the correct syntax the portal filter uses.  So here goes.

I have a portal that lists records inside Commissions_Detail table.  I want to only show records in the portal that meet the following criteria:

 

1. The Sale_Date field is within the current month and year (This will only show commission records that are from the current month and year)

2. The Employee_ID field is equal to the $$Current_Staff_ID global variable (This will ensure that records that are in the portal that meet the date requirement will only show those items that relate to the current employee id of the current logged in user)

 

Any help in this direction would be greatly appreciated.

thanks!

Rob

[Tom Assia]

Hello,

One of the best way for doign this is thinking about data you want to filter.

For example, you say :

This will only show commission records that are from the current month and year

You have two solutions :

Make a calculation of the month and year base on the Sale_Date something like

Month(Sale_Date = Month(GetDate(Today))

and

Year(Sale_Date) = Year(GetDateToday))

But this solution make 3 calculations for each ligne.

In the tables commission, have you got and auto entry for the month and year of the date ?

If so, you could make

Month_Sale_Date = Month(GetDateToday))

and the same for year,

the portal will be more fluent.

An other way is using the link between table and portal to set Month and year in the graph, it's again best solution. You could make a script on the layoute loading.

Tom

[Rob Ross]

Thank you Tom for your suggestion.  I was able to solve the problem with the following calculation in the portals filter section.

Month ( Get ( CurrentDate )) = Month (Sales » CommissionDetail::Sale_Date) and Year ( Get (CurrentDate)) = Year ( Sales » CommissionDetail::Sale_Date ) and Sales » CommissionDetail::Employee_ID = $$CurrentStaffPK

this is working as I expect.  I just didn't get the order that things needed to be placed in.

thanks again for all your help!

Rob

[Tom Assia]

No problem, thank you for the reply,

Maybe create two fields month and year in the commission table would improve speed

For the order chek if hou had specify one in the link

Tom