Related Filtered field

Hello,

I have an app where I have two tables for Location Codes. One is a sync up with SAP for All active Location Codes and the second table (Location Code request) has a form to Change or request the creation of a new Location Code. In the Location Code request form I also have a STATUS field that tells us if the request is New, in-process or Completed. Of-course if it is completed, it would mean the New Location Code has been created in SAP with the next sync up run (which is daily) and my SAP for All active Location Codes table will now include that New Location Code record from Location Code request table.

Now, I have another table called Asset forms where users can create an Asset Capitalization request and as part of that form can choose from the 'existing' (SAP for All active Location Codes) table or choose from an 'in-process' (Location Code request) list. In the in-process list, we did not want the Location Code requests that had a status of 'Completed' as these should ideally be picked from the SAP for All active Location Codes table. So I used Quickbase action to create a duplicate of the Location Code request table but filtered for anything other than "Completed" Status (lets call this Duplicate Location Code requests).

So the problem I am having is that when a Location Code request gets marked as Completed, the Asset form loses the Location Code request choice as that no longer lives in that table Duplicate Location Code requests when it gets updated via Quickbase actions (again daily). Was hoping to create a field that would say if the record number of Duplicate Location Code requests is 33. I would then need to freeze that Record ID# '33' and then lookup Record ID# 33 in Location Code request. As Location Code request table will hold all values, the chosen Location Code will continue to inhabit in the Asset form.

Or is there a better easier way to achieve this? 

Thanks!

Deepa

------------------------------
Deepa Prashant
------------------------------