Multiple finds from multiple fields help

[wingwalker]

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

[Ugo DI LUCA]

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.

[wingwalker]

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

[Ugo DI LUCA]

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 ?

[wingwalker]

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

[Ugo DI LUCA]

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

[wingwalker]

Hi Hugo,

I don

[wingwalker]

Hi Hugo,

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

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

Thanks again,

Carl

[Ugo DI LUCA]

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 :!!

[wingwalker]

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

[Ugo DI LUCA]

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

[wingwalker]

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

[Ugo DI LUCA]

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.

[wingwalker]

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

[Ugo DI LUCA]

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.

[wingwalker]

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

[-Queue-]

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

[Ugo DI LUCA]

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.

[wingwalker]

Hi guys,

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

Carl

[Ugo DI LUCA]

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 !!

[wingwalker]

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

[Lee Smith]

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

[wingwalker]

Here are my files. I hope this helps.

Carl

Service Finder.zip

[Ugo DI LUCA]

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

[Ugo DI LUCA]

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.

[wingwalker]

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

[Ugo DI LUCA]

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.

[wingwalker]

Hugo,

By all means send the file you

[Ugo DI LUCA]

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...

[wingwalker]

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

[wingwalker]

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

[Ugo DI LUCA]

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.

[-Queue-]

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

[Ugo DI LUCA]

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

[wingwalker]

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