Route Planning

[PascalB82]

Hi there !

I've created a long time ago a database for our client's list, Service Calls and Route Planning. The Client List and the Service Calls are related by the phone number of the client. So when we create a new service call, there's a script that copy/paste the phone number from the list to the service call. All the other fields ( Name, address, City, etc. ) are "lookup" so they "enter" automatically. When we do the route planning for a particular day, there's a script that find the service calls for that day and copy/paste all the info of the service calls ONE by ONE in seperate fields for seperate service call. Now that we have 2 different client's lists and service calls ( one for spas and the other for Fitness Equipments ) and upgraded from Filemaker 8 to 11, I want to make it simpler. I want that the Route Planning updates by itself when we create a new service call from both databases.

I need help !! I cannot find the way to do it properly !

Thanks !

[comment]

I don't get this part:

there's a script that find the service calls for that day and copy/paste all the info of the service calls ONE by ONE in seperate fields for seperate service call.

I would think all you need is a table of Clients and a table of ServiceCalls. No information needs to be copied from one table to another - that's what relationships are for. I am not sure what's involved in planning a route - wouldn't it be just a matter of finding the calls for that day and arranging them in some order?

Now that we have 2 different client's lists and service calls

That's probably not a good idea. Why not have one table for each, with a Type field?

[PascalB82]

I am not sure what's involved in planning a route - wouldn't it be just a matter of finding the calls for that day and arranging them in some order?

Yeah, it's just a matter of finding the calls for that day but they don't need to be arranged in a particular order. I just need to see what are the calls we need to do.

That's probably not a good idea. Why not have one table for each, with a Type field?

Sorry for the noob question, but what's a Type field ?

[comment]

what's a Type field ?

Just a field where you would enter either "Spa" or "Fitness Equipment".

[PascalB82]

wouldn't it be just a matter of finding the calls for that day and arranging them in some order?

Is there a simple way to do it ? Now my script is performing a find in the Service Calls for a particular day and copy/paste all the info in seperate fields in the Route Planner for each service call it finds.

Just a field where you would enter either "Spa" or "Fitness Equipment".

I know what you mean, but my boss doesn't want to put spa clients with fitness clients and vice-versa.

I wanted to show you what I meant by service calls and route planner, but I'm unable to upload files.

[comment]

Now my script is performing a find in the Service Calls for a particular day and copy/paste all the info in seperate fields in the Route Planner for each service call it finds.

That's the part I still don't get. If you have found the calls for a particular day, why not simply show them in a list view? What's the point of duplicating the information elsewhere?

Another way you could show the calls is in a portal based on matching the selected date (in a global field) with the date field in the Service Calls table.

my boss doesn't want to put spa clients with fitness clients and vice-versa.

[shrug] What can I say...

I'm unable to upload files.

You need to zip them first.

[PascalB82]

my boss doesn't want to put spa clients with fitness clients and vice-versa.

I'm trying to convince my boss to merge both client lists, but the thing is if we have a client with a spa AND a fitness equipment, when we will create the service calls, how can I tell that I'm doing the call for the spa or the fitness since the tables (client's list and service calls) are related by the phone number of the client ?

[Vaughan]

... the tables (client's list and service calls) are related by the phone number of the client

This is really NOT a good idea. What happens when a client's number changes?

Clients should have an auto-entered serial number as their primary key.

Also, it's the service call record that has a TYPE field to mark it as spa or fitness equipment. It's possible that a client could have both.

[PascalB82]

This is really NOT a good idea. What happens when a client's number changes?

Clients should have an auto-entered serial number as their primary key.

Also, it's the service call record that has a TYPE field to mark it as spa or fitness equipment. It's possible that a client could have both.

I see what you mean ! That's a real good idea ! Thanks a bunch !

I'm trying to do my route planning with portals. I want to make something like the file I attached ( This is my current route planning ). Can I do it similar with portals ?

Feuille de route.zip

[PascalB82]

Any ideas anyone ?

[PascalB82]

Bump !

[bruceR]

Bump !

You have not included the necessary files in your zip file.