HELP I'am having wrong results after calculation!

[Jukkie]

Hello there,

Is there someone who can help me out on the my problem explained below!

DATABASES:

I work with two databases called: Reseller.fp5 & Customers.fp5.

IMPORTANT FIELDS:

Fields in database RESELLER.fp5 are: name reseller, bronze, silver and gold

Fields in database CUSTOMERS.fp5 are: name reseller, total and state reseller

RELATIONSHIP:

I made a relationship between reseller.fp5 and customers.fp5 db between the fields: name reseller.

WHAT I WANT:

What I want is the status of the reseller. Is the reseller a bronze, silver or a Gold reseller.

WHAT I DID:

I made a record in the Resellers.fp5 database and it looks like:

Name reseller: JUKKIE, Bronze: 50, Silver: 51 and Gold: 101

Now I gonna make a record in the database Customers. It looks like:

Name reseller: JUKKIE, Total: 53

The result of the field "state reseller" should be silver, because the number is >= 51 and < 101.

MY CALCULATION:

If(Total <= Reseller::bronze; global_bronze ;

If(Total >= Reseller::silver < Customers::gold ;global_zilver;

If(Total >= Reseller::gold ; global_goud ; "" )))

WHAT AM I DOING WRONG?:

[ESpringer]

I'm baffled about why you want this data structure...

Question: How could you find the status of a *reseller* by using a fact about *one customer*? Why would you generate a field for "state of reseller" in the customer's record? If the same reseller has multiple customers, then (on your approach) this reseller has multiple states: If I am a reseller and one of my customers totals 50 and another totals 101, then I am both bronze and gold?

It seems the logical thing is to have a ResellerGrandTotal as a calc field *in* the reseller record

Sum(Customers::Total)

And then simply have the Reseller State field calculated *in* the reseller's record:

Case(ResellerGrandTotal <= 50; "bronze";

ResellerGrandTotal <= 100;"silver"; "gold")

Then, each customer's record could also display the state of the reseller simply as a related field on the layout, if you need that.

Have I completely misunderstood your project?

[Jukkie]

Hello ESpringer,

I understand why you are confused. But I tried to tell it in a short story. The hole database is having a lot more fields.

Your story is what I'am builing. The Total I'am talking about is SUM of all the customers of the specific reseller. The reason I made a reseller database is that it is possible that a reseller got other quota's than an other reseller.

Because of that I can't use fixed numbers in the calculation. So I do a lookup in de reseller database what numbers should be used for this reseller.

Thanks your calculation It also works with my varible field Margin reseller::silver

My result become:

Case(Total <= Margin reseller::bronze; global_bronze;

Total <= Margin reseller::silver;global_silver; global_gold)

I hope you understand it a little bit more. But it is difficult to explain the hole structure of the databases.

Greetings,

Jukkie

[ESpringer]

I suspected you had your reasons... By the way it seems you only need TWO quota threshold number fields, right? One number represents the line between bronze and silver, while the other represents the line between silver and gold.

[Jukkie]

You where right about that there are just two fields needed to get finaly 3 results.

Thanks a lot for helping me out!!!

Greetings Jukkie