1 2 Previous Next 19 Replies Latest reply on Apr 2, 2009 1:06 PM by philmodjunk

    Calculation to number question

    mgxdigital

      Title

      Calculation to number question

      Post

      Hey!

      I'm trying to figure out a calcuation field that will do the following:

      Figure out how many orders shipped out each day and then show that number.

       

      For instance, on the 26th, it will find all jobs that shipped out on that day and then the result will be something like "5"

      and then on the 27th, it will show "9"

       

      And i am want to add this to a monthly calendar view so that on every day on the calendar it shows how many jobs shipped out that day.

       

      Would anyone be kind enough to help me figure this out?

      Thanks!

        • 1. Re: Calculation to number question
          philmodjunk
            

          Two methods:

           

          If it is possible to list the group of jobs in a portal. (You don't actually need the portal, just a relationship that would make it possible.)

          Count(TableName::NonBlankField) will give you the number of records if TableName is the name you would put in our hypothetical portal as long as NonBlankField is not blank in any of the records. Usually, the id field that is part of the relationship for TableName is your best choice.

           

          Define a Summary Field as "Count of" the same field we would use in the above count function. You can use this field in List type reports to display counts of records in SubSummary, Footer, Grand Total type report parts.

           

          You'll have to consider which option works best for your layouts and scripts.

          • 2. Re: Calculation to number question
            mgxdigital
               Is it possible to accomplish this in as a field calculation instead of a portal. Thinking of the end result of the number of shipped orders on a calendar view, i would like to be able to just add a field that took that day and calcualted it automatically. What do you think?
            • 3. Re: Calculation to number question
              philmodjunk
                

              robin wrote:
              Is it possible to accomplish this in as a field calculation instead of a portal. Thinking of the end result of the number of shipped orders on a calendar view, i would like to be able to just add a field that took that day and calcualted it automatically. What do you think?

              The Count() function IS an expression you can use in a field definition or in script steps. My reference to the portal was:

               

              IF you have a relationship that lets you display the group you want to count, IF you were to set it up in a portal, THEN the Count() function can be used to give you the count of records. You don't actually need the portal, just the correct relationship in order for count to return the value you want.


              • 4. Re: Calculation to number question
                mgxdigital
                  

                Awww..I see.

                Well I have yet to set up the relationship to find the group that I want to count.

                 

                Our orders do have ship dates, so is there a way to maybe make a calc field called "shipped orders per day" and have it find the orders shipped that day so that I would have them there. Otherwise i'm not sure how to set up a portal like that. I have the relationship built and can make the portal to show all orders, but how can i limit it to show only what ships per day?

                • 5. Re: Calculation to number question
                  philmodjunk
                    

                  robin wrote:

                  Awww..I see.

                  Well I have yet to set up the relationship to find the group that I want to count.

                   

                  Our orders do have ship dates, so is there a way to maybe make a calc field called "shipped orders per day" and have it find the orders shipped that day so that I would have them there. Otherwise i'm not sure how to set up a portal like that. I have the relationship built and can make the portal to show all orders, but how can i limit it to show only what ships per day?


                   

                  Not knowing exactly what tables and relationships you have created at this point makes it difficult to explain in detail. That's why I referred to a portal. If you can define a relationship that displays the records that will work, then count() can be used.

                   

                  In your case, you need a relationship that uses Shipping Date in its relationship.

                   

                  Let's assume one table, Invoices, for simplicity.

                  Date Shipped is recorded in the Field "ShippingDate".

                   

                  Make a "self join" relationship linking Invoices to itself by dragging from the ShippingDate field out from the table and then back to ShippingDate in your Relationship Graph.

                  Name this new Table Instance "ShipSameDate".

                   

                  Define a calculation field in Invoices as Count(ShipSameDate::ShippingDate) and place it on your Invoices layout.

                  Now you'll see a count of all the invoices with the same shipping date as the current invoice.

                   

                  That work for you?


                  • 6. Re: Calculation to number question
                    mgxdigital
                      

                    Hey Phil!!

                    This worked great!

                     

                    To add to it, is it possible to also have the number of shipments per day be divided by the salesperon, like to show how many shipments per salesperson went out daily?

                     

                    The salesman field is in a different database called Customers. It is related to the packing slips through a field called "customer" in both db's.

                     

                    What do you think?

                     

                     

                    • 7. Re: Calculation to number question
                      philmodjunk
                        

                      robin wrote:

                      Hey Phil!!

                      This worked great!

                       

                      To add to it, is it possible to also have the number of shipments per day be divided by the salesperon, like to show how many shipments per salesperson went out daily?

                       

                      The salesman field is in a different database called Customers. It is related to the packing slips through a field called "customer" in both db's.

                       

                      What do you think?

                       

                       


                       

                      Modify your calculation from Count(ShipSameDate::ShippingDate) to be Count(ShipSameDate::ShippingDate)/Customers::SalesPerson or define it in a new calculation field.

                       

                       

                      To improve readability when you put this field on a layout, select a number format that limits the number of digits to be displayed after the decimal.


                      • 8. Re: Calculation to number question
                        mgxdigital
                          

                        Hey Phil,

                        I did exactly what you said and the field shows a ? mark. Even when I click into the field, there is still the ?

                         

                        My calc is as follows:

                        Count (ShipSameDate::Ship Date)/Packing_Slips_MGX Customers1::Salesperson

                        • 9. Re: Calculation to number question
                          philmodjunk
                            

                          the question mark suggests that Packing_Slips_MGX Customers1::Salesperson is blank or zero. Since dividing by zero produces an undefined result, you get the question mark.

                           

                          Place Packing_Slips_MGX Customers1::Salesperson on your layout and check to see if that's the case.

                          • 10. Re: Calculation to number question
                            mgxdigital
                              

                            It's letters.

                            We have 4 salespeople and under that field, it should their initals. I guess that is why it cannot be divided.

                             

                            Anything else we can try?

                            • 11. Re: Calculation to number question
                              philmodjunk
                                

                              Yep, you need a number not text.

                               

                              Such as another use of the count() function? :smileywink:

                              • 12. Re: Calculation to number question
                                mgxdigital
                                  

                                I'm sorry. I'm confused. If made a Count of the salesman and that gave me numbers that I know are wrong.

                                 

                                If I have the saleperson field, then I made a copy of it and relate it to itself using hte salesperson field and related that to the packing slip database throught company and then make the count function....it just doesn't work.

                                 

                                Can i have baby steps :)?

                                • 13. Re: Calculation to number question
                                  philmodjunk
                                    

                                  robin wrote:

                                  I'm sorry. I'm confused. If made a Count of the salesman and that gave me numbers that I know are wrong.

                                   

                                  If I have the saleperson field, then I made a copy of it and relate it to itself using hte salesperson field and related that to the packing slip database throught company and then make the count function....it just doesn't work.

                                   

                                  Can i have baby steps :)?


                                   

                                  Let's start with the basics.

                                   

                                  What is Salesperson? A text field with a single text entry that identifies a single salesman?

                                   

                                  Describe the SalesPerson Table and how exactly it is related to your invoices table or is SalesPerson a field in the Invoices table?


                                  • 14. Re: Calculation to number question
                                    mgxdigital
                                      

                                    Salesmen is a text field in our Customers database. There are 4 salespeople and they are identified by their initals.

                                     

                                    The customers database is connected to the packing slips database but a field in both called company name (=)

                                    The salesman field is based on the company.

                                     

                                    There is a field in the packing slips database called shipdate. And with your help I made a count feild called "sameshipdatecount" that shows me how many jobs shipped on a particular day.

                                     

                                    Now, I would like to add another number to tell me out of those jobs that shipped per day, how many were per each salesperson.

                                    Something like this:

                                     

                                    Orders shipped today: 28 (sameshipdatecount)

                                    Orders per salesperson:

                                        Dan - 5

                                        Doug - 10

                                        Sharon - 7

                                        Justin - 5

                                     

                                     

                                    1 2 Previous Next