Hello Guys,
i have a problem joining two tables in PowerApps. I use SharePoint Lists as my datasources.
These are my tables:
Order
| ID | Project | Date | Customer | ... |
| 1 | P-001-Test | 28.07.2020 | Peter | |
OrderPosition
| ID | OrderID | Material | Amount | Unit |
| 1 | 1 | Rubber | 10 | pcs |
| 2 | 1 | Pen | 20 | pcs |
| 3 | 1 | Pencil | 10 | pcs |
Since I don't want to drag all the columns from the list "Order" into the list "OrderPosition" i've splitted the lists and would like to join both tables again in a Gallery in PowerApps using the ID from Order and OrderID from OrderPosition.
I'm stuck at the following point:
//At first I'm collection the orders for a specific project based on a filter (Dropdown)
ClearCollect(
colProjects;
Filter(Order;Project="P-001-Test")
);;
//After that i want to collect all the Order positions which have the same OrderID as the ID in my colProjects collection WITHOUT USING IN (can't deal with delegation here)
ClearCollect(
colOrderPosition;
AddColumns(
Filter('OrderPosition';OrderID = colProjects.ID);
"Project";
LookUp(
Order;
ID = 'OrderPosition'[@OrderID]
)
)
);;
The formula above works if i do not the filtering but then unfortunately i get all the rows from my source OrderPosition, eventhough "Project" is blank.
What i want is to get all the orderpositions to my specific project.
Unfortunately there will be a lot of data in this list in a short time, so I want to filter the whole thing by Project or ID from the List Order, so that i do not push tons of data in my app...
