Extraction of Files
Report used is – JJ A/R Aging Detail for Invoice Migration
Saved Search – JJ Credit memo migration
- Creation of the Unique ID
Concatenate Document Number(Col F) + Internal ID (Col H)
Example – 89307 + 12285 = 8930712285
- Creation of External ID of the customer
Concatenate NS + Internal ID (Col H)
Example – NS12285
- Bring in the External ID using the Vlookup from the Search file. The internal id of the Search file will be the External ID (Legacy Internal ID) in the report.
- Check the date format of Date and Due Date – mm/dd/yyyy
- Memos need to be created
Main Memo – Concatenate Created from + Actual posting period (this has to be taken from the main report which is later changed to May 2021 in the file prepared.)
Formula to receive in the format is
CONCATENATE(X2,” “,IF(K2<>””,CONCATENATE(“created from=”,K2),””),CONCATENATE(” Legacy posting period=”,F2))
Eg : Created from=Sales Order #18843 Actual posting period=Mar 2019
Line Memo – Concatenate Item Name(AL) + Description(AE) = Invoice Description
- Next is the mappings
Department & BU Mapping
Subsidiary Mapping
https://drive.google.com/file/d/15vbZmwV0SOem2tZYpqhKev3XQCD50I6H/view?usp=sharing
COA Mapping
https://drive.google.com/file/d/1gUNkafqTm2q7SNy9Tk4AmS993isO8FIY/view?usp=sharing
Tax Code Mapping
https://drive.google.com/file/d/1yQo0lKCUVP59SmL4CVuBAgJ39jkeS_ba/view?usp=sharing
Item Mapping
The Item name is used as the Opening Balance itself not the actual item so there is no need to map the item here.
- Item Rate need to be calculated
Formula will be =Round(Amount due to foreign currency/quantity,2)
Before that the symbols needs to be erased by using If and Replace. Use custom for the decimals.
- Check for the currency. If it is Euro change it to Netsuite consistency like EUR.
Important Points to be noted
- For the records with subsidiary CIP , CIP form should be used for Subsidiary=CIP. This form have field BEPA and should be mapped. Tnese records need to be imported separately.
- Use ‘open balance’ Item instead of actual item.( the revenue account for this item will be open balance.)
- Exchange rate should be the exchange rate in the transaction record itself.
- The posting date for transactions will be May 2021.
The Import screen shots
