Edit Relationship/ Sort Problem

[Monoux]

Hi,

I'm having a problem with getting a correct sort via two tables using the sort functionality in the edit relationship window.

I'm working with Filemaker Pro Advanced V8.0.3 on an PC running XP home.

I have three tables A, B and C. I need information visable in table A from table C. Table A has a relationship to table B and B has a relationship to table C, so displaying a field from table C in a table A layout is not a problem. Where I have a problem is with the sort between table B and C.

Table A contains telephone calls made by customers. Each call has a Customer ID against it. This ID is the relationship field that links table A to table B. Table B contains all the calling plans the customer has ever had. So that I get the current plan related to Table A the relationship between tables A and B is sorted in descending order using the plan activation date field in table B. All this works wonderfully.

In table B I have a field called Calling Plan which is linked to table C using this field. Table C holds all the calling plans I offer with the prices for different call types. The relationship between table B and C is also sorted in the same way as between table A and B, that to say descending order using the plan activation date field in table B.

The problem I have is that if a customer has more that one record in Table B (All the customers calling plans) the relationship between table B and C does not appear to work. The information that is being displayed in the table A layout from table C field is incorrect. The relationship between B and C works, but the sort does not function. The system is using an old Calling Plan of a customer's in table B to make the relationship instead of the most recent Calling Plan as specified in the Edit Relationship sort window.

Any help would be much appreciated

Regard

Monoux

[Søren Dyhr]

Shouldn't this be structured as a many 2 many structure, with table A as the join table??

Customer -< Calls >-Callingplans

--sd

[Monoux]

Thanks for the note. I've simplified my explination for ease of undersatnding. The structue I have is

Customers - Calls - Customer Plans - Calling Plans

I'd like to keep the Customers table as lean as possible so when they change their plans a script creates a new record in the Customers Plans table and records CustomerID, Date of change, Active, Calling Plan Name. This way I have the one table with all the Customer Plans in one place, which gives me a history I can show to users via a Customers layout.

I can't see why the sort between Table B and C doesn't work as I think it should. Maybe it's disregarded and only the sort between tablers A and B are taken into consideration?

Cheers

[comment]

This is a known problem. A simple solution, in your case, could be using a single-row portal from Calls to Customer Plans. I believe fields from Calling Plans, when placed in the same portal, should show the correct data.

As an aside: I am surprised that your Calls to Customer Plans relationship is not based on the date of the call. If a customer changes plans, how do you track under which plan a specific call was made?

[Monoux]

Many thanks for the information.

I'll get to work with the portal idea straight away. If I get any problems I'll post again.

As for the way we price it's because I receive a monthly data file with all the calls from the Telcom and I only allow the customers to change plans at the start of every month. It's done like this initially because the data was monthly and my Filemaker skills were limited, and now because letting people change plans mid month would complicate the database considerable for a small customer benefit.

Many thanks again

[Monoux]

Hi Again,

I've just thought I can't use a portal unless it works with a script.

The way the system prices is that a script runs and prices every call one by one. He is an example of a calculation I use in the script to price a National Call

Round(

If(

GetAsNumber(Calls::CallDuration)<60;

CallingPlansCustomers::NationalConnFee+CallingPlansCustomers::NationalCallCosts;

CallingPlansCustomers::NationalConnFee+CallingPlansCustomers::NationalCallCosts+Ceiling ( Calls::SecondsPast1stMinute/CallingPlansCustomers::SecondsPerUnit )*(CallingPlansCustomers::NationalCallCosts / CallingPlansCustomers::ChargeableUnits)

);

6)

The first minute is always charged and the rate per minute is taken from CallingPlansCustomers. CallingPlansCustomers is where the individual calling plans are stored with all the rates for different call types. The script runs from the Calls table.

Any ideas?

Thanks

[comment]

Oh. I thought this was for displaying information on a layout. For a calculation, I think I would lookup the correct Plan from Customer Plans into the Call record, then use that to lookup the rate from another TO of Calling Plans. This can be done during the import, so no additional script should be required.

[Monoux]

Yes that's cracked it. All is working as I need it and without the need to do too many other changes.

Many thanks

PS Any idea when the bug will get fixed?

[comment]

I am not sure this qualifies as a bug; it's more like a missing feature.