With() is Slower?

(0) Share

Report

Posted on by iwonder

1,146

Thank you for taking the time to read my question.

I had this code for my Gallery so that I can search my Dataverse table and be able to sort it by column based on what column a user clicks on.

I thought I'd try to clean it up and used a With()... but now I get a not responding, do I want to wait for PowerApps to finish message from my browser. Why is using the With() slower than my much longer code?

Original Code:

If(
 IsBlank(varBarStartDate),
 If(
 And(
 tCol = Blank(),
 tSort = Blank()
 ),
 Search(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 )
 ),
 AssetSearch.Text,
 "cra98_assettype",
 "cra98_assetstatus",
 "cra98_assignedto",
 "cra98_category",
 "cra98_citag",
 "cra98_manufacturername",
 "cra98_notes",
 "cra98_osname",
 "cra98_productname",
 "cra98_purchasedfrom",
 "cra98_serialnumber",
 "cra98_assetsite",
 "cra98_assetstatus",
 "cra98_warrantyinfo",
 "cra98_m3assetid"
 ),
 And(
 tCol = "ProductName",
 tSort = "Asc"
 ),
 Sort(
 Search(
 Filter(
 Assets,
 Region = varUserRegion
 ),
 AssetSearch.Text,
 "cra98_assettype",
 "cra98_assetstatus",
 "cra98_assignedto",
 "cra98_category",
 "cra98_citag",
 "cra98_manufacturername",
 "cra98_notes",
 "cra98_osname",
 "cra98_productname",
 "cra98_purchasedfrom",
 "cra98_serialnumber",
 "cra98_assetsite",
 "cra98_assetstatus",
 "cra98_warrantyinfo",
 "cra98_m3assetid"
 ),
 ProductName,
 Ascending
 ),
 And(
 tCol = "ProductName",
 tSort = "Desc"
 ),
 Sort(
 Search(
 Filter(
 Assets,
 Region = varUserRegion
 ),
 AssetSearch.Text,
 "cra98_assettype",
 "cra98_assetstatus",
 "cra98_assignedto",
 "cra98_category",
 "cra98_citag",
 "cra98_manufacturername",
 "cra98_notes",
 "cra98_osname",
 "cra98_productname",
 "cra98_purchasedfrom",
 "cra98_serialnumber",
 "cra98_assetsite",
 "cra98_assetstatus",
 "cra98_warrantyinfo",
 "cra98_m3assetid"
 ),
 ProductName,
 Descending
 ),
 And(
 tCol = "CITag",
 tSort = "Asc"
 ),
 Sort(
 Search(
 Filter(
 Assets,
 Region = varUserRegion
 ),
 AssetSearch.Text,
 "cra98_assettype",
 "cra98_assetstatus",
 "cra98_assignedto",
 "cra98_category",
 "cra98_citag",
 "cra98_manufacturername",
 "cra98_notes",
 "cra98_osname",
 "cra98_productname",
 "cra98_purchasedfrom",
 "cra98_serialnumber",
 "cra98_assetsite",
 "cra98_assetstatus",
 "cra98_warrantyinfo",
 "cra98_m3assetid"
 ),
 CITag,
 Ascending
 ),
 And(
 tCol = "CITag",
 tSort = "Desc"
 ),
 Sort(
 Search(
 Filter(
 Assets,
 Region = varUserRegion
 ),
 AssetSearch.Text,
 "cra98_assettype",
 "cra98_assetstatus",
 "cra98_assignedto",
 "cra98_category",
 "cra98_citag",
 "cra98_manufacturername",
 "cra98_notes",
 "cra98_osname",
 "cra98_productname",
 "cra98_purchasedfrom",
 "cra98_serialnumber",
 "cra98_assetsite",
 "cra98_assetstatus",
 "cra98_warrantyinfo",
 "cra98_m3assetid"
 ),
 CITag,
 Descending
 ),
 And(
 tCol = "M3AssetNum",
 tSort = "Asc"
 ),
 Sort(
 Search(
 Filter(
 Assets,
 Region = varUserRegion
 ),
 AssetSearch.Text,
 "cra98_assettype",
 "cra98_assetstatus",
 "cra98_assignedto",
 "cra98_category",
 "cra98_citag",
 "cra98_manufacturername",
 "cra98_notes",
 "cra98_osname",
 "cra98_productname",
 "cra98_purchasedfrom",
 "cra98_serialnumber",
 "cra98_assetsite",
 "cra98_assetstatus",
 "cra98_warrantyinfo",
 "cra98_m3assetid"
 ),
 M3AssetID,
 Ascending
 ),
 And(
 tCol = "M3AssetNum",
 tSort = "Desc"
 ),
 Sort(
 Search(
 Filter(
 Assets,
 Region = varUserRegion
 ),
 AssetSearch.Text,
 "cra98_assettype",
 "cra98_assetstatus",
 "cra98_assignedto",
 "cra98_category",
 "cra98_citag",
 "cra98_manufacturername",
 "cra98_notes",
 "cra98_osname",
 "cra98_productname",
 "cra98_purchasedfrom",
 "cra98_serialnumber",
 "cra98_assetsite",
 "cra98_assetstatus",
 "cra98_warrantyinfo",
 "cra98_m3assetid"
 ),
 M3AssetID,
 Descending
 ),
 And(
 tCol = "Site",
 tSort = "Asc"
 ),
 Sort(
 Search(
 Filter(
 Assets,
 Region = varUserRegion
 ),
 AssetSearch.Text,
 "cra98_assettype",
 "cra98_assetstatus",
 "cra98_assignedto",
 "cra98_category",
 "cra98_citag",
 "cra98_manufacturername",
 "cra98_notes",
 "cra98_osname",
 "cra98_productname",
 "cra98_purchasedfrom",
 "cra98_serialnumber",
 "cra98_assetsite",
 "cra98_assetstatus",
 "cra98_warrantyinfo",
 "cra98_m3assetid"
 ),
 AssetSite,
 Ascending
 ),
 And(
 tCol = "Site",
 tSort = "Desc"
 ),
 Sort(
 Search(
 Filter(
 Assets,
 Region = varUserRegion
 ),
 AssetSearch.Text,
 "cra98_assettype",
 "cra98_assetstatus",
 "cra98_assignedto",
 "cra98_category",
 "cra98_citag",
 "cra98_manufacturername",
 "cra98_notes",
 "cra98_osname",
 "cra98_productname",
 "cra98_purchasedfrom",
 "cra98_serialnumber",
 "cra98_assetsite",
 "cra98_assetstatus",
 "cra98_warrantyinfo",
 "cra98_m3assetid"
 ),
 AssetSite,
 Descending
 ),
 And(
 tCol = "AssignedTo",
 tSort = "Asc"
 ),
 Sort(
 Search(
 Filter(
 Assets,
 Region = varUserRegion
 ),
 AssetSearch.Text,
 "cra98_assettype",
 "cra98_assetstatus",
 "cra98_assignedto",
 "cra98_category",
 "cra98_citag",
 "cra98_manufacturername",
 "cra98_notes",
 "cra98_osname",
 "cra98_productname",
 "cra98_purchasedfrom",
 "cra98_serialnumber",
 "cra98_assetsite",
 "cra98_assetstatus",
 "cra98_warrantyinfo",
 "cra98_m3assetid"
 ),
 AssignedTo,
 Ascending
 ),
 And(
 tCol = "AssignedTo",
 tSort = "Desc"
 ),
 Sort(
 Search(
 Filter(
 Assets,
 Region = varUserRegion
 ),
 AssetSearch.Text,
 "cra98_assettype",
 "cra98_assetstatus",
 "cra98_assignedto",
 "cra98_category",
 "cra98_citag",
 "cra98_manufacturername",
 "cra98_notes",
 "cra98_osname",
 "cra98_productname",
 "cra98_purchasedfrom",
 "cra98_serialnumber",
 "cra98_assetsite",
 "cra98_assetstatus",
 "cra98_warrantyinfo",
 "cra98_m3assetid"
 ),
 AssignedTo,
 Descending
 ),
 And(
 tCol = "AssignedStatus",
 tSort = "Asc"
 ),
 Sort(
 Search(
 Filter(
 Assets,
 Region = varUserRegion
 ),
 AssetSearch.Text,
 "cra98_assettype",
 "cra98_assetstatus",
 "cra98_assignedto",
 "cra98_category",
 "cra98_citag",
 "cra98_manufacturername",
 "cra98_notes",
 "cra98_osname",
 "cra98_productname",
 "cra98_purchasedfrom",
 "cra98_serialnumber",
 "cra98_assetsite",
 "cra98_assetstatus",
 "cra98_warrantyinfo",
 "cra98_m3assetid"
 ),
 AssetStatus,
 Ascending
 ),
 And(
 tCol = "AssignedStatus",
 tSort = "Desc"
 ),
 Sort(
 Search(
 Filter(
 Assets,
 Region = varUserRegion
 ),
 AssetSearch.Text,
 "cra98_assettype",
 "cra98_assetstatus",
 "cra98_assignedto",
 "cra98_category",
 "cra98_citag",
 "cra98_manufacturername",
 "cra98_notes",
 "cra98_osname",
 "cra98_productname",
 "cra98_purchasedfrom",
 "cra98_serialnumber",
 "cra98_assetsite",
 "cra98_assetstatus",
 "cra98_warrantyinfo",
 "cra98_m3assetid"
 ),
 AssetStatus,
 Descending
 ),
 And(
 tCol = "Category",
 tSort = "Asc"
 ),
 Sort(
 Search(
 Filter(
 Assets,
 Region = varUserRegion
 ),
 AssetSearch.Text,
 "cra98_assettype",
 "cra98_assetstatus",
 "cra98_assignedto",
 "cra98_category",
 "cra98_citag",
 "cra98_manufacturername",
 "cra98_notes",
 "cra98_osname",
 "cra98_productname",
 "cra98_purchasedfrom",
 "cra98_serialnumber",
 "cra98_assetsite",
 "cra98_assetstatus",
 "cra98_warrantyinfo",
 "cra98_m3assetid"
 ),
 Category,
 Ascending
 ),
 And(
 tCol = "Category",
 tSort = "Desc"
 ),
 Sort(
 Search(
 Filter(
 Assets,
 Region = varUserRegion
 ),
 AssetSearch.Text,
 "cra98_assettype",
 "cra98_assetstatus",
 "cra98_assignedto",
 "cra98_category",
 "cra98_citag",
 "cra98_manufacturername",
 "cra98_notes",
 "cra98_osname",
 "cra98_productname",
 "cra98_purchasedfrom",
 "cra98_serialnumber",
 "cra98_assetsite",
 "cra98_assetstatus",
 "cra98_warrantyinfo",
 "cra98_m3assetid"
 ),
 Category,
 Descending
 ),
 And(
 tCol = "WarrExp",
 tSort = "Asc"
 ),
 Sort(
 Search(
 Filter(
 Assets,
 Region = varUserRegion
 ),
 AssetSearch.Text,
 "cra98_assettype",
 "cra98_assetstatus",
 "cra98_assignedto",
 "cra98_category",
 "cra98_citag",
 "cra98_manufacturername",
 "cra98_notes",
 "cra98_osname",
 "cra98_productname",
 "cra98_purchasedfrom",
 "cra98_serialnumber",
 "cra98_assetsite",
 "cra98_assetstatus",
 "cra98_warrantyinfo",
 "cra98_m3assetid"
 ),
 WarrantyExpireDate,
 Ascending
 ),
 And(
 tCol = "WarrExp",
 tSort = "Desc"
 ),
 Sort(
 Search(
 Filter(
 Assets,
 Region = varUserRegion
 ),
 AssetSearch.Text,
 "cra98_assettype",
 "cra98_assetstatus",
 "cra98_assignedto",
 "cra98_category",
 "cra98_citag",
 "cra98_manufacturername",
 "cra98_notes",
 "cra98_osname",
 "cra98_productname",
 "cra98_purchasedfrom",
 "cra98_serialnumber",
 "cra98_assetsite",
 "cra98_assetstatus",
 "cra98_warrantyinfo",
 "cra98_m3assetid"
 ),
 WarrantyExpireDate,
 Descending
 )
 ),//After here then filter from the column chart
 If(
 And(
 tCol = Blank(),
 tSort = Blank()
 ),
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 And(
 tCol = "ProductName",
 tSort = "Asc"
 ),
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 And(
 tCol = "ProductName",
 tSort = "Desc"
 ),
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 And(
 tCol = "CITag",
 tSort = "Asc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 CITag,
 Ascending
 ),
 And(
 tCol = "CITag",
 tSort = "Desc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 CITag,
 Descending
 ),
 And(
 tCol = "M3AssetNum",
 tSort = "Asc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 M3AssetID,
 Ascending
 ),
 And(
 tCol = "M3AssetNum",
 tSort = "Desc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 M3AssetID,
 Descending
 ),
 And(
 tCol = "Site",
 tSort = "Asc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 AssetSite,
 Ascending
 ),
 And(
 tCol = "Site",
 tSort = "Desc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 AssetSite,
 Descending
 ),
 And(
 tCol = "AssignedTo",
 tSort = "Asc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 AssignedTo,
 Ascending
 ),
 And(
 tCol = "AssignedTo",
 tSort = "Desc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 AssignedTo,
 Descending
 ),
 And(
 tCol = "AssignedStatus",
 tSort = "Asc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 AssetStatus,
 Ascending
 ),
 And(
 tCol = "AssignedStatus",
 tSort = "Desc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 AssetStatus,
 Descending
 ),
 And(
 tCol = "Category",
 tSort = "Asc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 Category,
 Ascending
 ),
 And(
 tCol = "Category",
 tSort = "Desc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 Category,
 Descending
 ),
 And(
 tCol = "WarrExp",
 tSort = "Asc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 WarrantyExpireDate,
 Ascending
 ),
 And(
 tCol = "WarrExp",
 tSort = "Desc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 WarrantyExpireDate,
 Descending
 )
 )
)

New code using With()

With(
 {
 SearchData: Search(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 )
 ),
 AssetSearch.Text,
 "cra98_assettype",
 "cra98_assetstatus",
 "cra98_assignedto",
 "cra98_category",
 "cra98_citag",
 "cra98_manufacturername",
 "cra98_notes",
 "cra98_osname",
 "cra98_productname",
 "cra98_purchasedfrom",
 "cra98_serialnumber",
 "cra98_assetsite",
 "cra98_assetstatus",
 "cra98_warrantyinfo",
 "cra98_m3assetid"
 )
 },
 If(
 IsBlank(varBarStartDate),
 If(
 And(
 tCol = Blank(),
 tSort = Blank()
 ),
 SearchData,
 And(
 tCol = "ProductName",
 tSort = "Asc"
 ),
 Sort(
 SearchData,
 ProductName,
 Ascending
 ),
 And(
 tCol = "ProductName",
 tSort = "Desc"
 ),
 Sort(
 SearchData,
 ProductName,
 Descending
 ),
 And(
 tCol = "CITag",
 tSort = "Asc"
 ),
 Sort(
 SearchData,
 CITag,
 Ascending
 ),
 And(
 tCol = "CITag",
 tSort = "Desc"
 ),
 Sort(
 SearchData,
 CITag,
 Descending
 ),
 And(
 tCol = "M3AssetNum",
 tSort = "Asc"
 ),
 Sort(
 SearchData,
 M3AssetID,
 Ascending
 ),
 And(
 tCol = "M3AssetNum",
 tSort = "Desc"
 ),
 Sort(
 SearchData,
 M3AssetID,
 Descending
 ),
 And(
 tCol = "Site",
 tSort = "Asc"
 ),
 Sort(
 SearchData,
 AssetSite,
 Ascending
 ),
 And(
 tCol = "Site",
 tSort = "Desc"
 ),
 Sort(
 SearchData,
 AssetSite,
 Descending
 ),
 And(
 tCol = "AssignedTo",
 tSort = "Asc"
 ),
 Sort(
 SearchData,
 AssignedTo,
 Ascending
 ),
 And(
 tCol = "AssignedTo",
 tSort = "Desc"
 ),
 Sort(
 SearchData,
 AssignedTo,
 Descending
 ),
 And(
 tCol = "AssignedStatus",
 tSort = "Asc"
 ),
 Sort(
 SearchData,
 AssetStatus,
 Ascending
 ),
 And(
 tCol = "AssignedStatus",
 tSort = "Desc"
 ),
 Sort(
 SearchData,
 AssetStatus,
 Descending
 ),
 And(
 tCol = "Category",
 tSort = "Asc"
 ),
 Sort(
 SearchData,
 Category,
 Ascending
 ),
 And(
 tCol = "Category",
 tSort = "Desc"
 ),
 Sort(
 SearchData,
 Category,
 Descending
 ),
 And(
 tCol = "WarrExp",
 tSort = "Asc"
 ),
 Sort(
 SearchData,
 WarrantyExpireDate,
 Ascending
 ),
 And(
 tCol = "WarrExp",
 tSort = "Desc"
 ),
 Sort(
 SearchData,
 WarrantyExpireDate,
 Descending
 )
 ),//After here then filter from the column chart
 If(
 And(
 tCol = Blank(),
 tSort = Blank()
 ),
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 And(
 tCol = "ProductName",
 tSort = "Asc"
 ),
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 And(
 tCol = "ProductName",
 tSort = "Desc"
 ),
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 And(
 tCol = "CITag",
 tSort = "Asc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 CITag,
 Ascending
 ),
 And(
 tCol = "CITag",
 tSort = "Desc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 CITag,
 Descending
 ),
 And(
 tCol = "M3AssetNum",
 tSort = "Asc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 M3AssetID,
 Ascending
 ),
 And(
 tCol = "M3AssetNum",
 tSort = "Desc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 M3AssetID,
 Descending
 ),
 And(
 tCol = "Site",
 tSort = "Asc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 AssetSite,
 Ascending
 ),
 And(
 tCol = "Site",
 tSort = "Desc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 AssetSite,
 Descending
 ),
 And(
 tCol = "AssignedTo",
 tSort = "Asc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 AssignedTo,
 Ascending
 ),
 And(
 tCol = "AssignedTo",
 tSort = "Desc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 AssignedTo,
 Descending
 ),
 And(
 tCol = "AssignedStatus",
 tSort = "Asc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 AssetStatus,
 Ascending
 ),
 And(
 tCol = "AssignedStatus",
 tSort = "Desc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 AssetStatus,
 Descending
 ),
 And(
 tCol = "Category",
 tSort = "Asc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 Category,
 Ascending
 ),
 And(
 tCol = "Category",
 tSort = "Desc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 Category,
 Descending
 ),
 And(
 tCol = "WarrExp",
 tSort = "Asc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 WarrantyExpireDate,
 Ascending
 ),
 And(
 tCol = "WarrExp",
 tSort = "Desc"
 ),
 Sort(
 Filter(
 Assets,
 Region in Split(
 varUserRegion,
 ","
 ),
 AssetType = varBarType,
 WarrantyExpireDate >= varBarStartDate,
 WarrantyExpireDate <= varBarEndDate
 ),
 WarrantyExpireDate,
 Descending
 )
 )
 )
)

I am fixing it up so its easier to read but also, so that I can add one more search, on a date field... not sure if that's possible

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@iwonder
Oh My!!

What is slowest is the amount of time you are spending typing all that in! You are repeating things over and over and over and PowerApps will need to evaluate over and over and over.

You are missing one critical function - SortByColumns!

With that function, you can use your variables to specify the sort column and direction both. So there is NO need to make so many If conditions and then Sort statements.

Your entire formula can be replaced with the following:

With( { SearchData: Search( Filter(Assets, Region in Split(varUserRegion, ",") && (IsBlank(varBarStartDate) || WarrantyExpireDate >= varBarStartDate) && (IsBlank(varBarEndDate) || WarrantyExpireDate <= varBarEndDate) && (IsBlank(varBarType) || AssetType = varBarType), ), AssetSearch.Text, "cra98_assettype", "cra98_assetstatus", "cra98_assignedto", "cra98_category", "cra98_citag", "cra98_manufacturername", "cra98_notes", "cra98_osname", "cra98_productname", "cra98_purchasedfrom", "cra98_serialnumber", "cra98_assetsite", "cra98_assetstatus", "cra98_warrantyinfo", "cra98_m3assetid" ); _order: Switch(tSort, "Asc", "Ascending", "Desending"); _col: Switch(tCol, "ProductName", "ProductName", "CITag", "CITag", "M3AssetNum", "M3AssetID", "Site", "AssetSite", "AssignedTo", "AssignedTo", "AssignedStatus", "AssetStatus", "Category", "Category", "WarrExp", "WarrantyExpireDate", <defaultColumnNameHere> ) }, SortByColumns(SearchData, _col, Coalesce(tSort, <defaultOrder>) ) )

Ideally, you can eliminate even more by simply setting the tSort variable to be either "Ascending" or "Descending" in your other formulas. And also, tCol, to just be the real name of the column you want to sort on.

You should have only ONE filter function in your formula. It should specify the criteria in a Boolean way to produce either true or false results per record. This will eliminate the multiple filters and If's for the conditions you have.

Note: for the scenario where the variables are blank, specify the default column names and default sort order in the locations in the formula above.

Also, as mentioned, you will need the real column names for the Sort to work properly - not the Display Names.

I hope this is helpful for you.

Was this reply helpful? Yes No
iwonder 1,146 on at

Like (0)

Report
Copy link

Link copied!
Hi @RandyHayes

I have no idea why I did not think SortByColumns()... Thank you

2 questions:

1. I don't understand what is happening in this part
(IsBlank(varBarStartDate) || WarrantyExpireDate >= varBarStartDate) && (IsBlank(varBarEndDate) || WarrantyExpireDate <= varBarEndDate) && (IsBlank(varBarType) || AssetType = varBarType)

I would like to filter my gallery by Region because I need to limit who can see what. That is why I have the Split(). After that, I want to use Search() to find records in the filtered dataset so that users don't need an exact match. They type these values into a TextInput called AssetSearch. Right now I am only able to search on Text values. If I type in a date value... something like 01/27/2022 the search does not work. Maybe that part goes in the Filter instead of Search and I use Match() to find out if a date has been entered? Or maybe I need to add 2 date pickers, otherwise I need to parse out 2 dates from a string somehow...

The other way to limit what is shown in the gallery is for the user to look at a Column Chart. They click on a column which brings up a gallery of all the different item types for that column. They can then select one which sets 3 variables:
Set(varBarStartDate,Date(Left(ThisItem._rYearMonth,4),Mid(ThisItem._rYearMonth,5,2),1)); Set(varBarEndDate,Date(Left(ThisItem._rYearMonth,4),Value(Mid(ThisItem._rYearMonth,5,2))+1,0)); Set(varBarType,ThisItem._rAssetType);
The user is then taken to the main Assets screen and I want to filter my Gallery on these values (auto populate the search criteria). The values are put into the AssetSearch Input box so the user can see what the filter value is but I want to act on them independently as there is more than one value / column being searched. That's why my original code checked if IsBlank(varBarStartDate). If it's blank, then the users is not coming from the Column Chart, but rather is using AssetSearch to find records.

2. I am able to sort Ascending on any column, but Descending does not work. Here is the code as I have implemented it and I'm not sure why Descending is not working:
With( { SearchData: Search( Filter( Assets, Region in Split( varUserRegion, "," ) && (IsBlank(varBarStartDate) || WarrantyExpireDate >= varBarStartDate) && (IsBlank(varBarEndDate) || WarrantyExpireDate <= varBarEndDate) && (IsBlank(varBarType) || AssetType = varBarType) ), AssetSearch.Text, "cra98_assettype", "cra98_assetstatus", "cra98_assignedto", "cra98_category", "cra98_citag", "cra98_manufacturername", "cra98_notes", "cra98_osname", "cra98_productname", "cra98_purchasedfrom", "cra98_serialnumber", "cra98_assetsite", "cra98_assetstatus", "cra98_warrantyinfo", "cra98_m3assetid" ), _order: Switch( tSort, "Asc", "Ascending", "Descending" ), _col: Switch( tCol, "ProductName", "cra98_productname", "CITag", "cra98_citag", "M3AssetNum", "cra98_m3assetid", "Site", "cra98_assetsite", "AssignedTo", "cra98_assignedto", "AssignedStatus", "cra98_assetstatus", "Category", "cra98_category", "WarrExp", "WarrantyExpireDate", "cra98_productname" ) }, SortByColumns( SearchData, _col, Coalesce( tSort, Ascending ) ) )

Thanks again for all your help. I learn so much from you and am truly greatful.

Was this reply helpful? Yes No
iwonder 1,146 on at

Like (0)

Report
Copy link

Link copied!
Hi @RandyHayes

This part finally clicked this morning... I blame Friday afternoon...

(IsBlank(varBarStartDate) || WarrantyExpireDate >= varBarStartDate) && (IsBlank(varBarEndDate) || WarrantyExpireDate <= varBarEndDate) && (IsBlank(varBarType) || AssetType = varBarType)

and I am now able to sort Descending as well.

I also fixed up my code so that it uses the proper field names and Ascending and Descending.

With( { SearchData: Search( Filter( Assets, Region in Split( varUserRegion, "," ) && (IsBlank(varBarStartDate) || WarrantyExpireDate >= varBarStartDate) && (IsBlank(varBarEndDate) || WarrantyExpireDate <= varBarEndDate) && (IsBlank(varBarType) || AssetType = varBarType) //This checks to see if the variables are blank or not. If they're not then they're a part of the filter ), AssetSearch.Text, "cra98_assettype", "cra98_assetstatus", "cra98_assignedto", "cra98_category", "cra98_citag", "cra98_manufacturername", "cra98_notes", "cra98_osname", "cra98_productname", "cra98_purchasedfrom", "cra98_serialnumber", "cra98_assetsite", "cra98_warrantyinfo", "cra98_m3assetid" ) }, SortByColumns( SearchData, tCol, Coalesce( tSort, Ascending ) ) )

thanks

Was this reply helpful? Yes No
iwonder 1,146 on at

Like (0)

Report
Copy link

Link copied!
@RandyHayes

In the end I had to change it to this so that I could filter using the Input Box AssetSearch OR search using variables populated when a user clicks on a Gallery item on a different screen

With( { SearchData: Filter( Assets, Region in Split( varUserRegion, "," ) && (IsBlank(varBarStartDate) || WarrantyExpireDate >= varBarStartDate) && (IsBlank(varBarEndDate) || WarrantyExpireDate <= varBarEndDate) && (IsBlank(varBarType) || AssetType = varBarType) //This checks to see if the variables are blank or not. If they're not then they're a part of the filter ) }, SortByColumns( If(IsBlank(varBarStartDate),Search(SearchData,AssetSearch.Text, "cra98_assettype", "cra98_assetstatus", "cra98_assignedto", "cra98_category", "cra98_citag", "cra98_manufacturername", "cra98_notes", "cra98_osname", "cra98_productname", "cra98_purchasedfrom", "cra98_serialnumber", "cra98_assetsite", "cra98_warrantyinfo", "cra98_m3assetid" ),SearchData), tCol, Coalesce( tSort, Ascending ) ) )

Was this reply helpful? Yes No
iwonder 1,146 on at

Like (0)

Report
Copy link

Link copied!

Hi @RandyHayes

I'm continuing to test and it seems my original formula without With() works better than the formula you suggested. It seems I hit a delegation issue with your formula and I don't with my original formula.

With my original formula I can search my Dataverse table and return the expected records (8 records). With the formula you suggested using the same search term I only get 1... because it's in the first 500 records. My formula is not slow either....

I do like your formula as it's more compact, so how can I make it work as well as my LONG formula?

Thanks again for all your help and time.

Was this reply helpful? Yes No
iwonder 1,146 on at

Like (0)

Report
Copy link

Link copied!
Hi @RandyHayes

This is much shorter thanks to you... again.

This still responds as I expect with 8 records in my test search and is shorter than the original and doesn't seem to have a delegation issue.

If( IsBlank(varBarStartDate), If( And( tCol = Blank(), tSort = Blank() ), Search( Filter( Assets, Region in Split( varUserRegion, "," ) ), AssetSearch.Text, "cra98_assettype", "cra98_assetstatus", "cra98_assignedto", "cra98_category", "cra98_citag", "cra98_manufacturername", "cra98_notes", "cra98_osname", "cra98_productname", "cra98_purchasedfrom", "cra98_serialnumber", "cra98_assetsite", "cra98_m3assetid", "cra98_warrantyinfo", "cra98_m3assetid" ), With({tData: Search( Filter( Assets, Region in Split( varUserRegion, "," ) ), AssetSearch.Text, "cra98_assettype", "cra98_assetstatus", "cra98_assignedto", "cra98_category", "cra98_citag", "cra98_manufacturername", "cra98_notes", "cra98_osname", "cra98_productname", "cra98_purchasedfrom", "cra98_serialnumber", "cra98_assetsite", "cra98_m3assetid", "cra98_warrantyinfo", "cra98_m3assetid" )},SortByColumns(tData,tCol,tSort ))),//After here then filter from the column chart If( And( tCol = Blank(), tSort = Blank() ), Filter( Assets, Region in Split( varUserRegion, "," ), AssetType = varBarType, WarrantyExpireDate >= varBarStartDate, WarrantyExpireDate <= varBarEndDate ), With({tData:Filter( Assets, Region in Split( varUserRegion, "," ), AssetType = varBarType, WarrantyExpireDate >= varBarStartDate, WarrantyExpireDate <= varBarEndDate )},SortByColumns(tData,tCol,tSort) )) )

Thoughts?

Thanks again

Was this reply helpful? Yes No