Hi all,

I have a table Customers and a table Products. They are joined over a table CustomersXProducts. A portal on the Customers layout from the CustomersXProducts table shows the items a customer buys.

Each record in CustXProd. has a field called One, where a 1 is the automatically created value. A field Total is a summary of field One.

I show that Total in table Products over the relationship ProductID=ProductID, so that in the layout Products I can see how many of each product have been sold.

(See attached)

(Something tells me I should be able to do a Count of Related Records or something, but that eludes me for the moment, hence the method used. )

What I want to do next is create a product ranking. I want to give the product a ranking according to which one sells most, then second-most, third-most etc.

The most sold product gets a 10, the second-most a 9, third-most an 8 etc. I would like to do this in a calc or over a relationship, as any changes in the order of 'what was sold most' should be reflected as soon as a new product is added to the CustomerXProducts table. I want to use those thusly created numbers in another calculation.

Can someone point me in the right direction?

TIA :

ProductRanking.zip

You have no more than 10 products? If it is possible attach your file another. It is wrong in for extracting.

I can not see your file. But i think that, here will help Max function.

I saw 'Max' but can't think of a way to implement it.

The numbers were just an example, by the way. In reality they will be .50 .55 .60 .65 etc. The more an item is sold, the less it 'weighs' in terms of commission.

I can d/l and open the ZIP ok. ??

I've done away with my 'One' field now, by creating a calculation field 'Count' in Products: Count (CustomersXProducts::Prod_ID).

I suppose I could sort that field now and script a loop that distributes the values by simply taking the first record, set the field, go to the next record, set the field, etc, but that means I have to use a script, which needs active involvement - plugins are not an option at this moment.

So I'm still at the same problem: what does a calc look like that says

Case (

this is the highest number;.50;

this is the second highest number;.55;

this is the third highest number;.60;

1)

ProductRanking.zip

With Get (RecordNumber) as an unstored calc and sorting the table by Count I sort of solved it: an extra field for the ranking is now:

Case(

Get(RecordNumber)=1;.50;

Get(RecordNumber)=2;.55;

Get(RecordNumber)=3;.60;

Get(RecordNumber)=4;.65;

etc.

But I still need to sort the table before I can do this. Still hoping for a good calc without script ;)

I do not think this can be accomplished without a script. You either need to sort the records or make adding products to customers transactional, and update the number of purchased products when each is sold. If you choose the update route, then you could use a value list based on a concatenated field of the number of purchased items and the ProductID, and a calculation to determine where each product ranks in the value list.

In the meantime, you can make your current calculation more efficent by using

Choose( Get(RecordNumber) - 1; 50; 55; 60; 65 ... )

Thanks for the input, Queue.

Oh well, maybe a next version (isn't 8 around the corner?) will include something that allows me to do a calculation on a specific record in relation to (the value in) another record.

Choose, by the way, won't work: what I forgot to mention is that this list is finite. I'm working my way up from .50 to 1.0 and all records after that get 1.0 as well.

(Or can I tell Choose to use one specific value for all records after a certain value?)

Choose, by the way, won't work: what I forgot to mention is that this list is finite. I'm working my way up from .50 to 1.0 and all records after that get 1.0 as well.

What I have to say will not help towards the basic problem but "may" make the calculation simpler to type. Basically you are calculating (45 + g*5)/100 (g = Get(RecordNumber)) except it is not allowed to be bigger than 1.

I think the following will do the job

Let ([roof = "1" ;level=Evaluate("0.01*(45 + " & Get(RecordNumber) & "*5)")]; Min(roof;level ))