Looping over table and doing lookup in script editor
Hi there!
I am preparing a data set which resides in two database tables associated by a common attribute ("CPN"). Table A (warehouse qty on hand): [ProdID] [QOH]
Table B (products needed): [ProdID] [Deadline] [QTY] (always = 1) Order By [Deadline] asc
I need to conduct a lookup for each row between Table A and Table B and for each row to subtract [QTY] from [QOH]. Table B is ordered by [Deadline] as I need the products with the closest deadline to be treated first.
Imagined outcome: Option 1) A flag in Table B which indicates if QOH for a ProdID has reached zero. I will then be able to create a list of products we're able to produce with the current warehouse QOH stats.
Option 2) A column in Table B stating what the adjusted QOH is for the record's ProdID (okay if it's a negative number).