Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics SL forum
Answered

Voucher (AP) Import Transaction template

Posted on by 170

Does anyone have the template to upload a csv or excel file into Dynamics SL 2011 Accounts payable?

 

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Voucher (AP) Import Transaction template

    Hi Cheryl!

    I understand what you mean with a lot of fields to sift through from the control file. I've done some TI's for large screens and I've come up with a way to format my initial data spreadsheet to keep things together. I'm not sure if it will help, but here's how I sift through the information when I'm first starting. 

    First, I pull up the control file and list out each field and the corresponding number for each level into Excel. I almost never need every field, but this helps me visualize how the data will fall: 

    Anything on the screen itself that is a required field, I highlight in Red. Then, I take one line of data and start plugging in the fields on the screen to see which ones will auto populate (accounts, for instance usually pull from a default. Dates can default to the current date, etc.). If the system will pull it, then it's more work for me to put it into a column too (unless the values should be different. For instance, you do not want a default account to be used. In that case, you would have to populate with the account information that you want to use).

    Once I figure out which fields require me to supply the data, then I start stripping out the spreadsheet and omitting the fields that I do not need.  I'm then able to populate the rest of my data file much faster since I know what I do and don't need. 

    When I'm finished I delete the header rows that I threw in there and save the file first as an Excel file and then a second file as a .csv (just in case you have to go back and try a few times with the data file - This will make it much easier). You will sometimes have to open the .csv file in Notepad to look and see if it's doing anything weird. Sometimes it will add extra spaces after values or throw in a comma out in the middle of nowhere at the end of a line. I just do a Find / Replace on those if it's pretty uniform on every line. Save it again after everything checks out there, and you're good to go. 

    I know this process seems long and clunky to some of the veterans who have done this several times, but this is what works for me and helps keep my thoughts organized as I'm building out the data file. I hope this helps! 

    -Jenny 

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Voucher (AP) Import Transaction template

    Cheryl,

    You are correct that the control macro can look overwhelming.  You do not need to supply all the level and fields represented in the control macro.  the control macro follows the flow of the data entry screen so the best place to start is to pay attention to the fields that you would populate if you were manually entering the voucher and disregard the levels and fields that you never manually populate.  Having said that, you cannot ignore fields that sit between fields at a level that you do need but  you can supply blank (empty) responses to those fields.

    The biggest challenge with using Transaction Import on batch screens is the fact that the screen is going to have a batch level, a document level and a detail (transaction) level and the batch level data needs to be in the first line and the document level in the next and then one or more detail level lines (for the various expense accounts associated with the voucher).  You loop between the document level and the detail level for each voucher.  So, if the vendor is giving you electronic invoices, those are only supplying the document level information.  You have to front-end the import file with the batch info first (including the control total) and you have to supply the detail level information as the vendor is not providing you expense account information.  That means your spreadsheet has to have a lot of smarts or a lot of manual entry and, if the later, may not save any time over manual entry into the invoice and memo screen.  If your manual input also includes terms entry and or PO entry then you have even more levels to deal with.

    So, start with the fields you would normally populate when entering a voucher manually.  If you can communicate those back, I can give you a sample csv file that would do that but, until one knows how much detail goes into a voucher entry, the variations are too many to provide in this response.

  • Bethshop Profile Picture
    Bethshop 10 on at
    RE: Voucher (AP) Import Transaction template

    [quote user="Cheryl Baker"]

    Does anyone have the template to upload a csv or excel file into Dynamics SL 2011 Accounts payable?

     I would like the excel template.  I have created the control file , but there are so many fields and notes, that I don't know where to start.  Does anyone have the actual excel template I can use to import ?

     

    Thank you.

    Does anyone have the template to upload a csv or excel file into Dynamics SL 2011 Accounts payable?

     

    [/quote]

     

  • Re: Voucher (AP) Import Transaction template

    Hi Cheryl,

    I think you are referring to the Control Macro.  That is the document that indicates what information goes into what field in the import.  You can generate a Control Macro in the Administration module (Control Macro Generator screen).  Be sure to generate an Intelligent macro instead of a Simple one.  

    Let me know if you need more help.

  • Verified answer
    Cheryl Baker Profile Picture
    Cheryl Baker 170 on at
    Re: Voucher (AP) Import Transaction template

    Hi Carolyn,

    I was looking to import vendors invoices directly into Solomon, such as Fedex or DHL.  I know when I import my budget from an excel/CVS file, I need to have all columns in a certain order with specific formatting for particular columns, i.e. general, text, etc.  It would save time instead of keying in hundreds of rows for those bills.

    Thanks,

    Cheryl

  • Re: Voucher (AP) Import Transaction template

    Hi Cheryl,

    Are you looking for a sample data file in order to import documents into Voucher and Adjustment Entry?  I'm not clear on what type of template you are looking for.  If you could explain what you are trying to accomplish I could better answer your question.  Thank you.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,459 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,783 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans