10 Replies Latest reply on Jan 26, 2010 11:10 AM by jamesdalbright_1

    I need to define a field so it that it keeps track of how many emails I sent in the past 24 Hrs.

    jamesdalbright_1

      Title

      I need to define a field so it that it keeps track of how many emails I sent in the past 24 Hrs.

      Post

      I have a summary field that I defined to calculate how many emails I sent out based on any found set but what I really would like is to continually know how many emails I sent out with in the 24 hr period prior to the current time. I am trying to find out how to define the existing summary field or a different field to accomplish this.  Thanks.

        • 1. Re: I need to define a field so it that it keeps track of how many emails I sent in the past 24 Hrs.
          philmodjunk
            

          How do you log the date, time of the email? A date field and a time field or just a time stamp field?

           

          Let's say your time stamp field is called SentTimeStamp in a table called EmailLog

           

          Perform a find like this in a script:

          #86400 seconds = 24 hours

          Enter Find Mode[]

          Set FIeld [EmailLog::SentTimeStamp; "> " & Get(timestamp) - 86400

           

          The summary field you have already defined will then report your count of emails over the last 24 hours.

           

          There are also ways to set up a relationship with an unstored calculation field that can be set up to return the same total without a script.

           

          ParentTable::24HourKey < EmailLog::SentTimeStamp

           

          define 24 hour key as Get(timestamp) - 86400 and make it unstored

           

          Define a calculation field Count(EmailLog::SentTimeStamp) in ParentTable to get your count.

          • 2. Re: I need to define a field so it that it keeps track of how many emails I sent in the past 24 Hrs.
            jamesdalbright_1
              

            Thanks for responding. I am not sure how to go about setting up the field that does not require the script. I am not sure how to go about setting up the first line "ParentTable::24HourKey < EmailLog::SentTimeStamp.  I created a calculation field named 24HourKey and defined it Get(currenttimestamp) - 86400 but I could not figure out how to make it unsorted.

            The parent table in this case is named Campaigns and the timestamp field is named Campaign Date.   

            • 3. Re: I need to define a field so it that it keeps track of how many emails I sent in the past 24 Hrs.
              philmodjunk
                

              "I am not sure how to go about setting up the first line ParentTable::24HourKey < EmailLog::SentTimeStamp"

               

              This line describes a relationship between two table occurrences. You'd use Manage | Database | Relationships to create this. Using the information from your last post, it would look like this:

               

              Campaigns::24HourKey < ?????::Campaign Date

               

              You haven't indicated the name of the table where Campaign date is defined. That name should go in place of ????? in the above example. (If it's the same table as Campaigns, you can still do this by making a second instance of the Campaigns table occurrence.)

               

              To make 25HourKey ustored (not unsorted), double click the field's definition and click the storage options button in the specify calculation dialog that appears. You'll see an option there labeled "Do not store..."

              • 4. Re: I need to define a field so it that it keeps track of how many emails I sent in the past 24 Hrs.
                jamesdalbright_1
                  

                I could not get it to work and this is what I have done so far.

                I created a calculation field 24HourKey in the Campaigns Table and defined it as Get (CurrentTimeStamp) - 86400

                I created a second occurrence of Campaigns and called it campaigns_Campaigns.

                I connected the two tables together via the Campaigns::24HourKey and campaigns_Campaigns::CampaignDate(time stamp field) and defined the relationship Campaigns::24HourKey (less than or equal to) campaigns_Campaigns::CampaignDate

                I created a calculation field called Camparigns::24HourContactCount and defined the calculation as Count (CampaignDate).

                I placed both Campaigns::24HourKey and Campaigns::24HourCampaignsContactCount on the Campaigns layout under the recipients portal that contains the contacts that the campaign were sent to.

                The Campaigns::24HourKey field showed the number 1 which was not correct.

                The Campaigns::24HourContactCount showed 6.34e + 10, which was also not correct

                The correct 24HourContactCount should have been 126 and this was based on two campaigns each with 63 contacts with in the 24 hour period. 

                Does the fact that the recipient portal shows the contacts that the campaigns were sent to, which is on the Campaigns layout shows records from the campaigns_campaigncontacts_Contacts table, which is an occurrence of the Contacts table?

                In the past in order to count the contacts in the recipients portal I had to define a subtotal calculation field as follows: LayoutObjectNames ( "Forum Email Campaign Management" ; "Recipients" ) & Count( campaigns_campaigncontacts_Contacts::Contact ID )

                And in order to get the total counts I created a summary field that added the subtotal fields.

                I then created a script button that would find the campaigns that fell within a 24 hour period and the total count would appear.

                The object of all of this is to keep a running count of the current time minus 24 hour period. 

                • 5. Re: I need to define a field so it that it keeps track of how many emails I sent in the past 24 Hrs.
                  philmodjunk
                    

                  I created a calculation field 24HourKey in the Campaigns Table and defined it as Get (CurrentTimeStamp) - 86400

                  This should also be set as Unstored (click Storage options and select "Do not store...") If you don't do this, the calculation will not update correctly. Then make sure it's return type, combo box in lower left corner of specify calculation dialog, has TimeStamp selected.

                   

                  I created a second occurrence of Campaigns and called it campaigns_Campaigns.

                  Good.

                   

                  I connected the two tables together via the Campaigns::24HourKey and campaigns_Campaigns::CampaignDate(time stamp field) and defined the relationship Campaigns::24HourKey < campaigns_Campaigns::CampaignDate

                  OK (You can post than or equal symbols by typing in < and selecting the underline style for it.)

                   

                  I created a calculation field called Camparigns::24HourContactCount and defined the calculation as Count (CampaignDate)

                  As you discovered, this returns 1 as it counts only the current record in Campaigns instead of all related records in Campaigns_Campaigns. Use Count (Campaigns_Campaigns::CampaignDate) to count all the related emails in the last 24 hour period.

                  • 6. Re: I need to define a field so it that it keeps track of how many emails I sent in the past 24 Hrs.
                    jamesdalbright_1
                      

                    I did everything you said and it returns the correct timestamp in the 24HourKey field but

                    after redefining the 24hourContactCount field to Count (Campaigns_Campaigns::CampaignDate) it

                    returns the number of records or timestamp fields that fall with in the 24 hour period. In my trial I dated two email campaigns to fall with in the 24 hour period each having 63 contacts. The 24HourContactCount field should have returned 126 instead it returned 2. Some how I need the 24HourContactCount field to add up the Contact Subtotal fields, which are set up to calculate the number of contacts in the recipients portal in each of the records and only add up the subtotal fields of the records that fall with in the 24 hour period. The Campaigns::Contact Subtotal field is on the Campaigns layout that is set up to show records from the Campaigns table.   

                     

                    • 7. Re: I need to define a field so it that it keeps track of how many emails I sent in the past 24 Hrs.
                      philmodjunk
                        

                      24hourContactCount field is now correctly working. It's just not producing the results you need. It's currently designed to count he records in campaigns_campaigns instead of the number of records in a related table of contacts.

                       

                      You can do this with a change in the calculation.

                       

                      Let's assume that your contacts table is called contacts and is linked to Campaigns.

                       

                      Define a calculation as ContactCount in Campaigns as Count(Contacts::ContactID) //any field in contacts that is never blank will work here.

                       

                      define 24hourContactCount as Sum(campaigns_Campaigns::ContactCount)

                       

                      That should correctly compute the total you want.

                      • 8. Re: I need to define a field so it that it keeps track of how many emails I sent in the past 24 Hrs.
                        jamesdalbright_1
                          

                        Yes! Thank you. That corrected the return. Can I in some way hide the 24HourKey and the Count Contacts

                        fields and just show the 24HourContactCount field? 

                        • 9. Re: I need to define a field so it that it keeps track of how many emails I sent in the past 24 Hrs.
                          philmodjunk
                             You don't need to put either field on a layout in order for 24HourContactCount to calculate totals correctly. You can just remove them from your layouts.
                          • 10. Re: I need to define a field so it that it keeps track of how many emails I sent in the past 24 Hrs.
                            jamesdalbright_1
                               Thank you very much for the many times that you have helped me with the Email Management Campaign program.