Trying to Filter a Gallery using LookUp function

[bobgodin]

I have a Gallery of data that is grouped on a field called Title and I am filtering on 2 date fields and a TextBox that allows filtering on licensee and/or authorisation - see below code

SortByColumns(
    AddColumns(
    GroupBy(
        With(
            {
                _preFilter: Filter(
                    Claims,
                    startdatetime >= DatePickerFrom_2.SelectedDate && enddatetime <= DatePickerTo_2.SelectedDate && (StartsWith(licensee,TextInput7_1.Text) || StartsWith(authorisation,TextInput7_1.Text)Or StartsWith(LookUp(Events,eventID=eventID,wma.Value),TextInput7_1.Text))
        )
            },
            If(
                !IsBlank(Searchbox_1.Selected.eventID),
                Filter(
                    _preFilter,
                    (eventID in Searchbox_1.SelectedItems.eventID)
                ),
                
                    _preFilter
            )
        ),
    "Title",
    "Data"
),
"thewma",LookUp(Events,eventID=Text(First(Data).eventID),wma.Value),
"theeventid",First(Data).eventID,   
"licensee",First(Data).licensee,
"meterno",First(Data).meterno,
"startdatetime",First(Data).startdatetime,
"enddatetime",First(Data).enddatetime,
"startread",First(Data).startread,
"endread",First(Data).endread,
"dateentered",First(Data).dateentered,
"dateofform",First(Data).dateofform,
"MLcalculated",Sum((Data),MLcalculated),
"manualusage",First(Data).manualusage,
"comments",First(Data).comments
),
"licensee",SortOrder.Ascending)

I am trying to add another filter which involves a LookUp to a different Sharepoint list and this is the one I can't get to work

The code should LookUp eventID from the Events list and match it with eventID from the Claims list and return the wma value from the Events list so it can be searched on (StartsWith....)

In the AddColumns section of my code I have successfully added "thewma" column using a LookUp function but when I try a similar approach in the Filter section it fails with "Fetching items failed, possible invalid string in filter query"

I am missing something obvious I think - could someone point me in the right direction please?

thanks

[WarrenBelz · Accepted solution]

Hi @bobgodin ,

You also have a Delegation issue there with the relational lookup. Try this for a start

SortByColumns(
   AddColumns(
      GroupBy(
         With(
         {
            _preFilter: 
            Filter(
               Claims,
               startdatetime >= DatePickerFrom_2.SelectedDate && 
               enddatetime <= DatePickerTo_2.SelectedDate && 
               (
                  StartsWith(
                     licensee,
                     TextInput7_1.Text
                  ) || 
                  StartsWith(
                     authorisation,
                     TextInput7_1.Text
                  ) 
               )
            )
         },
         Filter(
            AddColumns(
               _preFilter,
               "IDEvent",
               LookUp(
                  Events As _Event,
                  _Event.EventID = EventID
               ).wma.Value
            ),
            (
               Len(Searchbox_1.Selected.eventID) = 0 ||
               eventID in Searchbox_1.SelectedItems.eventID
            ) &&
            TextInput7_1.Text in IDEvent
         ),
         "Title",
         "Data"
      ),
      "thewma",
      First(Data).IDEvent,
      "theeventid",
      First(Data).eventID,   
      "licensee",
      First(Data).licensee,
      "meterno",
      First(Data).meterno,
      "startdatetime",
      First(Data).startdatetime,
      "enddatetime",
      First(Data).enddatetime,
      "startread",
      First(Data).startread,
      "endread",
      First(Data).endread,
      "dateentered",
      First(Data).dateentered,
      "dateofform",
      First(Data).dateofform,
      "MLcalculated",
      Sum(Data,MLcalculated),
      "manualusage",
      First(Data).manualusage,
      "comments",
      First(Data).comments
   ),
   "licensee",
   SortOrder.Ascending
)

also I cannot test this, but may be a less complex approach to adding all those fields back

With(
   {
      _preFilter:
	  SortByColumns(
         Filter(
            Claims,
               startdatetime >= DatePickerFrom_2.SelectedDate && 
               enddatetime <= DatePickerTo_2.SelectedDate && 
               (
                  StartsWith(
                     licensee,
                     TextInput7_1.Text
               ) || 
               StartsWith(
                  authorisation,
                  TextInput7_1.Text
               )
            )
         ),
         "licensee",
         SortOrder.Ascending
      )
   },
   With(
      {
         _Grouped:
         AddColumns(
            GroupBy(		 
               Filter(
                  AddColumns(
                     _preFilter,
                     "IDEvent",
                     LookUp(
                        Events As _Event,
                        _Event.EventID = EventID,
                     ).wma.Value
                  ),
                  (
                     Len(Searchbox_1.Selected.eventID) = 0 ||
                     eventID in Searchbox_1.SelectedItems.eventID
                  ) &&
                  TextInput7_1.Text in IDEvent
               ),
               "Title",
               "Data"
            ),
            "_ID",
            First(Data).ID
         )
      },
      Filter(
         _preFilter,
         ID in _Grouped._ID
      )
   )
)

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]

Hi @WarrenBelz thanks for your prompt response - however it's not quite working from about halfway down the code - see screenshot...

any ideas?

thanks

[WarrenBelz]

HI @bobgodin ,

Just missing a couple of commas (challenge of keeping brain fully engaged when free-typing large chunks of code . . .) - I thought you might have spotted them.

[bobgodin]

Hi @WarrenBelz - I have added a couple of commas and nearly there I think:

could you help me out with the last little bit please?

[WarrenBelz]

@bobgodin ,

Is EventID the same data type in both lists or is it a Number in Claims and Text in Events ?

. . . . . 
AddColumns(
   _preFilter,
   "IDEvent"
   LookUp(
      Events As _Event,
      _Event.EventID = Text(EventID)
  ).wma.Value
),
. . . . . .

Also any error messages are hugely helpful in trying to debug issues with code of this size - I have run it here on a couple of test lists with the same field types and it is valid code.

[bobgodin]

Hi @WarrenBelz sorry for the tardy response....I just moved a couple of brackets around and got it working:

GroupBy(		 
               Filter(
                  AddColumns(
                     _preFilter,
                     "IDEvent",
                     LookUp(
                        Events As _Event,
                        _Event.eventID = eventID)
                     .wma.Value
                  ),
                  
                     Len(Searchbox_1.Selected.eventID) = 0 ||
                     eventID in Searchbox_1.SelectedItems.eventID
                   &&
                  TextInput7_1.Text in IDEvent
                  ),
               "Title",
               "Data"
            ),
            "_ID",
            First(Data).ID
         )
      },
      Filter(
         _preFilter,
         ID in _Grouped._ID
      )
   )
)

thanks very much for the assistance as always 🙂

[WarrenBelz · Accepted solution]

@bobgodin ,

You have left out two brackets, which are important to separate and/or sets - I posted

(
   Len(Searchbox_1.Selected.eventID) = 0 ||
   eventID in Searchbox_1.SelectedItems.eventID
) &&
TextInput7_1.Text in IDEvent

and you have

Len(Searchbox_1.Selected.eventID) = 0 ||
EventID in Searchbox_1.SelectedItems.eventID &&
TextInput7_1.Text in IDEvent

you might check this is actually getting the result you require.

[bobgodin]

thanks @WarrenBelz