I'm trying to find a way to return the value of one line item in a list, using two other line items.
I have one large module built off sequence numbers as the rows, and line items as the columns (no page dimensions). Each row is a seperate transaction (either a product being added or a product being removed).
I have one line item that will tell me the date that a recently removed product was originally added (this shows up on the row with the product being removed), and another line item that has the account number.
What I am trying to do:
I need a way to find out what the employee ID was on the row (transaction) where my product was added. This transaction would have the same account number, and the transaction date would match the removal date on the row where the product has been removed.
Long story short - I am trying to have the employee number of the person who originally added the product, show up on the row (transaction) where the product has been removed, using only the date and the account number.
I have created an example below that is overly simplified as I am dealing with ~100k transactions each day for all different products and thousands of employees, but hopefully it demonstrates what I am trying to do.
Thanks in advance for any assistance!
An intriguing problem - this could be a job for the ISFIRSTOCCURRENCE() command. What I would do to solve this one is the following:
Hope that helps!
Can an Account have more than one Product assigned to it? If it can, I would create a hierarchy with Products (numbered list) rolling up to the Account using a similar method that Zaf mentioned above (isfirstoccurrence()).