Unified Operations & Dynamics AX Forum

Expand all | Collapse all

Bulk Trade Agreement Journals

  • 1.  Bulk Trade Agreement Journals

    Posted 11 days ago
    Hi!
    We are using D365 F&O and are currently in the process of updating pricing and have quite a few entries to make. Is there a way to create new trade agreement journals in bulk, such as using the Open in Excel option and doing it there? Open to any suggestions on ways to make this process a bit easier...

    Thanks
    Justin Cooper


  • 2.  RE: Bulk Trade Agreement Journals

    Posted 10 days ago
    Hi Justin

    You can upload them from Excel using Data Management. There are a number of entities depending on the type of trade agreement you are using. If you search for entities starting with "Open Sales" you will find all the customer facing trade agreement entities.

    My recommendation is to key a couple in manually, run an export to use as a template and then update them as you needed and re-import.

    Thanks,

    ------------------------------
    Arush Kuthiala
    Western Computer
    Toronto ON
    ------------------------------



  • 3.  RE: Bulk Trade Agreement Journals

    SILVER CONTRIBUTOR
    Posted 10 days ago
    Justin,

    There is a way to make it easier using the Excel add-in (must be on Office 2016 or newer to use).  First create the trade agreement journal in Dynamics, after that open the lines and then click the little Office icon at the top right and click on the first option "Open in Excel; Open sales price journal lines".  Click on download to save the file, then open it.  You'll need to "Enable editing" and probably log into the add-in, but then you can enter the data in Excel and once finished press "Publish" in the add-in and it'll sync to the journal you created.

    Hope this helps, feel free to contact me with questions.

    ------------------------------
    Jonathan Anderson
    Managing Consultant
    BKD, LLP
    Denver CO
    ------------------------------



  • 4.  RE: Bulk Trade Agreement Journals

    Posted 10 days ago
    Thank you, Jonathan!

    I think this should work for us. 1 question I have regarding this - there is a column that has the journal number populated. If I wanted to create a template that could be used going forward, how would that work? Would they have to always first go in and create the Journal and then copy that number to the template?


  • 5.  RE: Bulk Trade Agreement Journals

    SILVER CONTRIBUTOR
    Posted 9 days ago
    Yes there is a column for the journal number so you could make a template, but the journal would have to exist in D365 first before they could work with the template so either way they'd have to create the journal within Dynamics first then open the Excel sheet.  Creating a template could improve the columns that are in the sheet to only those required by your business though, so depending upon your users preference it may be the better solution.

    Here's a screen shot of the Excel tool

    Excel Add-in Example

    ------------------------------
    Jonathan Anderson
    Managing Consultant
    BKD, LLP
    Denver CO
    ------------------------------



  • 6.  RE: Bulk Trade Agreement Journals

    Posted 6 days ago
    This is all excellent feedback, thank you very much for the information and assistance!

    ------------------------------
    Justin Cooper
    Zip-Pak
    ------------------------------



  • 7.  RE: Bulk Trade Agreement Journals

    Posted 6 days ago
    Thank you ALL very much for the help!


  • 8.  RE: Bulk Trade Agreement Journals

    SILVER CONTRIBUTOR
    Posted 9 days ago
    Jonathan,
    When using the Excel Add-in, in a price update scenario such as Justin asked about, would I first populate the journal lines by "Selecting" existing trade agreements, then use "Copy and Revise" to duplicate each line?  Then in Excel would I make my updates:  one line I would change the END DATE on the current price, on the second line of each pair I would update the START DATE and enter the new price?  Will Excel Add-in update the existing trade agreement with the new end date so we are retaining the history of our pricing and so we can do this set up several days or weeks before the new prices take effect?  I don't want to end up with THREE trade agreements in the end, just two (one expiring soon, and the other to start the day after that.)

    (Sorry if terminology is different... I work in AX 2012 R3 and am not sure if D365 has the same "Select" and "Copy and Revise" buttons.)

    ------------------------------
    Lynn Kinsman
    Senior Systems Analyst, Pricing, Rebates, Projects
    Kodak Alaris Inc.
    Rochester NY
    ------------------------------



  • 9.  RE: Bulk Trade Agreement Journals

    TOP CONTRIBUTOR
    Posted 9 days ago
    Hi Lynn,

    Your scenario is possible. We use the same approach at a customer in the Netherlands. First prepare the journal with using the copy and revise then continue editing using the Excel add-in. You can even delete lines from Excel which are not required to be updated. Then these lines will also be removed from the journal.

    ------------------------------
    kind regards,

    André Arnaud de Calavon
    Solution Architect, Microsoft MVP - Microsoft Dynamics Business Solutions
    ------------------------------



  • 10.  RE: Bulk Trade Agreement Journals

    SILVER CONTRIBUTOR
    Posted 6 days ago
    One last question.  Under Dynamics AX 2012, in order to use the "Edit in Excel" option on a trade agreement journal, you have to have system admin level access because the design uses the "Add Data" design. (We do not have "Add Tables" enabled in our landscape for reasons I do not fully understand but I guess it was very risky or a resource hog.)  Has this been addressed with D365 so that we can use standard security roles to control access to the table via Excel Add-in rather than requiring system administrator access?

    ------------------------------
    Lynn Kinsman
    Senior Systems Analyst, Pricing, Rebates, Projects
    Kodak Alaris Inc.
    Rochester NY
    ------------------------------



  • 11.  RE: Bulk Trade Agreement Journals

    TOP CONTRIBUTOR
    Posted 6 days ago
    Hi Lynn,

    You can use normal security roles for the D365 Excel connector.

    ------------------------------
    kind regards,

    André Arnaud de Calavon
    Solution Architect, Microsoft MVP - Microsoft Dynamics Business Solutions
    ------------------------------



  • 12.  RE: Bulk Trade Agreement Journals

    TOP CONTRIBUTOR
    Posted 6 days ago
    Lynn - Your comment about users needing SysAdmin role for Edit in Excel of trade agreements doesn't quite fit what I have seen implementing this, non-sys admin users can edit trade agreement journals in Excel that they launched from the client.  Edit in Excel from the trade agreement journals works slightly differently than a normal Add-Data -> Add Data when you are in Excel.  The "Edit in Excel" button copies a workbook from the Resources node of the AOT and then opens Excel.

    What is the error your users are getting when they are in Excel?  I have seen instances where the user's default connection details in Excel points to a non-prod environment where they don't have the same permissions as PROD, and that causes confusion.

    And FYI its a small mod to make the Add-Tables option available to non-sys Admins for the tables users have access to edit in the client.

    ------------------------------
    Colby Gallagher
    Manufacturing Systems Consultant
    Agility Business Solutions
    Brecksville OH
    ------------------------------



  • 13.  RE: Bulk Trade Agreement Journals

    Posted 6 days ago
    This is a great question - If we were to create a new entry for a product and not change the previous entries "end date" but the "start date " of the new entry is > than the previous start date, would it be the new "active" price for that product?

    Example
    Product A:
    Price $1
    From Date 01/01/2018

    New journal entry
    Product A
    Price $1.25
    From Date 01/01/2019

    Would any new invoices generated starting 01/01/2019 have the $1.25 price by default? Or would we have to put that end date/TO date on the original entry?

    ------------------------------
    Justin Cooper
    ------------------------------



  • 14.  RE: Bulk Trade Agreement Journals

    TOP CONTRIBUTOR
    Posted 6 days ago
    Hi Justin,

    Editing the old price record to get an end date is more safe. If the option Find next is enabled on the new price, it will try to find the lowest available price in active records. Setting an end date, would make the old record expired.

    ------------------------------
    kind regards,

    André Arnaud de Calavon
    Solution Architect, Microsoft MVP - Microsoft Dynamics Business Solutions
    ------------------------------



  • 15.  RE: Bulk Trade Agreement Journals

    Posted 5 days ago
    Edited by Justin Cooper 5 days ago
      |   view attached
    So what would be the best way to go about editing the "to" date on the previous records?

    Would I choose the "select" option, select "Yes" for prices then choose "All" for customers and Items (screen shot attached). Once it loads the items, open the lines in Excel and update the "to" date on the old entries and then create new entries for the updated prices?


    ------------------------------
    Justin Cooper
    ------------------------------



  • 16.  RE: Bulk Trade Agreement Journals

    TOP CONTRIBUTOR
    Posted 5 days ago
    Hi Justin,

    Once you have selected the prices using the select option there are some options. You can first delete lines with prices which should remain untouched. Eventually you can do this also using the Excel add-in.
    Then you can use the option Copy and revise which will create new lines and mark the old ones to be updated. You can actually set a new start date for the new lines using this function. Using the Excel add-in, you can then easily provide the old lines with a To date and fill the correct new prices on the existing lines. I did use the Vlookup function for getting the prices from another feed.
    Then publish the changes and check the journal before posting.

    ------------------------------
    kind regards,

    André Arnaud de Calavon
    Solution Architect, Microsoft MVP - Microsoft Dynamics Business Solutions
    ------------------------------



  • 17.  RE: Bulk Trade Agreement Journals

    Posted 5 days ago
    Edited by Justin Cooper 5 days ago
    Thank you for the prompt response!

    In the "copy and revise" settings window, do I need to toggle any of the options, or can they all be set to "no"?

    I tried the copy and revise option but I guess I dont really understand what exactly it is doing. It didnt appear to really do anything after it processed but im guessing that I am missing something.

    Sorry for the all of the questions! This is going to be a huge help to our Business!

    ------------------------------
    Justin Cooper
    ------------------------------



  • 18.  RE: Bulk Trade Agreement Journals

    TOP CONTRIBUTOR
    Posted 5 days ago
    Hi Justin,

    Copy and revise will ensure you can edit the old existing price line and a new one will be created based on the settings in the dialog.
    Depending on the needs, you can enable some of the options. For sure, you can set the start date for the new lines like I mentioned above.

    ------------------------------
    kind regards,

    André Arnaud de Calavon
    Solution Architect, Microsoft MVP - Microsoft Dynamics Business Solutions
    ------------------------------



  • 19.  RE: Bulk Trade Agreement Journals

    Posted 3 days ago
    This is great! We've done a bit of testing and it appears that this will work for us.

    Appreciate all of the help!

    ------------------------------
    Justin Cooper
    ------------------------------