9 Replies Latest reply on Feb 13, 2014 12:57 PM by willrollo

    count issue - again!

      Title

      count issue - again!

      Post

           I have four tables that are linked to each to other using keys. 
           Customers - Invoice details - invoice items - products.
            
           I also have a table called invoices that is linked to the invoice details table. The idea of this invoices table is that it creates an invoice number for the invoice details records that show the user the contents of an invoice. (I also have a quote and order table seat up in this way also).
            
           When I export these invoice details records as a csv file, the required format for importing into my book keeping software is that each line item creates a record in the csv -This record must contain various fields, such as invoice number, custom renumber, price, tax etc.
            
           So one export may contain two invoices for two customers. If customer 1 has an invoice with three lines on and the other customer’s invoice is a single line, then the csv export file will have 4 records on it. 
            
           What I want to do is display in a dialogue this message “ You have exported 2 customer’s invoices. A total of 2 invoices”
            
           I have looked at the sum of reciprocal and counts but cant seem to get the required result. I am launching the export from an invoice details layout, if this matters at all…I can post my current script and any other data required….Thank you

        • 1. Re: count issue - again!
          philmodjunk

               Summing the reciprocal should work, but I doubt that you need to do that.

               How do you go about pulling up the found set of Invoice Items to export?

               I suspect that you are on an Invoices or Invoice Details layout at some point in this process and then you can count the invoices then and put the count into a variable.

          • 2. Re: count issue - again!

                 Hi Phil

                 See below for my script - it shows how I find the invoice to export

                   
            •           Set Error Capture [ On ]
            •      
            •           Allow User Abort [ Off ]
            •      
            •           Freeze Window
            •      
            •           Commit Records/Requests [ Skip data entry validation; No dialog ]
            •      
            •           #Validate Dates
            •      
            •           If [ PREF::gDateStart and IsEmpty ( PREF::gDateEnd ) or PREF::gDateEnd and IsEmpty ( PREF::gDateStart ) ]
            •      
            •           #Missing both start and end dates
            •      
            •           Show Custom Dialog [ Title: "Warning"; Message: "You need to have both a start and end date."; Default Button: “OK”, Commit: “Yes” ]
            •      
            •           Exit Script [ ]
            •      
            •           Else If [ PREF::gDateStart > PREF::gDateEnd ]
            •      
            •           #Start date greater than end date
            •      
            •           Show Custom Dialog [ Title: "Warning"; Message: "The end date must be on or after the start date."; Default Button: “OK”, Commit: “Yes” ]
            •      
            •           Exit Script [ ]
            •      
            •           End If
            •      
            •           Close Window [ Name: "Report New Invoices"; Current file ]
            •      
            •           New Window [ Name: "Report New Invoices"; Height: 850; Width: 710; Top: 10; Left: 10; Style: Document; Close: “Yes”; Minimize: “Yes”; Maximize: “Yes”; Zoom Control Area: “Yes”; Resize: “Yes” ]
            •      
            •           Go to Layout [ “Invoice Export” (invoicedetails_INVOICEITEM) ]
            •      
            •           If [ IsEmpty (PREF::gDateStart) and IsEmpty (PREF::gDateEnd) ]
            •      
            •           #No date specified, find all
            •      
            •           Show All Records
            •      
            •           Constrain Found Set [ Specified Find Requests: Find Records; Criteria: INVOICE DETAILS::fk_Invoice: “*” AND Invoices::Exported: “N” ] [ Restore ]
            •      
            •           Else
            •      
            •           #Find by specified date range.
            •      
            •           Enter Find Mode [ ]
            •      
            •           Set Field [ Invoices::Invoice Date; PREF::gDateStart & ".." & PREF::gDateEnd ]
            •      
            •           Perform Find [ Specified Find Requests: Find Records; Criteria: INVOICE DETAILS::fk_Invoice: “*” AND INVOICE DETAILS::fk_Order: “*” Omit Records; Criteria: INVOICE DETAILS::Visible Invoice: “1” ] [ Restore ]
            •      
            •           Constrain Found Set [ Specified Find Requests: Find Records; Criteria: INVOICE DETAILS::fk_Invoice: “*” AND Invoices::Exported: “N” ] [ Restore ]
            •      
            •           End If
            •      
            •           If [ Get (FoundCount) = 0 ]
            •      
            •           #No records found
            •      
            •           Close Window [ Current Window ]
            •      
            •           Refresh Window
            •      
            •           Show Custom Dialog [ Title: "Message"; Message: "No new invoices found within the specified date range."; Default Button: “OK”, Commit: “Yes” ]
            •      
            •           Exit Script [ ]
            •      
            •           End If
            •      
            •           View As [ View as List ]
            •      
            •           Show/Hide Toolbars [ Show ]
            •      
            •           Print Setup [ Orientation: Portrait; Paper size: 8.26" x 11.69" ] [ Restore; No dialog ]
            •      
            •           Sort Records [ Keep records in sorted order; Specified Sort Order: Invoices::Invoice Date; ascending ] [ Restore; No dialog ]
            •      
            •           Set Variable [ $Path; Value: "file:" & Get ( DesktopPath ) & "Invoices " & MonthName ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) )& ".csv" ]
            •      
            •           Adjust Window [ Resize to Fit ]
            •      
            •           Go to Record/Request/Page [ Last ]
            •      
            •           Go to Record/Request/Page [ First ]
            •      
            •           Set Field [ INVOICE DETAILS::Export Count; Count(Invoices::Invoice Number) ]
            •      
            •           Show Custom Dialog [ Title: "Export"; Message: "Export " & Count(invoicedetails_CUSTOMERS__billing::Alphacode) & " Customer'(s) invoices to the desktop? A total of " & INVOICE DETAILS::Export Count & " invoices."; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ]
            •      
            •           If [ Get(LastMessageChoice)= 2 ]
            •      
            •           Close Window [ Current Window ]
            •      
            •           Exit Script [ ]
            •      
            •           End If
            •      
            •           Export Records [ File Name: “$Path”; Automatically open; Character Set: “Macintosh”; Field Order: INVOICE DETAILS::fk_Invoice INVOICE DETAILS::fk_Invoice Date invoicedetails_CUSTOMERS__billing::Alphacode INVOICE DETAILS::Reference invoicedetails_INVOICEITEM::Quantity invoicedetails_INVOICEITEM::Description invoicedetails_INVOICEITEM::Price invoicedetails_INVOICEITEM::Tax Percentage invoicedetails_INVOICEITEM::Nominal Code ] [ No dialog; Format output using current layout ]
            •      
            •           If [ Get(LastError)=0 ]
            •      
            •           Replace Field Contents [ Invoices::Exported; Replace with calculation: "Y" ] [ No dialog ]
            •      
            •           Else
            •      
            •           Show Custom Dialog [ Title: "Warning"; Message: "Export Failed"; Default Button: “OK”, Commit: “Yes” ]
            •      
            •           End If
            •      
            •           Close Window [ Current Window ]
            • 3. Re: count issue - again!
              philmodjunk

                   Perform your find on either the Invoices or Invoice Details layout instead of InvoiceItem. The found count will then be your number of invoices and you can use set variable to put the value of Get ( FouncCount ) into that variable.

                   Then Use Go to Related Records with the "match found set" option to pull up the INvoiceItem records you need for your report.

              • 4. Re: count issue - again!

                     Thank you Phil

                     That worked for getting the invoice count into my dialogue box. I also want to show number of different customers in the same dialogue. This seems a little more fiddley then the invoice count!

                     Any ideas?

                     Thank you

                • 5. Re: count issue - again!

                       Hi Phil - Did you have a chance to look over the above issue as well? Need to count number of different customer records on this invoice export - thank you

                  • 6. Re: count issue - again!
                    philmodjunk

                         Sorry, been recovering from Pneumonia and haven't had the energy to dig all the way to the end of the list.

                         I can't tell from you post what you want to do "number of different customers" is a bit vague. Is this a count? A list of customer names? Or do you want the current customer's name to appear in the dialog?

                    • 7. Re: count issue - again!

                           Dear Phil

                           I hope you are fully recovered - Although I have not experience pneumonia, I have had a double lung transplant, so can sympathise with how you have been feeling!

                           The result I am looking for is for my dialogue box to provide a message saying how many (count) customers have been invoiced. So some customers may have had one, some more than one. But I want a count of both. e.g. 12 customers with a total of 42 invoices have been exported. This is just so the user can have confirmation of the export without having to check over the xls in detail....Does this make sense? The invoice count is sorted thanks to your prev response. May be a sum of recip again..but unsure ...Thank you

                      • 8. Re: count issue - again!
                        philmodjunk

                             What a script could do is use GTRR with the Match found set option to pull up a found set of client records that link to the found set of invoices. That could then be captured and put in a variable much like you count of the invoices.

                        • 9. Re: count issue - again!

                               Thank you Phil - good idea - I shall test out tomorrow...