Getting value(s) from related records

[enquirerfm]

I have two tables which relate to each other. I want to get a value (cost comparison) which is an average weighted by quantity (for one month say) for a matching item into one of the tables - tables are matched . I have no idea how to do this - sorry! Can anyone help? let me try and give an example.

File 1 has a list of items e.g. products: laced shoe, trainer, sandal etc; colours: brown, white, fawn etc; sizes: 6,7,8,9,10 etc (these are also the matching fields in the other table) but it also has, for example, a field 'price paid'. Now what I want to do is add in a field 'latest cost' which is an average of the cost data picked up from, say March 2010, in another table which lists the same fields as above (e.g. products, colours, and sizes) but which also contains a different field which is 'supplier prices'.

So, if we select one product in one table e.g. Product: trainer - colour: brown - size: 10 - price paid $5. In my other table I have the same fields in several records which show 'supplier prices' $3 (2 suppliers) ,$3.5 (1 supplier), $4 (8 suppliers)- it will automatically create a field with the value of an average weighted by qty of all the 'supplier price' data from March 2010.

Is this clear at all?

I have already created a portal which enables me to see very well what all the prices are. I can even produce a report which will give me the figure I want. But I need to be able to archive this for future calculations and at the moment I do this manually by simply taking the calculated figure in the report and placing it an empty number field.

[comment]

You can calculate the weighted average of child records in two ways:

1. Define a calculation field cWeightedPrice in the Prices table =

Price * Quantity

then calculate the weighted average price in the Products table as =

Sum ( Prices::cWeightedPrice ) / Sum ( Prices::Quantity )

2. Define a summary field in the Prices table as weighted average, then refer to it directly in the calculation field/s in the Products table (this may work better in more recent versions).

[enquirerfm]

Thanks but the complicating aspect to this is that I don't want to calculate an average of the whole file but just those prices/costs which are relevant to one month e.g. March 2010. So the weighted average calculation has to be limited to one month, say, or an average across three.

[comment]

This limit should be set up in the relationship (i.e. define another relationship that shows only price records for the selected month).

Alternatively, you can find the price records in the selected month and produce the report from the Prices table.

[enquirerfm]

Thanks but not sure how to do this. Remember I already can see the data - reference to the related records through the portal but I don't understand how to actually capture this and post it to a field. The figure in the report only shows when I run the script and is in preview mode, when I go to browse mode it's lost. Many thanks for your help, much appreciated.

[comment]

This part is not clear:

I already can see the data - reference to the related records through the portal

Do you see all the prices of the product - or only the prices for the relevant month?

[enquirerfm]

All the prices - so Oct, Nov, dec etc all individually listed for each product. Yes, not the av. weighted figure for the specific month... sorry I was not clearer.

[comment]

See if this helps:

WeightedAvg.fp7.zip

[enquirerfm]

I am grateful to you - you understood the problem completely and your solution was exactly what I asked for - I worked through it very slowly and made some notes on a sheet of paper - it worked first time. Thank you! This Forum is excellent.

Having calculated the figure for one month by keying in a date in the relevant month (into gDate) is there a simple way of producing automatic monthly results which are then stored for subsequent use so that I could, for example, produce a report which showed the av figures for each month? Is this just a case of multiplying the gDate/cWeightedAverage fields to represent each month - that would seem to work?

[comment]

I am not sure why you need to duplicate the summary data in another table. You can always find the records in the month/s of interest and produce a report (from the Prices table) summarized by product and month (or vice versa).

[enquirerfm]

I'll come back to this... but what can I do where there are no prices for a particular month. How do I then pull in the prices from the previous month?

[comment]

I am not sure I understand what you're asking. Perhaps you should explain in more detail what is the ultimate purpose here.

[enquirerfm]

Thanks for our interest. My initial example was a simplification because it needed to be to understand the initial issue which is now solved. However, as with all things FM, the more one does, the more one wants to do and my ultimate goal is really as follows: my DB is a file of items which has different values in time: the cost at the time of acquisition and a price in the market at a particular moment e.g. March 2010. In the example you sent me therefore, I am able to pull a 'value' based on the price file during a particular month but when there is no data for that month there is no av weighted price to calculate whereas it is effectively whatever might have been calculated from the month before. Thus for any item in any month I need to see some kind of av weighted figure.

However, in order to view the progress of the value of my items I also need to see the evolution over time of the different items at different times e.g. October, November,December 2009 etc, also pulling in values from the previous months where there are none. I currently do this manually because I created reports sorted by month etc but this was always rather arduous.

I need to be able to capture the value of any item for the month of say, March, and store that so I can use it as the basis for any calculation e.g. percentage increase/decrease over the year, monthly increase, comparisons between different products etc etc.

Anyway, I hope this is clearer. Actually, obtaining a real time av weighted value from the price file was a major step forward but I would also like to be able to see on a page somewhere the av. weighted prices for Jan, Feb and March, or even the last quarter or the last year and be able to use these calculations to extract trends.

Hope this is clearer and thanks again. I could upload what I have now but the DB file is large, the number of fields is also large, there are various dependent tables etc etc and it would likely complicate matters. I bow to your experience but perhaps it is easier working through a clean and fairly tight example to obtain the results one wants and then apply it to a real life situation?

Thank you for your help.

[enquirerfm]

I have written a script which sets the parameters from the fields we have already and which then copies the solutions into a field for the relevant month. Seems to work OK> I think my DB is a bit of a clunking workhouse rather than some sleek machine but it's doing the job.