Infopath 2010 list form to Filter Calendar Events
I am creating a browser based list form in Infopath 2010. This form is an interface to filter events based upon selection from year, week, category of events, location filters. The list is Calendar Events. I have 2 lists. First list is the main Calendar events list (which is data source for the main form). Second list is the list which holds the dates for the Fiscal Year.
My form/interface has following controls:
Year - Drop down list showing years
Week - Drop Down list showing week numbers
Category of Events - drop down list showing category of events
Location - drop down list showing all locations
Repeating Section - Shows Date, Week, Event, Location based upon selections from any of above filters
Now starts the fun: I want the Repeating section to show all results between dates from Fiscal Year list. And this does not work. Year, Week, Category and Location filter work and shows results from main calendar list but does not cross reference the Fiscal year list
Hope I could explain it better. If needed I can send the screenshot
My form/interface has following controls:
Year - Drop down list showing years
Week - Drop Down list showing week numbers
Category of Events - drop down list showing category of events
Location - drop down list showing all locations
Repeating Section - Shows Date, Week, Event, Location based upon selections from any of above filters
Now starts the fun: I want the Repeating section to show all results between dates from Fiscal Year list. And this does not work. Year, Week, Category and Location filter work and shows results from main calendar list but does not cross reference the Fiscal year list
Hope I could explain it better. If needed I can send the screenshot
ASKER
Actually it is working but I am missing just one last piece of showing events based upon Fiscal year dates. Attached is the screenshot of actual form with the results
advancedcalendarfilterformwithre.png
advancedCalendarfilter.png
advancedcalendarfilterformwithre.png
advancedCalendarfilter.png
Are you trying to determine the current and previous years? I will assume that you are treating each one separately.
I am going to do one side but if you have current and previous separate this would be done twice, once for each.
So you have your main calendar with records by date(Date), This would need to be queried.
you have your secondary list, which I assume three columns, also should be queried up front.
FY, FY_Start_Date, FY_End_Date
You have your drop down Current_Year_Select
On your repeating section you would add a formatting rule for each of these.
Date less than FY_Start_Date[FY=Current_Y
Date greater than FY_End_Date[FY=Current_Yea
This will hide records where the date is outside of the range that equals the selection.
The key here is using a filter. So when you select the FY_Start_Date field from the formula Builder, once you click on the field name, there will be a button in the lower left of the window called filter, Select that and then select the FY and Current_Year_Select fields.
Hope that helps.
I am going to do one side but if you have current and previous separate this would be done twice, once for each.
So you have your main calendar with records by date(Date), This would need to be queried.
you have your secondary list, which I assume three columns, also should be queried up front.
FY, FY_Start_Date, FY_End_Date
You have your drop down Current_Year_Select
On your repeating section you would add a formatting rule for each of these.
Date less than FY_Start_Date[FY=Current_Y
Date greater than FY_End_Date[FY=Current_Yea
This will hide records where the date is outside of the range that equals the selection.
The key here is using a filter. So when you select the FY_Start_Date field from the formula Builder, once you click on the field name, there will be a button in the lower left of the window called filter, Select that and then select the FY and Current_Year_Select fields.
Hope that helps.
ASKER
Appreciate your response. I will try this out and post an update here
ASKER
>Are you trying to determine the current and previous years? I will assume that you are treating each one separately. - Yes
I am going to do one side but if you have current and previous separate this would be done twice, once for each.
>So you have your main calendar with records by date(Date), This would need to be queried.
you have your secondary list, which I assume three columns, also should be queried up front.
FY, FY_Start_Date, FY_End_Date - I have opened data connection to the Fiscal Year List which has all these 3 columns
> You have your drop down Current_Year_Select
> On your repeating section you would add a formatting rule for each of these. - What do you mean by each of these?
Date less than FY_Start_Date[FY=Current_Y
Date greater than FY_End_Date[FY=Current_Yea
This will hide records where the date is outside of the range that equals the selection.
>The key here is using a filter. So when you select the FY_Start_Date field from the formula Builder, once you click on the field name, there will be a button in the lower left of the window called filter, Select that and then select the FY and Current_Year_Select fields. - I am not clear on this step
Would it be easier and ofcourse if you don't mind, I can send you my Infopath 2010 form, Both the lists?
I am going to do one side but if you have current and previous separate this would be done twice, once for each.
>So you have your main calendar with records by date(Date), This would need to be queried.
you have your secondary list, which I assume three columns, also should be queried up front.
FY, FY_Start_Date, FY_End_Date - I have opened data connection to the Fiscal Year List which has all these 3 columns
> You have your drop down Current_Year_Select
> On your repeating section you would add a formatting rule for each of these. - What do you mean by each of these?
Date less than FY_Start_Date[FY=Current_Y
Date greater than FY_End_Date[FY=Current_Yea
This will hide records where the date is outside of the range that equals the selection.
>The key here is using a filter. So when you select the FY_Start_Date field from the formula Builder, once you click on the field name, there will be a button in the lower left of the window called filter, Select that and then select the FY and Current_Year_Select fields. - I am not clear on this step
Would it be easier and ofcourse if you don't mind, I can send you my Infopath 2010 form, Both the lists?
A formatting rule with the condition
Date less than FY_Start_Date[FY=Current_Y
To do this in the first drop down on the left, select Date from your main list.
Middle drop down less than
Right Dropdown select Use a Formula
In the Insert Formula Window, select Insert Field or Group
Navigate to FY_Start_Date and select it
Click the Filter Data button in the lower left
Select Add
Left Drop Down select FY
Right dropdown select "Select a Field or Group"
Navigate to the Current_Year_Select in your main data source
Click OK a bunch of times
And then the Action is check the box Hide this Control
A second formatting rule on the repeating section with the condition
Date greater than FY_End_Date[FY=Current_Yea
And then the Action is check the box Hide this Control
Date less than FY_Start_Date[FY=Current_Y
To do this in the first drop down on the left, select Date from your main list.
Middle drop down less than
Right Dropdown select Use a Formula
In the Insert Formula Window, select Insert Field or Group
Navigate to FY_Start_Date and select it
Click the Filter Data button in the lower left
Select Add
Left Drop Down select FY
Right dropdown select "Select a Field or Group"
Navigate to the Current_Year_Select in your main data source
Click OK a bunch of times
And then the Action is check the box Hide this Control
A second formatting rule on the repeating section with the condition
Date greater than FY_End_Date[FY=Current_Yea
And then the Action is check the box Hide this Control
ASKER
These steps made it bit clear but unfortunately didn't work. Is there an email I can send my files to take a look?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was able to add Filtering feature on this form using Rules.
Screenshot would be good.