I am trying to get make a view or other way to get data, for a trip and count of items transported.
I am using Dataverse.
TripTable is the main table, with and id (trip_id).
TripItems is a table holding items in a specific transport. It holds it own ID field, and a trip_id field to mark which trip it belongs to.
It can be an indendepent table, or lookup towards trip table.
I want to read some items from trip table, say id, departure_time and destination.
Then the count of rows in the Items table, where the trip_id is matching.
I have tried a view and to create an object using AddColoumns, but have not found the solution
In SQL, it would be easy:
select trip.id, trip.departure_time, trip.destination,
(select count(id) from tripItems as ti where ti.trip_id=trip.id) as count
from trips
WHERE trip.deleted=0
Order by - would be lovely here
How can I do this using Dataverse and PowertApps?
