You’re offline. This is a read only version of the page.
359
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
Share
ReportSortByColumns(
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
You have left out two brackets, which are important to separate and/or sets - I posted
All responses (
Answers (
(
Len(Searchbox_1.Selected.eventID) = 0 ||
eventID in Searchbox_1.SelectedItems.eventID
) &&
TextInput7_1.Text in IDEventand you have
Len(Searchbox_1.Selected.eventID) = 0 ||
EventID in Searchbox_1.SelectedItems.eventID &&
TextInput7_1.Text in IDEventyou might check this is actually getting the result you require.
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 🙂
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.
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?
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.
Hi @WarrenBelz thanks for your prompt response - however it's not quite working from about halfway down the code - see screenshot...
any ideas?
thanks
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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
