Skip to main content

Notifications

Small and medium business | Business Central, N...
Suggested answer

How can I generate customer prices to a file?

Posted on by 93

I'm trying to find out how I can create an excel file to a customer where all the correct prices on products will be included. I have tried to use the function "Price list" and with filter on the customer but this doesn't work. We have Item. disc group with discount that is added in a price list for all customers and this discount doesn't show in the file that is created.

2022_2D00_11_2D00_16-14_5F00_55_5F00_46_2D00_Items-_2D00_-Dynamics-365-Business-Central.png

I need a solution that creates an excel file that collects sales prices for a selected customer and shows the "best price" for each item.

So in my example below prices must be collected from both Sales price list S00001 and S00002. Also collect "Unit price" from Item Card if the item isn't included in any sales price list. When I use the function "Price list" above the file doesn't inluce prices from Sales price list S00001. That is why I can't use that.

2022_2D00_11_2D00_16-15_5F00_53_5F00_31_2D00_Edit-_2D00_-Sales-Price-List-_2D00_-Dynamics-365-Business-Central.png

2022_2D00_11_2D00_16-15_5F00_54_5F00_46_2D00_Edit-_2D00_-Sales-Price-List-_2D00_-Dynamics-365-Business-Central.png 

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,748 Super User 2024 Season 1 on at
    RE: How can I generate customer prices to a file?

    Hi Maria Eriksson

    If you are not a developer yourself you might have to seek assistance from one that can help you create this report.

    It should be to too complex to have it done.  Are you an end-user?

  • Suggested answer
    DAnny3211 Profile Picture
    DAnny3211 9,272 Super User 2024 Season 1 on at
    RE: How can I generate customer prices to a file?

    Hi

    you need to have your partner develop a customisation to do this

    through a query or a custom page or an xmlport

    DAniele

  • Maria Eriksson Profile Picture
    Maria Eriksson 93 on at
    RE: How can I generate customer prices to a file?

    Hi Inge,

    Where should I create this report in BC? I'm not sure where to create this report that calcutates the accurate prices for customers.

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,748 Super User 2024 Season 1 on at
    RE: How can I generate customer prices to a file?

    With the combination of prices and discounts the best option i think will be a customized report and then you can have an Excel layout for that report that makes it informative for your customers.

    There are different helper functions available in Business Central that can be used to calculate the accurate price for a specific customer, so it should not be too complicated to generate the report you need.

  • TCJ Profile Picture
    TCJ on at
    RE: How can I generate customer prices to a file?

    I think the custom option is what you will need. There are so many ways in BC to modify a price.

    Years ago, I created a form which, when run, created a sales line for the customer in question for every active item. I filled the quantity field with 1 and showed the result as Item, Quantity, Price. Back in NAV days, the user could then copy and paste to Excel.

    That done, the complaint arose that it did not show volume discount tiers in sales pricing. So, I had to now loop over every sales price qty level to create a new sales line with the quantity from sales pricing.

    The funny part was, even then it was not "correct". Basically, over the years so many different setups (sales price, sales discount, invoice discount) had been created that there was no way to give the customer a complete, understandable price list.

    The joy of sales not wanting any restrictions on the creativity they can offer on pricing yet without having to understand how one setup interacted with another.

  • Maria Eriksson Profile Picture
    Maria Eriksson 93 on at
    RE: How can I generate customer prices to a file?

    Hi Zhu,

    I did it just like that but when we use Item disc. group on price list lines the excel shows that row instead of the items included in the item disc. group. So I guess this wont work.

    Do you know if there is any page in BC where I can find the customers prices instead? The prices is dynamic and I want to find a table that show these prices. Do you know what I mean?

    pastedimage1668760759781v1.png

  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 73,472 Super User 2024 Season 2 on at
    RE: How can I generate customer prices to a file?

    Hi, I did a simple test and it seems to be fine.

    For example,

    pastedimage1668726637005v1.png

    pastedimage1668726696014v2.png

    No filter:

    pastedimage1668726726793v3.png

    Add fitler for customer

    pastedimage1668726769229v4.png

    pastedimage1668726785466v5.png

    Hope this helps.

    Thanks.

    ZHU

  • Maria Eriksson Profile Picture
    Maria Eriksson 93 on at
    RE: How can I generate customer prices to a file?

    Hi, I tried to create a configuration package for this as well, but I wont get it to work. I'm not sure how to do this for prices.

    I created a package for table id 7001 (price list lines) and did a filter for a customer. But when I generate the excel file it doesn't show as I want it. The client has got Item Disc. Group on price list lines. So the excel file shows for e.g Item Disc. Group "11000" and a discount in % and not the products included in the Item Disc. Group.

    Do you know how to get lines for all products?

    /Maria

  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 73,472 Super User 2024 Season 2 on at
    RE: How can I generate customer prices to a file?

    Hi, How about using Configuration Package? This is a standard feature and requires no customization.

    How to Export and Import Data (Using Configuration Packages)

    https://yzhums.com/4286/

    Hope this helps.

    Thanks.

    ZHU

  • Suggested answer
    Isuru De Silva Profile Picture
    Isuru De Silva 10 on at
    RE: How can I generate customer prices to a file?

    Hi Maria Eriksson,

    Please check the below screen shots. I have setups as you mentioned.

    Also, I have mapped the 'Item Disc. Group' in the Item card

    Price setup for the customer

    Price.png

    Discount setup

    2110.Discount.png

    ''Item Disc. Group'' in the Item card.

    discount-group.png

    Then you can run the 'Item Price list' report

    Item-price-list.png

    request-page.png

    Select the Excel option

    pastedimage1668876725740v1.png

    Received the output ab below (Excel)

    print-out.png

    Hope this is OK.

    Thanks,

    Isuru.

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,149 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans