Blank Gallery/Errors - Request for help with Items formula

[tagustin2020]

@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

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"))

[RandyHayes]

@tagustin2020 

Hello again!

There are some slight syntax issues with your formula.

Change it to the following:

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) &&
                ((CountRows(lclFilter.requestors) = 0) || (requestors in lclFilter.requestors) &&
                ((CountRows(lclFilter.logistics) = 0) || (logistics in lclFilter.logistics) &&
                (!lclFilter.comp && (Status = "Shipped"))
            ),
            Coalesce(locSortColumn,"Company"),
           If(Coalesce(locSortAscending,true),Ascending,Descending)
        ),
        "requestors", 
        "logistics"
    )
)

See where that gets you.

[tagustin2020]

@RandyHayes 

Hi Randy,

Thank you for the prompt reply. The AddColumns part of the formula is happy now (thank you!), but the bottom part is not working yet.

As an experiment, I tried pasting in the DropColumns portion of the formula from the first post and it seems to have less errors.

Here is the revised formula:

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"))

[tagustin2020]

@RandyHayes 

Hi Randy,

I adjusted the DropColumns portion of the formula based on your suggestion. I removed Status = "Shipped" because Shipped isn't one of the combo box options which seemed to make it work a little better. I still have one curly bracket error showing up. Not sure how to fix that...

Here is the revised formula:

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) &&
              (!lclFilter.comp)
),
Coalesce(locSortColumn,"Company"),
If(Coalesce(locSortAscending,true),Ascending,Descending)
),
"requestors", "logistics"))

[RandyHayes]

@tagustin2020 

Yeah...the never ending problem for me of trying to freehand formulas in the forum editor - it's not a formula editor for sure!!

I see that there was an If function in the logistics that I missed and thus did not end-paren properly.

Also found two other parens missing 🙄

Formula now:

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) &&
                ((CountRows(lclFilter.requestors) = 0) || (requestors in lclFilter.requestors)) &&
                ((CountRows(lclFilter.logistics) = 0) || (logistics in lclFilter.logistics)) &&
                (!lclFilter.comp && (Status = "Shipped"))
            ),
            Coalesce(locSortColumn,"Company"),
           If(Coalesce(locSortAscending,true),Ascending,Descending)
        ),
        "requestors", 
        "logistics"
    )
)

I am not quite recalling what we did the the lclFilter.comp to fit into this.  We definitely don't want to have it by itself because I believe it before gave us completed rows.  If we leave it as is, then you'll only see results if that check is false...not what you want.

So, just refresh me on the logic you want there and we can incorporate that in.

[tagustin2020]

@RandyHayes 

Hi Randy,

No problem at all! The only red squiggly I was still getting was on the equal sign in the (Status = "Shipped") portion. I removed that line of the formula because I realized users will want to see shipped items in the default view as they will be coming to this list to verify their items have been shipped, view tracking numbers, etc.

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) &&
                ((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"
    )
)

My Gallery is still showing up blank. I notice when I click on the Apply Filters icon, the toggles remain in the Off position. I've attached screenshots (v2) of my latest formulas. Would you mind terribly taking a look? There is a varStatus variable in the OnStart that I'm not sure if needed in this scenario. The Items property for my Status combobox is Choices('Domestic Shipping Requests'.Status) and the DefaultSelectedItems is Blank(). Not sure if that Blank or the varStatus is causing the blank gallery issue. To confirm, I want the gallery to default to display all items regardless of their status or who submitted the request. I don't want to have an "All" option in the Status combobox dropdown because people will have to uncheck it in order to select other items such as "On Hold" or "Cancelled". Thanks so much Randy - Teresa

[RandyHayes]

@tagustin2020 

So in review of this, you are showing a Gallery with one record in it that is only showing the Status column value.

So now the question is, why are you not seeing the other labels??

I know in the past you had combined viewing and editing with two separate controls (label and textinput), is this the case in this app?  Is there a Visible property on those other labels that is causing it not to show?

The Logistics label appears obviously and it is also obviously not seeing a value in the 'Shipped By'

Your formula in that label text control is not correct and you will actually be seeing "Pending" for every record.  IN this case...the one record that is showing.

That formula should be : 

If(IsBlank(ThisItem.'Shipped By'.DisplayName), "pending",
   With({lname: Split(ThisItem.'Shipped By'.DisplayName, " ")},
       First(lname).Result & " " & Left(Last(FirstN(lname, 2)).Result, 1) & "."
   )
)

As for the main gallery Items formula, let's make a slight 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) &&
                ((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"
    )
)

If no joy from that, then let me know this...if you do not filter on anything, do you get results, or is this just a filtering issue?

[RandyHayes]

@tagustin2020 

Oh, and as for the varStatus variable...I don't see where you are using it and the formula (as was shown in your doc) is not valid...it has an extra comma at the end of the table records.

[tagustin2020]

@RandyHayes 

Hi Randy,

Thanks for all your help today. My gallery is still empty, but at least my formulas aren't bleeding red anymore. I removed the varStatus formula from the OnStart of the app.

I manually added test data to the SharePoint list. In theory, it should be showing up, but I'm going to go ahead and build a simple form screen into the app tomorrow morning and try entering data that way to see if it makes any difference. Seems doubtful, but I'm really scratching my head at this point. 

Have a nice evening,

Teresa

[RandyHayes]

@tagustin2020 

Were you able to get anywhere else on this toady?