web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Supply chain | Supply Chain Management, Commerce / Duplicated PoS sales f...
Supply chain | Supply Chain Management, Commerce
Suggested answer

Duplicated PoS sales for customer orders because because of order modification

(0) ShareShare
ReportReport
Posted on by Momochi 1,013

Hello community,

I'm developing an SSRS report  for retrieving sales transactions (Sales - Customer order) and I encountered an issue with modified orders resulting in a duplicated lines.

I'm retrieving the data from (RetailTransactionTable) and (RetailTransactionSalesLines).

For customer order transactions, the customer order net amount is duplicated and that is because changes done to the order in the PoS (for example: Changing the sales man or changing an item).

Currently i'm using a range condition in my query which is:

RetailTransactionTable.paymentAmount != 0

as a temporary solution, but I believe it will not age will...

What can I do or is there a field I can use to distinguish this kind of duplication?

Thanks in advance

I have the same question (0)
  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 298,959 Super User 2025 Season 2 on at
    RE: Duplicated PoS sales for customer orders because because of order modification

    Hi Momochi,

    The range on the amount field in the query is not ideal if there is no index on this field. When the number of transactions will grow, the query will become slower time after time. I'm not sure if it is correct that new records will be created in case of a modification. I have no experience with modified transactions. I have only seen retail statements with POS details processed during a nightly batch.

  • Momochi Profile Picture
    Momochi 1,013 on at
    RE: Duplicated PoS sales for customer orders because because of order modification

    Hi Andre

    I'm sorry for not being clear.

    The query simply selects "RetailTransactionTable" with it's Lines table "RetailTransactionSalesTrans" similar to the "Store Sales" screen in "Retail and Commerce".

    The thing is, anytime an order is modified, a new record will be created in the header table and in the lines table.

    What I was able to use is the "Payment" field in RetailTransactionTable, which will be 0 with the new modified records.

    So I simply added a range in the query to NOT include the records with 0 amount in the payment field.

    But it is not a perfect solution, because for example: if they updated the sales man in the order, the new sales man will be in the new record created after they modfiy the order, and the query will not bring the updated sales man.

    so that's why i'm looking for a better solution. I was not able to find another field to distinguish the order records.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 298,959 Super User 2025 Season 2 on at
    RE: Duplicated PoS sales for customer orders because because of order modification

    Hi Momochi,

    I'm not able to understand the query in this way due to having a lot of tables and where clauses. Anyhow have you verified if you can find "duplicate data" in one of the tables used?

  • Momochi Profile Picture
    Momochi 1,013 on at
    RE: Duplicated PoS sales for customer orders because because of order modification

    Hello @Andre !

    I'm using a AOT Query that translates into:

    UPDATE*: It seems there is an issue with formatting in replies, I couldn't use "Code Formatting" for the following code.

    select * from RetailTransactionTable WHERE RetailTransactionTable.entryStatus  != 1 RetailTransactionTable.type  != "SuspendedTransaction" join * from RetailTransactionSalesTrans WHERE RetailTransactionTable.TransactionId = RetailTransactionSalesTrans.TransactionId && RetailTransactionTable.store = RetailTransactionSalesTrans.store && RetailTransactionTable.Channel = RetailTransactionSalesTrans.Channel && RetailTransactionTable.terminal = RetailTransactionSalesTrans.TerminalId && RetailTransactionSalesTrans.transactionStatus != 1 join * from RetailStoreTable WHERE RetailTransactionTable.store = RetailStoreTable.StoreNumber Join * from InventTable WHERE RetailTransactionSalesTrans.ItemId = InventTable.ItemId join * from EcoResProductCategory WHERE InventTable.Product = EcoResProductCategory.Product join * from EcoResCategory WHERE EcoResProductCategory.Category = EcoResCategory.RecId join * from EcoResCategoryHierarchyRole WHERE EcoResProductCategory.CategoryHierarchy = EcoResCategoryHierarchyRole.CategoryHierarchy && EcoResCategoryHierarchyRole.NamedCategoryHierarchyRole = "Procurement"

    Is this what you wanted to know? if not i'm sorry can you clarify please

    Thanks!

  • Suggested answer
    Xusheng Profile Picture
    Xusheng on at
    RE: Duplicated PoS sales for customer orders because because of order modification

    Hi,

    Maybe you also can try column "RETAILTRANSACTIONTABLE.NUMBEROFPAYMENTLINES". Hope this can help you.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 298,959 Super User 2025 Season 2 on at
    RE: Duplicated PoS sales for customer orders because because of order modification

    Hi Momochi,

    Can you share in detail how you collect the data for this custom report? Have you verified if the tables themselves contains only unique data or has it also duplicates?

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 298,959 Super User 2025 Season 2 on at
    RE: Duplicated PoS sales for customer orders because because of order modification

    Moved to the Dynamics 365 Commerce forum

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Supply chain | Supply Chain Management, Commerce

#1
CA Neeraj Kumar Profile Picture

CA Neeraj Kumar 1,011

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 279 Super User 2025 Season 2

#3
BillurSamdancioglu Profile Picture

BillurSamdancioglu 215 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans