cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tagustin2020
Impactful Individual
Impactful Individual

Not able to search on 2 different columns

Hello,

 

I would like users to be able to search the gallery of my shipping app by either Company name or by Request Number. I am only able to search by Request Number. The full formula is below. The Search portion is the third line down. I've tried replacing the || with && and also with a comma. Not having any success thus far. Can someone let me know what I am doing wrong?

 

StartsWith(Company,txtSearchBox.Text) || Value(txtSearchBox.Text)='Request Number'

 

Thanks! Teresa

 

With({galItems:
    AddColumns(
        Filter('Domestic Shipping Requests', StartsWith(Company,txtSearchBox.Text) || Value(txtSearchBox.Text)='Request Number',
            'Request Date'>DateAdd(Now(),-1,Years),
            (!ckMine.Value || 'Requestor Name'.Email=varUser.Email),
            (!ckAssigned.Value || 'Logistics Staff Member'.Email=varUser.Email),
            'Request Date' >=dpFromDate.SelectedDate && 'Request Date' <=dpToDate.SelectedDate
        ),
        "requestors", With({lName: Split('Requestor Name'.DisplayName, " ")},
                    First(lName).Result & " " & Left(Last(lName).Result, 1) & "."),
        "logistics", If(IsBlank('Logistics Staff Member'.DisplayName), 
        "pending", With({lName: Split('Logistics Staff Member'.DisplayName, " ")},
                    First(lName).Result & " " & Left(Last(FirstN(lName, 2)).Result, 1) & ".")),
        "stat", Status.Value,
        "method", 'Shipping Method'.Value
    )},
    
    DropColumns(
        SortByColumns(
            Filter(galItems,
                (IsBlank(lclFilter.stats) || "All" in lclFilter.stats || Status.Value in lclFilter.stats.Value) &&
                If(CountRows(lclFilter.requestors) > 0, requestors in lclFilter.requestors, true) &&
                If(CountRows(lclFilter.logistics) > 0, logistics in lclFilter.logistics, true)
            ),
            Coalesce(locSortColumn,"ReqNumber"),
           If(Coalesce(locSortAscending,true),Descending,Ascending)
        ),
        "requestors", 
        "logistics"
    )
)

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@tagustin2020 

Hello there Teresa!  (I'm back!)

 

Please consider changing your Formula to the following:

With({galItems:
    AddColumns(
        Filter('Domestic Shipping Requests', 
            (StartsWith(Company, txtSearchBox.Text) || 'Request Number' = IfError(Value(txtSearchBox.Text), Blank())) &&
            'Request Date'>DateAdd(Now(),-1,Years) &&
            (!ckMine.Value || 'Requestor Name'.Email=varUser.Email) &&
            (!ckAssigned.Value || 'Logistics Staff Member'.Email=varUser.Email) &&
            'Request Date' >=dpFromDate.SelectedDate && 'Request Date' <=dpToDate.SelectedDate
        ),
        "requestors", With({lName: Split('Requestor Name'.DisplayName, " ")},
                    First(lName).Result & " " & Left(Last(lName).Result, 1) & "."),
        "logistics", If(IsBlank('Logistics Staff Member'.DisplayName), 
        "pending", With({lName: Split('Logistics Staff Member'.DisplayName, " ")},
                    First(lName).Result & " " & Left(Last(FirstN(lName, 2)).Result, 1) & ".")),
        "stat", Status.Value,
        "method", 'Shipping Method'.Value
    )},
    
    DropColumns(
        SortByColumns(
            Filter(galItems,            
                (IsBlank(lclFilter.stats) || "All" in lclFilter.stats || Status.Value in lclFilter.stats.Value) &&
                If(CountRows(lclFilter.requestors) > 0, requestors in lclFilter.requestors, true) &&
                If(CountRows(lclFilter.logistics) > 0, logistics in lclFilter.logistics, true)
            ),
            Coalesce(locSortColumn,"ReqNumber"),
           If(Coalesce(locSortAscending,true),Descending,Ascending)
        ),
        "requestors", 
        "logistics"
    )
)

 

I did not notice anything glaringly wrong with your logic, so the above may or may not be significant.  In general you are trying to do text and numeric search from the same input.  In one case you are checking text values in the other you are checking numeric.  This will not only be problematic from the conversion standpoint, but also will impact delegation of your filter criteria.  

So, try the above and then if no joy, we can explore moving this around a little differently.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

5 REPLIES 5
RandyHayes
Super User
Super User

@tagustin2020 

Hello there Teresa!  (I'm back!)

 

Please consider changing your Formula to the following:

With({galItems:
    AddColumns(
        Filter('Domestic Shipping Requests', 
            (StartsWith(Company, txtSearchBox.Text) || 'Request Number' = IfError(Value(txtSearchBox.Text), Blank())) &&
            'Request Date'>DateAdd(Now(),-1,Years) &&
            (!ckMine.Value || 'Requestor Name'.Email=varUser.Email) &&
            (!ckAssigned.Value || 'Logistics Staff Member'.Email=varUser.Email) &&
            'Request Date' >=dpFromDate.SelectedDate && 'Request Date' <=dpToDate.SelectedDate
        ),
        "requestors", With({lName: Split('Requestor Name'.DisplayName, " ")},
                    First(lName).Result & " " & Left(Last(lName).Result, 1) & "."),
        "logistics", If(IsBlank('Logistics Staff Member'.DisplayName), 
        "pending", With({lName: Split('Logistics Staff Member'.DisplayName, " ")},
                    First(lName).Result & " " & Left(Last(FirstN(lName, 2)).Result, 1) & ".")),
        "stat", Status.Value,
        "method", 'Shipping Method'.Value
    )},
    
    DropColumns(
        SortByColumns(
            Filter(galItems,            
                (IsBlank(lclFilter.stats) || "All" in lclFilter.stats || Status.Value in lclFilter.stats.Value) &&
                If(CountRows(lclFilter.requestors) > 0, requestors in lclFilter.requestors, true) &&
                If(CountRows(lclFilter.logistics) > 0, logistics in lclFilter.logistics, true)
            ),
            Coalesce(locSortColumn,"ReqNumber"),
           If(Coalesce(locSortAscending,true),Descending,Ascending)
        ),
        "requestors", 
        "logistics"
    )
)

 

I did not notice anything glaringly wrong with your logic, so the above may or may not be significant.  In general you are trying to do text and numeric search from the same input.  In one case you are checking text values in the other you are checking numeric.  This will not only be problematic from the conversion standpoint, but also will impact delegation of your filter criteria.  

So, try the above and then if no joy, we can explore moving this around a little differently.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

@RandyHayes 

 

Hi Randy,

 

Nice to hear from you! I plugged the new formula into the app. Unfortunately, it didn't work out. I wasn't able to search for either company names or request numbers. I went ahead and changed it back to company only. The ability to search on request number would be nice, but I don't think it is essential.  The Logistics Crew hadn't asked for it yet, I just figured they might so I was trying to be proactive. Thanks for letting me know that text and numbers don't like sharing the same box. Do you think I will run into any delegation issues with the formula as stated below?

 

With(
    {
        galItems: AddColumns(
            Filter(
                'Domestic Shipping Requests',
                StartsWith(
                    Company,
                    txtSearchBox.Text
                ),
                'Request Date' > DateAdd(
                    Now(),
                    -1,
                    Years
                ),
                (!ckMine.Value || 'Requestor Name'.Email = varUser.Email),
                (!ckAssigned.Value || 'Logistics Staff Member'.Email = varUser.Email),
                'Request Date' >= dpFromDate.SelectedDate && 'Request Date' <= dpToDate.SelectedDate
            ),
            "requestors",
            With(
                {
                    lName: Split(
                        'Requestor Name'.DisplayName,
                        " "
                    )
                },
                First(lName).Result & " " & Left(
                    Last(lName).Result,
                    1
                ) & "."
            ),
            "logistics",
            If(
                IsBlank('Logistics Staff Member'.DisplayName),
                "pending",
                With(
                    {
                        lName: Split(
                            'Logistics Staff Member'.DisplayName,
                            " "
                        )
                    },
                    First(lName).Result & " " & Left(
                        Last(
                            FirstN(
                                lName,
                                2
                            )
                        ).Result,
                        1
                    ) & "."
                )
            ),
            "stat",
            Status.Value,
            "method",
            'Shipping Method'.Value
        )
    },
    DropColumns(
        SortByColumns(
            Filter(
                galItems,
                (IsBlank(lclFilter.stats) || "All" in lclFilter.stats || Status.Value in lclFilter.stats.Value) && If(
                    CountRows(lclFilter.requestors) > 0,
                    requestors in lclFilter.requestors,
                    true
                ) && If(
                    CountRows(lclFilter.logistics) > 0,
                    logistics in lclFilter.logistics,
                    true
                )
            ),
            Coalesce(
                locSortColumn,
                "ReqNumber"
            ),
            If(
                Coalesce(
                    locSortAscending,
                    true
                ),
                Descending,
                Ascending
            )
        ),
        "requestors",
        "logistics"
    )
)

 

RandyHayes
Super User
Super User

@tagustin2020 

I don't see any particular delegation problems in the formula.  That's always not to say that there could be record limit issues - different problem.  But I am doubting that will be a problem either.

 

You can do the search against the text and number as you first started to do, so if the "feature" comes up again as something wanted, then we can always circle back on this.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@RandyHayes 

 

Thank you Randy. I'll let you know if they consider it a "must have". I won't suggest it to them 😉

 

Teresa

RandyHayes
Super User
Super User

@tagustin2020 

Ah, where's that spirit of adventure?? 🤣🤣

No problem.  Just let me know.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,334)