Calculation Field Based on Portal Loop
My solution has two tables; Orders and Routing Sequence. The relationship is Orders------<Routing Sequence, where each Routing Sequence record has an order number as is foreign key. The Routing Sequence records indicate the different departments each of our orders will eventually go through and contain a sequence number (1, 2, 3, etc.), department name, and status ("c" for complete or null for incomplete).
I would like to add a new calculated field to the Orders table that indicates the department the order is currently in. Would a loop be the best option here? My thought was that the calculation would loop for the related records according to the sequence number until it got to the first incomplete department and then return that department's name. I am, of course, open to any suggestion that would accomplish the same thing.
Thank you in advance for the help.
PS - I cannot change my solution's schema. I am importing/updating records from our company's accounting software.