5 Replies Latest reply on Dec 21, 2013 12:16 PM by bartprins

    Question about Count

    bartprins

      Hi there,

       

      I want to count a couple of records where the records have a specific field value for example "536" as CustomerId

       

      I came upon this method which counts well but i want the add the function of counting a specific value, do you have an idea ?

       

      If ( IsEmpty ( Count ( Customer::CustomerId ) ) ; 0 ; Count ( Customer::CustomerId ) )

       

      Much oblidged !!

       

      Bart Prins

        • 1. Re: Question about Count
          LyndsayHowarth

          In the customer table, the calculation above would give you 1 for each record which has a customer ID.

          You would then need a summary field for counting the number of records with a customer ID... and those without.... because entering zero is a value to count.

          You would be better off using a "" instead of the 0... so it only counts the 1s.

          With your calculation you could use a Total summary field which would add up all the 1s.

           

          A Summary field can be used to count or total across all the records.

          When used in combination with Sub-Summary Parts on a layout and sorting of a break field you can get sub totals using those same summary fields on the sub-summary part. If they are put in the footer or header or leading or trailing parts they will be grand totals. (unless you make them cumulative so footer and header parts give a progressive value.

           

          When data is viewed through a relationship the portals show summary field info as broken down the relationship match. You could have your Customer table and a relationship to another table instance called perhaps 'Customers by Company' where the match field was the Company name, you could show all the people in that company in a portal and the summary field would show a count of the people in the same company as the current customer record.

           

          There are many ways to do this... just some suggestions to expand your concept of what is possible.

           

          - Lyndsay

          1 of 1 people found this helpful
          • 2. Re: Question about Count
            erolst

            Bart Prins wrote:

             

            Hi there,

            I came upon this method which counts well but i want the add the function of counting a specific value, do you have an idea ?

             

            Not to distract from Lyndsay's comprehensive description, but you could try

             

            ValueCount ( List ( Customer::yourValueField ) ; valueToBeCounted )

             

            which is most often useful in scripts, for Conditional Formatting etc., but not necessarily as a calculation field with a hardcoded valueToBeCounted, because then you'd need one field per value.

             

            If you need up-to-date statistics for, say, CustomerProducts combinations, think about adding a new table in which to store these numbers and maintaining it when adding and editing transactions.

            • 3. Re: Question about Count
              bartprins

              Hi Lyndsay and Erolst,

               

              Thank you both for your replies !!

               

              I was thinking to simple with this one i think hahaha still learning a lot thanks !

               

              I have a couple of tables, for Example lets say CustomerTbl, ProductTbl,  InvoiceTbl, i have to make a text export which combines these and says in a extra table how many records i have exported to the textfile. For instantance 1 customer with 4 products and 2 invoices. 

               

              The tables are linked via relationship and a key which is called CustomerId which shows up in every tabel. I want to count every record which has this specific CustomerId (for instance "536") and put these values in another table, lets call that one ExportTbl in the fields CustomerCount, ProductCount and InvoiceCount.

               

              Is this possible ? Should i still use the Count function ?

               

              Much oblidged again!!

               

              Bart

              • 4. Re: Question about Count
                erolst

                Bart –

                 

                this is of course much more convoluted and needs more than a simple Count().

                Bart Prins wrote:

                and a key which is called CustomerId which shows up in every tabel.

                That doesn't sound right – what is a customerID doing in the Products table?

                 

                The usual setup for an invoicing system looks like this:

                 

                Customer --> Invoices --> LineItems <-- Products, with these fields:


                _pk_customerID --> _fk_customerID, _pk_invoiceID --> _fk_invoiceID, _pk_lineItemsID, _fk_productID <-- _pk_productID

                 

                You see that outside of the Customers table, the customerID is only used (as a foreign key) within the Invoices table. The connection of a customer to potentially many products is via their direct relationship to the Invoices table, and their (indirect) relationship to the line items join table. In all of this, LineItems is the focal point that brings it all together – and it's missing from your setup.

                 

                Here is one method to perform your export within this structure. Assuming that you have a LineItems table, and that you're interested in a specific date range, use a script to …

                 

                Go to Layout Invoices

                Find all invoices within the date range and use Get ( FoundCount ) to save the count in a variable

                Go to Related Record (Show only related, Match found set) with Customers as target (Display records from). Again, take note of the found count in a variable.

                Go (back) to Layout Invoices

                Go to Related Record (Show only related, Match found set) with Products as target. Take note of the found count in a variable.

                Go (back) to Layout Invoices

                Go to Related Record (All Matching, Related only) with LineItems as target

                 

                and perform your text export (which would probably use some kind of sorting and grouping).

                 

                If the export was successful, go to your statistics table and create a new record with the values from the variables for distinct customer, invoice and product count.

                 

                If you're searching by other criteria, the script can be adapted for a different starting point.

                • 5. Re: Question about Count
                  bartprins

                  Hi Erols,

                   

                  Thanks yes the criteria were somewhat different but this helped me out very much, thanks again learning and learning haha, much obidged for all your answers. I'm gonna post yet another but am getting there again thank you very much for the help!!

                   

                  Bart