How to control which rows from a referenced list are options in a form dropdown?

[g_weight]

I am trying to filter out and only display some of the related references as options in a form view dropdown menu. When in the form view to add a new item to the Logs table, I enter an ID, choose a Company from a dropdown list of rows in the Companies referenced table, then choose a Boom (essentially an order #) from a dropdown list of rows in the Purchases table. The only options for Boom in that dropdown are from the Related Purchases list. That is, I can only choose a purchase from Company A if I have selected Company A in the form. I like this aspect of the app behavior, but I want to further filter the available options. I only want to display a dropdown option for a Boom if the Status column is "Outstanding". Once the order is "Complete" I will no longer be adding items to it and those old orders will quickly add clutter to the form if it is a dropdown option. I also do not want to delete orders once they become Complete.

I tried adding suggested values where I use a SELECT() statement to create a list of only the related Booms where the Status column is "Outstanding", but these would not display. I even tested by just using LIST("1", "2", "3") as suggested values but these would not show up either. I think it may have something to with the data validity check (Purchases[Boom]) that was automatically added to the Logs[Boom] column when I created the reference relationship. 

Below are some screenshots showing my column structure: 

[dbaum · Accepted solution]

Drop-down from Valid_If - AppSheet Help