cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tagustin2020
Post Prodigy
Post Prodigy

Blank Gallery/Errors - Request for help with Items formula

@RandyHayes 

 

Hello Randy,

 

I hope you are doing well. I've begun work on a Shipping App. The gallery will be very similar to the Engineering Request App you helped me with so I did my best to mimic the formulas used in that app. I am getting 2 red squigglies as seen in the formula screenshot below. The gallery Items error messages are "unexpected characters" and "invalid argument type".

 

One of the differences between the two app data sources is the Status column. In the Engineering App I had a separate SharePoint list to manage the various statuses and related percentage complete. In the Shipping SharePoint list, I used a Choice column as I only have New, Shipped, On Hold and Cancelled as options (no percentage complete column). I realize I could use a single line of text column and hard code these choices in the app, but that might make it more difficult for others to add an additional status option if needs change down the line. Let me know your thoughts on that... I would also like to add a "Display My Requests" feature (they want users to see all requests by default) and date filters. 

 

In regards to the gallery labels, they were all working fine until I entered the Gallery Items formula. The Items property formula errors seem primarily focused on the Search box and Display My Requests checkbox. I'm getting "unexpected characters", "this identifier isn't recognized" and "invalid use of '.'". Since there are so many related pieces, I've attached a Word doc that includes all the details I think you will need to help me discover what I'm doing wrong. Let me know if there is anything else you need. I really appreciate your expert eye as I have already spent many hours trying to find the mistake(s) in this formula. Teresa

 

GalleryErrorsSS.png

HighlightErrorsSS.png

With({galItems:
AddColumns(
Filter(
'Domestic Shipping Requests',
txtSearchBox.Text in Company),
ckMine.Value && 'Requestor Name'.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('Shipped By'.DisplayName),"pending", With({lName: Split(ShippedBy.DisplayName, " ")},
                    First(lName).Result & " " & Left(Last(lName).Result, 1) & "."))},
DropColumns(
SortByColumns(
Filter(galItems,
(IsBlank(lclFilter.stats) || "All" in lclFilter.stats || Status in lclFilter.stats) &&
  If(CountRows(lclFilter.requestors) >0, requestors in lclFilter.requestors, true) &&
If(CountRows(lclFilter.logistics) >0, logistics in lclFilter.logistics, true) &&
              If(!lclFilter.comp && Status = "Shipped", false,true)
),
Coalesce(locSortColumn,"Company"),
If(Coalesce(locSortAscending,true),Ascending,Descending)
),
"requestors", "logistics"))

 

23 REPLIES 23

@RandyHayes 

 

Unfortunately not. Can you think of any obvious reason my gallery is showing up blank based on the latest formulas I have attached here? It's so strange...

 

Teresa

RandyHayes
Super User III
Super User III

@tagustin2020 

Well I can see one limiting factor in the formula.

That is with the ckMine part.  As the formula reads, you will only get records where ckMine.Value is true and the Requestor Name email is the varUser email.

If chkMine is not checked you will get no results.  And in your screenshots...it is not checked.

Your formula should be:

With({galItems:
    AddColumns(
        Filter('Domestic Shipping Requests',
            txtSearchBox.Text in Company,
            (!ckMine.Value || 'Requestor Name'.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('Shipped By'.DisplayName), "pending", With({lName: Split(ShippedBy.DisplayName, " ")},
                    First(lName).Result & " " & Left(Last(FirstN(lName, 2)).Result, 1) & "."))
    )},
    
    DropColumns(
        SortByColumns(
            Filter(galItems,
                (IsBlank(lclFilter.stats) || "All" in lclFilter.stats || Status.Value in lclFilter.stats.Value) &&
                ((CountRows(lclFilter.requestors) = 0) || (requestors in lclFilter.requestors)) &&
                ((CountRows(lclFilter.logistics) = 0) || (logistics in lclFilter.logistics))
            ),
            Coalesce(locSortColumn,"Company"),
           If(Coalesce(locSortAscending,true),Ascending,Descending)
        ),
        "requestors", 
        "logistics"
    )
)

 

Let's see where that gets 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

@RandyHayes 

 

Thank you for the formula. Before entering this formula, I had tried clicking the Show Mine box just in case that was the issue, but still nothing shows up whether or not the box is checked. I pasted this latest formula in and still nothing. 

 

Here is a partial screenshot of my SharePoint data source with made up test data. This is the first time I haven't used the Title field, read a blog post discouraging it. I removed the Required aspect in the SP data source, but is having that be empty causing the issue?

 

DataSourceSS.png

 

Teresa

RandyHayes
Super User III
Super User III

@tagustin2020 

Title is perfectly fine to use and should be used and not left empty.  It is a column just like any other column in your list.  Yet another bad advice blog!!

 

What I am not seeing in your picture is the request date column. Also, is the Status column text or a choice column?  I'm assuming since there is not error in the formula provided that it is a choice - just wanted to be clear.

_____________________________________________________________________________________
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

@RandyHayes 

 

Hi Randy, 

 

I only showed a few of the columns in the screenshot. The Status column is a Choice type. with choices of New, Shipped, On Hold and Cancelled. The Default value is new. My Request Date column is of course a date column.

I'm going into a 3-hour meeting now, but will check the forums again at the end of the day. Thanks for hanging in there with me on this. I think I should just go ahead and use the Title column for one of my text fields (e.g. Company) since I would like the Sort to default to that column and see if that makes a difference. I realize the gallery formula, sorting icon formulas, etc. would have to be adjusted as well.

 

Teresa

RandyHayes
Super User III
Super User III

@tagustin2020 

Okay...was just checking.  The key to troubleshooting this is looking at the criteria of the filter.

Looking at it there are initially 3 conditions to meet:

1) The search text typed in the searchbox needs to be in the Company column.  If it is (including blank), it will be true

2) The "Mine" checkbox needs to be unchecked OR if checked, then the Requestor Name needs to match the varUser email.  If either of those conditions are true, then true

3) The Request Date needs to be newer or the same as the selected From date and the Request date must be older or the same as the To date.

 

If all of the above are true, then the datasource record will be included in the table results.

The above gives you your first table of records (these are in the galItems scoped variable).  

 

Then you are filtering that with the following criteria:

1) either the filter stats is blank or "All" is selected in it, or the record status value is in it

2) either the filter requestors selection is empty or the selected requestors are in the requestors column (added in the prior filter)

3) either the filter logistics selection is empty or the selected logistics are in the logistics column (added in the prior filter)

 

If ALL of the above are true, then the record is returned in the resultant table of the entire formula.

 

 

So, to troubleshoot, let's take the formula apart a bit.

First try this:

With({galItems:
    AddColumns(
        Filter('Domestic Shipping Requests',
            txtSearchBox.Text in Company,
            (!ckMine.Value || 'Requestor Name'.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('Shipped By'.DisplayName), "pending", With({lName: Split(ShippedBy.DisplayName, " ")},
                    First(lName).Result & " " & Left(Last(FirstN(lName, 2)).Result, 1) & "."))
    )},
    
    DropColumns(
        SortByColumns(galItems,
           Coalesce(locSortColumn,"Company"),
           If(Coalesce(locSortAscending,true),Ascending,Descending)
        ),
        "requestors", 
        "logistics"
    )
)

Does that produce results?  If so, then the issue is in the secondary filter.  If not, then it is in the primary.

 

 

_____________________________________________________________________________________
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

@RandyHayes 

 

Hi Randy,

 

Thank you for the revised formula. We now have some results appearing in the gallery! I did go ahead and use the Title column for the Destination label so I edited the Items formula to reflect that change. The Display My Requests feature is working. I went ahead and added one for the Logistics crew members as well (I'll hide it from the Requestors later). The Search feature is not working quite yet. The Sort icons for the Text and Number type columns are working. The Choice and Person sorting icons are not working (Status, Requestor, Method, Logistics). The Date filters are working, but I didn't know how to add the logic to the Default property of the tglApplyFilters toggle so I was wondering if you could help me with that as well as the Search and Sorting icons. I've attached a doc that shows the formulas I am using for the Sorting icons and Search bar. Would appreciate a refresher on how to use only 1 icon for the Search/Cancel Search both in terms of which one is visible and the OnSelect property. As I recall you had a trick for putting that into the toggle as well. I'm really trying to learn your expert methods.

I'll be taking my mother to an appt tomorrow morning so won't be able to check this post until afternoon so no rush on the response. Thank you Randy. You're the best! Teresa

 

NewGalSS.png

Gallery Items property:

With({galItems:
    AddColumns(
        Filter('Domestic Shipping Requests',
            txtSearchBox.Text in "Title",
            (!ckMine.Value || 'Requestor Name'.Email=varUser.Email),
            (!ckAssigned.Value || 'Shipped By'.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('Shipped By'.DisplayName), "pending", With({lName: Split(ShippedBy.DisplayName, " ")},
                    First(lName).Result & " " & Left(Last(FirstN(lName, 2)).Result, 1) & "."))
    )},
    
    DropColumns(
        SortByColumns(galItems,
           Coalesce(locSortColumn,"Title"),
           If(Coalesce(locSortAscending,true),Ascending,Descending)
        ),
        "requestors", 
        "logistics"
    )
)

Apply Filters Toggle Default property:

!IsBlank(lclFilter.stats) ||  
(!IsBlank(lclFilter.requestors) && CountRows(lclFilter.requestors)>0) ||
(!IsBlank(lclFilter.logistics) && CountRows(lclFilter.logistics)>0) 

 

 

 

RandyHayes
Super User III
Super User III

@tagustin2020 

Yes, none of the searching/filtering would have worked with that last formula.  It was a test to see if we had issues in the top With formula or the inner formula.

 

So, if there were results, then that means the issue is in the filter (second) part/inner of the formula.

 

Let's change to this:

With({galItems:
    AddColumns(
        Filter('Domestic Shipping Requests',
            txtSearchBox.Text in Company,
            (!ckMine.Value || 'Requestor Name'.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('Shipped By'.DisplayName), "pending", With({lName: Split(ShippedBy.DisplayName, " ")},
                    First(lName).Result & " " & Left(Last(FirstN(lName, 2)).Result, 1) & "."))
    )},
    
    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,"Company"),
           If(Coalesce(locSortAscending,true),Ascending,Descending)
        ),
        "requestors", 
        "logistics"
    )
)

I recall there was a reason that I original put the If logic in the formula for the row count, but don't recall at the moment why.  So, let's put it back in to see if that is where it went wrong.

 

As for the Toggle...what issue are you seeing on that?  It looks like the formula there should be performing as needed.  Is there something else needed to be added?

 

Pretty sure the only purpose of that toggle was to give us a dynamic variable to determine if filtering was in place.  Based on that, the other icon and text could adjust themselves according to the toggle value.

 

_____________________________________________________________________________________
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

@RandyHayes 

 

Hello Randy,

 

Thank you for the revised formula. I changed it a little to add the Logistics checkbox and Title column references. The revision is below. Can you help me add logic to the Apply Filters toggle for the date pickers? Right now, the label below the search box does not change over to REFINE FILTERS | CLEAR FILTERS when I select dates to filter on. The gallery is filtered by date okay, it is just the label that is the issue. It stays in the APPLY FILTERS mode. The sorting icons for the Person (Requestor & Logistics) and Choice (Status & Method) are not working yet. When I click them, the gallery goes blank. The Text and Number columns work fine. I've attached a doc with Apply Filters and Sort Icon formulas. All of the other Filters are working great. I really appreciate your help ironing out these issues! Teresa

 

Latest Items formula:

With({galItems:
    AddColumns(
        Filter('Domestic Shipping Requests',
            txtSearchBox.Text in 'Company Title',
            (!ckMine.Value || 'Requestor Name'.Email=varUser.Email),
            (!ckAssigned.Value || 'Shipped By'.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('Shipped By'.DisplayName), "pending", With({lName: Split(ShippedBy.DisplayName, " ")},
                    First(lName).Result & " " & Left(Last(FirstN(lName, 2)).Result, 1) & "."))
    )},
    
    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,"Title"),
           If(Coalesce(locSortAscending,true),Ascending,Descending)
        ),
        "requestors", 
        "logistics"
    )
)

 

Latest Apply Filters toggle Default formula:

!IsBlank(lclFilter.stats) ||  
(!IsBlank(lclFilter.requestors) && CountRows(lclFilter.requestors)>0) ||
(!IsBlank(lclFilter.logistics) && CountRows(lclFilter.logistics)>0) 

 

RandyHayes
Super User III
Super User III

@tagustin2020 

Okay, let me break apart a couple of things from the doc.

RandyHayes_0-1619818486473.png

The Icon formula should be this (and equally so on all the other icon formulas but with their respective names)

If(locSortColumn<>"Status" || (locSortColumn="Status" && !locSortAscending), 
   Icon.ChevronDown, Icon.ChevronUp
)

 

The only thing the Default property for the toggle is missing (from what I am hearing) is the inclusion of the DatePickers. 

BUT, first, do your DatePickers have a default value?

 

For the sorting, your gallery goes blank because your Items formula is getting an error.  The error will be from the SortByColumns function in it.  For the Requestors and Logistics what you have will not work as you are setting the column variable to "Requestors" and the formula has "requestors" in it.  Same thing for Logistics, you are setting to "ShippedBy", but your formula has "logistics" in it.  You need to match the names in the sorting variable to the exact name of the column in your Items formula.  So replace "Requestors" with "requestors" and "ShippedBy" with "logistics".

 

I think that is it at the moment.  Fix those issues and then let me know about the default dates for the date pickers.

 

 

_____________________________________________________________________________________
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

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Solution Authors
Top Kudoed Authors
Users online (29,674)