Combining Child Records So a Gallery Can Filter by Visitor Name

Sometimes the gallery is bound to a parent table, but the value I want to search lives in a related child table.

In this case:

• gp_visit_requests contains the main gatepass request
• gp_visit_details contains one or more guest records for each request
• the relationship is visit_details.RequestID -> visit_request.ID

The requirement was to show only the parent request rows in the gallery, while still allowing search by visitor name from the child records.

The Problem

If the gallery uses only the request table, a text search like txtKeyword.Text in ... cannot see names stored in gp_visit_details.

That means a request with three guests will still appear as one gallery row, but the search box will not find it unless the guest names are brought into the parent dataset first.

The Pattern

I load the child details into a collection, then create a second collection for requests with one extra calculated column called VisitorNames.

That extra column is a concatenated string of all child visitor names related to the request.

Concurrent(
    Refresh(gp_visit_details),
    Refresh(gp_visit_requests)
);

ClearCollect(
    colVisitDetails,
    gp_visit_details
);

ClearCollect(
    colVisitRequests,
    AddColumns(
        gp_visit_requests As parent,
        "VisitorNames",
        Concat(
            Filter(
                colVisitDetails,
                Value(RequestID.Value) = parent.ID
            ),
            ThisRecord.'Visitor Name',
            ", "
        )
    )
);

Why This Helps

This approach keeps the gallery bound to the parent request records, but gives each record a searchable summary of its related guests.

That means:

• one request still shows as one gallery item
• all guest names are available for filtering
• the expensive lookup work happens once when the collection is prepared

Gallery Items Formula

Once VisitorNames exists in colVisitRequests, the gallery filter becomes much easier to write.

Sort(
    Filter(
        colVisitRequests,
        If(
            varViewMode = "super",
            'Entry date' >= dtEntryDatePicker.SelectedDate &&
            'Entry date' < DateAdd(dtEntryDatePicker.SelectedDate, 1, TimeUnit.Days),
            true
        ),
        IsBlank(ddStatus.Selected.Value) || ddStatus.Selected.Value = "All" || Status.Value = ddStatus.Selected.Value,
        IsBlank(ddRequestType.Selected.Value) || ddRequestType.Selected.Value = "All" || 'Request type'.Value = ddRequestType.Selected.Value,
        IsBlank(ddPurpose.Selected.Value) || ddPurpose.Selected.Value = "All" || Purpose.Value = ddPurpose.Selected.Value,
        IsBlank(ddGate.Selected.Value) || ddGate.Selected.Value = "All" || 'Entry gate'.Value = ddGate.Selected.Value,
        IsBlank(txtKeyword.Text) || txtKeyword.Text in VisitorNames
    ),
    Created,
    SortOrder.Descending
)

Notes for Future Me

• This works well when I need parent rows in the gallery but child values in the search.
• The combined text field is especially useful for quick keyword filtering.
• If the child table becomes very large, I should re-check performance and delegation limits.
• If needed, I can extend the same pattern for passport numbers, organization names, or other guest attributes.

Result

This makes the gallery feel smarter without changing the screen structure.

I still show one row per request, but I gain the ability to search using the people attached to that request. For request-driven apps, that is often the behavior users expect.