Hi all,
Currently converting a Canvas App to sit on Dataverse for Teams (previously sat on SharePoint Lists). Having established all relationships, I am struggling to replicate a particular set of filter criteria on a couple of galleries. One such example is below.
I have three relevant tables: 'Organisations', 'People', and 'Activity'
The gallery to display 'Activity' records has a series of filters linked to multi-select combo boxes on the same screen. Most logical tests are fairly straightforward, however a few are filtering the 'Activity' gallery based on many-to-many relationships between the 'Activity' table and the 'Organisations' and 'People' tables respectively.
A slightly anonymised copy of the Items property code is shown below. Also, here are the many-to-many relationship names within Dataverse as used in the formula - all are OOB many-to-many:
- 'cr02d_Act_rel_Peo_Staffother' - staff attendees, related to records in the People table
- 'cr02d_Act_rel_Peo_MainOrgAtt' - primary activity attendees, related to records in the People table
- 'cr02d_Act_rel_Peo_OtherOrgAtt' - alternative activity attendees, related to records in the People table
- 'cr02d_Act_rel_Org_OtherOrgs' - activity attendee organisations, related to records in the Organisations table
Filter(
//// sort by date, ordered ascending or descending as selected
Sort(
Activities,
Date,
If(
searchDateOrder = SortOrder.Ascending,
SortOrder.Ascending,
SortOrder.Descending
)
),
//// filter by date
Date >= dbox_StartDate_SearchAct.SelectedDate,
Date <= dbox_EndDate_SearchAct.SelectedDate,
//// filter contact reason
IsBlank(cbox_MtgReason_SearchAct.SelectedItems) || IsEmpty(cbox_MtgReason_SearchAct.SelectedItems) || Reason in cbox_MtgReason_SearchAct.SelectedItems.Value,
//// filter meeting format
IsBlank(cbox_MtgFormat_SearchAct.SelectedItems) || IsEmpty(cbox_MtgFormat_SearchAct.SelectedItems) || Formats in cbox_MtgFormat_SearchAct.SelectedItems.Value,
//// filter teams
IsBlank(cbox_Team_SearchAct.SelectedItems) || IsEmpty(cbox_Team_SearchAct.SelectedItems) || Teamlead.teams in cbox_Team_SearchAct.SelectedItems.Value,
//// filter staff - need to check 'lead' and 'other' attendees
IsBlank(cbox_TeamStaff_SearchAct.SelectedItems) || IsEmpty(cbox_TeamStaff_SearchAct.SelectedItems) || Teamlead.FullName in cbox_TeamStaff_SearchAct.SelectedItems.FullName ||
//// this construction is required since there can be more than one person selected in filter, and more than one person related as other staff.
true in ForAll(
ThisRecord.'cr02d_Act_rel_Peo_Staffother',
ThisRecord in cbox_TeamStaff_SearchAct.SelectedItems
),
//// filter other org staff
IsBlank(cbox_OrgStaff_SearchAct.SelectedItems) || IsEmpty(cbox_OrgStaff_SearchAct.SelectedItems) ||
//// need to check main org attendees using same construction as above
true in ForAll(
ThisRecord.'cr02d_Act_rel_Peo_MainOrgAtt',
ThisRecord.FullName in cbox_OrgStaff_SearchAct.SelectedItems
) ||
//// need to check other org attendees using same construction as above
true in ForAll(
ThisRecord.'cr02d_Act_rel_Peo_OtherOrgAtt',
ThisRecord.FullName in cbox_OrgStaff_SearchAct.SelectedItems
),
//// filter main and other orgs
IsBlank(cbox_Orgs_SearchAct.SelectedItems) || IsEmpty(cbox_Orgs_SearchAct.SelectedItems) || MainOrg.OrgName in cbox_Orgs_SearchAct.SelectedItems.OrgName ||
//// need to check each value in OtherOrgs field
true in ForAll(
ThisRecord.'cr02d_Act_rel_Org_OtherOrgs',
ThisRecord.OrgName in cbox_Orgs_SearchAct.SelectedItems.OrgName
)
)
The 'true in ForAll()' elements do work, however warn about referencing the many-to-many relationship and potential performance impacts. The warning is valid - these filters are extraordinarily slow with c.500 records in each table.
Is there a more efficient way to handle this type of gallery filtering against a related record that can handle multiple selections, or do i need to go back to the drawing board on this app?
