Sorting by calculated field

[enquirerfm]

Hard one to explain.

I have 2 DBs - File A and B. One of which is a price file (B)and the other which contains amongst other fields a couple of calculation fields one of which uses a price found in the price file.

In DB File A I have certain fields which relate to a product.

e.g. Product: [field] XYZ

Rated by me:[field] 80 {outof 100}

Rated by my partner:[field] 82 {outof 100}

av score: [field] 81.5 {summary field}

However, since some of the scores don't have such a nice easy number, I have another field into which av score is inserted which rounds them up or down to the nearest .5. To do this I use the formula GetSummary(summary field,break field)when producing one of the reports.

I use the av. score to calculate a figure based on its cost (from the price file) which is referenced based on matching certain criteria. I then calculate this ratio and express it as a 3 digit decimal e.g. Say the price from the price file ( is $10.00, the quotient is 8.1 (81.5/10). What I want to do is produce a report which sorts this data according to this new ratio so I can see which products have the best quality/price quotient and nothing seems to work.

I have a report which will sort by product name, av. score even but I can't do it for the quotient presumably because it is a transient number which is only calculated on the basis of the script which produces the other reports and cannot be stored.

Generally, is there a way to use transient data for sorting info and what do I do in this circumstance? I have real difficulty understanding this formulation. Many thx. Hope this is clear.

[jamesducker]

I don't see why you are using summary fields here.

Sounds to me like the "averageScore" should be a calculation field, along the lines of:

round ( ( yourRating + yourPartnersRating ) / 2 ; 1 )

Then the "quotient" field should be another calc field:

averageScore/fileB::price

Then you can sort the data by the "quotient" field, just like you can sort by "product name" and everything else. Sorting by the quotient field will be slower though because the calculation will be 'unstored' since its data is being built from two different tables.

Hope that helps. Let us know if you get any more problems.

James

[enquirerfm]

Each product may have 1-6 different scores attached to it hence the need to use summary because, if I'm right, it automatically calculates the average from whatever number of scores are available. Otherwise, I'd have to set up something more complicated which I don't know how to do.

[comment]

Why don't you define a calculation field in the Products table =

Average ( Scores::Score ) / Price

then sort the products by this field?

[enquirerfm]

Doesn't work.

[comment]

Well, it works for me. If you want help, provide more information than "Doesn't work."

[enquirerfm]

Why don't you define a calculation field in the Products table =

Average ( Scores::Score ) / Price

then sort the products by this field?

Well, I think it would have to be:

Average (Scores) / Price file::price

This gives a calculation which isn't correct because it doesn't agree with the output from the summary field. Why would that be? The summary field and the reports I have produced thus far produce single values for the products in question. This route seems to produce multiple values for each product e.g. if there are 4 separate scores for the product then it produces 4 quotient figures - because each average is different? One can, however, SORT on the field - you are right. So that is a step in the right direction. Or, maybe I need to produce a mid-way calculation?

Edited June 2, 2010 by Guest

[comment]

See that's what comes from using A and B for names. Earlier you said:

Each product may have 1-6 different scores attached to it

That would make a table of Products and a table of Scores. The prices, I believe, are in the Products table and the goal is to sort the records in the Products table by their rank (calculated as the average score divided by the price).

The suggested calculation needs to be in the Products table, too.

[enquirerfm]

Thanks for all of your help. I have created a calculation field in the other file and this seems to work because I can now sort on this field.