Concatenate Record IDs into Global for Filtering Inventory
I've got a simple inventory system to track individual, serialized products (actually, barrels). Each barrel is unique and must be tracked individually, but can be assigned to a Customer. Relevant tables include:
- Inventory (for each barrel)
- Companies (for customers and vendors)
- BIN Company Info (holds Globals for filtering)
Unique record IDs are present for Inventory, Companies, and Customer POs. Foreign keys are also present for relating them all together and it seems to work fine.
I want to easily sort through what inventory is available and what is already assigned to certain customers. So, I've got a layout based on "BIN Company Info" table that uses global fields to filter inventory based on things such as location, status, minimum age, and customer. The "Bin Company Info" table is related to an Inventory table occurrence via a bunch of AND statements between the Globals and their relevant counterparts. For the barrel owner's (i.e. the customer), this is FKCustomerID in the Inventory table = FilterCompanyID in Bin Company Info table. But filtering based on customer doesn't quite work yet.
I'd like to be able to use a popover menu, drop down, or similarly easy method to select one, all, or none of the companies and thus view who owns which barrels and which barrels are "available" for assignment. I can do this successfully and select a single company at a time to filter by, but I can't figure out how to do this for all (or no) companies with the same filter field--how do I add all of the FKCustomerIDs in the Inventory table to the FilterCompanyID Global field, which is what controls the results?
I thought a button next to the popover that ran a script to add all FKCustomerIDs to the Global filter variable would work, but I couldn't figure out how to loop through the Inventory table, collect each unique FKCustomerID, then append each of those IDs to the Global filter.
Probably a pretty simple script technique (if a script is the best way to go here), I just can't seem to find it. Any help is appreciated!