Having trouble looking up data from a different Sharepoint list

[bobgodin]

I have a basic Gallery to display my data - the source is Sharepoint. I have a collection which I created to overcome the delegation issues in my app called GalleryDataNew

I have the following on my Gallery Items property:

If(
        varWHS = "false",
        SortByColumns(
            Filter(
                Filter(
                    colGalleryDataNew,
                    Substitute(
                        Substitute(
                            Lower(varUserMail),
                            "@rdmw.qld.gov.au",
                            ""
                        ),
                        ".",
                        " "
                    ) in Lower(officers) || Substitute(
                        Substitute(
                            Lower(varUserMail),
                            "@rdmw.qld.gov.au",
                            ""
                        ),
                        ".",
                        " "
                    ) in Lower(approver) || Substitute(
                        Substitute(
                            Lower(varUserMail),
                            "@rdmw.qld.gov.au",
                            ""
                        ),
                        ".",
                        " "
                    ) in LookUp(
                        BusinessUnits,
                        unitname in ThisRecord.unitname,
                        contact3name
                    )
                ),
                searchbox_3.Text in officers || searchbox_3.Text in Text(tripid)
            ),
            "tripid",
            SortOrder.Descending
        )
    )

I have a text box above the Gallery which allows searching on the officers and tripid fields

My query is trying to say "display all trips where the logged on user is any of the following":

• The officer on the trip
• The approver of the trip
• The escalation contact for the business group that the officer is associated with

I am ok with the 1st 2 dot points as the fields are directly within the collection "GalleryDataNew" it's when I come to try the LookUp that it fails:

//this is showing the first contact3name in the Business Units list
LookUp(
      BusinessUnits,
      unitname in ThisRecord.unitname,
      contact3name

I think I am missing something obvious but I can't figure out what it is!  any help much appreciated

[v-jefferni]

Hi @bobgodin ,

There is a part of criteria in the inner Filter function which I cannot understand very well:

Filter(
    colGalleryDataNew,
    Substitute(
        Substitute(
            Lower(varUserMail),
            "@rdmw.qld.gov.au",
            ""
        ),
        ".",
        " "
    ) in LookUp(
             BusinessUnits,
             unitname in ThisRecord.unitname,
             contact3name
         )
)

Could you please share more details about the BusinessUnits list?  What types are unitname and contact3name columns?

Basically, if you have a unitname column in the collection of type text, and single line of text contant3name column in BusinessUnits list, this part should be:

LookUp(
             BusinessUnits,
             unitname = ThisRecord.unitname,
             contact3name
)

Best regards,

[bobgodin]

Hi @v-jefferni The unitname and contact3name fields are both single line of text in the BusinessUnits List and the unitname column in GalleryDataNew collection is also single line of text

thanks

[WarrenBelz · Accepted solution]

Hi @bobgodin ,

I was working on saving you a heap of code here as below (which also contains a suggestion)

With(
   {
      _UserMail:
      Lower(
         Substitute(
            First(
               Split(
                  varUserMail,
                  "@"
               )
            ).Value,
            ".",
            " "
         )
      )
   },
   Filter(
      colGalleryDataNew,
      (
         _UserMail in Lower(officers) || 
         _UserMail in Lower(approver) || 
         _UserMail in 
         LookUp(
            BusinessUnits As _Units,
            _Units.unitname = unitname
         ).contact3name
      ) &&
      (
         searchbox_3.Text in officers || 
         searchbox_3.Text in Text(tripid)
      )
   )
)

the issue here is that LookUp returns the first matching record, so you would simply need equals as it only has one value to match. If you are wanting to return several matching records and use the in statement, you need Filter instead of LookUp

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

[bobgodin]

@WarrenBelz thanks again Warren - that did the trick!