9 Replies Latest reply on Apr 18, 2012 11:15 AM by philmodjunk

    Portal Running Balance Issue



      Portal Running Balance Issue


      Hi, As you can see in the image, the field of Cumulative Achieved is basically a running total and its Project Target - Activity Achieved. Everything is fine and I am getting a simple running balance output but I want the running balance to be sorted according to the activities. For example the activities Training has a target of 50, so every time the achieved figure is input the cumulative achieved should be updated accordingly to the activity. 


        • 1. Re: Portal Running Balance Issue

          Hmmm, seems like you have Project Target + Activity Achieved. At least that produces the figures shown: 40 + 10 = 50; 10 + 3 = 13; 13 + 50 = 63.

          Sure that's the running total you want?

          I believe that you can get the running totals that you want with a self join that matches portal records by Activity. But I need a few more details to set up that example. What is the relationship used for this portal? (What table names, what field names etc.)

          • 2. Re: Portal Running Balance Issue

            Thanks Phil

            Bascially I want to sum up all the ACTIVITY ACHIEVED figures (Activity wise) in the CUMULATIVE ACHIEVED field. below is the relationship 

            • 3. Re: Portal Running Balance Issue

              I need to match up the table occurrences shown to your layout.

              Is the layout basd on WorkPlan or MPR? (I'd guess work plan.)

              Does the portal refer to Details?

              I'm intrepeting your posts here to be a request to compute the total of Details::act_achieved for all records in Details with the same value in Activity and wp_id. That correct? (making this a "running" total will be a bit tricky, but  first let's get the right sub totals here.

              If that's correct, define a summary field in Details, sTotalAchieved, as the total of act_achieved.

              Make a new occurrence of details. In Manage | Database | relationships, make a new table occurrence of detailsby clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box, to DetailsSameProjAct.

              We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

              Add it to your relationships like this:

              Details::activity = DetailsSameProjAct::activity AND
              Details::wp_id = DetailsSameProjAct::wp_id

              Now you can place DetailsSameProjAct::sTotalAchieved in your portal row to show the subtotal achieved for that activity and project. This isn't yet a running total, but let me know if I am on the right track before I take things in that direction.

              • 4. Re: Portal Running Balance Issue

                Thanks its working correct. what are the further steps so I can make it like a running total

                • 5. Re: Portal Running Balance Issue

                  What form will that running total take in your report? How often might it happen that editing a previously record will need to cause a recalculation of the entire series of running total values for a given activity?

                  At issue here is the fact that your sample screen shot shows the various entries in no specific order (at least that can be seen by looking at the screen shot). There might be a better option for setting up a running total if the portal entries were grouped so that you see:

                  Workshop Within the Community
                  Workshop Within the Community

                  In other words, a sort order is specified that groups the portal records by activity.

                  The method that I have in mind that does not require a specific order to the portal rows.

                  If you do not already have a serial number field defined in Details that uniquely identifies each detail record, add one and use Replace Field Contents to give them a serial number value in the order that they were created. (This is a one time fix to give existing records a serial number.) Records in their unsorted order will be in their creation order so you simply would show all records and then use Replace field contents to give them a serial number.

                  Let's call this field, Detail_ID and then modify the relationship posted earlier to be:

                  Details::activity = DetailsSameProjAct::activity AND
                  Details::wp_id = DetailsSameProjAct::wp_id AND
                  Details::Detail_ID > DetailsSameProjAct::Detail_ID

                  Now you can define a calculation field cRunningTotal as: act_achieved + DetailsSameProjAct::sTotalAchieved to compute the running total for each activity.

                  • 6. Re: Portal Running Balance Issue

                    Hi, Thanks for the support Phil, I am getting a very odd type of error, as you can see in the screenshot I have entered the achieved figure 1 in all the activities but in the cumulative field i am getting the figure 2. 


                    Please note this is only 1 record I have created. 

                    • 7. Re: Portal Running Balance Issue

                      When you check it in Manage | Database | fields, what data type is the Detail_ID field?

                      Can you open manage | Database | Relationships, find the link between Details and DetailsSameProjAct, double click it and then upload a screen shot of that dialog box here?

                      • 8. Re: Portal Running Balance Issue

                        Dear Phil, 

                                         I have attached the details and the detailssameproject link window, also the Details_ID field is Act_ID and the type is (Indexed, Auto enter Serial) 




                        • 9. Re: Portal Running Balance Issue

                          Your relationship has a small error that is matching a record to itself and that is producing the 2 shown.

                          The last pair of match field shown is:

                          Act_id = Act_id

                          I specified using an inequality operator in place of the = sign:

                          Act_id > Act_id