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.
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?
"...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
Set Variable [$Name; Value: Leftwords ( Get ( ScriptParameter ) ; WordCount ( Get ( ScriptParameter ) ) - 1 ) ]
Set Variable [$Type; Value: Rightwords ( Get ( ScriptParameter ) ; 1 ) ]
Go To Layout ["ValueTable" (ValueTable)]
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.
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.