Script, report or calculation?

[PiedPiper]

I have an Employee database and 26 employees are also salesmen (designated as SALES in Employee Type field. I have created an Activity database which will track dates and customers contacted. I want to create the sales records ahead of time - at least one for each 'working' date for each salesperson. If sales contacts a customer, they would use the existing record and can create more, but if they don't use the one date record, I want it to display "no compensation" and if they have more than 6 sales calls that day, I want it to display "wage". If a salesperson doesn't perform at least one sales call, they don't get paid for that day; if they do 1-6, they get salary and if they do over 6 calls, they get additional compensation at hourly wage rates.

Our owner wants to be able to view the weekly sales results (by salesperson) by clicking a button and our payroll wants the reports on the 16th and 31st of each month for the prior two weeks.

Do I need a script or just a report or a calculation? I hve been trying to figure out how to generate a new date for each sales person - and my script works for creating the weekly dates but I can't figure out how to have it insert each salesperson's code and repeat with each salesperson.

And how do i count Activities per salesperson per week to see how they get paid and to generate a report? Did I explain enough?

[LiveOak]

The key here (as almost always) is to set up the files with the correct architecture. It sounds like you are reusing records in your Activity file. I wouldn't do this. I would have an Employee file, a Customer file, and an Activity file in which each contact is a separate record.

This is similar to the problems created when a timecard type file is created with too much information (say a week for one employee) is put in a single record. It makes if VERY hard to separate and summarize data. It is always good, in these situations, to make each transaction/record the smallest atom of data.

Once you have organized your contacts this way, it is easy to find, sort and summarize them by month/week/employee or by customer or by whatever. If you have multiple contacts by multiple dates in a single record, there is virtually no way to do this.

You need 1) the aforementioned structure, 2) the correct layouts with appropriate subsummary parts and Summary fields to place in the parts, 3) scripts to find/sort/print the reports.

-bd

[PiedPiper]

Hi Live Oak. Thank you for your recommendations. I think I understand what you are saying ...

I have Employee db with EmployeeID and EmployeeType (SALES). I have a Customer db with CustomerID and EmployeeID (the assigned salesperson). I have created an Activity db which would have ActivityDate, EmployeeID, CustomerID, ActivityCode and Notes.

Each Activity line will be a new date, salesperson and customer. To identify 'days missed', I tried a calculation (frown). It appears it's easier to identify a blank record, than to identify a missing one. If Date <> exists!! That's part of the confusion I'm dealing with.

My script uses a counter to create records in Activity (one date for each work-week day for each salesperson - their starting minimum)and when they create a sales activity, it finds that first pre-created date (if empty), otherwise it greates a new sales activity record. But when creating the 'beginning' required activities, Activity doesn't know the Employee to insert? I think I can write the loop script once I figure out how to grab the EmployeeID - do I use = Employee::EmployeeID if Type = SALES or do I use Customer::EmployeeID?

My view of the data needs to be:

Week#:

John Smith

5/12/2003 (Monday) 5 calls salary

5/13/2003 (Tuesday) 7 calls Wage

5/14/2003 (Wednesday) No compensation

... and then sub-totaled on these three results.

etc.

I don't even know which database to use to generate this report or where I would put the calculation(s) once I've figured it out. I have joined Activity to Customer on CustomerID and have joined Activity to Employee on EmployeeID.

Sorry this is so long. Thank you

Pete (oh this is a pain to try to edit! Sorry!)

[PiedPiper]

Hi, maybe I'm not explaining what I need very well. Doews anyone have any ideas? I keep working on this and coming up blank. I told our owner that he should hire one of you instead of handing me a book ... to no avail. Is this even possible? Please help.

Pete

[PiedPiper]

I really need help writing this script. Or am I doing it all wrong? Activity is joined to Employee on EmployeeID. I

[McCormick]

This is a bit more complicated than I can totally get without examining the database. I'm going to try to be helpful anyway, because I've been there. Hopefully it will be of some help if I point out a few pitfalls and solutions.

- I don't see how to exit the loops in the "CreateDates" script. That might account for the 15,000 records. "Exit Loop If..."

- I notice in the script above that you have "Perform Find/Replace" when you might have wanted "Perform Find." The former replaces data with other data ("French" with "Freedom", for example). The latter executes your search parameters.

- Is EnterWeekStart a date, or a week, or something else?

- It is easier to find a blank record than a missing one. It sounds like you're choosing to have it automatically generate a lot of blank records beforehand, and then fill them in as they happen. That's probably best. One weak spot in FMP is that it's hard to save a found set, so it's hard to check for something being missing without losing your current found set (although if you try, it is good to turn on Error Capture, so that finding no records becomes a CurrentStatus instead of just an error). One option for generating those blanks: say you want to generate a week's worth. Have a script that will do that and gets triggered when FMP is opened, but only if the date is Monday (or Friday, perhaps). Part of an opening script:

If(DayName(Today)= "Friday")

Perform Script("Build a Week")

- I can't tell quite what happening/ supposed to happen, but in the Activity script "CreateDates" you have a step that assigns an EmployeeID number, but it gets that number by using the relationship that already exists with the Employee db. That seems circular, since I would have that relationship exist joined by EmployeeID in the first place.

- If you're new to FMP, here are two tricks that can help open up some options for you:

1) You can make a relationship between a db and itself. Say, for example, in ativities you have a "Date" field. You can create a calculation date field "DatePlusOne" (the next day), and create relationship between Date and DatePlusOne, which lets you use concepts like "tomorrow".

2) If you're linking from one db to a second and then to a third, you can't look "through" the middle one (say, from Customer "through" Activity to Employee). However, you can have a calc field in the middle one that is equal to a value in another database. For some tasks, this is redundant, for instance, in Activitiy you can always print "Employee:LastName". But the way it is useful is that by having it, now Customer can call up "Activity:EmployeesLastName" where it could not otherwise call up "Activity:Employee:LastName".

I hope this was of some help;, or that you've solved it already and moved on. Good Luck!

[PiedPiper]

Thank you McCormick for answering me. Unfortunately, since I had problems with this whole process, I told my boss to back off and give me a month to learn some basics of Filemaker before I was required to provide solutions

Then, I promptly forgot that someone else might respond later - and I hadn't learned about being notifieed if someone answered. I know quite a bit more now but not nearly enough Your suggestions have helped quite a bite.

I'm now ready to again attempt a solution on this and hopefuly it will make more sense this time. Boss-o has changed his mind on what he wants, so I'll pull together his new ideas (and half our office staff also wants it to do stuff), and see how the new requirments line up with my previous attempts.

It looks like the entire process will chnge somewhat. I'll let ya'all know if I get stuck. BTW, is it ever okay to post a question to just see if there would have been abetter way to do something? Even if I've solved it? I feel like I can possibly come up with things to get something to work but I always wonder if there would have been a better way. Thanks for all the help.

Good Lord! Why does it spreadthings out like this? Drives me insane! I must be doing sometying wrong.

Pete