Method Community

This is a two part question. The first has to do with populating a single drop down filter using table data. The second has to do with creating multiple drop down filters for a single grid.

The difficulty I've run into is how to populate options for a drop down filter based on data stored in the table under a given field. For example: I've got a sales order grid, and one of the columns displayed on the grid is the "Sales Rep" field. I'd like to make a drop down filter that will be automatically populated with the various entries available for the "Sales Rep" field, so that if future sales orders are entered with new names in the "Sales Rep" field, the drop down list is updated to include those names in the list without me having to do anything. That way, a manager would be able to look through each employee's sales orders without having to remember their Sales Rep ID and type that into the manual filter.

My second question is about the possibility of having multiple drop down filters for the same grid. See, I not only want to filter sales orders based on the sales rep, but also based on whether they are marked as complete or not. If I were to use a single drop down filter I would need a separate filter option for each combination - ie "Sam, completed SOs", "Sam, incomplete SOs", "Karen, completed SOs", etc. Instead of that, I'd like one drop down filter just listing the sales reps, and a second drop down filter with just two options: "complete" and "incomplete".

Edit: I found my answer for the second question (http://www.methodintegration.com/cs/forums/p/897/3020.aspx#3020), which is no. Bummer.

So here's what I'm thinking now. I set the one drop down to self-populate from the Sales Rep field. Then I set the "IsComplete" field I've created for the Sales Order table as a column in the grid, so users can use the manual filter header and just filter by "yes" or "no" in that column. Any suggestions?

Sam-

Both options are possible.

For the first question you can drag a drop down object to the screen and make it point to the Initial field in the SalesRep table (This is done in step 4 when editing a drop down object)  and then create a Filter view (Step 3 when editing a grid object) for the grid that filters based on that drop down.

For the second question you can do the same thing as in question 1 drag 2 drop down objects to the screen set them up to reference the appropriate tables and fields and create a new filter view to reference those drop downs.

If you use the Field Services/Scheduling & Invoicing Method app you see something similar to this in the Work Order List and how it filters based on date ranges.

-Michael

Michael,

Thanks for the suggestion. I'm running into problems creating my drop down objects, however.

I've created a drop down which I've stuck in the cell above my grid. Here's what I did in each step of the editing of that drop down:

Step 1: I named the caption of this dropdown "Sales Rep", and changed it to be read only.

Step 2: I didn't add any actions

Step 3: I didn't create any validation rules (these are read only drop downs)

Step 4: Selected table "SalesOrder", and field "SalesRep". In the box where I can select a value as default, there's a long list with many repetitions of the various Sales Rep names, as well as blank lines. I want the default to be blank (as in don't filter until a name is selected).

Step 5: I didn't choose any filters, I want every Sales Rep that ever was put on a Sales Order to show up on that drop down.

Step 6: No changes here, every role has permission.

After I apply those changes and publish my screen, though, the drop box is nowhere to be found! You can see the caption text, and empty space next to it where the drop box is supposed to show up, but the actual box with the down arrow and whatnot is missing.

Sam-

It because you made the drop down read only, it doesn't have to be read only. You need to base the drop down off the SalesRep table using the Initial field.

-Michael

Thanks for that, now the drop down is showing. My next issue is that every repetition of the same Sales Rep is showing up. So it won't just list "CT, KJD, SAM". It lists "CT, CT, CT, KJD, KJD, SAM, SAM, SAM, KJD, KJD, KJD.." and so forth.

I'm guessing that I can use the filter step on the drop down edit screen to make the list show only one instance of each Sales Rep, but I can't see how to make that happen, short of the "build selection list" option, which looks like it requires manually entering the list yourself.

smohyee-

What you are trying to do can't be done with filters on a drop down. You just need to recreate that drop down based off the Initial field in the SalesRep table.

Creating a drop down based on the SalesRep table will ensure that anytime a new sales rep is added it will be avalible in that drop down. It will also only show unique values since the Initial field in the SalesRep table is a unique field.

-Michael

Ah, I finally understand what you've been saying (you only had to say it 4 times! ). I need to use to Sales Rep table for the dropdown, not the Sales Order table that was using for the grid. Now I've got a working dropdown object that will provide a list of unique Sales Rep initials.

If anyone else is reading this thread to do what I did, FYI: to link the dropdown to the grid you need to go into step 3 of the Grid customization, like Michael said. I originally thought I would have a problem because I had to pick a field from the Sales Order table that the grid was based off of to filter. But I picked the Sales Rep field from the Sales Order table - for my filter instruction I put "Equal to", I got the value "from the screen", and so was able to choose the dropdown object. Fortunately for me the Sales Rep field in the Sales Order table contains the same values as the Initial field in the Sales Rep table.

Anywho, that officially wraps up this problem for me. Thanks Michael!