Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
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 880

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

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,642 Super User 2024 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 880 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... 291,642 Super User 2024 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 880 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... 291,642 Super User 2024 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... 291,642 Super User 2024 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

Quick Links

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,642 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,371 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans