4 Replies Latest reply on Dec 2, 2009 5:46 PM by angharad

    Conditional Value List with values from two different fields

    angharad

      Title

      Conditional Value List with values from two different fields

      Post

      I've looked through the value list documentation & posts and can't seem to find a relevant answer.  Relatively new user Filemaker 10.

       

      I'm building a database for a nonprofit.  There are two reasons people give - because they attended an event, or in response to an appeal.  I have a donations table, an event table, and an appeals table.  I would like data from the "Event_Name" field in the event table, and data from the "Appeal_Name" field in the appeal table, both to provide a value list for the field "Donation_Reason" in the donations table.  Using the conditional value list box I can get either the data from Event_Name, or that from Appeal_Name, to appear, but not both.  What am I doing wrong?

       

      Thanks for any help you can give.

        • 1. Re: Conditional Value List with values from two different fields
          philmodjunk
             You need all your data in the same table. You will either need to create a new table just for your value list and export all your values from both tables to it or you will need to find a way to replace your current event and appeals tables with a single unified table.
          • 2. Re: Conditional Value List with values from two different fields
            angharad
              

            Thanks so much, PhilModJunk!  Wow, you're quick.  Would you be so kind as to give me still more help implementing your solution?  I think I want to keep two tables, because I don't want users to accidentally credit an event donation to an appeal, or vice versa.  Is there a slick way I could get new entries in the "Event_Name" field, and new entries in the "Appeal_Name" field, to automatically go to my new field (& table) that generates my value list?

             

            Peace,

            Angharad

            • 3. Re: Conditional Value List with values from two different fields
              philmodjunk
                

              "...I don't want users to accidentally credit an event donation to an appeal, or vice versa."

              With the right layout/script design, it should be easy to prevent such mistakes.

               

              If you really want to generate a "merged" table of values and are using FMP 10, you can use the Commit Record script trigger in Layout Setup... to perform the following script

               

              Freeze Window

              Set Variable [$Name; Value: Leftwords ( Get ( ScriptParameter ) ; WordCount ( Get ( ScriptParameter ) ) - 1 ) ]

              Set Variable [$Type; Value: Rightwords ( Get ( ScriptParameter ) ; 1 ) ]

              Go To Layout ["ValueTable" (ValueTable)]

              New Record/Request

              Set Field [ValueTable::Name; $Name]

              Set FIeld [ValueTable::Type; $Type]

              Go To Layout [Original Layout]

               

              When you set this up on your events layout, specify the following calculated parameter:

               

              Events::Event Name & " " & "Events"

               

              You can put the same trigger on your appeals layout, but specify

               

              Appeals::Appeal Name & " " & "Appeals"

               

              Now you can create a relationship that links to ValueTable::Type to control whether Events or Appeals data is visible in the value list.

               

              Note that this is a lot of scripting that has to be set up just right, where a merged table with layout/script/relationship techniques to keep appeal and event records separate won't need this approach.

              • 4. Re: Conditional Value List with values from two different fields
                angharad
                   Well, your script worked perfectly and I learned a lot implementing it.  But I think you're right and the elegant solution will be in layout/script design.  So you answered my question, solved my problem, AND educated me.  Thank you very much indeed.