11 Replies Latest reply on Aug 9, 2011 4:44 AM by brianquillin

    Calculation to change record 'status'

    brianquillin

      Title

      Calculation to change record 'status'

      Post

      I have budget items assigned to be either 'need' or 'have'.

      I have contributions linked and able to be assigned to a budget item.  (the related contribution ID and amount are accurately displaying on the budget item layout.

      I created a calculation field titled status_AUTO stating: If (budget_amount = Contributions::amount ; status_budgetline = "have" )

      On the budget layout, the radio button set indicating 'need' or 'have' is not updating when the contributed amount = the budgeted amount.  I tried to create a value list using this new calculation value but an error message said it would not work since it could not be indexed.  

      Maybe a dumb question, but here it is: Where do I place the status_AUTO field on the budget layout? OR how do I make it work?

        • 1. Re: Calculation to change record 'status'
          brianquillin

          I've decided to change the calculation field to this:

          If (total_needed = 0 ; status_budgetline = "have" )

          Total_needed is a calculation of budget_amount - total_contributed (which adds all related contributions).

          Regardless, I still can't make my brain find the correct path to change the status field for the record.

          • 2. Re: Calculation to change record 'status'
            philmodjunk

            Try: If ( Total_needed = 0 ; "have" )

            Status_budgetline = "have" is a boolean expression that compares the value of Status_budgetline to the literal text "have" and returns True (the number 1 ) if they are equal and False (the number zero) if they are not.

            • 3. Re: Calculation to change record 'status'
              LaRetta_1

              Hi Brian,

              If you simply need to display the word 'have' next to an item (if your TotalNeeded = 0) then you might consider an alternative rather than add another calculation to your table.  Here's an idea:

              Place your TotalNeeded field on your layout and go to Inspector and the Data tab.  Turn off entry to the field (Behavior > uncheck Browse Mode) and below in Data Formatting, change the number format from General to Boolean.  Remove the data from 'show non-zeros as yes' and change the 'show zeros as' to have.

              We have many tools available (merge fields, merge variables, conditional formatting etc) which can replace calculation fields in various situations - easing field bloat and keeping our systems fast. :^)

              • 4. Re: Calculation to change record 'status'
                brianquillin

                Thanks to you both.

                I have a status field for each budget item indicating it as 'have or need'.  I am using this value to filter a portal on the layout also so I need to retain it as a field.

                I tried: Try: If ( Total_needed = 0 ; "have" ) - but no change in the status value.  (I realize I have to associate that status field with this calculation, but I'm not sure how.

                Again, this may be a dumb question, but how and where on the layout do I incorporate that calculation to cause the radio buttons on the layout to toggle automatically (from need to have) when the budget line has received funded?

                • 5. Re: Calculation to change record 'status'
                  philmodjunk

                  You can't use this method to modify values in the portal. The expression only changes the value of a field in the current record--which is in the parent record--not the portal in your case.

                  You'd need either a script to modify the value in this field of every related portal record, or you need a calculation field in the portal records that refers back to the parent record in order to compute the correct status.

                  • 6. Re: Calculation to change record 'status'
                    brianquillin

                    I believe this can still work for me.  I changed the field on my parent record page to reference status_AUTO (which I used Phil's recommended formula) instead of status_budgetline and it produced the desired result.

                    Last part, here's the kicker:  Can I set a default result for status_AUTO to reflect the 'need' value when a new record is created.  If the calculation continues to work as Phil suggested, then I'm good to go.

                    (The portal is actually Budget 2 on the Budget Parent Page).  I'll test that out now.

                    • 7. Re: Calculation to change record 'status'
                      LaRetta_1

                      Do you want a calculation which displays "have" or nothing (as you originally requested) or do you want to include now "need"?  A radio button wouldn't normally be used with a calculation because radio button implies User input and they cannot input on a calc.   I am unclear when you say, "when the budget line has received funded?" Also, is this calculation a true calculation?  Or is it text field with auto-enter calculation on it?

                      "I have a Status field for each budget item ..."

                      This sends red flares ... usually have 'like' fields in a table indicates they should be related records.  I suggest that you upload your file somewhere (it can be empty clone) then provide a link to it here in a response back, hopefully clarifying some of my questions and adding a bit more information. 

                      • 8. Re: Calculation to change record 'status'
                        brianquillin

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

                        Let me know if you cannot receive this file.

                        The layouts are still in development but the two layouts to consider here are "Show Budgets" (that's the layout in question) & then the "Contributions" layout where contributions can be linked to a budget item.

                        Once you see the "Show Budget" layout, I think you'll see quickly what I'd like to accomplish.  I would like for the radio button status to toggle from a default value of "need" to "have" when the 'total_needed' field on the page is $0.

                        • 9. Re: Calculation to change record 'status'
                          brianquillin

                          Since you're seeing the file anyway.  I'd also like to know the easiest way to filter the left side portal based on the "show_ID" (or "show_name") field.  Any suggestions would be appreciated.

                          Thanks again. 

                          • 10. Re: Calculation to change record 'status'
                            LaRetta_1

                            I'm sorry, Brian, I get Error 404 when trying to retrieve the file.

                            • 11. Re: Calculation to change record 'status'
                              brianquillin

                              I deleted it from the dropbox folder.  I realized that I was more concerned with my layout than making the data function properly.  I have modified that particular layout and those calculations are functioning as needed.  Thanks.

                              I still have other issues I need to address but I'll post separately as they are different issues.

                              Thanks for your time.