AXForum  
Вернуться   AXForum > Microsoft Dynamics AX > DAX Blogs
All
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 02.06.2018, 00:13   #1  
Blog bot is offline
Blog bot
Участник
 
22,325 / 771 (70) +++++++
Регистрация: 28.10.2006
kurthatlevik: D365FO – Some nice excel tricks
Источник: https://kurthatlevik.com/2018/06/01/...-excel-tricks/
==============

When working with importing master data into Dynamics 365 you will experience that they are available in different data entities. In a typical retail project you would need to import data like released products, item barcodes, external item numbers price. It is also common that we get the master data in many files and in different formats. It is therefore quite beneficial to know a few tricks so that it becomes easer to work with loads of data. Here are my tips.

Export all/selected rows (You should know this!)


From any grid in D365FO you can export selected/all rows to excel by right clicking on the grid. The tip is therefore to make a personalization to the grid, so that it contains the fields you want to export to excel.



Then Excel opens with the selected columns. (PS! This export is limited to 10.000 rows)



Use excel to create a filter


Let’s say we have a excel spread sheet with item numbers, and want to filter in D365FO on these items. Here is a very valuable tip.
  1. Copy the items column from excel and paste as rows in a new excel sheet.(Transpose)

  1. Then copy the row, and paste into notepad

  2. Then do a search, replace in notepad, where you copy the space/tab and replace it with comma (,)



  3. Then copy the content here and use it on a “match” filter in D365FO


     
  4. Then you have created a filter on the selected field. It seams the “match” filer is capable of handling quite a lot of text.

This is nice when some asks you to “Please fix these 200 items”. You then filter them and quite quickly go through them to fix it.

Learn Excel VLOOKUP


VLOOKUP is essential to learn, because it let’s you check and lookup data across multiple excel sheet. A typical scenario in the retail world is when the vendor sends a new pricelist, and you want to import them. Often this is delivered as a excel sheet with the vendor item number, item barcode and the price. Most retailers prefers to have their own item numbers. But then you have the issue of mapping the item barcode from the vendor pricelist and trying to find your own product number. Here is how I recommend my customers to do it:
  1. Export all D365FO item barcodes to excel (There is an entity for this, or open the barcodes from the retail menu)
  2. In the vendor excel price list, create a VLOOKUP field to lookup the D365FO product number based on the item barcode.

  3. Then you can create an excel sheet where you have your own product number, and you can import them using “open in excel” or through a data management import job.
     

     
Happy weekend friends !



Источник: https://kurthatlevik.com/2018/06/01/...-excel-tricks/
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
alexef: Sample Extension v2.0 — Dynamics NAV / AL extension / SaaS — ALF Export Setup Tabs to Excel Blog bot NAV: Blogs 3 05.10.2018 23:13
kurthatlevik: A Practical Guide for Dynamics 365 Iterative Implementation Blog bot DAX Blogs 0 15.09.2017 01:21
kurthatlevik: D365FO – Test & Feedback Blog bot DAX Blogs 0 02.03.2017 20:11
kurthatlevik: New Dynamics AX and the Excel Add-on Blog bot DAX Blogs 0 07.04.2016 20:13
kurthatlevik: New Microsoft Dynamics AX – A guide for using retail sales prices and discounts Blog bot DAX Blogs 0 01.12.2015 18:12
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 10:17.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.