13 Replies Latest reply on Apr 4, 2013 11:28 AM by philmodjunk

    Running Total with fields from different tables

    rounakjain

      Title

      Running Total with fields from different tables

      Post

           Two Tables:
           Table X (Think of it as Purchase Invoice Table)
           Table Y (Think of it as Payment Voucher Table)

           No relationship defined yet between Table X and Table Y

           Table X Fields:
           1. Name
           2. Bill Amount
           3. Date
           and some other fields

           Table Y Fields:
           1. Name
           2. Payment Amount
           3. Date

           What I want:
           A ledger showing running balance of net payment.
           The ledger will be based on Name.
           Each portal line will have fields: X::Date, X::Bill Amount, Y::Date, Y::Payment Amount, Net Running Balance
            

        • 1. Re: Running Total with fields from different tables
          philmodjunk

               I wouldn't link tables by name. Names change and names are not unique. A serial number field that uniquely identifies each customer will be a much safer method to use. In any case, each record in the Payment Voucher table also needs to link to a specific invoice, not a specific customer.

               PurchaseInvoice------<PaymentVoucher

               PurchaseInvoice::__PurchaseInvoiceID = PaymentVoucher::_fkPurchaseInvoiceID

               This assumes that a given payment voucher cannot be "split" over two or more purchase invoices. If that can be done an additional join table is needed to document the "split" of the voucher amount.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               This is in addition to the relationshp that matches by Customer (Preferrably by ID instead of by name). You will still need the original relationship you described for the portal.

               Now you can define a running total summary field, rsTotalPayment, that computes the total of Payment Amount. Your Net Running balance would be a calculation field defined as: rsTotalPayment - PurchaseInvoice::Bill Amount.

               Note: You will need two different Tutorial: What are Table Occurrences? that refer to the PurchaseInvoice table to get both the running total and the portal to work.

                

          • 2. Re: Running Total with fields from different tables
            rounakjain

                 Thanks Phil.

                 I am not a complete newbie. I understand the reasons for using ID, concepts like table occurences, primary key, foreign key etc. My brain is rusty because I have not had to modify my database much in the last 12 months other than adding scripts. Can you please elaborate on the following:

                 

                      Note: You will need two different Tutorial: What are Table Occurrences? that refer to the PurchaseInvoice table to get both the running total and the portal to work.

            • 3. Re: Running Total with fields from different tables
              philmodjunk

                   Did you click the link provided in that sentence? It will take you to an explanation of the term.

              • 4. Re: Running Total with fields from different tables
                rounakjain

                     As I wrote earlier, I understand what table occurrences are, I meant to ask what relationships would I need with the occurrences that I create? I know how to create a running total but i just can't figure it out how i can do it when fields from two different tables are involved.

                • 5. Re: Running Total with fields from different tables
                  philmodjunk

                       Sorry, I missed the phrase "table occurrences" in your preceding post. I was trying to determine the best "starting point" for a more detailed explanation.

                       You'd need something like this:

                       PaymentVouchers|ClientID>------PurchaseInvoices-------<PaymentVouchers|InvoiceID

                       PurchaseInvoices::_fkClientID = PaymentVouchers|ClientID::_fkClientID
                       PurchaseInvoices::__pkInvoiceID = PaymentVouchers::InvoiceID::_fkINvoiceID

                       For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                       This assumes that you are setting up your portal in a PurchaseInvoices layout. The portal would refer to PaymentVouchers|ClientID and I am assuming that you also have a related table of Clients (customers) linked into invoices by clientID.

                       Your Net Running Balance calculation would be set up to specify the PaymentVouchers|InvoiceID table occurrence in the "context" drop down found at the top of Specify Calculation.

                       Hmmm, I tested this idea for a single invoiced amount and it worked. On further thought, I think this method will need modification if your business practices permit creating new invoices before the balance due on past invoices is zero.

                  • 6. Re: Running Total with fields from different tables
                    rounakjain

                         Thanks again for the reply.

                         I had ignored a lot of details earlier so as to simplify the question but as it has turned out that the lack of details have actually made it difficult for you to understand my problem. I am sorry for that.

                         I will explain everything now.

                         There is a "Supplier Invoice" table with fields
                         1. ID (Auto-enter serial)
                         2. Name (name of the supplier)

                         There is a "Purchase Invoice" table along with "Purchase Invoice Details" table.

                         There is a "Payment Voucher" table with these fields: (I will explain the purpose of every field later)
                         1. ID (Auto-enter serial)
                         2. Name
                         3. Amount
                         4. Cheque No
                         5. Bank Statement ID
                         6. Date
                         7. Supplier ID

                         I download my bank statement from the internet and import it in the table named "Bank Statement".

                         The fields in "Bank Statement" Table are
                         1. Date
                         2. Description
                         3. Cheque No
                         4. Amount
                         5. Amount Type (Credit or Debit)
                         6. Balance
                         7. Balance Type (Credit or Debit)
                         // The data in all fields above is directly imported from the bank statement downloaded.
                         8. ID (Auto-enter serial)
                         9. Creditor
                         // The Creditor Field is a drop-down value list of "Supplier Invoice::Name". After I select a name from the drop-down, a script is run. It first checks if "Bank Statement::ID" value equals value in "Payment Voucher::Bank Statement ID" in all the records of "Payment Voucher". If the record does not exist (which will always be the case unless we are editing a record in the "Bank Statement" table), it creates a new entry in the  "Payment Voucher" table. Then, it copies the following
                         "Bank Statement::Date" copied to "Payment Voucher::Date"
                         "Bank Statement::Creditor" copied to "Payment Voucher::Name"
                         "Bank Statement:Cheque No" copied to "Payment Voucher:Cheque No"
                         "Bank Statement:Amount" copied to "Payment Voucher:Amount"
                         "Bank Statement:ID" copied to "Payment Voucher:Bank Statement ID"

                         The "Payment Voucher::Supplier ID" is retrieved via Lookup.


                         Supplier Invoice::ID = Purchase Invoice::Supplier ID
                         Supplier Invoice::ID = Payment Voucher::Supplier ID
                         These two relationships enable me to see two different portals in the "Supplier Invoice" table
                         1. a portal showing all invoices
                         2. a portal showing all payment vouchers

                         I want one portal instead of two and that portal must show me net running balance.

                         Also, I do not want to relate invoices with payment vouchers. This is because:
                         Payment may even be made in advance i.e. there can be a payment made even without any purchase invoice.
                         There can be split payments for a single invoice.

                         Kindly let me know if there are any more clarifications to be made.

                          

                    • 7. Re: Running Total with fields from different tables
                      philmodjunk

                           Using letters of the alphabet instead of names seldom makes it easier for someone to answer your questions and the devil is almost always in the details.

                           It would appear that you have this relationships:

                           PaymentVoucher-----BankStatement

                           BankStatement::ID = PaymentVoucher::BankStatementID

                           I think you have a one to one relationship here. For every Record in BankStatement, you have one and only one PaymentVoucher record. I will simply observe at this point that when you have a one to one relationship, this raises the possibility that you do not actually need two related tables, the fields from both tables could be combined into a single table--simplifying the system where you currently have a script creating new matching records in PaymentVoucher.

                           Your ledger will require the creation of either a new table where each record holds data referenced from either PaymentVoucher or  SupplierInvoice, but never both in the same record. Or you will need additional records created in your PaymentVoucher table that refer to data in the PaymentVoucher table instead of BankStatement. The only data that need be copied to this table are the relevant ID fields. This data can be copied to the Ledger table one record at a time in a looping script or it can be transferred in batches via Import Records where Import Records copies over the ID values from found sets of records in both SupplierInvoice and BankStatement. I will set this up using a completely new table...

                           SupplierInvoice-----<Ledger>------BankStatement

                           SupplierInvoice::ID = Ledger::_fkSINvoiceID
                           BankStatement::ID = Ledger::_fkBStatementID

                           Then the following fields defined in Ledger can be used to produce the desired ledger of payment and invoice amounts:

                           Calculation Fields:

                           SupplierAmount:   SupplierInvoice::InvoiceAmt (use the name of your field in SupplierInvoice that stores or computes the invoice total
                           PaymentAmount:   BankStatement::Amount
                           cBal: PaymentAmount - SupplierAmount
                            

                           Summary Field:

                           sBalance: Summary field, total of cBal

                           Any other fields from either supplier Invoice, Bank Statement or Payment Voucher can be added directly to the layout do to the relationships used.

                           Note: Relying on relationships like this ensures that any changes to values used to determine voucher or invoice amounts automatically update the values you see in the ledger. However, this does produce a layout that will search, sort and update more slowly than if you physically copied the data over from the Invoice and Bank Statement tables. If you find that the performance hit on this layout is too great, changing the two Amount fields in the ledger table to number fields and copying over the data can be done, but now you have to take extra care that any subsequent changes in the bank statement or supplier invoice fields are also made to the corresponding amount fields in the Ledger table so that everything stays "in synch".

                      • 8. Re: Running Total with fields from different tables
                        rounakjain

                             Many thanks again, Phil
                             I did what you wrote.
                             There is one problem.
                             As I wrote earlier,
                        There is a "Supplier Invoice" table with fields
                             1. ID (Auto-enter serial)
                             2. Name (name of the supplier)
                             
                             There is a "Purchase Invoice" table along with "Purchase Invoice Details" table.



                             So I cannot do this:
                             SupplierAmount:   SupplierInvoice::InvoiceAmt (use the name of your field in SupplierInvoice that stores or computes the invoice total


                             Since Supplier Invoice has a one to many relationship with Purchase Invoice, the InvoiceAmt will be available only in Purchase Invoice table.

                             If I do this:
                             SupplierAmount: Purchase Invoice::Amount After Tax
                             Then, the first value in  "Amount After Tax" keeps repeating again and again for each different Supplier.

                              

                             Also, when I will import from the "Bank Statement" table using "ID", how will the supplier ID be copied in the "Ledger" table. Shall I set up "Ledger::Supplier ID" as a calculation field?

                        • 9. Re: Running Total with fields from different tables
                          philmodjunk

                               It is not clear what you mean by a "supplier invoice". If the amount you need is from Purchase INvoice then refer to the field in Purchase Invoice and change the relationship involved to link to purchase invoice instead of Supplier Invoice.

                               

                                    Also, when I...

                               You'll need to use either a script that loops throug the newly imported bank statement records or an Import Records action that imports this data from Bank Statement. But you will no longer need an ID field for the Supplier, you'll need an ID field for the Purchase Invoice.

                          • 10. Re: Running Total with fields from different tables
                            rounakjain

                                 Please look at the image. http://imgur.com/n6NZxPN

                                 According to what you have planned, I am assuming that "Supplier Invoice Ledger" will be a portal in "Supplier Invoice". That portal will show all "Purchase Invoice::Amount after Tax" and "Bank Statement::Amount" (via the Calculation fields that you asked me to create in "Supplier Invoice Ledger") and running balance for the Supplier record being viewed.

                                 Let us say i do an import of the "ID" values from Purchase Invoice table, I will get all the "Purchase Invoice::Amount after Tax" values in a calculation field of "Supplier Invoice Ledger".

                                 "Supplier Invoice Ledger" portal in "Supplier Invoice" table will show the calculation field as desired.

                                 But later when i do an import of the "ID" values from "Bank Statement", I will get all the "Bank Statement::Amount" values in another calculation field of "Supplier Invoice Ledger".

                                 How will "Supplier Invoice Ledger" portal in "Supplier Invoice" table show the calculation field containing "Bank Statement::Amount" entries?

                            • 11. Re: Running Total with fields from different tables
                              philmodjunk
                                   

                                        According to what you have planned, I am assuming that "Supplier Invoice Ledger" will be a portal in "Supplier Invoice

                                   I pictured this as a List view layout based on Supplier Invoice Ledger. Thus no need for a Supplier ID.

                                   To make this work in a portal so that you only see the ledger entries that pertain to a given supplier, you'd need that Supplier ID and this field can be set up with a looked up value field option to copy that value from the related Supplier Invoice record. (If importing records, you'll need to enable auto-enter options.)

                                   You could also do this by linking the records from Bank Statement by Invoice ID to show just the entries relevant to the current Supplier Invoice, but this is what you indicated that you did not want to do...

                              • 12. Re: Running Total with fields from different tables
                                rounakjain

                                     I have been able to solve my problem and achieved what I wanted to. Planning to post an article just to clear up my head along with helping others. Many thanks, Phil.

                                     One question:

                                     I have written a script for importing records. Since, we are only importing "ID", how to import with the option "update the matching records and add remaning records as new records"? Or is there some other way to make sure that records which are already present are not added again.

                                • 13. Re: Running Total with fields from different tables
                                  philmodjunk

                                       I don't see why you would specify "update matching" for this. I would think that you would be adding new records each time when importing. (And you can set up a found set of records to import and just import those new records.)