Daily Email Reporting

[Former Member]

Dear experts,

Tried to find something like in the forum but I couldn't.

I created a daily email report for directors to advise them on: Today's sales, Month to Date sales, Previous month sales etc,

The screenshot of the report is below (Of course, all the numbers are made up ). This is in the body of an email.

The report works fine in all scenarios, but one. Today's sales does not include any monetary update to an order raised before today.

Can someone please advise how we can include the "today's updates" in Today's sale figures?

Thank you in advance.

Best Regards,

K

[Former Member · Accepted solution]

Hi Kanu

Not sure I am following you 100%. Do you want today to include previous days when the orders have been amended? If so, you need to look at the Update Date and possibly link to the audit tables ADOC, DOC1, etc.

Kind regards

Peter Juby

[Former Member · Accepted solution]

Hi Peter,

You are spot on mate.

How to work with audit tables; any link with ORDR and RDR1?

A simple query to help me get some clue on this will be helpful.

Thanks & Regards

K

[Former Member · Accepted solution]

Hi Kanu

ADOC and ADO1 are central to all marketing documents, so they contain all changes to all marketing documents. For ORDR entries, you will need to look at ObjType = '17'. The next important point is that the current "LIVE" version of a document in the ORDR table will have a LogInstanc number of 0, whilst any changes are recorded as 1 - n with 1 being the oldest change. Therefore the actual LogInstanc number of the current "LIVE" version is MAX(ADOC.LogInstanc) + 1.

The rest of the fields are pretty much identical between ORDR and ADOC, and between RDR1 and ADO1 so mapping is very easy.

So what you are looking for involves the MAX(ADOC.LogInstanc) entries create date or even post date, compared to LogInstance 0 in ORDR's update date. Where the create date is prior to the update date and the update date = today. Then get the sales amount from the prior record, compared to the current value and subtract to get today's portion.

Sample code to show joins and MAX(LogInstanc):

SELECT T0.DocNum, T1.DocTotal

FROM ORDR T0 INNER JOIN ADOC T1

ON T0.DocEntry = T1.DocEntry AND T1.ObjType = '17'

AND T0.LogInstanc = (SELECT MAX(T2.LogInstanc) FROM ADOC T2 WHERE T2.DocEntry = T1.DocEntry)

WHERE T0.UpdateDate = GetDate() AND T0.UpdateDate > T0.CreateDate

You can also base the difference of Create & Update dates straight from the ORDR entry.

Hope this all makes sense.

Kind regards

Peter Juby