Conditional auto-creation of records

[stockingup]

Hi everyone... I am trying to get around the following problem:

I have a table "Customers" and a table called "Service Events". Office staff are able to create a service event via a portal in the customers table, and log the service by adding a description, service type and service date. (This is later processed by another department).

My problem is that I have been asked to make the system such, that if there is not future jobs logged for the customer, one automatically gets logged based on the recommended service interval (which is calculated based on previous visits, or alternatively set by the logistics department).

I can't think of a way to automatically schedule a job if there are no future ones scheduled. Keeping in mind that we have about 10,000 records in the customers table, what do you recommend?

I have considered having an end of day, or end of week, or even end of month script that can be run, and loops through the customers table verifying if a related record in the service events table exists. If not, one gets created based on the recommended service frequency.

What do you think? My only immediate concern is the length of time it will take to process so many records.

Best Regards,

Jason V.

[bcooney]

I don't see any problems with a looping script creating future service records. If this runs at 3am via a server script, how many customers do you think would be in the final found set? You'd need to look at more than the existence of a related record from customers--you'd need to find all customers that do not have an OPEN service date (however that's defined).

Calculating the date for the new service is trivial with a supplied interval in a pref. You can even get clever and skip weekends and holidays. However, you mention the logistics dept. How do they affect your rules?

[stockingup]

Thanks for the reply bcooney.

and yes, you're right. It wouldn't be as simple as looking for related records. I would have to check for related records with type = Service and a date > current date. I would also have to check whether it was an active or terminated customer.

However, overall, the concept shouldn't be very different.

Regarding the service frequency, it takes the average cash collected over the past X visits, looks at the total number of visits and the average time between visits, and then works out a recommended interval based on what is set as the "target" cash. So lets say for instance, over the past 10 Visits, the average cash collected was 10£, and the average interval was 10 days. If the target cash was set at £20, it would increase the recommended interval to 20 days (twice as long should result in twice the average cash - provided nothing breaks or goes empty!).

However, Logistics have the option of setting a fixed frequency for high value customers, such as every week. In these cases it would look for a value in this field - say "Forced_Frequency". If the field is empty, it will take the recommended.

See any immediate problems?

Thanks for all the advice!

[comment]

How about something simple (relatively speaking)

1. Find all future service events (in their own table);

2. Go to Related Record [ Customers ; Match found set ];

3. Show Omitted Only

4. Import from Customers into Service Events (incl. a NextCall field calculated in Customers).