A brain storming way...
1) create a table for each of your Screenshot column headings that contained your db info for each
2) Create a 4th table called say.... Activitities
3) Each Time a Lead, for example, was correctly done... The Leads table would create a new Activities record with TM's ID, Timestamp and Activity type [Lead]
4) Same would be true for the Appointment table... It would create a new Activity with TM's ID, Timestame and Activity type [Appointment]
5) Create calculations, finds and such form Activities table to generate your reports...
A feature that you may find helpful is that you can add fields to a table that auto-enter the date, the time or the timestamp (date and time together) the record in that field was last modified. And a text field can be set to auto-enter the account name of the person modifying the record.
A relationship can match by account name and date, or even a range of dates to count the number of records modified by a given user.
Hi Jim and Phil. Thanks for your answers.
I currently have a "Notes" table that records all lead status changes automatically as a telemarketer makes a change...
I.E : Telemarketer calls client and they book an appointment, the status changes to "Appointment Booked"... or if the customer is no longer in business, the status reflects that...
This was originally done so that a lead could contain (relate to) many "notes" and we could effectively track a telemarketers progress through a lead....
In effect, I am already recording the information that you suggested, Jim. I think that is all I require.
The "Leads" table also already records a "last modified" time and user, based on any changes to the record.
Where I am stumped is how to display that data as per the above in a report. The counting and displaying of the related data...
If I understand correctly, your Notes table has dates of last Modification and only one indicator of status....Status.... say New, Lead, Appointment and Inactive. I am not sure that is the right words but you would be using only one Status at a time and a last Modified time.
I would create Activiites table as i suggested above and still create a New Activities Record each time the Status Changed. This new table would be "Transparent" or not affected your current Notes Records. It would record the info, needed to generate your Screen Shot Report (SSR) as I noted before.
That SSR would be made as a Layount in your Activities table.
Ideas for calculations in the Activities DB...
1) Age = Get (Current Date) - CreateDate // this would give you the age in days since creation... then 0 = today , <7 = week, <30 = this month
2) Do a Summary report for dates, types, TM and counts.
"1) Age = Get (Current Date) - CreateDate // this would give you the age in days since creation... then 0 = today , <7 = week, <30 = this month"
Awesome, Jim, Thankyou!
I think this was the piece of my puzzle that I was missing.
If I then create a field in the Activities table called "Marketer Name" and use that field to make the relationship between activities and Leads the "Last Modified by" field, then I can simply create records in the activities field with the "Marketer Name" using a drop down list of the Staff name (which will always relate to "modified by"... and the calculation fields should populate accordingly.
I very much appreciate your help mate. :) thanks.
Sometime I get clear thinking be designing a "Paper" system without computers. For example...
You create a standard index card with Lead info required...ie., Name, date, type of lead, phone #, notes.....
A TM must fill out that card for each lead contact and take to the Activities Assistant in Room 223. There the assistant records the TM ID#, LeadID# on the card and Timestamps that card info in the Activites Book. TM has now a verified Contact for pay reasons.
Once a day, the Activities assistant is asked to create a "Production Report" from their record book. They would get (find) only the records for the last 30 days [ current date -30 ], put the "found set" of records on the conference room desk, sorted in chronilogical order, then take pad of paper for counting by TM ID# ( they dont need the TM's names because those are kept in the Personnel office) in a separte row..
Ok now from the stack of Activities "found set" , go to first record, make a mark if it today and mark for week and mark in the month column on the pad.
loop and go to next record
Finish and copy the record pad for all that that need it.
oh my , let me think how to encode what is did in Filemaker.
Personnel dept need TM info for payrole, taxes, performance detail and that's a Table using a unique TM ID#
Production dept needs Lead information the includes unique information for the Leads Table, each Lead has a unique LeadID# from the Lead information Table.
Activities Group needs daily Activities info in a Table too.
Let's count them... 4 different Tables needed and their relationships become fairly obvious and the info [fields, calc, etc] in each table is done as the business needs.