3 Replies Latest reply on Aug 18, 2011 11:43 AM by philmodjunk

    Value lists as text coding as numbers



      Value lists as text coding as numbers


      Hi there, I am new to FileMaker Pro and need to get this figured out by a deadline tomorrow. I have completed a database from a survey with over 100 fields. Many of the fields use the same value lists. As an example there are 14 different communites in the "communities" value lists that come through in a drop down menus. This is done several times through the survey. I have need to keep the option for "other" for those infrequent communities. My question is how do I have the communities be encoded as numbers? I am using FileMaker Pro 11.0v3. Also, when the other option is chosen and an obscure community is typed in, that needs to come through as a text value, not a numeric one. 

      Whoever could help me I would be forever greatful!! We are a small business without a tech person --> I am it!

        • 1. Re: Value lists as text coding as numbers

          Hmmm, your database design, (Survey with over a hundred fields), makes this more difficult than it needs to be.

          Are these single value fields or can some of the store more than one value? (Such as selecting two or more checkboxes in a single field...)

          What do the numbers represent? Do you want a count of how many times a specific value was selected or are these to be codes that uniquely identify each value with a number?

          • 2. Re: Value lists as text coding as numbers

            Most are single value fields, though I have some checkboxes where selection of two or more can occur. Yes, so for the third community on the dropdown list coming up as Yorktown, I would want a '3' to be displayed in the database. That is my main issue

            • 3. Re: Value lists as text coding as numbers

              Warning, due to the structure of the database, this will get really messy. I'm not suggesting a design here, just a "quick and dirty" fix that may enable you to meet your deadline. If you plan to repeat this project, I strongly suggest that you fully redesign your database system so that you can avoid this issue in the future. There are several threads here in this form that discuss how to design a database for taking/recording surveys that you may want to search out to learn more...

              Add a new table to your database. Give it at least 2 fields, one will be the number corresponding to the value in your value list. The other will be the value itself. If you already have such a table of values, just add the number field to it and enter numbers that correspoind to the value.

              Now, link your field with the value list to the matching field in this table to create a relationship between them. (This is done in Manage | Database | Relationships). You can then refer to the number field in this related table to get the value's position in the value list as a number. Since you appear to have many fields that use the same value list, you'll need to create multiple occurrences of this table and link each to one of the different fields formatted with this same value list.

              Say you have a "Communities" value list with San Francisco, Oakland, San Jose as its values and you want 1 returned for San Francisco, 2 for Oakland, etc.

              Your "Communities table" would have three records:

              Code     Name
              1           San Francisco
              2           Oakland
              3           San Jose

              In your original Survey table, you have Two fields that use this value list, BirthCommunity and ResidenceCommunity. Use Manage | Database | Relationships to create this relationship:

              SurveyTable::BirthCommunity = Communities::Name

              Now you can add the Communities::code field to your layout to show the code and calculations can refer to Communities::Code if you need that. To do the same for the ResidenceCommunity field, return to Manage | Database | Community and select Commnities by clicking it, then click the button with two green plus signs to make  copy of this table occurrence. Now you can create this relationship:

              SurveyTable::ResidenceCommunity = Communities 2::Name

              and you can refer to Communities 2::Code to get the code.

              This only works for fields where only a single value can be selected. I'm not sure how you want to do with the codes for a field where two or more values can be selected as this implies producing more than one code for that field.