Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Custom Value Lists?


coyotl
 Share

Recommended Posts

Hello,

 

I have a table containing a company name, and a 'type' field that is represented by a value list set of checkboxes (i.e., "vendor", "manufacturer", etc.)

 

What I would like to do is create a custom value list containing company names for all companies that are "vendor"s, another for all companies that are "manufacturer"s, etc.

 

Is this at all possible? If not, does anyone have any suggestions as to how this could be done?

Link to comment
Share on other sites

In FileMaker parlance, that's not a "custom" value list, but instead one (or more) value lists based on field values (optionally: restricted by relationship).

 

Three valid approaches:

 

1) (the one I recommend in FileMaker 7/8): Define a calc field, text, Case(Type="Vendor", Company Name). Define a second calc field, text, Case(Type="Manufacturer", Company Name). Repeat for every value of "type" for which you want a value list. Create value lists based on the value of each of those fields.

 

2) Create calc fields, text, in the table or tables from which you need to access the values of the different value lists, these fields being constants (specified text strings). One field = "Vendor", another field = "Manufacturer", etc. Establish relationships between each of these to your Company table, thus provoking the generation of new Table Occurrences "Vendors", "Manufacturerers" and whatever other values for "type" you do this for. Create value lists for each, defined as values for Company Name as viewed through the respective relationships. Contributes to a nasty tangled mess in your relationship diagram, otherwise functionally as good as option 1 above.

 

3) The dynamic version. Requires more futzing around on the data-input end of things, less klunky in the architectural side of things. Define one field in the tables from which you need to access the values of the different value lists, a global field, g.Type. Establish a relationship between g.Type and Type in your Company table, thus provoking the generation of a single new Table Occurrence "SelectedCoType" or similarly-named equivalent for every table occurence you have to establish it from. Create a value list for it (or each of those if plural) based on values for Company Name as viewed through that relationship(s). Put g.Type on the layouts for users to select the company type before clicking into the field from which they should select the Company Name.

Link to comment
Share on other sites

Hmmmm, well I hope you don't ever run into a situation where you have two Manufacturers named Brown Mfg. because you won't EVER be able to select the second Company from a value list. Value lists suck for ANY names because there is always possibility of duplicates and value lists remove duplicates. A conditional value list is fine on Customer TYPE but not Customer NAME.

 

Without (yet) a clear idea of your purpose, it sounds like a portal (based upon selection of Manufacturer from radio button or popup attached to global text) producing a list of Companies might fit the need? Just a thought ...

 

LaRetta smiley-smile

Link to comment
Share on other sites

That's a good point. If you're going to go the route I described, you need to require that each name be unique. (You can always create an alias field to use for invoices and correspondence, if the gyrations you go through to make them unique results in companies not being called the same thing as what they call themselves)

Link to comment
Share on other sites

Hence the reason for unique IDs to begin with. I would hope they exist in this structure. Maybe if we know the purpose of the request for company names in a 'custom' value list, it will all become clear.

Link to comment
Share on other sites

  • 2 weeks later...
Hence the reason for unique IDs to begin with. I would hope they exist in this structure. Maybe if we know the purpose of the request for company names in a 'custom' value list, it will all become clear.

Well, I'm defining a database for a fountain pen collection. In addition to the 'pen' table, I have an 'address' table. The 'address' table has a field that indicates whether the address is a vendor, a manufacturer, an artisan, or a combination of two or three of these.

 

In the 'pen' edit screen, I have a pop-up menu for the pen manufacturer, and another pop-up menu for the person who sold me the pen (the vendor). I'd like to populate these with only the appropriate list of names.

 

coyote

Link to comment
Share on other sites

To combine my original advice with LaRetta's observation —

 

Either set the NAME field in the Addresses table to be required to be Unique in field validation and use my first suggestion in post #2, OR

 

Have a serial number for each value in Addresses (unique, created at record creation time) and use my first suggestion except have the value list show the serial number and the Name field. The field that gets input in the Pen screen is actually the serial number, and it either looks up the related Addresses::Name field or it simply displays related values (based on relationship of Addresses ID to Addresses ID).

 

I think the first of these options is easier to use: the value list will be alphabetical by the value in the Name field, whereas in the second option it would be in order of Address ID unless you had it sorted by Name, in which case if you had two entries of the exact same name only the first one would show in the value list, thus eliminating the advantage of doing it in the first place. (The tradeoff is that if you actually do have two totally different entries with the same name, you can't put them in that way).

 

At any rate, define a Vendor_VL field, calc, text —

 

Case(PatternCount(Addresses::Type, "Vendor")>0, Addresses::Name) {for 1st option} OR

Case(PatternCount(Addresses::Type, "Vendor")>0, Addresses::Address ID) {for 2nd option}

 

Then make a value list of all values of that field {1st option} or that field + the corresponding Name field {for 2nd option} and that's your Vendors. Repeat process for other Types.

Link to comment
Share on other sites

Okay, here is what I'm doing...

 

(Note that the 'address::type' field may have both "Vendor" and "Manufacturer" selected.)

 

The address record has a serial field called 'id'. I've also defined a field 'address:vendor_id' defined as a calculated value using the formula:

Case( Exact ( FilterValues ( address::type ; "Vendor" ) ; "Vendor¶" ); address::id )

Then I define the value list 'vendors' to be "Use values from field", "address::vendor_id" and "address::company". "Include all values", "Show values only from the second field", and "Re-sort based on English" are all checked.

 

The problem is that the resulting value list seems to have every company name, including those that are not vendors. I've checked and my 'address::vendor_id' calculation seems to work correctly. What am I doing wrong?

 

(Oh, and 'company' is defined to be a unique value.)

Link to comment
Share on other sites

FilterValues should work as well as PatternCount. I would not think you'd have to add the hard return. But is it working now?

 

In fact, if it isn't working, try removing the hard return. "¶" is a delimiter, FileMaker doesn't see it as part of the value but as the place where one value stops and the next begins.

Link to comment
Share on other sites

FilterValues should work as well as PatternCount. I would not think you'd have to add the hard return. But is it working now?

It is working to put the correct id into the fields. (Verified with the data viewer.) What isn't working is the value list I'm trying to generate... it still gets every item.

 

In fact, if it isn't working, try removing the hard return. "¶" is a delimiter, FileMaker doesn't see it as part of the value but as the place where one value stops and the next begins.

I used FilterValues just as an extra safety step. PatternCount would cause errors if one value was part of another... i.e., if two possible values were 'Manufacturer' and 'fact'. A little paranoid, but FilterValues ensures that the new-line delimited values are treated as unique atoms.

 

Strangely, FilterValues does apprend a new-line ('¶') to the value it returns.

 

A question... does string comparison using '=' compare case-sensitive or case-insensitive?

 

The value list still isn't working...

Link to comment
Share on other sites

a) re: "PatternCount would cause errors if one value was part of another", I do usually reference the hard returns in the PatternCount function, like so:

 

Case(PatternCount("¶"&ValueListItems(Get(FlleName), "ValueListName")&"¶", "¶"&String&"¶")>0, Table::ID Field)

 

The appended returns front and back handle the possibility of it being the very first or very last value in the value list; all other values are already going to be preceded and followed by a hard return.

 

 

b) Put the actual calculated field you created for generating the VL on a list view layout and look at the values its returning. If you're getting values for records that don't have Vendor checked, something is fishy in the formula. If the values returned look as you'd expect (coming up blank for non-Vendor records), problem has to be in the way the value list is defined.

Link to comment
Share on other sites

b) Put the actual calculated field you created for generating the VL on a list view layout and look at the values its returning. If you're getting values for records that don't have Vendor checked, something is fishy in the formula. If the values returned look as you'd expect (coming up blank for non-Vendor records), problem has to be in the way the value list is defined.

Thanks, the list view layout is a good tip, and I think I understand what's happening. What I don't understand is how to fix it.

 

When I specify that the value list should contain the values from 'address::vendor_id' and 'address::company', I get only the companies for which 'address:vendor_id' is non-null. The entries in the list look like

5 Bexley Pen Company

If I tell the value list to 'Sort values using second field', however, I get every address, with some looking like:

_Grayson Tighe

(Where the underscore represents a space.)

 

The same thing happens if I say 'Show only values from the second field', which is what I want. What concepts am I missing? How can I get the value list to show only the 'address::company' field for records where 'address::vendor_id' is non-null?

 

coyote

Link to comment
Share on other sites

You actually might be better off going with the names themselves instead of the IDs. It's very un-SQL-like to do so, but FileMaker ain't SQL.

 

Keep in mind:

 

• Unless your Name values are all required to be unique, sorting by names is going to cause you just as many headaches using unique ID numbers as you'd have using the Names themselves and having multiple entries of the same name.

 

• If, on the other hand, you do require the Names to be unique, your only issue is "what happens if the company changes its name". Aside from that, it's a unique string and works as well as a unique numerical string to specifically identify the record. With the added advantage that you know what "The Acme Pen Company" means whereas "59932" is meaningless unless accompanied by "The Acme Pen Company" as explanatory second field.

 

 

 

Be all that as it may, you do have yet another option. Mildly cumbersome to explain, not bad once you've got it set up.

 

Change your field def:

 

Case(PatternCount("¶"&ValueListItems(Get(FlleName) , "ValueListName")&"¶", "¶"&String&"¶")>0, Table::Name &"|"&Table::ID Field)

 

Then create a brand new text field in Pens, let's call it SelectedVendor for this example. This field will replace Vendor ID on your layout and will be the field that gets the new value list.

 

Then edit your existing Vendor ID field in Pens (the one you had the old value list attached to), put an auto-enter calc (do replace existing values), Evaluate[Middle(Pens::SelectedVendor, Position(Pens::SelectedVendor, "|", 1, 1)+1, Length(Pens::SelectedVendor)) {Pens::SelectedVendor}].

 

In other words, every time a new entry gets popped into your new field SelectedVendor, auto-enter just the Vendor ID portion of it that's appended on the right-hand end into the Vendor ID field.

 

Which in turn should give a valid connection to Addresses based on Vendor ID::Addresses ID. (Or whatever you call those fields).

Link to comment
Share on other sites

Change your field def:

 

Case(PatternCount("¶"&ValueListItems(Get(FlleName) , "ValueListName")&"¶", "¶"&String&"¶")>0, Table::Name &"|"&Table::ID Field)

 

Then create a brand new text field in Pens, let's call it SelectedVendor for this example. This field will replace Vendor ID on your layout and will be the field that gets the new value list.

 

Then edit your existing Vendor ID field in Pens (the one you had the old value list attached to), put an auto-enter calc (do replace existing values), Evaluate[Middle(Pens::SelectedVendor, Position(Pens::SelectedVendor, "|", 1, 1)+1, Length(Pens::SelectedVendor)) {Pens::SelectedVendor}].

 

In other words, every time a new entry gets popped into your new field SelectedVendor, auto-enter just the Vendor ID portion of it that's appended on the right-hand end into the Vendor ID field.

 

Which in turn should give a valid connection to Addresses based on Vendor ID::Addresses ID. (Or whatever you call those fields).

Not surprisingly, this is a little confusing to me. First, when you say:

Case(PatternCount("¶"&ValueListItems(Get(FlleName) , "ValueListName")&"¶", "¶"&String&"¶")>0, Table::Name &"
|
"&Table::ID Field)

what does the vertical bar do? It shows up in my value list (i.e., "A.T. Cross|1".) Is it simply a separator that you can search for later, or does it have special meaning (like '¶')?

 

Secondly, won't the disparity between the values in the values list ("A.T. Cross|1") and the values in the field (1) cause the list to not display the company name for the selected ID when this record is displayed?

 

One of the reasons I have avoided using company names and used IDs instead is to save storage. Am I wrong in thinking that the replication of text strings is significant?

 

coyote

Link to comment
Share on other sites

what does the vertical bar do? ...Is it simply a separator that you can search for later...?

 

The pipe is a separator that is easily "middled" for in the auto-enter formula for the actual Vendor ID field.

 

won't the disparity between the values in the values list ("A.T. Cross|1") and the values in the field (1) cause the list to not display the company name for the selected ID when this record is displayed?

 

Not at all, because you aren't entering "A.T.Cross|1" as a value in Vendor ID, you're entering it as a value in a newly created field, SelectedVendor. When you do so, the Vendor ID field auto-enters "1", calculating that value via "Middle" and "Position" within an auto-enter "Evaluate" clause from the value "A.T.Cross|1" in SelectedVendor.

 

The relationship between tables is still based on Vendor ID, not SelectedVendor.

Link to comment
Share on other sites

Not at all, because you aren't entering "A.T.Cross|1" as a value in Vendor ID, you're entering it as a value in a newly created field, SelectedVendor. When you do so, the Vendor ID field auto-enters "1", calculating that value via "Middle" and "Position" within an auto-enter "Evaluate" clause from the value "A.T.Cross|1" in SelectedVendor.

Okay, but doesn't the user still entries that look like 'A.T. Cross|1' when they select the vendor for the current record from the pop-up menu?

Link to comment
Share on other sites

Yes, the viewer perceives that to be the value they are selecting. (Is that a problem?)

Well, it would be much nicer if they saw simply the name of the company, and the record ID was invisible to them.

Link to comment
Share on other sites

Try this, then:

 

Two fields:

 

VendorOnly Name =

Case(Addresses::Type="Vendor", Addresse::Company Name)

 

Vendor ID =

Case(Addresses::Type="Vendor", Addresses::ID Field)

 

 

Make your value list from Vendor ID, also showing VendorOnly Name, sorting by VendorOnly Name.

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use