I have found a couple of similar threads about this but none the solutions quite fit my requirements.

I have a 'Products' table and in that table I have a field that holds the the quantity sold of a particular product. I also have a 'current product' (with Yes/No drop down selection) and a Category field (with pre-defined categories , again with a drop down menu) . I would like a field that holds the sales 'ranking' based on the fact is A) a current product and B. the product category.

The threads that I have found suggest a

self-relationship but I can't seem to get this to work.

Any help would be much appreciated.

I have attached a basic version of the file.

Edited August 4, 200916 yr by Guest

The simple way to do this is by producing a report: find the current products, and sort them by amount sold.

If you want to show this "live" at all times, you will indeed need a self-join to count products that sold more. Do you want an overall rank, or within the category only?

Note:

It's best to use a Number field for Boolean values (1 for True, 0 or empty for False).

Edited August 4, 200916 yr by Guest

I need the rank number in a field not sorted on a layout and yes, I would like it ranked by category.

Try the attached.

Note that switching a product's "current" status may require a refresh.

Productexample.fp7.zip

thank you very much for that - I can see now that I was not far off which is a good sign. I didn't have a product id field.

What's the easiest way to refresh should a product become discontinued?

A button set to Refresh Window[]. Or attach a script trigger to the Current field. Or make the Current field itself a button that toggles the status and refreshes the window.

I didn't have a product id field.

The ID field is not essential here - I just wanted to count a field that couldn't be empty.

Edited August 4, 200916 yr by Guest

thanks for your help, I shall now implement this into my database

Right... well i've replicated the solution in my database and it shows a ? in the ranking field.

I've studied the fields (and where neccesary , made sure they are set to Number rather than Text)

and no luck . I have also made sure that the ranking field is set to general format so it won't make the result look strange.

any ideas?

Yes, I had opened the field area right up but no joy i'm afraid.

Here's a method which adjusts to the found set, some refreshing issues still exists however:

--sd

ProductexampleSd.zip

right, I've found the problem . in Comments example , the Number Sold field is simply a number field that is indexed. in my database is a unstored calc field that is the Sum of (sales line items: Item Quantity).

therefore would it be best to have the sales ranking relative to 'sales line items' rather than the'products' table?

thanks

in Comments example , the Number Sold field is simply a number field that is indexed.

Yes - because that's how it is in your original example file.

Maybe something like this can work for you - I haven't tested it much.

Note that the self-join must be sorted.

Productexample.fp7.zip

Sorry, I should have mentioned that the quantity sold was coming from a different table.

I've just looked at the example file - it looks like that will work great.

The only thing i noticed is a refresh button doesn't update the ranking when the sales quantity increases so I will look for an alternative way of doing this.

thanks again

Edited August 6, 200916 yr by Guest

Note that the self-join must be sorted.

...Ah yes List( does is just as well, if not better!

--sd

i noticed is a refresh button doesn't update the ranking when the sales quantity increases

Try making the button do Refresh Window [Flush cached join results].

Comment ive got a curiosity question here..

In your example file cTrue is an unstored calc set = True (no quotations) and calc result is number..

Why not set calc result to text and set = "True"...

Whats the difference?

True is a reserved word in Filemaker, and it evaluates to 1.

See also:

http://fmforums.com/forum/showpost.php?post/228583/