I need to be able to automatically look up service intervals allowing us to contact our customers when service is due. I know what I want to do, but I

Hi,

I can see the need but I am clueless about your files structure. What if this customer was to purchase 2 different 'products' with different service intervals ?

Could you be more specific about your structure ?

There surely is a way through this with calculations... Just give us a chance to help you more on this.

Hi Ugo and thanks for the post.

RE: I can see the need but I am clueless about your files structure. What if this customer was to purchase 2 different 'products' with different service intervals ?

This does in fact happen and they are treated like two seperate customers. In other words, John Doe purchases a 2003 B&S 5 HP he is assigned a unique ID number and the service intervals are entered. If they purchases another product, he is assigned another ID number and the new service interval information is entered. This does not happen often, but that is the way we handle it. There is very little chance the service intervals are the same!

I really appreciate your post Ugo, and let me know if you need more information.

Carl

Two separate customers : Huh. What's your Contact File like then ?

Anyway, I'm sure there's a better way to handle this, and I'm quite sure you'd need a "Service Follow-Up" file somewhere here with a portal showing the Customers according to the purchase dates in your "Line Item".

Does it make sense ?

Hi Ugo,

I wish I could explain things better so you could help me. It's my fault, I'm new to FileMaker and I'm just not able to explain myself very well. This post hasn't had much activity and I couldn't figure out if I was asking for too much help. Or I wasn't explaining myself well enough and I guess it's the latter. But I thank you for trying to help.

Carl

Carl,

Let see if we can help you some more with this one...

Your original querry was :

"I need to be able to automatically look up service intervals allowing us to contact our customers when service is due. I know what I want to do, but I

Hi Hugo,

I don

Hi Hugo,

I forgot to anwser your question "How a product relate to a customer ?"

[Contact] Model Purchased : : [Auto Info] Model

Thanks again,

Carl

Hi,

One more question. Even if the files you're developing are not intended to deal with invoices, you have some purchases stored in them. How are they stored ?

If you could find a way to have only one record per customer, this would make your life really easier, and even this table of contact "appointments" would be straightforward to built.

I'd like to help you more on this anyway. It wasn't clear enough for me about how your intervals are built and what are the text fields you were talking about, to try out a calculation though...

ANYONE ELSE TO THE RESCUE :!!

Hi Hugo,

RE: One more question. Even if the files you're developing are not intended to deal with invoices, you have some purchases stored in them. How are they stored ?

It is stored in the [Contacts] file in a one to one relationship. One Customer and One product.

RE: If you could find a way to have only one record per customer, this would make your life really easier, and even this table of contact "appointments" would be straightforward to built.

As you can see from the first answer. "One record per customer" is the way I want it to be set up.

RE: It wasn't clear enough for me about how your intervals are built and what are the text fields you were talking about, to try out a calculation though...

I'm not sure what "text feilds" your talking about Hugo. Let me know and I'll try to fill in the blanks.

Thanks again for your help, it looks like it's just you and me!

Carl

Hi,

wingwalker said:

There are 8 fields (currently Text Fields) that will each have a number to indicate the number of months in which a customer needs to be contacted. (6 for 6 months 12 for 12 months etc.).

These are the text fields I was talking about.

These fields reside in both the [Contact] file and the [Auto Info] file. The 8 fields in the [Contact] file use

Hi Hugo and thanks again for your reply,

Yes, the 8 text fields in the [Contact] file are auto filled from the 8 fields in the [Auto Info] file. Every month the manufacturers send us a list of new products and the 8 service dates. We then enter this information into the [Auto Info] file.

I never thought about converting number of months to dates. I guess because we get it by months, number of months is all I thought about. With what I said, what do you think about the use of dates?

Maybe one way to think of this is buying a car. With the car comes a manual with service dates based on mileage and/or months. With the engines we sell it is strictly based on months. But the principle is the same. We my have one manufacturer who makes 15 different engines and each of them have different service schedules.

I hope this helps Hugo.

Carl

Well,

So your manufacturer sends the 8 fields like 3Mo 6Mo 9 Mo...

Is there a logic to follow with this schedule or is it rather randomly ? If it's rather random, then why not have a Schedule File where you'd enter each schedule with an Id and have this Id entered in the Auto File. No problem later for your lookup procedure then, which is correct and safe BTW.

Then you won't need the 8 fields anymore in the Auto File nor in the Contact File. You just would need the related Schedule Id.

Now I assume the manufacturer gives an indicative schedule and you adjust it to your calendar of services. That's why I'd convert these Months into real dates.

Therefore, I'd enter the parameters in the Schecule File and will have the calc in the Customer File to determine what is the next schedule date.

If this make sense, we may start thinking about this f... calc.

Hello again Hugo,

RE: Is there a logic to follow with this schedule or is it rather randomly ?

I just looked at my [Auto Info] file and should pass this along. We sell over 250 engines from 25 to 30 different suppliers. Although each has it's own maintenance schedule, there is probably no more than 15 to 20 actual different schedules. In other words, if one engine has a schedule like 3, 6. 10, 18, 28, 39, 44, 55, months. There may be 30 other engines with that exact schedule. Don't know if this helps but I thought you should know.

You lost me on the ID part, but I think you know enough about what I'm doing to know better than I the way to approach it.

Thanks again Hugo,

Carl

20 different schedules are enough IMO to drop them out in a Schedule File.

Sorry if you got lost.

If you create this file, each record would have an Unique ID and its particulars.

"In other words, if one engine has a schedule like 3, 6. 10, 18, 28, 39, 44, 55, months. There may be 30 other engines with that exact schedule."

You see you can "group" some engines together...It's easier.

Then, you'd have an ID called ID_Schedule right in the Auto-File, relating to the ID of the Schedule File to which it relates.

The problem I can see with the 8 fields is that if one manufacturer goes to a 9th, you'd have to rebuild the whole job.

May be a unique field would suffice which all months entered as text separated with a carriage return.

Feel free to send a file or attach a sample here so we can have a closer look if you can.

Hi Hugo,

I asked the owner of the company if I could send some of our files and I thought I was going to have to put him on a respirator. He gets all nervis and jerky like a Christmas turkey anytime someone talks about sending something away, or letting something out of the business. He was one of the last hold outs to use the telephone. Great guy, but not too Internet savvy to say the least.

Anyway, I'm sorry! It seems I've wasted your time for nothing. Time means a great deal to me and I'm sure it does to you as well. I'm really, really sorry that I wasted yours!

Carl

Attach clones of the files, then none of your data leaves the company, and we can still see your structure.

A Forum is where you get help and where some exchanged take place. It's not a Big Bazar where we 'stole' ideas and datas.

You won't waste my time. I'm OK for some clarifications, but I'm more afraid of invading that thread and your brain with MY ideas, rather than answering YOUR needs. May be someone else would point you in the right direction, and I'm 100% sure someone already done this JOB.

I'm still a bit confused why noone (except JT ) jumped on to this thread, as it IS an interresting topic. When threads become quickly longer, I realised that members tend to go to new topic rather than reading from the beginning.

So here's an idea.

Start a new thread. Chut...Noone knows it would be a duplicate post, and you'll get other answers. And I'll add mines.

If not, my mail adress is always available.

Hi guys,

The clone idea is a good one. How do I attached a FileMaker document to the forum?

Carl

Zip it ...

Yes, it was a good idea as I just received them.

You didn't explained that each period was assigned a task

Oil change : 3

Tire Rotation : 6

Fluids Check : 12

You....

Sure you'll get plenty of answer right now !!

Yes each maintenance schedule does have a task. No one out of the industry would understand the actual names we use so I used Automobile type tasks in the example. I'm sorry, I didn't know this was important.

Do you know how to post the file here on the forum?

Thanks Ugo,

Carl

First thing is to put the files in a folder (if you haven't already do so) Zip or stuff, perfer the Zipped format. and then type a note like here are my files. Right be low the this reply box are two boxes. One says Add this thread to my favorites. and the other says [color:"red"] I want to preview my post and/or attach a file Click the second one and hit continue.

At this point, you will see a preview of your message and right below that is a Browse Button Hit that and it will allow you to find your Zipped file. Highlight it and hit Open and it will attach it to your email. Then just hit the Continue button.

HTH

Lee

Here are my files. I hope this helps.

Carl

Service Finder.zip

Carl,

Sorry if this is a bit long, but going backward to some work I've done, I went though the same process about 4 months ago for a project which unfortunately never ended. I may have kept a postable draft somewhere and will look if it can be posted here.

Here is the logic I opted. May be someone would comment it. As it never ended, I cannot verify it.

1. Create a Calendar File which will hold, except the CalendarDate field

- a field t_closedCat (text) where you will input if necessary the reason of " closure " (Holiday, Week-End).

Important. Week-ends included in an Holiday period will be marked as Holiday.

- a field t_holidayType (text) where you

Backwards, the c_DateToCheck is working as such....

Forgot though to mention that a script would need to be launched when the purchase is entered, in order to set all your 8 service tasks next dates...

It would just be a combination of 8 sub-scripts IMO.

Hi Hugo,

I wish I could say that I follow what you posted, but I don't. That was a concern from the beginning, that I would get the information and then not know how to implement it. Is there a way to do this a piece at a time, maybe starting with finding the 8 maintenance fields? Maybe that will help me to get things going and understand it better.

Carl

Hi

True you didn't start with an easy one.

Note that I'm not 100% sure that this whole thing would absolutely work, but I thought relationships could solve the problem here.

As you can establish a relationship on dates (or textToDate), creating a Calendar File (each record in that file being obviously a Date) was IMO a good thing.

There was some work to be done (calculations) though in that file, in order to be used afterwards with your Service Planning.

I started by marking those dates which would be closed days where no service could be done (either Week-End and Holidays).

A Boolean calculation (0 or 1) based on the 't_closedCat' field, was added, as I find it easier to work with.

Then I grouped certain dates together involving a t_holidayType field (XMas, ...)

The beauty of relationships is that it alows to retrieve the Last value of a Group. And a relationship can be made within the same file (this is what we'd call a SelfJoin).

Thus if all dates from Dec 24th to Jan 4th are flagged with HolidayType = "XMas", we can group all these dates together using a SelfJoin on HolidayType (So relating the HolidayType field to the HolidayType field within the Calendar file).

Then, the Max function used here will return the last date in that group, so Max(SelfJoin::CalendarDate)----> Jan 4th.

Adding a day with Max(SelfJoin::CalendarDate)+1 will lead you to the Next opened day.

The process was slightly different for Week-ends. The calculation included in the Case Statement

CalendarDate + MOD(9- DaysOfWeek(CalendarDate)+7, 7)

will return the next Monday given any Calendar Date.

As explained, all Week-ends falling inside a period of Holidays would be flagged as "holidays", in order to prevent an appointment to be made on a Monday in that period of "closure".

Note that in the project I was, the customer could choose its day, and this calculation was therefore changed according to the preferences specified by the customer.

Basically now, imagine you customer bought a vehicle on sept 24th and the OilService is 3 Month. The c_checkDate would return an appointment on Dec24th

If now you link MainFile:c_checkOilDate (Dec24th) to CalendarFile:CalendarDate (Dec24th), you'd fix the FinalOilDate to CalendarFile: c_NextOpening (Jan5th).

I hope it helps.

I finally found the files. If someone need them, I'd try to drop a sample using this technique.

Hugo,

By all means send the file you

Whoah...

Want my PayPal account

All this make obviously sense, but when you want to keep track of "Actions" or "Events", and when you're dealing with "Many to Many" relationships, which is your case (Many customers, many machines, many services,...manymanymany), you need more than the three files you have.

That was my first point, and still is. You'd need to develop the db a bit more relationally to make it work better.

What you're talking about is a range relationship and can be done. I wonder why you wouldn't split them by weeks then, which would be even easier.

Hope I'd have some time for the file this evening. There is small work to do before I can post it but I will...

Great Hugo,

I was afraid I was saying something really stupid!

And if you feel that "splitting them by the week is even easier", that would work great. Man, we may get this to work after all, what do you think?

Carl

Hugo,

Why do you think no one has ventured an opinion on all of the discussion we've had on this post? It just seems like something many on the forum would have a comment. I've not been around the forum that long but it just seems peculiar.

Thank you, thank you, thank you for answering the post and hanging in with me. You put a lot of work in here and I could not thank you enough.

Carl

Carl

I really don't know....

Some may think "Let Ugo go along with his SelfJoins and Relationships and we'll punish him with our brand new whip"

I needed a break since I was working on the files I promised. I'll post the sample which I adapted to your particular needs. I had some spare time, you lucky guy.

You'd have to wait till tomorrow for the Punishment Day

They are like more "friendly" before week-ends, except one.

<crack!> Who might that one be, Ugo?

So....here is the draft.

Sorry for the delay. I didn't thought this Friday would have been so hard, and I had some other job to do. I surely won't have time to develop it more than it is before a while. Next week will surely be harder at the office, but I'm ready to discuss it with you if you need.

The calendar part of it is whay would need IMO some more settings. Some holidays have been entered in the demo for 2003, but you may add some more. Just enter list mode and enter the holiday name (or whatever you need -i.e. "Inventory"- in the "Holiday Category" field.

Week ends are autocalculated, even those which falls within a Holiday.

It does what you expected I think.

- schedule all services at the moment of the purchase

- group by week and year each "task" so that you can easily get a list of "todos" using a range relationship

- create appointments for each services independantly

- once one service is done, automatically schedule next task

- allow to view all models and their contracts

- allow to affiliate multiple cars to one customer with only one customer id

- check for holidays, week-ends before any appointment validation. You may select the day through a related value list that gets those opened days within a given week.

- move from records to records.

Now 2 main comments.

1. As I was adapting this file to your needs, I realized you didn't sepcified if each manufacturer had its own "services types". If so, you should (and it would be better anyway) drop these services in a Service file, so that if you need to add one, you'd have more flexibility.

2. This file is based over a lot of relationship and cross-records calculation. In the real file, you should avoid this, using scripts.

At opening, you'd be presented with a list of those contracts which one of the 9 "date fields" falls within the next 5 weeks. You may also filter this list. For the demo purpose, 11 records already have been created in the "Contract File". You would need to schedule record 11 with the button.

Try it out and tell if it works.

Now, anyother may give comments on this thread which has really surprisingly never been so quiet....

CARMANAGEMENT.zip

Hi Hugo,

You're a man of your word. Saturday it is! I'll get into it tonight, after the company leaves, I'm not suppose to be on the computer so don't tell my wife.

Hugo you've really stayed with me on this. How many times and in how many different ways can I say thank you.

Carl

Hi Hugo,

I've been getting use to your files today and I'm having some trouble. I entered my name and address info, entered a new car and service dates the same as you used and entered the same purchase date you used. But I cannot get my name to come up in the "List of appointments to be scheduled"!

What am I doing wrong?

Carl

His name is Ugo not Hugo

Hi Lee and thank's for letting me know. I looked through the post and I used Ugo at the beginning and why I changed it to Hugo and don't know. But I'm glad you brought it to my attention. Sorry Ugo!

BTW, did you download Ugo's solution and did you try to make the schedule work and did it work for you?

Thank's again Lee,

Carl

Hi,

Sorry for that. While setting the Customer Ids, I had to go back as I hadn't set the Ids correctly. I thought it was fixed, while it's not. The next ID in the File will be ID0010, but Mr VELOCE already has it. So if you don't mind, either delete Mr VELOCE, or create a new one and your name will appear.

Good Morning Ugo,

I deleted Mr. Veloce, but my name still did not come up. I double checked to make sure the purchase date was the same as you used on one of the other vehicles (MERCEDES 350 SL 2002) shown on the "List of appointments to be scheduled" and it was.

What do you think?

Carl

I think I may have to repost a new one...

What ID have been attributed for your new Customer ? Check that it isn't a duplicate. Sorry for that, it sticks but the way I set the Ids was quite a fantasy.

Where doesn't he come up. Through the Lookup in the Main Layout of the Contracts File, or in the Portals for either the Model, the Contract, the scheduler ?

Try the following in the meanwhile. Delete all records from the Scheduler the Contracts file, and the Customer File. Import from your sample file all customers you had created. Checks that the Ids are OK.

Then, goes in the Contract File and affiliate the Customers and the Cars, giving a Purchase Date that would be from March 2003 to April 2003 (so that you'd got something to see within the next 5 weeks, as the minimum interval was set to 6 months in nearly all models specs).

Then, hit the "Launch Scheduler" button for each contract.

I'll check back your answer prior to investigate in the File itself. In my opinion, it's all ID Problem.

Hi Ugo,

I still could not get it to work. I did a total delete of the three files you noted above and re-entered my data including a purchase date of March 22, 2003 and ran the "Launch Scheduler" but the "List of appointments to be scheduled" is still empty.

Sorry to keep bothering you,

Carl

So I tried again and it works fine. Your mail box does'nt

I think you were waiting for DATES to be scheduled just when the Purchase was submitted, while I opted for a schedule of WEEKS.

This gives the user the ability to planify ulteriorly any services with a Customer.

That's why, at opening, the file drives you to a portal with thos services to be done within a range of 5 weeks. Now, you'd be able to plan a Date of appointment, with the customer using the "Appointment" button.

You'd get a list of those opened days, excluding week-ends and Holiday.

If what you want is to instantly schedule a day at submission of a purchase, then it would be even more easy. But there are chances that planning a date 18 months in advance will lead to a lot of cancellation, while scheduling it 4-5 weeks before is rather the way we usually go.

Hi Ugo,

I have tried every way I know to get it to work, but to no avail. If it is working on your end, then there is some logic I've overlooked that is keeping it from working for me.

""RE: But there are chances that planning a date 18 months in advance will lead to a lot of cancellation, while scheduling it 4-5 weeks before is rather the way we usually go.""

I do not want to make a schedule 18 months out. What I want to do is on the 18th month from the date the engine was purchased, send the customer a reminder notice the engine is due for service and again on the 24th month etc. The customer would then call in to schedule an appointment.

But as I said, this database will not be used to schedule appointments. It is used as a "reminder" only! So, if an engine is purchased January 18, 2003 and the first reminder is set for 6 months. A reminder would come up July 18, 2003 to contact that customer.

I tried my eMail earlier and it worked fine. I'm sorry you had trouble.

I worked for four hours today trying to figure out what I'm doing wrong with your solution. I mean, if it works for you it should work for me right! But I never got it to work! Computing can be frustrating at times.

Thanks for all your help Ugo,

Carl

I just found out yesterday what some of you have most likely already figured out. It was in fact the ID's that kept the solution from working. Once I went in and made all the ID numbers the same in each file, everything worked great.

Now I need to figure out how to work it into my solution and the world will be a beautiful thing.

Ugo you're the best, thanks for everything!

Carl

While I have not had the luxury to read this entire thread I understand that your end goal

is to show you a list of customers you need to conatct at some future date.

When ever I deal with customers and it involves dates I strongly recommend a plugin or you can use this solution HERE This will also allow you to prepend and postpend data to the keys so when you want to find a customers via a range you can.

Instead of marking literal 3/6 or 12 months you should specify an actual date. Then every day when you run a script you will find the necessary records based upon start date and end date and any other data that you append.

This also is much faster than performing finds, especially in a large file. Even if you enter number for "months to contact in" you can easily calculate the actual date, and extend it round the day up or down to the nearest work day.

Hi Stephen,

I appreciate your reply. I went to the site you showed on the post "HERE" and downloaded the pdf file on Smart Ranges. A little heddy reading for a novice but I get the idea. You made mention of a sample file for Smart Ranges but I could not find where to go for the download. Can you help me with this?

Thanks Stephen,

Carl

At the bottom of this Article,

http://www.onegasoft.com/tools/smartranges/index.shtml

It says:

It's a long article, so if you'd like to have it printed get the PDF version with FileMaker samples (206K).

HTH

Lee

Hi Lee,

I had downloaded the PDF file. What I'm after is the "Sample file". You will note when you click on Stephen's HERE link, at the bottom of that post is another link that says "Here is the instructins and file samples". I took that to mean that there is a sample files that goes along the the PDF file.

That's what I'd like to have if there is one, is the sample file.

Carl

Hi Carl,

This is crazy.

I downloaded these files a long time ago (the site was different then), but by the way it is written, it sure sounded like there would be both.

There is a contact link there, why not write them and see what they have to say.

HTH

Lee