Can you create a dual search for a table

So I have need to create a Table that allows users to search by a account to find an order.   That’s rather simple,  The users are also asking to be able to have a additional search box to limit those results further by orders from that account that also have a product item.   

The issue is that the products are in a related object to the order.   Is it possible to do this?

Hi Ambrose. Can you help us understand your data model a little more? How are the Account, Order, and Product items related? One account to many orders, > one order to many products? It sounds like you want a multi-step process, which you might organize with the Wizard or Tab components. Then inside each wizard step or tab, you could set up a table for each of these 3 objects. You can set up one model for each of the objects. Then, you can set up a condition on the Order model that will only return orders whose accountId is in the Accounts model. And, you can do the same thing for Products, and only return product rows whose Order Ids are in the order model. You would need to be careful about when each of the models get queried, with this cascade of models & conditions.

If your users need to search though accounts and simply choose one, in order to see its orders and products, you might be able to use the Search Component to return account records. There are lots of possibilities, depending on the specifics of your use case. 

I’m not sure what the specific field or object names are, but hopefully this summary makes enough sense as a starting point. 

Sure In salesforce there is a table Orders that has the fields account, Order number etc…   There is also a linked one to Many relationship to the Order Product (we renamed it to Order Item) table that has Order Number and Order Item (Sku).    

The main goal is to have something like the following:

So the user can enter one or both fields and get results that contain orders that match.

Any clearer?

Hi Ambrose. Sorry for the delayed response. I wanted to ask. In the current configuration, with just one search box in the table, are your users able to search for account name, order item, or both? 

With the additional information you’ve shared, it sounds to me like you may need to set up one search box on the Orders object that lets users search for account, and another search box that points to a model on the Order Item object. Perhaps the Order Item model could have a model condition that returns only order items whose Order Ids are present in the Order object?