8 Replies Latest reply on Apr 16, 2012 4:39 PM by FM_Rookie

    Value List from Two Fields in Same Record-FM11

    FM_Rookie

      Title

      Value List from Two Fields in Same Record-FM11

      Post

      I have a quandary...

      I have table CONTACTS with these fields:  Spouse1 and Spouse2

      I have a portal based on another table, ACTIVITIES, that I want a Value List to contain:
           Spouse1
           Spouse2
           Both (or "Spouse1 & Spouse2" would also work)

      I know I need to make a new TO for the value list, but don't know how to extract the values of the two fields to make the list.  I'm guessing I need to use global fields & maybe a script, but I don't know how.

      Thanks if advance!

        • 1. Re: Value List from Two Fields in Same Record-FM11
          philmodjunk

          What happens if you need a "spouse 3"? YOu may need to rethink how you link a contact to a spouse here as a join table may be much more flexible.

          Using what you have at the moment, you could base your value list on a third field that combines the values of Spouse1 and Spouse2 such as this calculation:

          List ( Spouse1 ; Spouse2 )

          You'll see the contents of Spouse1 and Spouse2 listed as separate entries in the value list.

          • 2. Re: Value List from Two Fields in Same Record-FM11
            FM_Rookie

            Spouse3 is not an option, as the table being referred to is read-only SQL.  If the marriage ends (widowed, divorced) it doesn't really matter for this file, as it will be noted in the main, related SQL read-only file through another interface.  I just need a value list of current husband, wife, both.  Still don't know how to do it.

            • 3. Re: Value List from Two Fields in Same Record-FM11
              philmodjunk

              The method I specified with the List function is intended for you to use if you don't change your table/relationship design.

              You specify the calculation field in place of your two separate fields as the source of values for your value list.

              • 4. Re: Value List from Two Fields in Same Record-FM11
                FM_Rookie

                OK, I created a field called "Select_Spouse" with a calculated field as:  List ("Husband"; "Wife") just to try to get the value list to work.  Then, I made a value list named "Select Spouse Value List" and have it refer to the above table & field.  But nothing pops up.  What am I missing?

                • 5. Re: Value List from Two Fields in Same Record-FM11
                  philmodjunk

                  What return type did you specify for the calculation field? Text or Number? Should be text.

                  Why use literal text in quotes for this? If you just want the words "wife" and "husband" in your list, use a custom value list.

                  • 6. Re: Value List from Two Fields in Same Record-FM11
                    FM_Rookie

                    I used it as a test for the LIST function (haven't used it before).  I'm learning each step so I'm taking what I know first, seeing if it works, then going on to the next thing (having it look up fields).  I seem to be having real problems...

                    I found out that you can't use a calculation field in making Value Lists, so you have to have a calculated entry (similar, & do-able if I can get this to work).  But I can't get the LIST function to work.  In the Value List, I presume you have "Show only related values from..." the table you get the names from?  I may just go with "Husband, Wife, Both" but then there are single people.  The reports would be confusing...  argh!

                    • 7. Re: Value List from Two Fields in Same Record-FM11
                      philmodjunk

                      I found out that you can't use a calculation field in making Value Lists, so you have to have a calculated entry (similar, & do-able if I can get this to work). 

                      Not true, you can use a calculation field as the SOURCE of your values for the value list, but you can't enter values into the calculation field nor would you want to. You would set up the original text fields with this value list that is drawn from the calculation field that combines the values.

                      I see no reason at this point for "include only related values..." unless there is a need for a relationship to limit the values listed to a related set of records.

                      • 8. Re: Value List from Two Fields in Same Record-FM11
                        FM_Rookie

                        It works!  Thanks PMJ!!