compare one to many and have proper result

[nostra_2000]

Hello I need help

I have to files in exel that I imported into FM8.5

One has info of a call center

File name is "Call Center.xls"

Date "date"

Time "time"

Phone called "nmber"

Phonoe origin "number"

On the other file I have

File name "Media info.xls"

Phone Called "number"

Time start "time"

Time end "Time"

CPS which is basically a number

I need to properly evaluate each call that comes in.. comparate to a number on the other table BUT also it has to be in between the Start Times and End times of each phone number.... So I can get the cost based on when the call came in...

This is the formula that Ive used in Filemaker8.5 as a test:

If (Call Center::Tel Called = Media Info::TEL IN and Call Center::Call Time ≥ Media Info::Start Time and Call Center::Call Time ≤ Media Info::End Time; Media Info::GROSS CPS ; 1)

I get the first cost only... it does not bring me the other costs from the other calls.. it only evaluates the first line...

I think I have to put in a script to go through every record on the first table... but I am really lost.. can someone help please..

I am including the exel files and the filemaker file that I have made...

Super thanks

J.

Call_Center.zip

[Fenton]

I believe that you would use a compound relationship, where the phone = phone AND the start time <= time AND the end time is => time. Then you'd use a Sum ( relationship::CPS ).

But that's about as far as I could get. Because I don't really know what exactly these things are.

[nostra_2000]

Thanks for your help...

ok.. I think I know what you mean in the first part with the compound relationship.. buy how do you do a SUM relationship.. could you give me an example?

Thanks

J.

[nostra_2000]

Fenton I got it super thanks..

Wow so easy.. thanks

J.

[nostra_2000]

Hello...

This is more tricky than the last one...

Included in this post is the file that I am talking about..

My problem now is that once I got the cost per call based from the info view table using a compund relationship... it correctly brings the correspoing cost of a call depending on what time is made..

Now I need to create a calculation that adds the calls of the same cost (but belong to the same telephone corresponding telephone) and divide it by the number of calls found with the same price.

Thanks for your help

J.

Purpose_Money.zip

[Søren Dyhr]

calculation that adds the calls of the same cost

Sorry for getting in so late but other matters needed attention, although I saw you private e-mail yesterday.

Problem here is that it isn't a calc' but a summary, due to the apparent very large number of records - although the required measure exists by putting a aggregate function Sum( in the related table ...will it take for ever to calc'

A pretty straight forward a subsummary report fits the bill better here, but perhaps you could utilize this instead of the timeconsuming layouting process I subsummary report really requires, since your mail indicated some ugency...

http://www.kevinfrank.com/download/kf-fast-summary.zip

--sd

[Fenton]

First, you need to look in FileMaker Help at the Aggregate functions, esp. Sum() and Count(). The documentation implies that they are for Fields or Repeating fields, but they also work across a relationship::field. It's worded that way because they first came out in FileMaker 2 (I believe), before relationships existed. This may help (and is useful to know in any case). It may well be

It may also be that Søren's suggestion to use a Subsummary report would be more appropriated.

Once again, I don't really know what you're trying to do exactly. Remember that we know next to nothing about your business. It does little good to ask us to solve a problem if we don't know exactly what it is.

"calls of the same cost (but belong to the same telephone corresponding telephone)"

makes little sense to me. Why does "of the same cost" matter? Is it the correct way to look at the logical correspondence, and why?

In other words, you've got multiple "calls" in both tables, and I'm at a loss as to where you need the totals, whatever they are.

For example, if the Gross CPS is a "rate per call" (which I don't know, but it's the only darn number) then it would be multiplied times the number of calls for that station-time frame (with no date).

Purpose_Money_fej.fp7.zip

[nostra_2000]

Fenton

You are the man... you really applied Akoms razor theorem...

Thanks... this is extactly what I needed.. My problem now that I saw the solution was that I was trying to get the totals and costs of calls in the call center table instead of the info view table...

The only thing that I change was the formula to get the cost per call based on the number of calls made within a range of time.. which was

GROSS CPS / Count ( Call Center::Call Time )

is basically an average...

Thanks so much...

J.

[Søren Dyhr]

So you're exercising Ockham's Razor even more than Fenton by changing multiplication into division instead

...since: "entia non sunt multiplicanda praeter necessitatem"

http://en.wikipedia.org/wiki/Occam's_Razor

--sd