Calculation on the same field in a Table

[jmsSA]

Hi All,

This one has got me stumped -

I am a noob, in the process of learing on the job - and as always I have set my goals qite high...

I am slowly developing a solution to run my catering company.... and up to now I have read, tried out, backed up and (not to pat myself too hard on the back) have a system that can track all my purchases, return the curent price, the average price over 3 and 6 months - distinguish products that can be used only in recipes, and those that can be sold as dishes in their own right and used in recipes - I can create recipes that take into account the above and the average waste factor (dirty potatoes to peeled) and adjust the price accordingly - and return a cost per portion. I can create quotes refferencing both products and recipes and add the cost of Hired items and staff.

I am now stuck on a problem (which may be a "wood for trees" scenario - for which I appologise)

I have generated a stock take table that is related to my Products and purchases and what I need to do is - at each stock take, I need to create a "Stock variance calculation" which very simply is "Last stock take value" (minus) "new stock take value" this can then be added to the "total purchased" for a period to give me the "Total stock" for that period - I have a summary feild that gives me the total purchased between 2 dates. I have tried everything I can find/ Think of to isolate the last 2 stock take values, but nothing is working.....

I know the above is a little disjointed, but perhaps there is someone out there who can give me some insight....

Ideally what I would like to acheive is the following

MAIN STOCK TAKE TABLE

id

date

comment

stock value - (sum of stock count * current stock price)

housekeeping

STOCK TAKE DETAILS TABLE

id

fk_Main Stock take id

fk_Product - (Links to products and therefore prices and purchases)

Stock count

??Last stock count

Stock variance (??last stock count-Stock count)

Housekeeping

Through the relationships I have, I can relate this back.......

[comment]

If i understand your question correctly, you need a relationship between the StockTakeDetails table and itself, defined as:

StockTakeDetails::ProductID = StockTakeDetails 2::ProductID

AND

StockTakeDetails::StockTakeDetailID > StockTakeDetails 2::StockTakeDetailID

Sort the related records from StockTakeDetails 2 by StockTakeDetailID, descending. This would make the most recent count of the same product the first related record in StockTakeDetails 2.

[jmsSA]

Thanks Comment - I had thought of that, but I can't seem to get it to work - I can do the calc on the 2 instances of the field returned by the self join filter, but can not for the life of me get it to return the correct instances - I can get (by using different sort criteria on either side of the filter) the first record and the most recent, the previous 2 records and a couple of other combos....... but NOT the latest record and the one prior to that.

What I had thought of was to create a field "CurrentStockTakeNumber" that counts the records in the Stock Take Main table (ie the number of stocktakes taken) and then calculating a "PreviousStockTakeNumber" (that is CSTN-1) - Then I clould creat a filter like this

StockTakeDetails::ProductID = StockTakeDetails 2::ProductID

AND

StockTakeDetails::PreviousStockTakeNumber = StockTakeDetails 2::CurrentStockTakeNumber

This would then tie up this stock take with the previous one......the problem is that the calcs to generate the current and previous stock take numbers can't be stored so don't work in the relationship......

[comment]

You didn't mention "the one prior to that" earlier. In any case, if the related records are sorted properly, this too should be possible by using GetNthRecord() function. I am not sure why you can't get this to work.

[jmsSA]

I'll persevere and get it right! I haven't worked on the development side for about 3 months now, so the brain is rusty. The reaso I want a separate table for stock takes is that I want to be able to search their history and or search over time periods that span various stock takes - say the whole year.. so need the info stored - I did toy with the idea of moving the stock take info to the main products table and hav 2 fields for current and previous and run a script to move everything over from current to previous each time I enter a stock take but that will loose all historical data.

One question on GetNthRecord - what is the syntax for returning the last(most recent) record and the last-1 record? if thats an easy one//

Thanks for your help so far

[comment]

One question on GetNthRecord - what is the syntax for returning the last(most recent) record and the last-1 record? if thats an easy one//

GetNthRecord ( Related::Field ; Count ( Related::Matchfield ) )

will return data from the last record of the related set. "Last" here means last in the sort order of the relationship - which is not necessarily the most recent one.

See also the Last() function.

[jmsSA]

SORTED!!! thanks for your help "Comment"

Just for the benefit of those reading this and it ending without the solution being described (and in case any expert out there reads this and goes....hey THERE's and problem) - this is how I solved it

The solution I found was the folllowing

To the STOCKTAKE table, I added a calculation feild aSTOCKTAKE_StockTakeNumber - an auto enter calculated number

Year ( STOCKTAKE_StockTakeDate )&Month ( STOCKTAKE_StockTakeDate ) with the result being a number

and to the STOCKTAKE_DETAILS table I added 2 feilds

1: aSTD_CurrentStockTakeNumber - Auto enter = STOCK_TAKE(PurhasingTO)::aSTOCKTAKE_StockTakeNumber

2: cSTD_PreviousStockTakeNumber - Calculation aSTD_CurrentStockTakeNumber-1

I then created a self join TO of the STOCKTAKE_DETAILS table

with fk_PRODMAIN_id = fk_PRODMAIN_id - to relate different stock takes of the same product

and cSTD_PreviousStockTakeNumber = aSTD_CurrentStockTakeNumber to relate the current stock take to the previous one

In My PURCHASE_DETAIL Table I have also created an auto enter number for each purchase that is a number made up of the year and month portion of the date on which the purchase was made. This now relates purchases made in any "STOCKTAKE Period" to their stock take

Now I seem to be able to calculate the difference between the "Starting stock" and the "end stock" for a period, AND the amount of any product bought in that period....and therfore the stock use for that product during the period or part thereof.

I realise that the integrity of this requires 1 stock take per month (2 stock takes in 1 month will have the same "year+Month" number) - but my solution is to protect the stock take date to only alow one instance per month.

The advantage is that at any time in the month, I can create a stock take for an item and see a real time useage for that product.

Thanks for the help