Summarize a Audit Trail

[Blewish]

Hello

I am trying to find a way of summarize the data types in the Stock Transaction table. I just want totals of stock form MO, AI etc. If anyone can help with this it would be much appreciated. I'm very new to this so I'm sure its something that can easily be achieved but I'm completely lost at the moment. Thank you in advance for any help that you can provide.

[brucedenney]

You need to flesh out what you want to do a bit, it is a bit unclear.



Start with a report such as the Product Audit Trail.

You could do this by creating multiple expressions in the detail section e.g. AO_Qty if the Transaction Type is "AO" then the expression value is the qty otherwise it is zero.

(You might actually want to put all the adjustments together to give you a net adjustment rather than have separate in and an out)

Insert a footer for each product code and do a sum of each expression there.

Hide the detail lines and you have a report that lists the movements for each product code.

The problem with this is that if Sage add another new transaction type you will need to alter the report, so best to have an "other" expression to catch the ones you have not defined.

There is a list of all the transaction types here.

http://www.makingithappen.co.uk/CI/stat ... STOCK_TRAN

[Blewish]

Hello,

Thanks for your reply. Sorry if i wasn't clear before i was looking to get a report that could break down where the product has gone and a summary of that so for example in March a product had these qty moved in these transaction types 45 AO, 80 MO and 75 GO. I hope this clarifies things.

Kind Regards,
Daniel

[brucedenney]

What I described should achieve that.

[Blewish]

Hello,

Thank you for your help but I'm really new to this and i am pretty useless. Im still having issues with getting the expression to take could you give an example what it should look like with one of the codes because everything else you said works except for the expression part i cant get it right still. Thank you again for the help.

[brucedenney]

I would add an expression

Code: Select all

STOCK_TRAN.TYPE="GO"?STOCK_TRAN.QUANTITY:0

This would be in the STOCK_CODE Footer with the function set to sum , resetting the sum on each STOCK_CODE Footer.

You would need to add multiple variants for all the different possible stock codes and or "other"

[Blewish]

Hello,

Thank you so much it works great. This has made things so much easier for me now.