Portal Filter By Months Ahead Of Current Date

[laguna92651]

I currently have a portal that I am filtering with 3 different global fields, all work fine. I would like to add a field to filter by a months block from current date. There is a sale date column in the form of 8/29/2017.

The date drop down filter field would have the following entries. The filter will be referenced to the current date.

If "3-6 mos" were selected only records in Dec 2017, Jan 2018 and Feb 2018 would show ( 3, 4 and 5 months from today's month).

This would be the drop down.

All Dates
< 0 mos
0-1 mo
0-3 mos
3-6 mos
6-9 mos
9-12 mos
>12 mos

This is what I currently have with the date filtering, using a custom function, this works fine.

( T20_ALL_OPPORTUNITIES::g_search_probability = 0
 or
T20_ALL_OPPORTUNITIES::g_search_probability   ≤  T20_ALL_OPPORTUNITIES::Sale_Probability )
 and
( T20_ALL_OPPORTUNITIES::g_search_priority = "All"
 or
FindWordPartsInText ( T20_ALL_OPPORTUNITIES::g_search_priority ; T20_ALL_OPPORTUNITIES::Priority ; 1 ))
 and
(IsEmpty ( T20_ALL_OPPORTUNITIES::g_search_opportunity )
 or
FindWordPartsInText ( T20_ALL_OPPORTUNITIES::g_search_opportunity ; T20_ALL_OPPORTUNITIES::Opportunity & ¶ & T20_ALL_OPPORTUNITIES::Account ; 1 ))

[comment]

I see two separate questions here. The first one is how to extract the two numbers from the value selected in the drop-down. The second question is how to use them in a filtering expression. The first question is actually the more difficult one, esp. since you have values that break the pattern (and will also require a different filtering expression).

To answer the second question, you could do something like:

Let ( [
start = ...................... ;
end = ....................... ;
startDate =  Date ( Month ( Get ( CurrentDate ) ) + start ; 1 ; Year ( Get ( CurrentDate ) ) ) ; 
endDate =  Date ( Month ( Get ( CurrentDate ) ) + end ; 1 ; Year ( Get ( CurrentDate ) ) )
] ;
startDate ≤ PORTAL_TO:YourDatefield
and
PORTAL_TO:YourDatefield < endDate
)

 

[laguna92651]

Thanks for the expression it worked great, only had to had a plus 1 to the end date to include last month. To extract the month numbers just created a dropdown with the selection list, then a script with if statements to set the Start/End fields that are used in your expression. Thanks again.

 

[comment]

1 hour ago, laguna92651 said:

then a script with if statements to set the Start/End fields that are used in your expression.

You can use Case() statements within the filtering expression itself instead.

[laguna92651]

I will give that a try, is there an advantage to using the case statements in the portal?

[comment]

2 hours ago, laguna92651 said:

is there an advantage to using the case statements in the portal?

You save 2 global fields, a script and a script trigger.