7 Replies Latest reply on Jul 30, 2014 8:33 AM by philmodjunk

    Invoice portal calculation issues

    MichaelValentino

      Title

      Invoice portal calculation issues

      Post

           I am making an invoice database and I am having an issue figuring out a SUM function. I have a layout with the customer information. On this layout I put in a portal that will show me a list of unpaid invoices for that particular customer. The portal list appears to be working properly, showing me only the invoices for the current customer however I can not figure out how to make a summary box for the totals of all of the invoices in the portal.

            

           I also made a portal for paid invoices and need to do the same summary for the paid invoices.

            

           Any help is greatly appreciated.

        • 1. Re: Invoice portal calculation issues
          philmodjunk

               Is this an unfiltered portal or are you using a portal filter to omit the paid invoices? Sum will compute a total based on the relationship but not any portal filter you might define as it does not refer to the design of your current layout. To use Sum would require using an unfiltered portal--an additional pair of match fields would have to filter the related records in the same fashion.

               But ExecuteSQL can compute the same total and use a WHERE clause to filter out the paid invoices much like the portal filter.

               And you can make a copy of your filtered portal, reduce it to a single row in size and put a summary field defined in the invoices table that computes a total and this value will reflect the filtering action to show a total based only on unpaid invoices.

          • 2. Re: Invoice portal calculation issues
            MichaelValentino

                 Of course it would have to be a difficult fix =). Yes I am using filtering to give me paid invoices in one portal, and unpaid in the other portal.  I am kind of a novice and by the sounds of it the fix is over my head..... How difficult would you say it is to re-organize and do the matching fields thing.....?

                  

            • 3. Re: Invoice portal calculation issues
              philmodjunk

                   The first and third options are pretty simple. The second option is the challenge as it requires a working knowledge of SQL and a willingness to do battle with an implementation of SQL that is not as user friendly as we might want.

                   Let's say that you have a field that stores either "Paid" or "Unpaid" to show the status of that Invoice and we'll call that field Status for the purposes of this example.

                   Define this field in customers, named constPaid:

                   "Paid"

                   and select text as the result type.

                   Then this relationship:

                   Customers::__pkCustomerID = Invoices|Paid::_fkCustomerID AND
                   Customers::constPaid = Invoices|Pad::Status

                   will match only to Paid invoices with that customer's ID.

                   Note: Invoices|Paid is my name for a new Tutorial: What are Table Occurrences? of Invoices. You'd add another new occurrence of Invoices for linking up unpaid invoices in the same fashion but with a different calculation field that matches only to "Unpaid" instead of "Paid" invoices.

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

              • 4. Re: Invoice portal calculation issues
                MichaelValentino

                     Ok, I am lost,  I am not sure if I understand exactly what it is you are explaining to me.  Any interest in solving this for me I can email the file to you?  If you have any interest, how much do you think it would cost?

                • 5. Re: Invoice portal calculation issues
                  philmodjunk

                       If you can apply what I am suggesting, you are then in good shape for generalizing the concept to other parts of this database and others.

                       I suggest that you ask follow up questions about the parts you don't understand.

                       What I am describing here requires creating two additional table occurrence "boxes" in Manage | Database | Relationships. If "table occurrence" is a new concept, please see this thread: Tutorial: What are Table Occurrences?

                       But if you'd rather I did the work for you, send me a private message. I don't provide quotes in public and never without first examining a copy of the file in question. Best guess is that this wouldn't take much more than 15 minutes to set up using the table occurrence method.

                  • 6. Re: Invoice portal calculation issues
                    MichaelValentino

                         Phil,

                            Did you get my private message? I asked for an email address to send the file because I didn't see a way to send it in the private message?

                          

                         Michael

                    • 7. Re: Invoice portal calculation issues
                      philmodjunk

                           I spent yesterday transporting a family member to a medical specialist some 2 hours drive from here so I have not yet checked my PM's. You can upload a file to a sharing site such as Drop Box and then include the link in the private message.