Hello
I am building an app that uses 2 data sets, both are Excel Tables.
The first is called OrderTable, looks like this
| StockNumber | ControlNumber | Yield | Category | Description | OnHand | CutItem |
07001 | 67001 | .95 | Seabass | Hybrid Bass | | |
| 07005 | 67005 | .95 | Trout | Boneless Charr | | |
| 07061 | 67005 | .95 | Trout | Frzn Charr | | |
The second is called InventoryTable, looks like this
| StockNumber | ControlNumber | Description | OnHand |
| 07001 | 67001 | Hybrid Bass | 100 |
| 07005 | 67005 | Boneless Charr | 10 |
| 07006 | 67006 | Haddock | 0 |
I had a vlookup formula in Excel that would match up records by ControlNumber, take the OnHand value from the InventoryTable, multiply that value by the Yield in the OrderTable, and populate the OnHand of the OrderTable with that resulting value. I did this because there could be many StockNumbers that match up to the same ControlNumber, and the ControlNumber is what dictates the OnHand inventory available to each stock number depending on their yield.
(Here's what that formula looked like)
=((VLOOKUP(B3,'InventoryTable'!$B$2:$D$800,3,FALSE))*C3)
I am unable to use data sets with formulas in Power Apps so I tried recreating this functionality using the LookUp function.
This is how my gallery is set up:
| StockNumber | Description | OnHand |
| 7001 | Hybrid Bass | 95 |
| 7005 | Boneless Charr | 95 |
| 7061 | Frzn Charr | 95 |
The formula in the OnHand field is:
(ThisItem.Yield) * (LookUp(InventoryTable, ControlNumber in OrderTable.ControlNumber, OnHand))
The problem here is that the OnHand calculation is only using the inventory from the first record and pasting that into all records. The second and third record of this gallery table should be 9.5 (Yield = .95, OnHand = 10).
Is it possible to have each record calculate based on its own control number?
I'm sorry if this is hard to follow.
Thanks for your help!
