Hi
@WarrenBelz,
Thanks for taking the time to walk through my formula — your explanation about record vs. scalar values made a lot of sense. To give you the full picture, here’s the code I’m running to build the collections before the patch step:
// Run the flow once and store the JSON result in a variable
Set(
varFlowResult,
containerFlow.Run({text: SearchInput1_2.Text}).containerevents
);
// LEFT GALLERY
// Parse JSON into base collection
ClearCollect(
colContainerEvents,
Table(ParseJSON(varFlowResult))
);
// Extract container references (only EQ)
Clear(tempAllContainers);
ForAll(
colContainerEvents As CurrentEvent,
ForAll(
Filter(
Table(CurrentEvent.Value.References),
Value.referenceType = "EQ" && !IsBlank(Text(Value.referenceValue))
) As FilteredReference,
Collect(
tempAllContainers,
{
ContainerValue: Text(FilteredReference.Value.referenceValue),
ReferenceValue: Text(FilteredReference.Value.referenceValue)
}
)
)
);
// Deduplicate container list
ClearCollect(
colUniqueContainers,
Distinct(tempAllContainers, ContainerValue)
);
// RIGHT GALLERY
// Flatten raw events
ClearCollect(
colEventsFlat,
AddColumns(
colContainerEvents,
EventType, Upper(Text(ThisRecord.Value.EventType)),
CodeT, Upper(Coalesce(Text(ThisRecord.Value.TransportCode), Text(ThisRecord.Value.EquipmentCode))),
LocationT, Text(ThisRecord.Value.Location),
VesselT, Text(ThisRecord.Value.Vessel),
ClassifierT, Upper(Text(ThisRecord.Value.eventClassifierCode)),
SealNumberT, Text(ThisRecord.Value.SealNumber),
ActualArrivalT, DateTimeValue(Left(Text(ThisRecord.Value.ActualDateTime),19)),
ClassifierRank, Switch(
Upper(Text(ThisRecord.Value.eventClassifierCode)),
"ACT", 3,
"EST", 2,
"PLN", 1,
0
),
DisplayDT, DateTimeValue(
Left(
Coalesce(
Text(ThisRecord.Value.ActualDateTime),
Text(ThisRecord.Value.EstimatedDateTime),
Text(ThisRecord.Value.PlannedDateTime),
Text(ThisRecord.Value.DateTime)
),
19
)
),
EventCreatedDT, DateTimeValue(Left(Text(ThisRecord.Value.EventCreated), 19)),
EventKeyT, Text(ThisRecord.Value.eventKey),
ContainerNumber, Text(First(Filter(Table(ThisRecord.Value.References), Value.referenceType="EQ")).Value.referenceValue)
)
);
// Filter transport/equipment events
ClearCollect(
colEventsFE,
Filter(
colEventsFlat,
EventType in ["TRANSPORT","EQUIPMENT"] &&
!IsBlank(CodeT)
)
);
// Build timeline with best candidate per EventKey
ClearCollect(
colTimelineUnique,
ForAll(
Distinct(colEventsFE, EventKeyT) As D,
With(
{Candidates: Filter(colEventsFE, EventKeyT = D.Value)},
With(
{Best: First(
SortByColumns(
Candidates,
"ClassifierRank", SortOrder.Descending,
"EventCreatedDT", SortOrder.Descending,
"DisplayDT", SortOrder.Descending
)
)},
{
EventKey: Best.EventKeyT,
Code: Best.CodeT,
EventType: Best.EventType,
Classifier: Best.ClassifierT,
ClassRank: Best.ClassifierRank,
Location: Best.LocationT,
Vessel: Best.VesselT,
SealNumber: Best.SealNumberT,
DisplayDT: Best.DisplayDT,
ActualArrival: Best.ActualArrivalT,
ContainerNumber: Best.ContainerNumber
}
)
)
)
);
So I do have flattened fields like ContainerNumber, LocationT, VesselT etc. in my collections.
The challenge is that when I try your suggested patch formula, I still get the same “The right side of the Equal operator must be a constant value” error. It seems Power Apps is still treating the lookup comparisons ('Sea Freight Container'.'Container Number' = _Data.ContainerNumber) as invalid, even though _Data.ContainerNumber is a text field.
I’ve also included a picture of the UI so you can see how the galleries and timeline are structured — hopefully that helps clarify how the collections are being used.
I suspect the issue might be that the lookup column in Dataverse (Sea Freight Container) is a record reference, and Power Apps doesn’t allow direct field comparisons inside a Filter on that type. Flattening works fine for my collections, but the Dataverse side still expects a record rather than a scalar.
Do you have any suggestions on how to restructure the LookUp/Filter so that I can match on the related record’s field value without hitting this constant‑value restriction?
Thanks again for your help — your breakdown has already clarified a lot, and I think I’m close to getting this working.
