[et_pb_section fb_built=”1″ fullwidth=”on” disabled_on=”on|on|off” _builder_version=”4.4.4″ z_index=”999″ custom_padding=”4px||5px||false|false” box_shadow_style=”preset3″ global_module=”3212″ saved_tabs=”all”][et_pb_fullwidth_menu menu_style=”inline_centered_logo” fullwidth_menu=”off” active_link_color=”#f1564b” dropdown_menu_bg_color=”#ffffff” dropdown_menu_line_color=”#f1564b” dropdown_menu_text_color=”#202657″ dropdown_menu_active_link_color=”#f1564b” logo=”https:\/\/wave-tsg.com\/wp-content\/uploads\/2019\/11\/WAVE-Coloured-Small.png” logo_url=”wave-tsg.com” logo_max_width=”13%” logo_max_height=”110px” show_search_icon=”on” search_icon_color=”#202657″ cart_icon_font_size=”14px” search_icon_font_size=”15px” disabled_on=”on|on|off” _builder_version=”4.0.3″ menu_font=”Arial|300||on|||||” menu_text_color=”#202657″ menu_font_size=”12px” custom_margin=”|0px||-5px|false|false” custom_padding=”|0px||0px|false|false” dropdown_menu_text_color__hover_enabled=”on|hover” dropdown_menu_text_color__hover=”#e02b20″ dropdown_menu_bg_color__hover_enabled=”on|desktop” dropdown_menu_bg_color__hover=”#ffffff” dropdown_menu_line_color__hover_enabled=”on|desktop”][\/et_pb_fullwidth_menu][\/et_pb_section][et_pb_section fb_built=”1″ admin_label=”Header” _builder_version=”4.0.3″ custom_padding=”39px||18px||false|false” locked=”off”][et_pb_row column_structure=”3_4,1_4″ use_custom_gutter=”on” gutter_width=”2″ _builder_version=”3.25″ max_width=”1280px” use_custom_width=”on” custom_width_px=”1280px”][et_pb_column type=”3_4″ _builder_version=”3.25″ custom_padding=”|||” custom_padding__hover=”|||”][et_pb_text _builder_version=”4.0.3″ _dynamic_attributes=”content” text_font=”Arial|700|||||||” text_text_color=”#202657″ text_font_size=”55px” text_line_height=”1.3em” ul_font=”||||||||” ol_font=”||||||||” header_font=”Arial|700|||||||” header_font_size=”55px” header_line_height=”1.5em” header_3_font=”||||||||” header_4_font=”||||||||” header_5_font=”||||||||” header_6_font=”||||||||” custom_padding=”||10px||false|false” text_font_size_tablet=”40px” text_font_size_phone=”30px” text_font_size_last_edited=”on|desktop” header_font_size_tablet=”40px” header_font_size_phone=”30px” header_font_size_last_edited=”on|desktop” border_color_all=”#000000″ border_style_bottom=”none” locked=”off” header_2_font_size__hover=”26px” header_2_font_size__hover_enabled=”26px” header_2_letter_spacing__hover=”0px” header_2_letter_spacing__hover_enabled=”0px” header_2_line_height__hover=”1em” header_2_line_height__hover_enabled=”1em” header_2_text_shadow_style__hover=”none” header_2_text_shadow_style__hover_enabled=”none” header_2_text_shadow_color__hover=”rgba(0,0,0,0.4)” header_2_text_shadow_color__hover_enabled=”rgba(0,0,0,0.4)”]@ET-DC@eyJkeW5hbWljIjp0cnVlLCJjb250ZW50IjoicG9zdF90aXRsZSIsInNldHRpbmdzIjp7ImJlZm9yZSI6IiIsImFmdGVyIjoiIn19@[\/et_pb_text][et_pb_image src=”https:\/\/wave-tsg.com\/wp-content\/uploads\/2020\/01\/Hor-Banner-600dpi.png” _builder_version=”4.0.3″][\/et_pb_image][\/et_pb_column][et_pb_column type=”1_4″ _builder_version=”3.25″ custom_padding=”|||” custom_padding__hover=”|||”][et_pb_blurb title=”@ET-DC@eyJkeW5hbWljIjp0cnVlLCJjb250ZW50IjoicG9zdF9hdXRob3IiLCJzZXR0aW5ncyI6eyJiZWZvcmUiOiJXcml0dGVuIGJ5ICIsImFmdGVyIjoiIiwibmFtZV9mb3JtYXQiOiJkaXNwbGF5X25hbWUiLCJsaW5rIjoib24iLCJsaW5rX2Rlc3RpbmF0aW9uIjoiYXV0aG9yX2FyY2hpdmUifX0=@” image=”@ET-DC@eyJkeW5hbWljIjp0cnVlLCJjb250ZW50IjoicG9zdF9hdXRob3JfcHJvZmlsZV9waWN0dXJlIiwic2V0dGluZ3MiOnt9fQ==@” icon_placement=”left” image_max_width=”32px” content_max_width=”1100px” _builder_version=”4.0.3″ _dynamic_attributes=”image,title” header_font=”Arial|600|||||||” header_text_align=”left” header_text_color=”#202657″ header_font_size=”18px” header_line_height=”3em” body_font=”Work Sans||||||||” text_orientation=”center” custom_margin=”|||-8px” animation=”off” border_radii_image=”on|100px|100px|100px|100px” border_color_all_image=”#864d6e” locked=”off” icon_color__hover=”#00ac69″ box_shadow_color__hover_enabled=”rgba(0,0,0,0.3)” box_shadow_color__hover=”rgba(0,0,0,0.3)” box_shadow_style__hover=”none” box_shadow_style__hover_enabled=”none” use_background_color_gradient__hover=”off” use_background_color_gradient__hover_enabled=”off” background_color_gradient_start__hover=”#2b87da” background_color_gradient_start__hover_enabled=”#2b87da” background_color_gradient_end__hover=”#29c4a9″ background_color_gradient_end__hover_enabled=”#29c4a9″ background_color_gradient_type__hover=”linear” background_color_gradient_type__hover_enabled=”linear” background_color_gradient_direction__hover=”180deg” background_color_gradient_direction__hover_enabled=”180deg” background_color_gradient_direction_radial__hover=”center” background_color_gradient_direction_radial__hover_enabled=”center” background_color_gradient_start_position__hover=”0%” background_color_gradient_start_position__hover_enabled=”0%” background_color_gradient_end_position__hover=”100%” background_color_gradient_end_position__hover_enabled=”100%” background_color_gradient_overlays_image__hover=”off” background_color_gradient_overlays_image__hover_enabled=”off” parallax__hover=”off” parallax__hover_enabled=”off” parallax_method__hover=”on” parallax_method__hover_enabled=”on” background_size__hover=”cover” background_size__hover_enabled=”cover” background_position__hover=”center” background_position__hover_enabled=”center” background_repeat__hover=”no-repeat” background_repeat__hover_enabled=”no-repeat” background_blend__hover=”normal” background_blend__hover_enabled=”normal” allow_player_pause__hover=”off” allow_player_pause__hover_enabled=”off” background_video_pause_outside_viewport__hover=”on” background_video_pause_outside_viewport__hover_enabled=”on”][\/et_pb_blurb][et_pb_blurb title=”@ET-DC@eyJkeW5hbWljIjp0cnVlLCJjb250ZW50IjoicG9zdF9kYXRlIiwic2V0dGluZ3MiOnsiYmVmb3JlIjoiIiwiYWZ0ZXIiOiIiLCJkYXRlX2Zvcm1hdCI6ImRlZmF1bHQiLCJjdXN0b21fZGF0ZV9mb3JtYXQiOiIifX0=@” use_icon=”on” font_icon=”%%128%%” icon_color=”#864d6e” icon_placement=”left” content_max_width=”1100px” use_icon_font_size=”on” icon_font_size=”16px” _builder_version=”4.0.3″ _dynamic_attributes=”title” header_font=”Arial||||||||” header_text_align=”left” header_text_color=”#202657″ header_font_size=”14px” text_orientation=”center” custom_margin=”|||” animation=”off” locked=”off” icon_color__hover=”#00ac69″ box_shadow_color__hover_enabled=”rgba(0,0,0,0.3)” box_shadow_color__hover=”rgba(0,0,0,0.3)” box_shadow_style__hover=”none” box_shadow_style__hover_enabled=”none” use_background_color_gradient__hover=”off” use_background_color_gradient__hover_enabled=”off” background_color_gradient_start__hover=”#2b87da” background_color_gradient_start__hover_enabled=”#2b87da” background_color_gradient_end__hover=”#29c4a9″ background_color_gradient_end__hover_enabled=”#29c4a9″ background_color_gradient_type__hover=”linear” background_color_gradient_type__hover_enabled=”linear” background_color_gradient_direction__hover=”180deg” background_color_gradient_direction__hover_enabled=”180deg” background_color_gradient_direction_radial__hover=”center” background_color_gradient_direction_radial__hover_enabled=”center” background_color_gradient_start_position__hover=”0%” background_color_gradient_start_position__hover_enabled=”0%” background_color_gradient_end_position__hover=”100%” background_color_gradient_end_position__hover_enabled=”100%” background_color_gradient_overlays_image__hover=”off” background_color_gradient_overlays_image__hover_enabled=”off” parallax__hover=”off” parallax__hover_enabled=”off” parallax_method__hover=”on” parallax_method__hover_enabled=”on” background_size__hover=”cover” background_size__hover_enabled=”cover” background_position__hover=”center” background_position__hover_enabled=”center” background_repeat__hover=”no-repeat” background_repeat__hover_enabled=”no-repeat” background_blend__hover=”normal” background_blend__hover_enabled=”normal” allow_player_pause__hover=”off” allow_player_pause__hover_enabled=”off” background_video_pause_outside_viewport__hover=”on” background_video_pause_outside_viewport__hover_enabled=”on” body_letter_spacing__hover=”0px” body_letter_spacing__hover_enabled=”0px” body_text_shadow_style__hover=”none” body_text_shadow_style__hover_enabled=”none” body_text_shadow_color__hover=”rgba(0,0,0,0.4)” body_text_shadow_color__hover_enabled=”rgba(0,0,0,0.4)”][\/et_pb_blurb][et_pb_blurb title=”@ET-DC@eyJkeW5hbWljIjp0cnVlLCJjb250ZW50IjoicG9zdF9jYXRlZ29yaWVzIiwic2V0dGluZ3MiOnsiYmVmb3JlIjoiIiwiYWZ0ZXIiOiIiLCJsaW5rX3RvX3Rlcm1fcGFnZSI6Im9uIiwic2VwYXJhdG9yIjoiIHwgIiwiY2F0ZWdvcnlfdHlwZSI6InBhZ2VfY2F0ZWdvcnkifX0=@” use_icon=”on” font_icon=”%%261%%” icon_color=”#864d6e” icon_placement=”left” content_max_width=”1100px” use_icon_font_size=”on” icon_font_size=”16px” _builder_version=”4.0.3″ _dynamic_attributes=”title” header_font=”Arial|600|||||||” header_text_align=”left” header_text_color=”#202657″ header_font_size=”14px” text_orientation=”center” custom_margin=”|||” animation=”off” locked=”off” icon_color__hover=”#00ac69″ box_shadow_color__hover_enabled=”rgba(0,0,0,0.3)” box_shadow_color__hover=”rgba(0,0,0,0.3)” box_shadow_style__hover=”none” box_shadow_style__hover_enabled=”none” use_background_color_gradient__hover=”off” use_background_color_gradient__hover_enabled=”off” background_color_gradient_start__hover=”#2b87da” background_color_gradient_start__hover_enabled=”#2b87da” background_color_gradient_end__hover=”#29c4a9″ background_color_gradient_end__hover_enabled=”#29c4a9″ background_color_gradient_type__hover=”linear” background_color_gradient_type__hover_enabled=”linear” background_color_gradient_direction__hover=”180deg” background_color_gradient_direction__hover_enabled=”180deg” background_color_gradient_direction_radial__hover=”center” background_color_gradient_direction_radial__hover_enabled=”center” background_color_gradient_start_position__hover=”0%” background_color_gradient_start_position__hover_enabled=”0%” background_color_gradient_end_position__hover=”100%” background_color_gradient_end_position__hover_enabled=”100%” background_color_gradient_overlays_image__hover=”off” background_color_gradient_overlays_image__hover_enabled=”off” parallax__hover=”off” parallax__hover_enabled=”off” parallax_method__hover=”on” parallax_method__hover_enabled=”on” background_size__hover=”cover” background_size__hover_enabled=”cover” background_position__hover=”center” background_position__hover_enabled=”center” background_repeat__hover=”no-repeat” background_repeat__hover_enabled=”no-repeat” background_blend__hover=”normal” background_blend__hover_enabled=”normal” allow_player_pause__hover=”off” allow_player_pause__hover_enabled=”off” background_video_pause_outside_viewport__hover=”on” background_video_pause_outside_viewport__hover_enabled=”on” body_letter_spacing__hover=”0px” body_letter_spacing__hover_enabled=”0px” body_text_shadow_style__hover=”none” body_text_shadow_style__hover_enabled=”none” body_text_shadow_color__hover=”rgba(0,0,0,0.4)” body_text_shadow_color__hover_enabled=”rgba(0,0,0,0.4)”][\/et_pb_blurb][\/et_pb_column][\/et_pb_row][\/et_pb_section][et_pb_section fb_built=”1″ use_custom_gutter=”on” gutter_width=”2″ specialty=”on” padding_1_tablet=”0px||” padding_1_last_edited=”off|desktop” admin_label=”Content” _builder_version=”4.0.3″ background_size=”contain” background_position=”top_center” inner_max_width=”1280px” custom_padding=”0px||0px||false|false” use_custom_width=”on” custom_width_px=”1280px” background_size__hover=”cover” background_size__hover_enabled=”cover” background_position__hover=”center” background_position__hover_enabled=”center” use_background_color_gradient__hover=”off” use_background_color_gradient__hover_enabled=”off” background_color_gradient_start__hover=”#2b87da” background_color_gradient_start__hover_enabled=”#2b87da” background_color_gradient_end__hover=”#29c4a9″ background_color_gradient_end__hover_enabled=”#29c4a9″ background_color_gradient_type__hover=”linear” background_color_gradient_type__hover_enabled=”linear” background_color_gradient_direction__hover=”180deg” background_color_gradient_direction__hover_enabled=”180deg” background_color_gradient_direction_radial__hover=”center” background_color_gradient_direction_radial__hover_enabled=”center” background_color_gradient_start_position__hover=”0%” background_color_gradient_start_position__hover_enabled=”0%” background_color_gradient_end_position__hover=”100%” background_color_gradient_end_position__hover_enabled=”100%” background_color_gradient_overlays_image__hover=”off” background_color_gradient_overlays_image__hover_enabled=”off” parallax__hover=”off” parallax__hover_enabled=”off” parallax_method__hover=”on” parallax_method__hover_enabled=”on” background_repeat__hover=”no-repeat” background_repeat__hover_enabled=”no-repeat” background_blend__hover=”normal” background_blend__hover_enabled=”normal” allow_player_pause__hover=”off” allow_player_pause__hover_enabled=”off” background_video_pause_outside_viewport__hover=”on” background_video_pause_outside_viewport__hover_enabled=”on”][et_pb_column type=”3_4″ specialty_columns=”3″ _builder_version=”3.25″ custom_padding=”|||” padding_tablet__hover=”0px||” padding_last_edited__hover=”off|desktop” parallax__hover=”off” parallax_method__hover=”on” padding_tablet=”0px||” padding_last_edited=”off|desktop” custom_padding__hover=”|||”][et_pb_row_inner _builder_version=”4.0.3″ custom_margin=”-25px||-34px||false|false” custom_padding=”0px||0px||false|false” locked=”off” parallax_1__hover=”off” parallax_1__hover_enabled=”off” parallax_2__hover=”off” parallax_2__hover_enabled=”off” parallax_3__hover=”off” parallax_3__hover_enabled=”off” parallax_4__hover=”off” parallax_4__hover_enabled=”off” parallax_method_1__hover=”on” parallax_method_1__hover_enabled=”on” parallax_method_2__hover=”on” parallax_method_2__hover_enabled=”on” parallax_method_3__hover=”on” parallax_method_3__hover_enabled=”on” parallax_method_4__hover=”on” parallax_method_4__hover_enabled=”on” use_background_color_gradient__hover=”off” use_background_color_gradient__hover_enabled=”off” background_color_gradient_start__hover=”#2b87da” background_color_gradient_start__hover_enabled=”#2b87da” background_color_gradient_end__hover=”#29c4a9″ background_color_gradient_end__hover_enabled=”#29c4a9″ background_color_gradient_type__hover=”linear” background_color_gradient_type__hover_enabled=”linear” background_color_gradient_direction__hover=”180deg” background_color_gradient_direction__hover_enabled=”180deg” background_color_gradient_direction_radial__hover=”center” background_color_gradient_direction_radial__hover_enabled=”center” background_color_gradient_start_position__hover=”0%” background_color_gradient_start_position__hover_enabled=”0%” background_color_gradient_end_position__hover=”100%” background_color_gradient_end_position__hover_enabled=”100%” background_color_gradient_overlays_image__hover=”off” background_color_gradient_overlays_image__hover_enabled=”off” parallax__hover=”off” parallax__hover_enabled=”off” parallax_method__hover=”on” parallax_method__hover_enabled=”on” background_size__hover=”cover” background_size__hover_enabled=”cover” background_position__hover=”center” background_position__hover_enabled=”center” background_repeat__hover=”no-repeat” background_repeat__hover_enabled=”no-repeat” background_blend__hover=”normal” background_blend__hover_enabled=”normal” allow_player_pause__hover=”off” allow_player_pause__hover_enabled=”off” background_video_pause_outside_viewport__hover=”on” background_video_pause_outside_viewport__hover_enabled=”on” use_custom_gutter__hover=”off” use_custom_gutter__hover_enabled=”off” gutter_width__hover=”3″ gutter_width__hover_enabled=”3″ make_equal__hover=”off” make_equal__hover_enabled=”off” border_radii__hover=”on||||” border_radii__hover_enabled=”on||||” box_shadow_style__hover=”none” box_shadow_style__hover_enabled=”none” box_shadow_color__hover=”rgba(0,0,0,0.3)” box_shadow_color__hover_enabled=”rgba(0,0,0,0.3)” max_width__hover=”100%” max_width__hover_enabled=”100%” filter_hue_rotate__hover=”0deg” filter_hue_rotate__hover_enabled=”0deg” filter_saturate__hover=”100%” filter_saturate__hover_enabled=”100%” filter_brightness__hover=”100%” filter_brightness__hover_enabled=”100%” filter_contrast__hover=”100%” filter_contrast__hover_enabled=”100%” filter_invert__hover=”0%” filter_invert__hover_enabled=”0%” filter_sepia__hover=”0%” filter_sepia__hover_enabled=”0%” filter_opacity__hover=”100%” filter_opacity__hover_enabled=”100%” filter_blur__hover=”0px” filter_blur__hover_enabled=”0px” mix_blend_mode__hover=”normal” mix_blend_mode__hover_enabled=”normal” animation_style__hover=”none” animation_style__hover_enabled=”none” animation_repeat__hover=”once” animation_repeat__hover_enabled=”once” animation_direction__hover=”center” animation_direction__hover_enabled=”center” animation_duration__hover=”1000ms” animation_duration__hover_enabled=”1000ms” animation_delay__hover=”0ms” animation_delay__hover_enabled=”0ms” animation_intensity_slide__hover=”50%” animation_intensity_slide__hover_enabled=”50%” animation_intensity_zoom__hover=”50%” animation_intensity_zoom__hover_enabled=”50%” animation_intensity_flip__hover=”50%” animation_intensity_flip__hover_enabled=”50%” animation_intensity_fold__hover=”50%” animation_intensity_fold__hover_enabled=”50%” animation_intensity_roll__hover=”50%” animation_intensity_roll__hover_enabled=”50%” animation_starting_opacity__hover=”0%” animation_starting_opacity__hover_enabled=”0%” animation_speed_curve__hover=”ease-in-out” animation_speed_curve__hover_enabled=”ease-in-out” hover_transition_duration__hover=”300ms” hover_transition_duration__hover_enabled=”300ms” hover_transition_delay__hover=”0ms” hover_transition_delay__hover_enabled=”0ms” hover_transition_speed_curve__hover=”ease” hover_transition_speed_curve__hover_enabled=”ease”][et_pb_column_inner saved_specialty_column_type=”3_4″ _builder_version=”3.25″ custom_padding=”|||” parallax__hover=”off” parallax__hover_enabled=”off” parallax_method__hover=”on” parallax_method__hover_enabled=”on” custom_padding__hover=”|||”][et_pb_image src=”@ET-DC@eyJkeW5hbWljIjp0cnVlLCJjb250ZW50IjoicG9zdF9mZWF0dXJlZF9pbWFnZSIsInNldHRpbmdzIjp7fX0=@” align_tablet=”center” align_phone=”” align_last_edited=”on|tablet” _builder_version=”4.0.3″ _dynamic_attributes=”src” width=”50%” width_tablet=”90%” width_phone=”89%” width_last_edited=”on|desktop” module_alignment=”center” custom_padding=”0px||0px||false|false” border_radii=”on|5px|5px|5px|5px” locked=”off”][\/et_pb_image][\/et_pb_column_inner][\/et_pb_row_inner][et_pb_row_inner _builder_version=”4.0.3″ custom_padding=”42px||0px||false|false”][et_pb_column_inner saved_specialty_column_type=”3_4″ _builder_version=”4.0.3″][et_pb_text content_tablet=”” content_phone=”” content_last_edited=”on|desktop” _builder_version=”4.5.3″ header_6_text_color=”#202657″ header_6_font_size=”23px” custom_margin=”0px||5px||false|false” custom_padding=”0px||0px||false|false”]<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.5.3″ text_font=”Arial||||||||” text_font_size=”16px”]<\/p>\n
The list view GUI in SharePoint is more than sufficient when a list owner\u2019s filtering requirements are simple and straightforward. But it is seriously lacking when it comes to tackling scenarios which require either a complex set of filters or many filters working in combination.<\/p>\n
This post describes some of the underlying functionality of SharePoint\u2019s list view GUI which contributes to its shortcomings, and it offers four suggestions of ways to overcome the challenges posed. The suggestions include ideas for everyone, including the casual SharePoint user, the power user responsible for managing the day-to-day needs of a SharePoint site, and the SharePoint administrator with some scripting chops.<\/p>\n
Don\u2019t care about the details of my experience or why the limitations exist? Are you just here to solve the bleeping problem? Skip directly to the solutions:<\/a><\/p>\n [\/et_pb_text][\/et_pb_column_inner][\/et_pb_row_inner][et_pb_row_inner column_structure=”1_2,1_2″ _builder_version=”4.5.3″ custom_padding=”2px||0px||false|false”][et_pb_column_inner type=”1_2″ saved_specialty_column_type=”3_4″ _builder_version=”4.0.3″][et_pb_text content_tablet=”” content_phone=”” content_last_edited=”on|desktop” _builder_version=”4.0.3″ header_6_text_color=”#202657″ header_6_font_size=”23px” custom_margin=”0px||5px||false|false” custom_padding=”0px||0px||false|false”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.0.3″ text_font=”Arial||||||||” text_font_size=”16px”]I recently had a customer come to me with a problem related to a library view in SharePoint. The customer had a library which contained check and electronic fund transfer (EFT) documents. His goal was to create a view that would display documents with payment dates more recent than 2018.<\/p>\n On the surface, this seemed pretty simple. But a there were a few additional factors that increased the complexity. First and foremost, check documents and EFT documents have different columns to capture the value for the date of the payment. Additionally, due to complications related to integrations with legacy systems, many (but not all) documents in the library have a value in both of the payment date columns (but that\u2019s a story for another post).[\/et_pb_text][\/et_pb_column_inner][et_pb_column_inner type=”1_2″ saved_specialty_column_type=”3_4″ _builder_version=”4.0.3″][et_pb_image src=”https:\/\/wave-tsg.com\/wp-content\/uploads\/2020\/03\/Problem.png” _builder_version=”4.0.3″ custom_padding=”29px||||false|false”][\/et_pb_image][\/et_pb_column_inner][\/et_pb_row_inner][et_pb_row_inner _builder_version=”4.0.3″ custom_padding=”18px||0px||false|false”][et_pb_column_inner saved_specialty_column_type=”3_4″ _builder_version=”4.0.3″][et_pb_text _builder_version=”4.0.3″ text_font_size=”16px”]My customer\u2019s attempt at creating the view\u2019s filters was a very logical one. He was looking for documents with the following criteria:<\/p>\n Checks with a check date later than 2018, or EFTs with a transfer date later than 2018.<\/strong><\/p>\n He created a view on the SharePoint document library with the following filters:<\/p>\n Document Type = check AND<\/strong><\/p>\n Check Date > 12\/31\/2018 OR<\/strong><\/p>\n Document Type = eft AND<\/strong><\/p>\n Transfer Date > 12\/31\/2018<\/strong><\/p>\n Being someone who is knowledgeable about math, and familiar with the order of operations, he assumed that SharePoint would perform the filters in the correct order to match his requirements \u2013 compare the results of the clauses separated by the AND operators first, then compare those results to each other using the OR operator.<\/p>\n Unfortunately, that was not the case. SharePoint only returned new EFTs, and excluded all other documents from the view, including new checks. Let\u2019s take a look at what happened.[\/et_pb_text][\/et_pb_column_inner][\/et_pb_row_inner][et_pb_row_inner _builder_version=”4.0.3″ custom_padding=”42px||0px||false|false”][et_pb_column_inner saved_specialty_column_type=”3_4″ _builder_version=”4.0.3″][et_pb_text _builder_version=”4.0.3″ header_6_text_color=”#202657″ header_6_font_size=”23px” custom_margin=”0px||5px||false|false” custom_padding=”0px||0px||false|false”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.0.3″ text_font=”Arial||||||||” text_font_size=”16px” custom_padding=”||21px||false|false”]The first thing to understand is that the filters in SharePoint\u2019s list view GUI do not respect the standard order of operations which says AND statements should be evaluated before OR statements. Furthermore, SharePoint\u2019s list view GUI does not allow for any statement grouping whatsoever. Instead, SharePoint evaluates the statements in exactly the order they are presented (top to bottom, if you\u2019re looking at them in the GUI).<\/p>\n This means that what SharePoint saw in the above filter was this:<\/p>\n ( ( ( Doc Type = check AND Check Date > 12\/31\/2018 ) OR Doc Type = eft ) AND Transfer Date > 12\/31\/2018 )<\/strong><\/p>\n Moving left to right in the above expression, SharePoint found all new checks first, added to that all EFTs, but then excluded all of the documents from that subset which didn\u2019t have a new transfer date. Since most checks do not have a transfer date value (for the most part, only EFTs have a value in this property), this last step excluded the check documents my customer was expecting to have included.<\/p>\n To generalize this topic a bit, besides the scenario my customer encountered, many people run into this issue when dealing with task lists. Trying to filter the list based on multiple statuses, dates, and users can lead to unexpected outputs and much frustration.[\/et_pb_text][et_pb_text _builder_version=”4.0.3″ header_6_text_color=”#202657″ header_6_font_size=”23px” custom_margin=”0px||5px||false|false” custom_padding=”0px||0px||false|false”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.0.3″ text_font=”Arial||||||||” text_font_size=”16px” custom_margin=”||-1px||false|false” custom_padding=”0px||0px||false|false”]The SharePoint list view GUI is not capable of incorporating parentheses to create multiple expressions which obey standard order of operation rules. Therefore, it is not capable of creating a view to correctly address any of the following expressions:<\/p>\n x AND ( y OR z )<\/strong><\/p>\n ( a OR b ) AND ( x OR y )<\/strong><\/p>\n ( a AND b ) OR ( x AND y )<\/strong><\/p>\n This has been a challenge faced by SharePoint users through many versions of the product. Any of you who have grappled with this challenge in the distant past may have found the answers you needed in a brilliant post at Nothing But SharePoint (http:\/\/www.nothingbutsharepoint.com\/2011\/08\/11\/understanding-filters-in-sharepoint-list-views-aspx\/<\/a>). But that site has moved, and the post is no longer accessible. And this issue has not been resolved in SharePoint Online or SharePoint 2019.[\/et_pb_text][\/et_pb_column_inner][\/et_pb_row_inner][et_pb_row_inner column_structure=”1_2,1_2″ module_id=”thesolution” _builder_version=”4.5.3″ custom_padding=”42px||0px||false|false”][et_pb_column_inner type=”1_2″ saved_specialty_column_type=”3_4″ _builder_version=”4.0.3″][et_pb_text _builder_version=”4.0.3″ header_6_text_color=”#202657″ header_6_font_size=”23px” custom_margin=”0px||5px||false|false” custom_padding=”0px||0px||false|false”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.0.3″ text_font=”Arial||||||||” text_font_size=”16px” custom_padding=”||21px||false|false”]Thankfully, there are several ways this challenge can be overcome. Some of them, for dealing with simpler expressions, are very easy to implement. Achieving the more complex filter combinations takes a bit more effort. The resolution methods below range from simple to moderately challenging, and from \u201cno code\u201d to \u201ccoding required\u201d.[\/et_pb_text][\/et_pb_column_inner][et_pb_column_inner type=”1_2″ saved_specialty_column_type=”3_4″ _builder_version=”4.0.3″][et_pb_image src=”https:\/\/wave-tsg.com\/wp-content\/uploads\/2020\/03\/Solution.png” _builder_version=”4.0.3″ width=”65%” module_alignment=”center” custom_margin=”-27px||||false|false” custom_padding=”0px||||false|false”][\/et_pb_image][\/et_pb_column_inner][\/et_pb_row_inner][et_pb_row_inner _builder_version=”4.0.3″ custom_padding=”0px||0px||false|false”][et_pb_column_inner saved_specialty_column_type=”3_4″ _builder_version=”4.0.3″][et_pb_text module_id=”Reorderfiltercriteria” _builder_version=”4.5.3″ header_6_text_color=”#202657″ header_6_font_size=”23px” custom_margin=”0px||5px||false|false” custom_padding=”0px||0px||false|false”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.0.3″ text_font=”Arial||||||||” text_font_size=”16px” header_6_text_color=”#202657″ header_6_font_size=”23px” custom_padding=”||21px||false|false”]<\/p>\n If you are attempting to create a view with three filter criteria with a mix of OR and AND; or if you need more than three criteria but only one comparison requires an OR operation; then you are in luck. This view can be achieved simply by reordering the criteria in the view GUI.<\/p>\n By placing the two filters separated by the OR operator first, and following that up with the filter criteria which require AND operators, SharePoint\u2019s interpretation of the filter criteria matches traditional order of operation rules.<\/p>\n Goal: x <\/em>AND (y <\/em>OR z<\/em>)<\/strong><\/p>\n Original SharePoint GUI order: x AND y OR z<\/em><\/strong><\/p>\n Rewritten SharePoint GUI order: y<\/em> OR z AND x<\/em><\/strong><\/p>\n Goal: (a<\/em> AND b<\/em>) AND (y <\/em>OR z<\/em>)<\/strong><\/p>\n Original SharePoint GUI order: a AND b AND y OR z<\/em><\/strong><\/p>\n Rewritten SharePoint GUI order: y<\/em> OR z AND a<\/em> AND b<\/em><\/strong><\/p>\n [\/et_pb_text][\/et_pb_column_inner][\/et_pb_row_inner][et_pb_row_inner _builder_version=”4.0.3″ custom_padding=”0px||0px||false|false”][et_pb_column_inner saved_specialty_column_type=”3_4″ _builder_version=”4.0.3″][et_pb_text module_id=”Rewrite” _builder_version=”4.5.3″ header_6_text_color=”#202657″ header_6_font_size=”23px” custom_margin=”0px||5px||false|false” custom_padding=”0px||0px||false|false”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.0.3″ text_font=”Arial||||||||” text_font_size=”16px” header_6_text_color=”#202657″ header_6_font_size=”23px” custom_padding=”||21px||false|false”]<\/p>\n If you have multiple filter criteria which compare a single choice property to different options in the property\u2019s list, you may be able to rewrite the comparison in way that allows you to omit a filter, and then take advantage of reordering the filters that are left, as described in Solution 1.<\/p>\n This scenario applies when your choice property has only one more option than what you are intending to include in your view.<\/p>\n Choice column Status has options for \u201cNew\u201d, \u201cIn Progress\u201d, and \u201cComplete\u201d<\/strong><\/p>\n Goal: Show list items that are \u201cNew\u201d or \u201cIn Progress\u201d<\/strong><\/p>\n Original SharePoint GUI filter: Status EQUAL TO<\/em> \u201cNew\u201d OR Status EQUAL TO<\/em> \u201cIn Progress\u201d<\/strong><\/p>\n Rewritten SharePoint GUI filter: Status NOT EQUAL TO<\/em> \u201cComplete\u201d<\/strong><\/p>\n Rewriting the comparison in this way removes the need for the OR clause, thus reducing the number of filters that you need.<\/p>\n However, this method would not work if the Status column contained a fourth option, \u201cOn Hold\u201d. In that scenario, a clause of Status NOT EQUAL TO<\/em> \u201cComplete\u201d would correctly return \u201cNew\u201d and \u201cIn Progress\u201d items, but it would also incorrectly return \u201cOn Hold\u201d items.<\/p>\n [\/et_pb_text][\/et_pb_column_inner][\/et_pb_row_inner][et_pb_row_inner _builder_version=”4.0.3″ custom_padding=”0px||0px||false|false”][et_pb_column_inner saved_specialty_column_type=”3_4″ _builder_version=”4.0.3″][et_pb_text module_id=”calculatedcolumns” _builder_version=”4.5.3″ header_6_text_color=”#202657″ header_6_font_size=”23px” custom_margin=”0px||5px||false|false” custom_padding=”0px||0px||false|false”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.0.3″ text_font=”Arial||||||||” text_font_size=”16px” header_6_text_color=”#202657″ header_6_font_size=”23px” custom_padding=”||21px||false|false”]<\/p>\n SharePoint calculated columns with nested IF statements in their calculation can be used to perform comparisons between two other fields in a list. If the simpler solutions above do not fit your situation, another option is to create new calculated columns in your list, and offload some of the comparisons from the view\u2019s filters onto the new columns.<\/p>\n Choice column Document Type has options \u201cCheck\u201d, \u201cEFT\u201d, \u201cPO Invoice\u201d, \u201cNon-PO Invoice\u201d, and many more.<\/strong><\/p>\n Choice column Status has options \u201cReceived\u201d, \u201cAwaiting Assignment\u201d, \u201cWorking\u201d, \u201cOn Hold\u201d, \u201cComplete\u201d, \u201cException\u201d<\/strong><\/p>\n Goal: Show payment documents (checks or EFTs) that can be worked on (Received, Awaiting Assignment, or Working)<\/strong><\/p>\n Original SharePoint GUI filter: Document Type EQUAL TO<\/em> \u201cCheck\u201d OR Document Type EQUAL TO<\/em> \u201cEFT\u201d AND Status EQUAL TO<\/em> \u201cReceived\u201d OR Status EQUAL TO<\/em> \u201cWorking\u201d<\/strong><\/p>\n New Yes\/No (Boolean) calculated column: \u201cIsPaymentDoc\u201d<\/strong><\/p>\n Formula: =IF([Document Type] = “Check”,1,IF([Document Type] = “EFT”,1,0))<\/strong><\/p>\n New Yes\/No (Boolean) calculated column: \u201cIsWorkable\u201d<\/strong><\/p>\n Formula: =IF([Status] = “Received”,1,IF([Status] = “Awaiting Assignment”,1,IF([Status] = “Working”,1,0)))<\/strong><\/p>\n Rewritten SharePoint GUI filter: IsPaymentDoc EQUAL TO<\/em> 1 AND IsWorkable EQUAL TO<\/em> 1<\/strong><\/p>\n By moving the two OR comparisons out of the filter and into calculated columns, the filter becomes much simpler, and can be accomplished through the SharePoint view GUI.<\/p>\n Although they do create additional overhead in the list, the presence of the new calculated columns can be made transparent to most users. The columns do not have to be displayed in any of the list\u2019s views, and by their nature, calculated columns do not appear in the default Edit Item form.<\/p>\n [\/et_pb_text][\/et_pb_column_inner][\/et_pb_row_inner][et_pb_row_inner _builder_version=”4.0.3″ custom_padding=”0px||0px||false|false”][et_pb_column_inner saved_specialty_column_type=”3_4″ _builder_version=”4.0.3″][et_pb_text module_id=”CAML” _builder_version=”4.5.3″ header_6_text_color=”#202657″ header_6_font_size=”23px” custom_margin=”0px||5px||false|false” custom_padding=”0px||0px||false|false”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.0.3″ text_font=”Arial||||||||” text_font_size=”16px” header_6_text_color=”#202657″ header_6_font_size=”23px” custom_padding=”||21px||false|false”]<\/p>\n If all else fails \u2013 the above solutions don\u2019t address your scenario, and adding calculated columns to the list isn\u2019t an option, either because of the data types in your list, or because it feels like unnecessary overhead \u2013 you can edit the code of the view\u2019s page to resolve the problem. This requires utilizing SharePoint Designer and adding some CAML code to the view. CAML (short for Collaborative Application Markup Language) is the XML-based markup language that SharePoint uses for defining views.<\/p>\n Microsoft has an introduction to CAML which can be found here: https:\/\/docs.microsoft.com\/en-us\/sharepoint\/dev\/schema\/introduction-to-collaborative-application-markup-language-caml<\/a>. It is a full overview of all the things you can do with CAML and an excellent read. But it is definitely geared toward developers. For this discussion, Microsoft\u2019s page on the CALM Query Schema is more pertinent: https:\/\/docs.microsoft.com\/en-us\/sharepoint\/dev\/schema\/query-schema<\/a>. It lists the schema elements (including operators) used to define the query in a view. It doesn\u2019t show you how to combine the elements to create a query, but it defines all of the components that you may need.<\/p>\n An in-depth tutorial on SharePoint Designer or CAML syntax is beyond the scope of this post (at least in its current state), but stay-tuned: I may add them at some point. In the meantime, both topics are well-covered by other bloggers, and can easily be located with a few simple Internet searches.<\/p>\n When you open your view in SharePoint designer, look for the section containing the view\u2019s query. It is denoted by the opening and closing Query tags: <Query><\/Query>. Insert your CAML query between these two tags and save your view.<\/p>\n Choice column Document Type has options \u201cCheck\u201d, \u201cEFT\u201d, \u201cPO Invoice\u201d, \u201cNon-PO Invoice\u201d, and many more.<\/strong><\/p>\n Choice column Status has options \u201cReceived\u201d, \u201cAwaiting Assignment\u201d, \u201cWorking\u201d, \u201cOn Hold\u201d, \u201cComplete\u201d, \u201cException\u201d<\/strong><\/p>\n Goal: Show payment documents (checks or EFTs) that can be worked on (Received, Awaiting Assignment, or Working)<\/strong><\/p>\n Original Query statement in view: <Query><\/Query><\/strong><\/p>\n Updated Query statement in view:<\/strong><\/p>\n <Query><\/strong><\/p>\n <Where><\/strong><\/p>\n <And><\/strong><\/p>\n <Or><\/strong><\/p>\n <Eq><\/strong><\/p>\n <FieldRef Name=”Document Type” \/><\/strong><\/p>\n <Value Type=”Text”>Check<\/Value><\/strong><\/p>\n <\/Eq><\/strong><\/p>\n <Eq><\/strong><\/p>\n <FieldRef Name=”Document Type” \/><\/strong><\/p>\n <Value Type=”Text”>EFT<\/Value><\/strong><\/p>\n <\/Eq><\/strong><\/p>\n <\/Or><\/strong><\/p>\n <Or><\/strong><\/p>\n <Eq><\/strong><\/p>\n <Or><\/strong><\/p>\n <Eq><\/strong><\/p>\n <FieldRef Name=”Status” \/><\/strong><\/p>\n <Value Type=”Text”>Received<\/Value><\/strong><\/p>\n <\/Eq><\/strong><\/p>\n <Eq><\/strong><\/p>\n <FieldRef Name=”Status” \/><\/strong><\/p>\n <Value Type=”Text”>Awaiting Assignment<\/Value><\/strong><\/p>\n <\/Eq><\/strong><\/p>\n <\/Or><\/strong><\/p>\n <Eq><\/strong><\/p>\n <FieldRef Name=”Status” \/><\/strong><\/p>\n <Value Type=”Text”>Working<\/Value><\/strong><\/p>\n <\/Eq><\/strong><\/p>\n <\/Eq><\/strong><\/p>\n <\/Or><\/strong><\/p>\n <\/And><\/strong><\/p>\n <\/Where><\/strong><\/p>\n <\/Query><\/strong><\/p>\n A few notes on CAML that come to light in the above query:<\/p>\n Remember that SharePoint designer has a knack for rewriting some manual changes once a page is saved, in the name of \u201ckeeping the environment safe\u201d. So be sure to test your view before publishing anything to the greater world.<\/p>\n Also, when at all possible, don\u2019t modify an out-of-the-box view. If you do so, it could subject your view to potential loss of code during future product updates if an out-of-the-box view is overwritten. If there are aspects of an out-of-the-box view that are appealing, create a copy of it and add your customizations to the copy.<\/p>\n As you can see, the above updated query is not short, and thus, not quick to write. But on the other hand, if you understand the CAML syntax, it not a complex query. Still, if it seems overwhelming to keep the logic of the query straight, there are tools that can help. \u201cU2U CAML Query Builder\u201d is a piece of free software that can gloss over some of the mystery for you. Please note, the software does have to be installed locally, and it does have to make a connection to your SharePoint environment and the list in question.<\/p>\n [\/et_pb_text][\/et_pb_column_inner][\/et_pb_row_inner][et_pb_row_inner _builder_version=”4.0.3″ custom_padding=”0px||0px||false|false”][et_pb_column_inner saved_specialty_column_type=”3_4″ _builder_version=”4.0.3″][et_pb_text _builder_version=”4.0.3″ header_6_text_color=”#202657″ header_6_font_size=”23px” custom_margin=”0px||5px||false|false” custom_padding=”0px||0px||false|false”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.0.3″ text_font=”Arial||||||||” text_font_size=”16px” header_6_text_color=”#202657″ header_6_font_size=”23px” custom_padding=”||21px||false|false”]As it turned out, the solution that worked best for my customer was number three. The values they needed to compare were contained in two choice columns, each of which had many more values than what was to be included in the filters. Thus, solutions one and two would not work. Columns of type Choice are a data type that can be used in a calculated column. They were not thrilled with the additional overhead of the calculated columns, but accepted it because it would allow a power user with no coding background to update the comparisons in the future if needed, without the need to again edit the view in SharePoint Designer.[\/et_pb_text][\/et_pb_column_inner][\/et_pb_row_inner][et_pb_row_inner _builder_version=”4.0.3″ custom_padding=”8px||||false|false”][et_pb_column_inner saved_specialty_column_type=”3_4″ _builder_version=”4.0.3″][et_pb_text _builder_version=”4.0.3″ header_6_text_color=”#202657″ header_6_font_size=”23px” custom_margin=”0px||5px||false|false” custom_padding=”0px||0px||false|false”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.0.3″ text_font=”Arial||||||||” text_font_size=”16px”]Nothing But SharePoint \u2013 Understanding filters in SharePoint List Views<\/p>\n * This site and page disappeared a while ago, but keep checking back, maybe it will be reconstituted.<\/p>\n http:\/\/www.nothingbutsharepoint.com\/2011\/08\/11\/understanding-filters-in-sharepoint-list-views-aspx\/<\/a><\/p>\n Microsoft \u2013 Introduction to Collaborative Application Markup Language (CAML)<\/p>\n https:\/\/docs.microsoft.com\/en-us\/sharepoint\/dev\/schema\/introduction-to-collaborative-application-markup-language-caml<\/a><\/p>\n Microsoft \u2013 Query schema<\/p>\n https:\/\/docs.microsoft.com\/en-us\/sharepoint\/dev\/schema\/query-schema<\/a><\/p>\n U2U CAML Query Builder<\/p>\n\n
The Problem<\/h6>\n
The Reason<\/h6>\n
Technical Issue<\/h6>\n
The Solution<\/h6>\n
1. Reorder filter criteria<\/em><\/h6>\n
\n
For example:<\/h6>\n
<\/h6>\n
<\/h6>\n
Pros:<\/h6>\n
\n
Cons:<\/h6>\n
\n
2. Rewrite comparisons to eliminate one or more filters<\/em><\/h6>\n
\n
For example:<\/h6>\n
Pros:<\/h6>\n
\n
Cons:<\/h6>\n
\n
3. Create new calculated columns to perform comparisons in order to eliminate filters<\/em><\/h6>\n
\n
For example:<\/h6>\n
Pros:<\/h6>\n
\n
<\/ul>\n<\/li>\n<\/ul>\n
Cons:<\/h6>\n
\n
4. Design your own filters using CAML by editing the view using SharePoint Designer<\/em><\/h6>\n
\n
For Example:<\/h6>\n
\n
Pros:<\/h6>\n
\n
Cons:<\/h6>\n
\n
Conclusion<\/h6>\n
Helpful Links<\/h6>\n