cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
johnjohn123
Super User
Super User

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

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

3 REPLIES 3
WarrenBelz
Super User
Super User

@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.

 

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

 

formula.png

 

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

@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.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,253)