Microsoft Dynamics AX (Archived)

Retail Transaction and Sales Table

(1) Share

Report

Posted on by Community Member

I am newbie on Dynamics AX, and I need to make a report about discount code in sales order line used by customer.

I found that the discount code can be found in RetailSalesDiscountLine and RetailTransactionDiscountLine table.

But there are no sales order number in RetailSalesDiscountLine. So how can I get the discount code by sales order line?

And are there any relationship between transaction and sales?

Thank you very much~~~~~~~~~

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Verified answer

Chaitanya Golla 17,225 on at

Like (0)

Report
Hi,

Following job/code provides you relationship between salesLIne and retailSalesDiscountLine. I couldn't find table RetailTransactionDiscountLine in my environment, so assumed it to be RetailTransactionDiscountTrans and comment it if not required. I don't have suitable data to test it, let me know if anything is missing.

static void RetailDiscountCode(Args _args) { SalesTable salesTable; SalesLine salesLine; RetailSalesDiscountLine retailSalesLine; RetailPeriodicDiscount retailPeriodicDiscount; RetailTransactionDiscountTrans retailTransactionDiscountTrans; while select salesLine join salesTable where salesTable.SalesId == salesLine.SalesId && salesTable.SalesId == "XYZ" join retailSalesLine where retailSalesLine.InventTransId == SalesLine.InventTransId join retailPeriodicDiscount where retailPeriodicDiscount.OfferId == retailSalesLine.PeriodicDiscountOfferId outer join retailTransactionDiscountTrans // Please comment this if not required where retailTransactionDiscountTrans.PeriodicDiscountOfferId == retailPeriodicDiscount.OfferId // comment if not required { info(strFmt("SalesId: %1, LineNum: %2, ItemId: %3, DiscountCode: %4", salesTable.SalesId, salesLine.LineNum, salesLine.ItemId, retailSalesLine.DiscountCode)); } }

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Thanks Chaitanya~~!

Typo on RetailTransactionDiscountLine , it is RetailTransactionDiscountTrans~~~

So InventTransId is the relation key between RetailSalesDiscountLine, RetailSalesLine and SalesLine table~~

Thank you very much~~!

Was this reply helpful? Yes No
Chaitanya Golla 17,225 on at

Like (0)

Report

Hi,

NP. Can you mark the helpful answers as verified.

Was this reply helpful? Yes No
vglu 15 on at

Like (0)

Report

Hi Dan,

Relation between SalesLine and RetailSalesDiscountLine you can find under relation node in RetailSalesDiscountLine table.

RetailSalesDiscountLine.InventTransId == SalesLine.InventTransId

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Vglu~~

I found that there are some record missing in RetailSalesdiscountLine (e.g. Mix and Match), so I change it to RetailTransactionSalesTrans, RetailsTransactionTable and RetailsTransactionSalesTrans. But there are no sales order line number in this three tables.

So how can I get the SO line number? actually I want to find out discount code by sales order line. Thanks~~~

Was this reply helpful? Yes No
vglu 15 on at

Like (0)

Report

SalesLine table has index for InventTransId field. Index not allow duplicates. This mean, if you have InventTransId - you can find SalesLine with Line number, or in different way, if you have SalesLine - you can find all discounts applied for this line. (you can get a few Discounts for one line)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

For InventTransId field in SalesLine table, how can I get the discount code and offer ID? As I cannot get them in sales table but in RetailsTransaction table, however I cannot linkup them with any field as key. Thanks~~~~

Was this reply helpful? Yes No