How i can only show items inside a canvase which have been created in the last 90 days

[johnjohn123]

I have the following formula, which shows items inside a canvase:-

FirstN(
 Switch(
    filterList,
    "byAllItems",
    Sort(
        Filter(
        '[dbo].[ItemList_View]',
        itemOrder < MinIdItem
        ),
        createdDate,
        Descending
    ),
    "byMatchItem",
    Sort(
        If( matchHasSubCat = 1
            ,//Filter with type, brand and color
            Filter(
            '[dbo].[ItemList_View]',
            id_Category = matchIdCategory And typeItem = "Found" And status = "Opened item" 
            And id_Type  = matchIdType 
            //And id_Brand = matchIdBrand And id_Color = matchIdColor
            And itemOrder < MinIdItem
            )
            ,//else filter only base on category
            Filter(
            '[dbo].[ItemList_View]',
            id_Category = matchIdCategory And typeItem = "Found" And status = "Opened item" 
            And itemOrder < MinIdItem
            )
        ),
        createdDate,
        Descending
    ),
    "byOpenedFoundItem",
    Sort(
        Filter(
        '[dbo].[ItemList_View]',
        typeItem = "Found" And status = "Opened item"
        And itemOrder < MinIdItem
        )
        ,
        createdDate,
        Descending
    ),
    "byClosedFoundItem",
    Sort(
        Filter(
        '[dbo].[ItemList_View]',
        typeItem = "Found" And status = "Closed"
        And itemOrder < MinIdItem
        ),
        createdDate,
        Descending
    ),
    "byTransitFoundItem",
    Sort(
        Filter(
        '[dbo].[ItemList_View]',
        status = "In transit"
        And itemOrder < MinIdItem
        ),
        createdDate,
        Descending
    ),
    "byArchivedFoundItem",
    Sort(
        Filter(
        '[dbo].[ItemList_View]',
        status = "Archived"
        And itemOrder < MinIdItem
        ),
        createdDate,
        Descending
    ),
    // Only found items have the status of "In transit" and "Archived"
    "byCmbChoice",
        Filter(filterListByItems, itemOrder < MinIdItem)
    ,
    "bySearch",
    advancedSearch
  )
  ,// Restrict number of item in page
  NB_ITEM_BY_PAGE
)

but how i can modify it so it only shows items created in the past 90 days only?

thanks

[WarrenBelz]

@johnjohn123 ,

Note this is free-typed and is syntax guidance (so please watch commas/brackets etc). It is also using the values you posted, so I am not sure where a couple might belong.

FirstN(
   Sort(
      Filter(
         '[dbo].[ItemList_View]',
         DateDiff(
            Created,
            Today(),
            Days
         ) <=90 &&
	     Switch(
            filterList,
            "byAllItems",
            itemOrder < MinIdItem,
            "byMatchItem",
            id_Category = matchIdCategory && typeItem = "Found" && 
            status = "Opened item"  && itemOrder < MinIdItem &&
            If( 
               matchHasSubCat = 1,
               id_Type  = matchIdType
            ),
            "byOpenedFoundItem",
            typeItem = "Found" && status = "Opened item" && itemOrder < MinIdItem,
            "byClosedFoundItem", 
            typeItem = "Found" && status = "Closed" && itemOrder < MinIdItem,
            "byTransitFoundItem",
            status = "In transit" && itemOrder < MinIdItem,
            "byArchivedFoundItem",
            status = "Archived" && itemOrder < MinIdItem,
            "byCmbChoice",
            itemOrder < MinIdItem,
            "bySearch",
            advancedSearch
         )
      ),
      createddate,
      Descending
   ),
    NB_ITEM_BY_PAGE
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[johnjohn123]

@WarrenBelzi tried to add the formula inline as follow, but i got delegation warning:-

so is this normal? now the canvase contain around 12000 items.. and after i added my filter the canvase will not return any item!!

[WarrenBelz]

@johnjohn123 ,

Try this

With(
   {
      wDate:
      DateAdd(
         Today(),
         -90,
         Days
      )
   },  
   FirstN(
      Sort(
         Filter(
            '[dbo].[ItemList_View]',
            Created >=wDate &&
            Switch(
               filterList,
               "byAllItems",
               itemOrder < MinIdItem,
               "byMatchItem",
               id_Category = matchIdCategory && typeItem = "Found" && 
               status = "Opened item"  && itemOrder < MinIdItem &&
               If( 
                  matchHasSubCat = 1,
                  id_Type  = matchIdType
               ),
               "byOpenedFoundItem",
               typeItem = "Found" && status = "Opened item" && itemOrder < MinIdItem,
               "byClosedFoundItem", 
               typeItem = "Found" && status = "Closed" && itemOrder < MinIdItem,
               "byTransitFoundItem",
               status = "In transit" && itemOrder < MinIdItem,
               "byArchivedFoundItem",
               status = "Archived" && itemOrder < MinIdItem,
               "byCmbChoice",
               itemOrder < MinIdItem,
               "bySearch",
               advancedSearch
            )
         ),
         createddate,
         Descending
      ),
      NB_ITEM_BY_PAGE
   )
)

or this

With(
   {
      wList:
      FirstN(
         Sort(
            Filter(
               '[dbo].[ItemList_View]',
               Switch(
                  filterList,
                  "byAllItems",
                  itemOrder < MinIdItem,
                  "byMatchItem",
                  id_Category = matchIdCategory && typeItem = "Found" && 
                  status = "Opened item"  && itemOrder < MinIdItem &&
                  If( 
                     matchHasSubCat = 1,
                     id_Type  = matchIdType
                  ),
                  "byOpenedFoundItem",
                  typeItem = "Found" && status = "Opened item" && itemOrder < MinIdItem,
                  "byClosedFoundItem", 
                  typeItem = "Found" && status = "Closed" && itemOrder < MinIdItem,
                  "byTransitFoundItem",
                  status = "In transit" && itemOrder < MinIdItem,
                  "byArchivedFoundItem",
                  status = "Archived" && itemOrder < MinIdItem,
                  "byCmbChoice",
                  itemOrder < MinIdItem,
                  "bySearch",
                  advancedSearch
               )
            ),
            createddate,
            Descending
         ),
         NB_ITEM_BY_PAGE
      )
   },
   Filter(
      wList,
      DateDiff(
         Created,
         Today(),
         Days
      ) <=90
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.