Inventory Database with fields :

INVENTORY IMAGE, LOCATION and STATUS.  The STATUS field has three options : AVAILABLE, SOLD  and ARCHIVE.

It is important to be able to sort and view Inventory Records by LOCATION.

 

My goal is to be able to Hide the Items marked ARCHIVE.  Which can be done with a simple script "Perform Find & Omit Multiple records".  My problem is that when ever Inventory is sorted or viewed by LOCATION, the OMITED records (marked "archive") reappear.

 

Any suggestions ?

 

 

Consider moving "Archived" inventory items to another table.

 

Sorting does not change the found set, so investigate what exactly is happening when you "view by Location". Sounds like there's another find happening that is not omitting status="Archived".

The "view by location" is a FIND based on the LOCATION which is what is bringing back the omitted records.

Creating another table and exporting the "archived" items to it occurred to me but seemed overly complicated.

I don't really understand your question. Every find will need to include the omit of Archived inventory items. So, change "View by Location".

 

Creating a sub-type/supertype table structure for archived inventory is more complicated, but addresses the need to list items that are not archived directly. Otherwise, you must control the finds. Perhaps a "Hide Archived" button on the list view that constrains the found set?

Barbara's right, a separate table is probably the best way to go. But for quick and dirty, try an onModeExit trigger that checks to see if it's leaving Find mode and if so, add a request omitting Archived records.