7 Replies Latest reply on Jan 31, 2015 11:29 AM by kasenofonte

    Select certain text from one field to create a drop list.

    kasenofonte

      I have a field that contains an address, some of which has two lines, I only want the first line to show up in a drop down, how can I do this?

       

      Thanks

        • 1. Re: Select certain text from one field to create a drop list.
          erolst

          Create a calculation field as


          GetValue ( addressField ; 1 )

           

          make sure to set the result type to text, and use that field in your value list definition.

           

          For use in a value list, the calculation field must be indexable; that means that the address field itself must be indexable, too.

           

          Just something to be aware of.

          • 2. Re: Select certain text from one field to create a drop list.

            Instead of using a calculated field, use a text field and in the definitions click the button to make it a calculation. This becomes text which is always index-able. Pure calculated fields can't be indexed because they can change in an instant.

             

            When you create or modify the record the field will calculate just like the other but without its disadvantages. If you change the address, then this will change. If you change another field, this will not change.

             

            The calculation in the definitions setup is good for changing when only the field involved change and when you want to index the result.

            A calculated field can be used for calculations based on other events, other fields, etc. Not indexable for that reason.

            • 3. Re: Select certain text from one field to create a drop list.
              erolst

              jackrodgers wrote:

              Pure calculated fields can't be indexed because they can change in an instant.  […]

              A calculated field can be used for calculations based on other events, other fields, etc. Not indexable for that reason.

              These statements are wrong.

              • 4. Re: Select certain text from one field to create a drop list.
                jbfmp11

                This method allows the user to see the 1st line of the address as a single line in the drop down list.

                The full contents (multiple lines) is inserted into field when selecting from drop down list.

                You can modify to show both address lines (or as many lines as you want) as a single line in the drop down list.

                New address line data can be entered if not found in drop down list.

                You can modify this technique to insert validation and formatting calculations as desired.

                 

                1. Assume input text field can contain a variable number of lines and field name is ‘data::address’ (table::field)

                 

                2. Create a text calculation field:

                 

                    data::addressindex = Substitute ( LeftValues ( data::address ; 1 ) ; Char ( 13 ) ; Char ( 32 ) )

                 

                Note:

                (a) choose number of lines that you think will make you drop down values unique; in this case 1.

                    change to 2 or greater if the first line of the address is not unique.

                (b) Char ( 13 ) is the return character. Char ( 32 ) is a space so it does not look ugly in the drop down list

                      I am using the Char function here, only to make clear in this text reply, what characters are being used.

                (c) By default, indexing is “turned on when required” on field creation.

                  

                3. Create/update the drop down Value List from the field data::addressindex

                 

                    Assume Value List name is ‘address index

                 

                4. In Manage Database|Relationships, duplicate the table ‘data’ as table ‘data2’ and self join using the relationship:

                 

                    data::addressindex = data2::addressindex

                 

                5. For the field ‘data::address’, change auto-enter field options: Calculated value:

                 

                    Lookup ( data2::address ; data::address )

                 

                Note:

                (a) Make sure ‘Do not replace existing value of field (if any)’ is un-checked.

                (b) Make sure the calculation is evaluated in the context of table: data

                 

                6. Create/update layouts with 'data::address' as the input field with drop down list named ‘address index’.

                • 5. Re: Select certain text from one field to create a drop list.
                  erolst

                  Wow, that is one convoluted method.

                   

                  Assuming that that …

                  jbfmp11 wrote:

                  This method allows the user to see the 1st line of the address as a single line in the drop down list.

                  The full contents (multiple lines) is inserted into field when selecting from drop down list.

                  … is what the OP wants, then here is the easy way:

                   

                  1. Create calculated one-liner

                  2. Create value list with 1. field: original address field, 2. field: one-liner calc, check “Show only values from second field”.

                  3. Apply value list to field.

                  • 6. Re: Select certain text from one field to create a drop list.
                    wimdecorte

                    erolst wrote:

                     

                    jackrodgers wrote:

                    Pure calculated fields can't be indexed because they can change in an instant.  […]

                    A calculated field can be used for calculations based on other events, other fields, etc. Not indexable for that reason.

                    These statements are wrong.

                     

                    Erolst is correct, Jack is wrong.  Stored calculations can be indexed without any problems.