Creating worklist based on date

[PaddyO]

I need a calc to base a daily work list from a record creation timestamp.

Example: Record of customer is created when he/she walks through the door. 7 days later a reference to that record needs to appear in the daily task list for the sales person to send a thank you card. Does that make sense?

Thank you.

[LaRetta]

Some ideas ...

1) Use a relationship to display portal:

Create a calculation called cToday (result is date) with: Get ( CurrentDate ) - 7. Be sure to set the storage options (check the 'do not store ...'). Create a second table occurrence of your table named Due Tasks. Join them thus:

mainTable::cToday :great: TaskDate

Create a portal on your main layout based upon Due Tasks. Now, whenever that Task Date is 7 days away, it will appear in your portal. There will be no need to sort the portal because the tasks will already be sorted by their natural sort state of creation date and the most urgent (most overdue) will appear at the top. If you really wish to restrict the portal display to only those tasks due EXACTLY 7 days away, then change the :great: to an =. But what happens if a task is older than the current date less 7? It should still display, right; at least until the task is done? If it’s a Saturday or Sunday or the day the person is sick, tasks could be missed. Which is why I mention a restriction on the calc to better control the period displayed. I can't make a suggestion because I don't know how you currently indicate that a task is done.

Portals are great because the list will self-update every time there is screen action of any kind (if you unstore the calc, that is). If you wish to perform a find, we can do that easily as well ... just use:

2) Perform a Find in your tasks table:

Enter Find Mode [ ] ... uncheck pause

Set Field [ TaskDate ; " :less: " & Get ( CurrentDate ) - 7 ]

Perform Find [ ]

3) Use a relationship to quickly jump to your tasks table to view the tasks in a list:

If your task list shouldn't be in a portal and you have too many records (100,000+) you might skip the find and instead use the first technique to establish your relationship. But instead of displaying in portal, script-step Go To Related Record [ Show Only Related ; Current Record ; "Tasks Due" ].

LaRetta

[PaddyO]

LaRetta,

Thank you for the quick response. What you describe is close to what I'm looking for except that I need to base the calc on the creation date timestamp of the customer record. Can I do that?

Basically, that creation timestamp date sets a marketing plan in motion where the salesperson is prompted to send letters or call for a year. There are 4 contacts in the first month then every 45 days after that.

But, the record they need to contact needs to not show up in the workplan until the day they're supposed to make contact. Can I do something like a case with a greater than or equal to based on the creation date that will return a result to bring the record into the portal for a "today's work plan" layout?

You bring up a good point about the record staying until action has been taken. I think it should stay there until it is "checked off".

Thank you for any help you can provide.

PF

[LaRetta]

Basically, that creation timestamp date sets a marketing plan in motion where the salesperson is prompted to send letters or call for a year. There are 4 contacts in the first month then every 45 days after that.

A few issues and then we'll be able to nail this down, I think:

1) Do you need to track any of the following pieces of information as well: HOW the salesperson makes contact (whether phone or letter), WHICH salesperson contacted them, or the exact date salesperson completed the task? Will you ever need to review this information?

2) Are there any other activities which are (or might be) generated from the marketing plan that you may wish to track? What if you decide to send a bulk mailing to SOME of those customers? Might it be useful to capture the date and information mailed as well?

3) Will you ever need to track HOW your customers were contacted vs. (example) their sales volume? This is valuable information in future definitions of the marketing plan. Reporting capabilities can also provide 'efficiency' reports showing which salespeople were on the ball or which might be dropping said ball.

If you are certain you won't need any of these three, then one ContactDate in the Customer record is all you need. It can be filled with the next upcoming contact date either via auto-enter calc or script – depending upon the following question: If the first 7-day mark is not addressed on that exact date (but instead 4 days later), do you want the NEXT 7-day mark to be 7 days from THAT date (or 14 days from the customer creation date)? These are business rules which must be pinned down. If you don't then you will hit an ooops moment when a salesperson calls a customer on 11/3 and FM tells them to again call the customer TWO DAYS LATER (11/5). Worse still, 11/5 is Sunday. Do they work on Sunday? Improper decisions here can also drop the ball on Customers. My number one pet peeve is how a customer can be overlooked; sometimes for YEARS! It is preventable lost revenue!! Each jump of your contact date can skew the results, depending upon normal admin inconsistency. By the end of it, your salespeople will need to reverse time to make contact. Hey, don’t laugh, I’ve seen it happen. So what are your rules here? What if the date is a weekend? What if the rep is out sick? Does someone else make contact? What happens when rep goes on vacation for two weeks? Is the call skipped or does s/he just dial twice (ha ha)?

If your answer to ANY of the questions above is yes (that you need additional tracking), then I suggest you use another table called Activities. You can have the following fields: ActivityDate, Type, CompletionDate and even comment. It can be used for tracking all kinds of activities. You can use the Type field value of MKTG. These records could appear by SalesPerson, by ActivityDate, by Customer and so on and it provides a history and all the functionality I noted in the first post will be at your command. You are talking about tracking 11-12 individual tasks PER YEAR per customer, after all. If you limit your thinking here, you may have to change your structure later. Been there done it.

One additional thought ... I would seriously consider designing this for browse-mode handling by the Sales Manager or Owner. As the business changes, they may want to change the timeline rules. They can have a duration checkbox/radio panel which they will use to finetune their sales team/customer interactions. Otherwise, you'll be tweaking calcs or adjusting relationships/scripts. In fact, be prepared that there will come a time that you will want to track different TYPES of customers by different marketing timelines or even EACH customer independently (depending upon prior sales patterns and so forth). But if structured properly, each customer would simply have their own duration checkbox/radio panel. How best to provide these rules will depend upon all your other decisions but it is a final piece I would encourage you to take. If you have recurring events, you may also be looking at a calendric structure instead. Well. I've said enough. Tell us your vision and we'll help you get there.

LaRetta

[PaddyO]

High praise and adulation to you LaRetta! We're thinking along the same line. I do indeed need to track the data for a manager's report. Also, I was quite concerned about how to handle vacation/sick days, or worse yet, employee turnover...

So the activities table is the magic here. I presume it would all be related by CustomerID, SalespersonID, and TransactionID, correct? My original thought was that the Transactions table would be the great gathering place for relational data since that is what would begin the marketing process, but it didn't resolve the tracking of marketing activities by salesperson.

LaRetta, I'm sure you can see my profile is "Novice" so I don't want to burdon the forum with a "how to" class unnecessarily. I do have a full set of CD's from Studio E Works with Jerry Robins that I've been making my way through. I'm just concerned that parts of the solution I'm developing isn't covered in the training. Although, I'm only on volume 5 of 10. If you feel this is going to be a bit of a boat anchor I will understand completely. Maybe I need to consider working with a more skilled developer than myself on a contract basis to get me over the hump.

Having said that, your insight is much appreciated! The idea of providing managers with the ability to tweak the marketing plan is a must. The end goal of this solution (from the Sales staff side) is for Sales staff to be interacting with the "Daily Work Plan" through the IWP. Does that send any red flags up that I'm not prepared for?

Of course the highest aim for all of this is to provide the sales manager a snapshot of Salesperson performance and to track closing ratios, implementation of the marketing plan, and track all other important sales data such as how the customer heard about the business, etc. Though, right now I just need to develop the Daily Work Plan piece.

Again, thank you for your time and insight.

Regards,

PF

[LaRetta]

The specific pieces aren't important. Please allow me to explain ... you are seeking answers to functionality when we don't (and maybe you don't) know your base yet. If your tables are established then EVERY table should have a unique computer generated ID. The joining will happen as you decide what you need. You can work through that in many ways; FM Forums, a Developer, books/videos. As long as you have your business goals clearly defined and design a good BASE structure, the rest is just popcorn.

As you work through the 'what ifs' draw (and note) them on your drawing. If yes, do this; if no, do this. Pretty soon you will SEE exactly what you need: "I need each salesperson to be able to view their activities overdue & coming due AND because of vacation, we need to be able to assign a temp rep to keep that reps activities up to date while they're on vacation". How do I need to join these table occurrences in my graph so the temp rep kicks into effect and the activities (as of a certain date) start showing in THEIR list?" I've found out (through experience) that unclear questions produce NO answers (or unclear answers) on forums. Create a demo file so you can try what you envision, then post it for clarification. It is much more likely you'll get help with a difficult concept if you present a file with the existing tables and fields.

Bottom line ... ALL tables are equal. By that I mean, if you learn the principle of the Customers/Invoices/LineItems structure, everything else is basically the same theory. Do you understand relational structures at all? Your project is pretty advanced stuff and I would suggest hiring help or being prepared to hit a steep learning curve. It can be done - I know. But it'll take a lot of sweat to pull it off. A good Developer can jump you miles ahead but you will still need to be very clear (do your homework) so time (money) isn't wasted.

So the activities table is the magic here. I presume it would all be related by CustomerID, SalespersonID, and TransactionID, correct? My original thought was that the Transactions table would be the great gathering place for relational data since that is what would begin the marketing process, but it didn't resolve the tracking of marketing activities by salesperson.

I’m unclear how Transaction table applies. Is this what you will call your activities table; if so then yes. Each activity will generate a new record. This Transaction table can then be plopped on the Customer layout to show a customer’s activities (joining CustomerID in each table occurrence); plopped on a Staff table to see all staff assignments (joining StaffID in each table occurrence) and so forth. Standard relational stuff!! You can’t add functionality without a good base.

The end goal of this solution (from the Sales staff side) is for Sales staff to be interacting with the "Daily Work Plan" through the IWP. Does that send any red flags up that I'm not prepared for?

None. But I haven't done any IWP yet.

But theory is theory. The only difference I can see is paying attention to which tools are used in scripting (be sure they are web compatible). But I believe that your structure (and most of the functionality) would remain the same regardless.

If you post an example of a specific problem, we can answer it. If you post your base tables, we can tell you if you are ‘flat file-ing’ it incorrectly. But without even knowing your current fields and structure, we're donkey-tail pinning (like my very very long prior response). Providing a file from which to work will save time and that is much appreciated. It takes TIME to throw together a working demo showing a proper related structure (for example) because there are four major tables (invoicing for example) involved and we have to create the data, as well as scripting and calcs to show it in action. If your base is sound, it will save you HUNDREDS of hours of correction and re-working. If structured wrong, you will pull out your hair, cuss the cyber gods and take up knitting instead. You are obviously on the ball; I commend you. Shoot for the stars right off the bat; don't settle for half-way. It's like building a bridge to go halfway across a river. It will take more effort to complete it and you'll NEVER eliminate the bump in the middle.

LaRetta

[PaddyO]

You are infectiously empowering!

My Transactions table is the gathering place simply because that is where all of the pertinent info seems to collide. Date of Transaction, Type of transaction (Demo or Purchase/Sold or Unsold), Salesperson info, Customer, Manager, Financial Institution used to finance the deal, and the type and description of the unit purchased. The Salesperson Tasks/Marketing Plan are all based on the Transaction type and date but I don't want to be short sighted and miss an opportunity to capture and track data.

I do have an understanding of relationships, Key Fields/Match Fields, Foriegn Fields, etc. According to your post I could do a better job of drawing out my relationships and thier actions.

I'll focus on that this weekend and get a file posted with some sample data. That should help get past the ether and into the real world.

You're a tremendous help.

Have an excellent weekend, and thank you again.

Patrick

[PaddyO]

It just hit me after tucking in the kiddos and rereading your post. I see the importance of the Activities table. It generates a new record when ANYTHING happens and is "super-related" if I can call it that. Wow, okay, I can see where you're headed with that. Makes perfect sense. I'll keep that in mind while I'm redrawing the base.

Thanks again.

PF

[PaddyO]

Okay, I think I have a workable base starting to form. Here's the link to the file.

http://homepage.mac.com/ofarrellcompany/FileSharing52.html

If you have a moment to look over the file and let me know if I'm 'flat-filing' it as you say. My activities table isn't developed yet. If I understand correctly my Activites table will pull data from the Transactions table and show date sensitive tasks which I can drop into a Salespersons Daily Task layout. From that point I can track which tasks are completed (or not) for reporting purposes, correct?

[PaddyO]

Hello again,

I've spent the last month or so reading, making it through tutorials, and looking at other solutions and I would be very appreciative if you would pass your eyes over my file. I'm still not sure how to use the 'Activities' table exactly. I'm getting closer though.

http://homepage.mac.com/ofarrellcompany/FileSharing61.html

The file I have posted here has the graphics stripped out of it except for the buttons.

Thanks