4 Replies Latest reply on Aug 12, 2014 9:36 AM by SimonSherwood_1

    Creating Invoices from 2 related tables

    SimonSherwood_1

      Title

      Creating Invoices from 2 related tables

      Post

           Hi All

            

           I'm after some pointers on how best to achieve this I don't have a specific query.  I have 2 tables Customers and Transactions.  Customers is populated with information about the customer, it has a form view with a portal to the transactions table where related transactions can be added to the transactions table.

           I want to make a button that will create an invoice, however I don't want all the related records from transaction showing, just the relevant one or two.  I could make a new table called invoices and make a script to populated fields with customer information, no problem but I'm not sure on how to only display the few records I want from the transactions table.

            

           Thanks in advance.

           Simon.

        • 1. Re: Creating Invoices from 2 related tables
          philmodjunk
               

                    I don't want all the related records from transaction showing, just the relevant one or two.

               What do you mean by that? What makes a transaction record "relevant"? If you have an invoice with three transaction records in the portal to transactions, do you want to print an invoice with those three transactions or do you have some subset of those three in mind? Those three recors ARE "all the related records" for that specific invoice after all.

               If you want to print an invoice from a layout based on transactions, but don't know how to limit the transactions to those linked to a particular invoice, there are two methods that a script can use to pull up just the transactions for a single invoice:

               Assuming that "Transactions" is the exact text of Show Related Records From in Portal setup...

               Use Go To Related records, with Transactions as the "table" and your print layout specified as the "layout" be sure to specify "show only related records".

               or

               Set up the script to perform a find for all transactions with a value in their match field that = the value of the current Invoice record's match field:

               Set Variable [$ID ; value: Invoices::PrimaryKeyFieldHere ]
               Go to Layout ["Invoices Print" (Transactions) ]
               Enter FInd mode []
               Set Field [ Transactions::ForeignKeyFieldHere ; $ID ]
               Set Error Capture [on]
               Perform Find []

          • 2. Re: Creating Invoices from 2 related tables
            SimonSherwood_1

                 Hi Phil

                  

                 Thanks for the answer it's a big help.  I need to limit the related records based on the date as most customers will have 100's of related records but only the most recent are relevant.  So sI could make a search based on Date as all the records have a date field as well as ID fields.

                 What is the neatest, aesthetically pleasing way to get the related records showing up? A filtered Portal or a Layout based upon transactions.  I would also like to save these invoices if at all possible, however I realise that might not be possible if the layout is based upon transactions although I could just email myself a copy I guess.

                  

                 Simon.

            • 3. Re: Creating Invoices from 2 related tables
              philmodjunk

                   Use a layout based on transactions.

                   I think there's a basic design issue here that should be addressed. In some systems an "order" and an "invoice" are two different things. A customer might place an order once and then "invoice" that order several times if the customer is slow to pay for the order. Is that what you have here?

                   The more typical set up that you see in this forum and elsewhere is that and Invoice and an Order are two names for the same thing:

                   Invoices----<Transactions>------Products|Services

                   Invoices::__pkInvoiceID = Transactions::_fkTransactionsID
                   Products|Services::__productID = Transactions::_fkProductID

                   Which fits your business model?

                   This is a key issue as you seem to need a date involved here for selecting your transactions. I don't quite see what role that date plays in your invoicing process. Which leads us to this:

                   

                         I would also like to save these invoices if at all possible,

                   If you have an Invoices table linked to transactions as shown here, there is nothing to save, this is already "saved" as the data in your database.

              • 4. Re: Creating Invoices from 2 related tables
                SimonSherwood_1

                     The invoice is produced after work is carried out, the majority of customers will pay on the day but a few businesses will need to be emailed an invoice for the service.  Essentially the invoice is just a bill.

                     Simon.