Hard coded filtered value list

[AndyCan]

Hi,

I'm sure this has been asked many times, but I haven't been able to get my head around the answers in other posts. Here's what I need to do:

I have a CONTACTS table with a client type field, eg. Supplier, Intermediary, Customer, etc. What I want to do is create a value list that only shows the Contacts tagged as Intermediaries. How do I go about doing this?

Thanks in advance!

[mleering]

Hi AndyCan;

The way that I typically go about this,

is to create a new stored calculation field in the CONTACTS table.

The calculation could be something like:

Case

(

contactType = "Supplier";

    contactName;

//Else

    ""

)

This calculation will only return the contact's name if the contact is of type "Supplier"... otherwise it returns a NULL value.

When FileMaker creates a ValueList, it will exclude any entries that have a null value.

So, essentially, you can make your value list use the calculation field.

HTH

[AndyCan]

Thanks mleering. That worked nicely.

I actually created two calculation fields. One for the primary key and one to hold the person's name. The primary key is what is stored in the new field as opposed to the name.

I'm having a bit of trouble though showing the persons name in a second field. What I normally do is have two fields on a layout, one on top of another. The inferior field holds the key and opens the value list. The field that goes on top is related to the persons name and is not editable in browse mode.

Since I'm in the contacts layout which is linked to the contacts table, All I'm seeing the the name of the contact for the current record and not the contact chosen as intermediate/supplier/etc.

Any opinions? Do I need to play around with new table instances in the relationship graph?

Thanks again!

[mleering]

No problem.

:

In most cases, it won't be necessary to have 2 calculations for this purpose though. In most cases, I use one of the value lists that inserts one field, but displays the contents from another. If you're doing that, then you can have the primary key as the field that gets inserted, and the calculation as the field that gets displayed. Even though the primary key might have a value for a particular record, if its corresponding calculation does not, then nothing will show up in the value list, and hence, that primary key can never be inserted into a field through that value list.

As for the incorrect contact displaying on the top-most field. Is that field pointing to the same table occurrence that the layout is based on? If it is, then that will need to be changed.

Assuming that the field that you can enter from browse mode is a field created specifically to store the supplier's ID, then I would have a relationship based on that ID... relating to another occurrence of Contact (ex SupplierContact). Once that relationship is created, then the top-most field on your layout can be directly pointing to the contact name from the SupplierContact table occurrence.

Best of Luck!

Matt

[littleb3]

Hi Mleering,

I have attempted to implement your solution and have run into an issue. The value list I am creating from the store calculation field fills in my primary key data. My preference is that the user would be to only see a secondary field which is a description (rather then both the ID number AND the description), but when I choose that option, the list suddenly includes the records which are returning the null value from the calculation.

Any thoughts on getting around this?

Thanks!

[mleering]

Hi littleb3;

it definitely sounds like something's awry.

The trick to remember about this technique is that when FileMaker creates a value-list showing only the 2nd field,

it will only add entries into your value-list that actually contain a value (other than NULL) in the 2nd field.

The first thing that I would do (and perhaps you've already tried this), would be to create a value list that only uses the calculation that you wrote.

Make sure that it's returning only the names (not IDs) of the people you want to see. If that's working, then the next step will be simple.

Simply change your value list to use the regular ID field as the first field, and your calculation as the 2nd field (and of course, only display the 2nd field).

I've attached an image that might tell a thousand words too.

Hopefully this clears things up for you,

but let me know either way!

Warm Regards

Matt

[littleb3]

Hi Matt.

Thanks so much for the help. I did already try this, the new issue seems VERY unusual. For some reason, when I do it that way, I get only a dozen or so result on my value list instead of the hundred that are in the database. It also fails in its sorting - they are all over the place in terms of order. This also happens when I sort by second field but am showing both.

For some reason it works perfectly when I have it set to the ID field and showing a second field but only when sorted by the first. Unfortunately this is a really cruddy way for a user to select form the choices, since the IDs don't mean much to them, only the description (second field).

Any clue why it would cut it off like that? The values that show up seem to be random too. They don't seem to stand out in any way. They are not in order of entry or alphabetical and have all the same info as any other given value.

I double checked that the calculation were working properly by adding their field to their layout and I am getting the proper result on the proper records so I think the issue lies somewhere in how FMP creates the value list. Though this is the first time I have done any sort of stored calculation field so it is possible I did it wrong.

Thanks!

Jessica