1 2 3 Previous Next 37 Replies Latest reply on Aug 10, 2011 4:38 PM by brianquillin

    Need a script for removing items from a value list

    brianquillin

      Title

      Need a script for removing items from a value list

      Post

      I have a conditional value list "using values from a field" (showing remaining budgeted items that need to be funded by contributions).

      One the contribution page, when entering a new contribution, the user will select the 'show_name' in the first pop up menu.  The conditional pop up menu below (the conditional value list - budgetline_ID & showing budgetline_name) needs to reflect ONLY the items that still need funding.  

      Each budget item may be funded by many contributions, therefore I have a total_needed field on the budget items layout.  When a new contribution is made toward that budget item, the total_needed field behaves correctly.

      ***When budget_line::total_needed = 0, I want to have that budget item removed from the conditional value list on the contributions layout.***

      What is my easiest method to acheive this outcome?  I am a novice 'filemaker' with no experience in scripting but it appears I may have to try it this time.  If scripting is my only option, I will need some pretty specific guidance on how to write it properly.

      Thanks in advance...

        • 1. Re: Need a script for removing items from a value list
          casey777

          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.

          • 2. Re: Need a script for removing items from a value list
            philmodjunk

            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.

            • 3. Re: Need a script for removing items from a value list
              brianquillin

              http://dl.dropbox.com/u/9855171/Solicitations%20Copy%205%20Clone.fp7

              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.

              • 4. Re: Need a script for removing items from a value list
                philmodjunk

                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.

                • 5. Re: Need a script for removing items from a value list
                  brianquillin

                  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.

                  • 6. Re: Need a script for removing items from a value list
                    brianquillin

                    My post from 12:56PM today provides more specific details about my needs.  (just in case you missed it)

                    • 7. Re: Need a script for removing items from a value list
                      philmodjunk

                      I was on ShowBudgets. I'll take a look at contributions and see what I find...

                      • 8. Re: Need a script for removing items from a value list
                        philmodjunk

                        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. Wink

                        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.

                        • 9. Re: Need a script for removing items from a value list
                          brianquillin

                          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!

                          • 10. Re: Need a script for removing items from a value list
                            philmodjunk

                            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]
                            End If

                            "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/

                            • 11. Re: Need a script for removing items from a value list
                              brianquillin

                              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.

                              • 12. Re: Need a script for removing items from a value list
                                philmodjunk

                                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.

                                • 13. Re: Need a script for removing items from a value list
                                  brianquillin

                                  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.

                                  • 14. Re: Need a script for removing items from a value list
                                    philmodjunk

                                    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" ) ]

                                    1 2 3 Previous Next