1 2 Previous Next 15 Replies Latest reply on Feb 7, 2017 4:40 PM by philmodjunk

    Running totals across portals

    nkolios

      I have a database which records lots of information as a daily record with child records that all have the Date record as the parent record.  One of the things that I keep a log of is our daily cash movements.  We used to just keep a long list which is a bit cumbersome and prone to errors so I have now set up a child record of cash movements but  still need to keep a running total.  The idea is that entries are made in a portal and not a report layout but I can not get the portal records to update because the summaries and calculations only look at the current context, i.e the records on that particular day.  Is there a way to do this, maybe I need a different relationship. 

       

      I know I can probably send the script to the list view do the calcs and then come back again but it seems over complicated.  I have uploaded an extract from the relationship diagram.

       

      Many thanks

       

      Niko

        • 1. Re: Running totals across portals
          philmodjunk

          How do these entries look in your portal to Safe?

          First row records the opening balance?

          Each in/out entry is a separate row

          Don't see a purpose for closing balance here.

          Do you have a calculation field that computes something like: Opening Balance + Cash In - Cash Out?

           

          Note that I am assuming that every date starts with an opening balance entry.

          • 2. Re: Running totals across portals
            nkolios

            Yes the portal looks to the Safe Layout.

            Each row is an entry for an item, a credit or a debit, I've added another screen shot with some test figures.

            Each in and out entry is a separate row.

            Closing balance facilitates the calculations I had before.  The script would take a closing balance and apply it to an opening balance in the next record.

            Closing balance is a calculation field which is opening balance - cash out + cash in.

             

            In the past I entered these figures on a report view and so the script would take the closing balance and set the opening balance of the next record. So the issue I now have is working out the best way to keep an opening and closing balance for the day.  I thought about creating another child table from Safe with those totals but I'm just not sure if its the best way of doing it.

            • 3. Re: Running totals across portals
              philmodjunk

              Seems to me that each date should start with an opening balance entry. This entry can just be a Cash In but with "opening balance" in the description field.

               

              The way I do this is to set up a calculation field like this:

               

              Cash In - Cash Out

               

              I then define a running total summary field to show the balance with each new transaction.

               

              The running total in the final transaction of the day should automatically show the closing balance for the day.

               

              For reporting purposes, you can use the "group by" option and restart the running total with each new date.

              • 4. Re: Running totals across portals
                nkolios

                I tried this.  I have a summary running total of the closing balance field in the safe table where the closing balance is the opening balance (closing balance from the previous record) - cash out + cash in.  This only seem to work for the first record subsequent records in that list don't give the correct results. 

                 

                Perhaps the opening balance/closing balance fields should be located in the Date table?

                • 5. Re: Running totals across portals
                  philmodjunk

                  Get rid of the opening balance field. Enter the opening balance into cash in with "opening balance" as the description.

                  • 6. Re: Running totals across portals
                    nkolios

                    But I need the cash in field otherwise it wont make sense for the user who needs to enter cash in and cash out entries.    Or do you mean use the same field twice just labeled differently?  And with the Cash In - Cash out calculation is it self referencing so Cash in = Cash in - Cash Out.  I've never used this before.

                     

                    Thanks

                    • 7. Re: Running totals across portals
                      philmodjunk

                      Keep the cash In field, get rid of the opening balance field.

                       

                      Yes, use the same field for both purposes and use different description text.

                      • 8. Re: Running totals across portals
                        nkolios

                        Hi,

                        This seems to work fine for updating one line of data on the portal but it doesn't carry over the closing balance to the next day.

                         

                        I set up a calculation to equate Cash In - Cash Out and then summarised that field to get the closing balance for that line.  But there is no carry over to the next date record.  It doesn't seem to summarise across the whole dataset.

                        • 9. Re: Running totals across portals
                          philmodjunk

                          Yes, each day would need to start with something that creates that initial entry for you. A script could take the total from the last entry of the previous day and make a new record with today's date and put that total as "Opening Balance" as the first record of the day.

                          • 10. Re: Running totals across portals
                            nkolios

                            Ok thanks.  The other issue for me is that if I have to go back and correct a mistake or add an entry to something that happened a week or so ago I'm going to have to recalculate everything after that point as the change is not going to filter through automatically if that Opening Balance field will need updating with a script. 

                             

                            Another thing that I am not sure about is when I take money from the safe and put it into petty cash for example how can I link those two fields or would I have to retain the ID number from one of these shared entries so I can use it to delete or update in the future. 

                             

                            I'm thinking it might have been easier to put these 3 table into one and just use relationships to separate them out.

                            • 11. Re: Running totals across portals
                              philmodjunk

                              I only see two tables so I have no idea how you have set up petty cash.

                               

                              You've raised a valid point. I've suggested treating "opening balance" as just another Cash In transaction as it makes the set up simpler and you were reporting issues with your Opening Balance Calculation.

                               

                              When you set it up as a calculation field, as can be done, you also have to make sure that it is only used in the first record of the day and not any subsequent calculations--as you discovered. This also set's up a chain of calculations that grows with each day and which might slow down screen updates after a while.

                               

                              On the other hand, a script could be set up that updates any Balance Forward entries made after the date where you made a correction and it wouldn't be to terribly complex to set up.

                               

                              Another option might be to not make corrections under a past date but to make corrections under the current date and then no such updates are needed--but this may not work for your business processes.

                              • 12. Re: Running totals across portals
                                nkolios

                                I think I will create a script to populate the Opening balance to the next day and update any subsequent records.

                                 

                                I have attached another graph with with the other tables. The scenario could be that money comes from the cash book to the safe, or it could go to the petty cash.  Money could also come from the safe to the petty cash or the cash book also.  I have used a script in the past to send from one table to the next but its complicated if there is a change or a deletion.  Which was the situation recently and the reason why I am trying to set things up differently and more robustly.  Maybe I need to start another discussion for this?

                                • 13. Re: Running totals across portals
                                  philmodjunk

                                  Yes this does sound like you are complicating your solution by using tables to handle different groups of very similar data that could all be stored in one table by adding an additional field to document the location. THis can then be an additional match field or a match field used in portal filters (or in find criteria) to enable you to work with just cash book or just Safe or just petty cash entries.

                                   

                                  To move funds from one to the other, you do a cash out entry for the location where the funds are being removed and a cash in transaction for the location receiving the added funds.

                                  • 14. Re: Running totals across portals
                                    nkolios

                                    The other thing I have now noticed is that to update the opening balance I need to come out of context and view the safe table as a list which has an effect on the running total summaries in the context of the portal.  So i think I will have to go back to the drawing board with this one.

                                    1 2 Previous Next