If I understand your question and with limited information about the relationship structure between your tables, I suspect you need to simply cause a change in the relationship connection whenever the budget item is funded. No scripting necessary for this.
What you describe is sometimes called a "dwindling value list". I'd really need to get a clearer picture of how you've set up the tables and relationships before I could suggest an approach to implement this. As TECman said, you may not need a script or indexing issues may require one, but we'd need to know more about your system.
Here is a clone of the current structure.
The value list in question in listed on the Contributions layout.
After the user selects the "Show Name", they will then select from the conditional value list of remaining budget items that need funding. Currently, the conditional list shows ALL the items related to a particular show (I need to have items removed from this list when the have been funded (or ShowBudget::total_needed = 0)
I've tried many combinations of formulas to achieve this so there may be some oddities in the tables. You really will have to take a look at it to see what I'm referring to.
Ok, I can find the field formatted with the first value list. ShowBudgets::Show_ID is formatted as a pop up menu with Show List as it's value list. But I don't find any field on this layout set up with a conditional value list.
I suspect you'll need a triggered script that sets a value in a field when each item is fully funded--which a conditional value list can then use as a "filter" to drop out the fully funded items.
Were you looking at the ShowBudgets Layout or the Contributions Layout. The conditional value list (BudgetLinesBYShow) is on the Contributions Layout. I want to make sure we're talking about the same thing before I start trying to fix it.
My post from 12:56PM today provides more specific details about my needs. (just in case you missed it)
I was on ShowBudgets. I'll take a look at contributions and see what I find...
We're in different time zones, but I think you must be 2 hours east of me--that makes it the post where you posted the download link. If I hadn't seen that post, I wouldn't have been able to download your clone.
As I suspected, the fact that you need a calculation that totals the value in another table (contributions) for your show budget record produces an indexing issue for using it in a relationship for your conditional value list.
Here's a simple calculation field, cNeededID, that you can use with your existing value list (after one small change) to drop out fully funded items from your value list:
If ( Status ≠ "Funded" ; BudgetLine_ID )
Use a script fired by a script trigger such as OnCommit on your Contributions layout to perform this script:
Set Field [ShowBudgets::Status ; If ( ShowBudgets::Total_Needed ≤ 0 ; "Funded" ; "" ) ]
Now, in your value list setup, specify cNeededID instead of BudgetLine_ID.
Note: It's also possible to forgo this calculation field and modify the relationship used by the conditional value list by including a field in Contributions that always returns "funded" in this relationship:
Contributions::constFunded = NeededShowBudgetsByShow::Status AND
Contributions::ShowID = NeededShowBudgetsByShow::ShowID
Either works, and both require a script to modify the status field when the budget line item is fully funded.
Note that deleting a contribution record will not return a budget item to the value list. You may want to use a script for such deletes that first zeroes the records contribution, runs the above update script and then deletes the contribution record.
Likewise, changes to Show Budgets records will require running this same script to add/remove items that now need a status change due to the budget change.
Wow, I'm going to begin digesting this and working on it now.
What is your recommended method? What is going to be the easiest solution to maintain or troubleshoot in the future? (should I create the new cNeededID field or modify the relationship?)
Your insight is tremendous. I will definitely need the script for returning a "funded" item BACK to the list if a contribution record is deleted. I've started doing more reading and research on writing scripts but I'm VERY inexperienced. Can you give me an example (or show me) the script I would need to accomplish this task.
You also mention the need to run the SAME script for the Show Budgets records. I'll admit you lost me there. (Would I need to modify the script at all?)
Last question (and probably very dumb) - Where do place the scripts? What fields would I attach them to?
Thanks. This solution has become a little bigger than originally conceived and I'm learning a lot of new FM functionality. Your help is VERY much needed and appreciated!
It's pretty much "6 of one, half a dozen of the other". Either way, you have to add a special purpose calculation field to control what items appear in the list, it's just that you would use one field in ShowBudget and the filed in Contributions. Take your pick. I lean just a few percentage points towards the second option (Using a constFunded field) as I think it will be easier to spot and correctly analyze should you revisit this part of your database design at a later date.
Here's a delete contribution script:
Show Custom Dialog ["Delete this contribution record?"]
If [Get ( LastMessageChoice ) = 1 //OK was clicked ]
Set field [Contributions::Amount ; 0 ]
Perform Script ["Update Budget Line Item Status"]
Delete Record [no dialog]
"Update Budget Line Item Status" is the name I'm giving the original one line script I posted earlier.
You can use an OnObjectExit or OnObjectSave trigger on the Contributions::Amount field to perform this same script to update the status.
Say you discover a mistake and a budgetamount was set at $3,000 instead of $30,000. After changing this amount in ShowBudget, you'd need to run the same script as this item may now need to change status. The same type of trigger on the BudgetAmount field can run this same trigger to update the status here as well. (Fortunately, adding or removing a record in ShowBudget will not need such an update.)
PS. having seen your database first hand, I suggest taking a look at this article on a method for better organizing table occurrences to match the function of your database layouts: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/
Back in your earlier post, you suggested using "a script fired by a script trigger such as OnCommit" on my Contributions layout. What field (or whatever) do I attach the "Update Budget Line Item Status" script to?
I see where to assign the "Delete Contribution Script" (it will go on the Contributions::Amount field).
I think I'm going to go with the first method and forgo modifying the relationships, depending on your response to this. (I was curious - was NEEDEDShowBudgetByShow the same TO as ShowBudgetByShow or would I have to create the new "NEEDED" one and connect it to Contributions as well?) - Maybe I missed it, but was constFunded a calculation field (how does it receive its value)?
Ok. I'm going to print your last two responses and have them in front of me as I work to correct this.
To repeat from my last post: "You can use an OnObjectExit or OnObjectSave trigger on the Contributions::Amount field to perform this same script to update the status." OnCommit looks to be a bit too general purpose a trigger for this.
I'd make that script a button as this for deleting the entire record rather than editing the amount. If you have FileMaker Advanced, you can even use a custom menu for this layout so that selecting delete record from the records menu or pressing the keyboard shortcut performs this script to delete the record while properly updating the status of the related budget item.
I used the new name so that I could use the different relationship required by the second option. If this value list is the only place where this relationship is used, you can use it, but renaming it to reflect it's purpose can be very helpful when you come back later and try to figure out how you set things up.
constFunded is a field of type calculation where you put "Funded" (quotes included) as the sole term for the calculation and select "text" as it's return type.
I think I see it now (but I may be wrong). Contributions::constFunded = NeededShowBudgetsByShow::status. IF the value of a record shows "Funded" in Show Budgets, then the conditional value list will only show Budget Items from the Selected Show. It seems as though this would show Funded items in the list rather than "Needed" Items.
Either way, your first method actually makes more sense to me so I think I'll begin down that rabbit hole. If you don't hear back from me, that means I've thrown my MacBook Pro in the toilet and I've commited myself to an institution for people who fail at FM.
My typo and good catch!
You can use the "not equals" operator in this relationship, but be careful, this still requires a value in ShowBudget in order for this to work, so you'd need to auto-enter text such as "not funded" when the record is created and modify the script to be:
Set Field [ShowBudgets::Status ; If ( ShowBudgets::Total_Needed ≤ 0 ; "Funded" ; "Not Funded" ) ]