Multiple search box for 30 different columns

[Nathan_m1]

Hi All, 

        I need multiple search boxes for 30 different columns where I have used gallery control and within the Gallery Items the following code is present:

SortByColumns (
Filter(
Normalizations,
Search(Normalizations,'Person.txt'.Text,'Employee Full Name'),
StartsWith(
'Employee Full Name',
txtsearch.Text

),
drp_Project.SelectedText.Value = "All" || Project = drp_Project.SelectedText.Value,
drp_Department.SelectedText.Value = "All" || Department = drp_Department.SelectedText.Value,
drp_Sbu.SelectedText.Value = "All" || SBU = drp_Sbu.SelectedText.Value,
drp_Serviceline.SelectedText.Value = "All" || ServiceLine = drp_Serviceline.SelectedText.Value,
Tier = drp_Tier.SelectedText.Value,
'Permanent ATC' = drp_Geo.SelectedText.Value,
Cycle = drp_cycle.SelectedText.Value,
'OnShore/OffShore' = drp_Onshore.SelectedText.Value
),
SortColumnName,
If(SortColumnType,
Ascending,
Descending
)
)

So within this code how I need to add the functions for 30 different columns Serach Boxes. Can anyone help me on this?

[WarrenBelz · Accepted solution]

Hi @Nathan_m1 ,

To keep it somewhat Delegable, you would do this

With(
   {
      wData:
      SortByColumns(
         Filter(
          Normalizations,
          StartsWith(
            'Employee Full Name',
            txtsearch.Text
          ),
          drp_Project.SelectedText.Value = "All" || Project = drp_Project.SelectedText.Value,
          drp_Department.SelectedText.Value = "All" || Department = drp_Department.SelectedText.Value,
          drp_Sbu.SelectedText.Value = "All" || SBU = drp_Sbu.SelectedText.Value,
          drp_Serviceline.SelectedText.Value = "All" || ServiceLine = drp_Serviceline.SelectedText.Value,
          Tier = drp_Tier.SelectedText.Value,
          'Permanent ATC' = drp_Geo.SelectedText.Value,
          Cycle = drp_cycle.SelectedText.Value,
          'OnShore/OffShore' = drp_Onshore.SelectedText.Value
         ),
         SortColumnName,
         If(
           SortColumnType,
           Ascending,
           Descending
         )
      )
   },
   Filter(
      wData,
      'Person.txt'.Text in 'Employee Full Name,
      SearchField2.Text in ListField2,
      SearchField3.Text in ListField3,
      SearchField4.Text in ListField4,
      . . . . . . . .
   )
)

but if you are using 30 search boxes on 30 different columns, I hope your users are regular beverage drinkers as they can probably make a cup of coffee (including boiling the water) waiting for the result to appear . . .

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Nathan_m1]

Thanks @WarrenBelz , Yeah it takes time to populate the results. Could you please say me what is wData here? Is it just a variable?

[WarrenBelz]

@Nathan_m1 ,

Yes, a very temporary Variable

[Nathan_m1]

 Hi @WarrenBelz ,

                          Currently I am using the below logic where the datas are not getting displayed if I am using the With function (For search boxes) if I remove the with function all the datas are being displayed correctly. Can you please help me on this?

Thanks.

Here is the code:

With(
{
SearchData:SortByColumns (
Filter(
Normalizations,
drp_Project.SelectedText.Value = "All" || Project = drp_Project.SelectedText.Value,
Tier = drp_Tier.SelectedText.Value,
Cycle = drp_cycle.SelectedText.Value,
'Permanent ATC' = drp_Geo.SelectedText.Value,
drp_Department.SelectedText.Value = "All" || Department = drp_Department.SelectedText.Value,
(drp_Sbu.SelectedText.Value = "All" && GroupSBU in Filter(
UserRoles,
roleName = "L2 SBU Delivery Head"
).value && VarSBU = true) || (drp_Sbu.SelectedText.Value = "All" && VarServiceline = true) || SBU = drp_Sbu.SelectedText.Value,
(ServiceLine in Filter(
UserRoles,
roleName = "Service Line Manager"
).value && VarServiceline = true) || (VarSBU = true) || ServiceLine in drp_Serviceline.SelectedItems,
'OnShore/OffShore' = drp_Onshore.SelectedText.Value
),
SortColumnName,
If(
SortColumnType,
SortOrder.Ascending,
SortOrder.Descending
)
)
 },

Filter(
SearchData,
PNumberSearch.Text in 'Person Number',
EmpNameSearch.Text in 'Employee Full Name',
PermanentAtcSearch.Text in 'Permanent ATC',
JobSearch.Text in Job,
TrackSearch.Text in Track,
TierSearch.Text in Tier,
PersonTypeSearch.Text in 'Person Type',
LastPromoDate.Text in 'Last Promotion Date',
HireDateSearch.Text in 'Employee Hire Date',
DepartmentSearch.Text in Department,
CegSearch.Text in CEG,
PracticeSearch.Text in Practice,
GroupPractSearch.Text in 'Group Practice',
LineManagerSearch.Text in 'Line Manager Name',
VirtusaExpSearch.Text in 'Virtusa Experience',
TotalExpSearch.Text in 'Total Experience',
ProjectSearch.Text in Project,
SbuSearch.Text in SBU,
CurrentProjSearch.Text in 'Current Project Allocation',
PdtLineSearch.Text in 'PDT Line',
ProjectManagedSearch.Text in 'Project Managed By',
RatingsGivenSearch.Text in 'Rating Given By',
ManagerRatingsSearch.Text in 'PreNormalized Ratings',
FinalizedRatingsSearch.Text in FinalRating,
StatusSearch.Text in FinalStatus,
ServiceLineSearch.Text in ServiceLine,
NormalizedBySearch.Text in If(
IsBlank(NormalizedBy),
"",
NormalizedBy
),
ReportingManagerSearch.Text in If(
IsBlank(ReportingManager),
"",
ReportingManager
),
HomeManagerSearch.Text in If(
IsBlank(HomeManager),
"",
HomeManager
)


)
)

[WarrenBelz]

Hi @Nathan_m1 ,

I have done some work on structure that may assist, but I do not believe I could possibly fully debug this without the actual data to test (which is not possible) . The suggested structure is below - note it is free-typed, so if there are bracketing / separator errors, you will need to find them.

With(
   {
      wHead:
      Filter(
         UserRoles,
         roleName = "L2 SBU Delivery Head"
      ).value,
      wManager:
      Filter(
         UserRoles,
         roleName = "Service Line Manager"
      ).value
   },
   With(
      {
         SearchData:
         SortByColumns(
            Filter(
               Normalizations,
               (
                  drp_Project.Selected.Value = "All" || 
                  Project = drp_Project.Selected.Value
               ) && 
               Tier = drp_Tier.Selected.Value &&
               Cycle = drp_cycle.Selected.Value &&
               'Permanent ATC' = drp_Geo.Selected.Value &&
               (
                  drp_Department.Selected.Value = "All" || 
                  Department = drp_Department.Selected.Value
               )
            ),
            SortColumnName,
            If(
               SortColumnType,
               SortOrder.Ascending,
               SortOrder.Descending
            )
         )
      },
      Filter(
         SearchData,
         (
            (
               drp_Sbu.Selected.Value = "All" && 
               GroupSBU in wHead && 
               VarSBU
            ) || 
            (
               drp_Sbu.Selected.Value = "All" && 
               VarServiceline
            ) || 
            SBU = drp_Sbu.Selected.Value
         ) &&
         (
            (
               ServiceLine in wManager &&
               VarServiceline
            ) || 
            (
               VarSBU || 
               ServiceLine in drp_Serviceline.SelectedItems &&
               'OnShore/OffShore' = drp_Onshore.Selected.Value
            )
         ) &&
         PNumberSearch.Text in 'Person Number' &&
         EmpNameSearch.Text in 'Employee Full Name' &&
         PermanentAtcSearch.Text in 'Permanent ATC' &&
         JobSearch.Text in Job &&
         TrackSearch.Text in Track &&
         TierSearch.Text in Tier &&
         PersonTypeSearch.Text in 'Person Type' &&
         LastPromoDate.Text in 'Last Promotion Date' &&
         HireDateSearch.Text in 'Employee Hire Date' &&
         DepartmentSearch.Text in Department &&
         CegSearch.Text in CEG &&
         PracticeSearch.Text in Practice &&
         GroupPractSearch.Text in 'Group Practice' &&
         LineManagerSearch.Text in 'Line Manager Name' &&
         VirtusaExpSearch.Text in 'Virtusa Experience' &&
         TotalExpSearch.Text in 'Total Experience' &&
         ProjectSearch.Text in Project &&
         SbuSearch.Text in SBU &&
         CurrentProjSearch.Text in 'Current Project Allocation' &&
         PdtLineSearch.Text in 'PDT Line' &&
         ProjectManagedSearch.Text in 'Project Managed By' &&
         RatingsGivenSearch.Text in 'Rating Given By' &&
         ManagerRatingsSearch.Text in 'PreNormalized Ratings' &&
         FinalizedRatingsSearch.Text in FinalRating &&
         StatusSearch.Text in FinalStatus &&
         ServiceLineSearch.Text in ServiceLine &&
         (
            IsBlank(NormalizedBy) ||
            NormalizedBySearch.Text in NormalizedBy
         ) &&
         (
            IsBlank(ReportingManager) ||
            ReportingManagerSearch.Text in ReportingManager
         ) &&
         (
            IsBlank(HomeManager) ||
            HomeManagerSearch.Text in HomeManager
         )
      )
   )
)

I will make the following suggestions: -

• Get the data filtered from the other list outside the main filter (see top With() statement)
• Group all Delegable queries in the main filter - then you have some chance of getting all your data
• Get some structure around your and/or arguments and clearly bracket/indent these - it will also make it much easier to follow.
• Group all the non-Delegable filter elements at the bottom

Having said all of that, I do not have a high degree of confidence that a filter of this enormous size will reliably produce the result you are after. It will also be very slow.

[Nathan_m1]

Thanks for replying will check with the solution you have mentioned.