web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics SL (Archived) / How do I edit multiply...
Microsoft Dynamics SL (Archived)

How do I edit multiply vendors at one time?

(0) ShareShare
ReportReport
Posted on by Community Member

Hi,

After extracting all the vendors from SL to Excel and filtering by Class ID, is there a way I can input a the same Default Expense Account to all of them at once instead of going through several hundred vendors and doing this one by one?

Thank you,

Lisa

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Apps Mexico Profile Picture
    Apps Mexico 1,090 on at
    RE: How do I edit multiply vendors at one time?

    Hi,

    As Rick said, if this change is for one time only, the easiest way to do that is running a t-sql query on the SQL Managment. You colud ask for helpt to the IT administrator.

    Excel Formula

    ="UPDATE Vendor SET ExpAcct = '" & ESPACIOS(H89) & "', Lupd_DateTime = GETDATE(), Lupd_User = 'SQL' WHERE VendId = '" & ESPACIOS(G89) & "'"

    RESULT
    232500 510000 UPDATE Vendor SET ExpAcct = '510000', Lupd_DateTime = GETDATE(), Lupd_User = 'SQL' WHERE VendId = '232500'

    Paste Results in Query and RUN
    UPDATE Vendor SET ExpAcct = '510000', Lupd_DateTime = GETDATE(), Lupd_User = 'SQL' WHERE VendId = '232500'

  • Suggested answer
    Community Member Profile Picture
    Community Member on at
    RE: How do I edit multiply vendors at one time?

    Lisa,

    To go with option 3 you would have to have rights to Transaction Import (98.500.00).  You would also have to have access to the administrative functions in order to both create the control file needed by Transaction Import and to launch Transaction Import.  Transaction Import is well documented in the SL documentation but it is a bit technical in nature.  Depending on your comfort with that tool, you may want to seek assistance from your Dynamics SL partner.

    The first step is to create the control macro for the vendor maintenance screen that will be used by Transaction Import.  This is done using the Control Macro Generator menu item under Administration.  You will use that screen to create the macro for the 03.270.00 screen ID.  Pay attention to where it creates this file.  I suggest that you change the location to the SL\Applications folder under Dynamics SL.  The control file tells Transaction Import how to read your CSV file that you will create.  While you can edit that control file to just what you want to do, unless you are technically comfortable with doing that, I would suggest you leave the control file as it was created by this process and create your CSV file to confirm to the control file structure.

    The control file basically matches the order that the screen accepts data.  In your case, all you want to supply is the vendor ID and the default expense account which means that you only need to supply data for Level0 and only 2 to of the fields (fields 1 and 48).  The first field of the CSV file needs to contain either "vendor" or "level0" so your spreadsheet will need one of those two values in column A.  You then follow with the data to be used during the process.   Transaction import nicely skips over blank fields in your CSV file so, if your Excel spreadsheet has the vendor ID in column B and the new default expense account in column AW with all the columns in-between left blank, you will be able to then save that spreadsheet as a CSV file and use that file with Transaction Import.  Again, I would suggest that you save this CSV file to the SL\Applications folder.

    Once you have created the control macro and the CSV file you launch Transaction Import.  I suggest that you press F4 to change the grid view to form view.  If you saved both the control file and the CSV file in the SL\Applications folder then your input to the various Transaction Import fields is easier.

    Date File Name - enter the name of your CSV file.  Be sure to include the .CSV extension

    Data File Type - leave as Ascii

    Screen ID - enter 0327000

    Control File Name - enter 0327000.ctl

    Output Log File - enter 0327000.log

    Uncheck the minimize option (this is so you can see your data flowing through the vendor maintenance screen).

    Click Begin Processing.  You will see the vendor screen open and data flow through it.

    When it is done, click on the Edit Errors button and scroll down to the bottom to make sure it reported 0 errors.

  • Community Member Profile Picture
    Community Member on at
    RE: How do I edit multiply vendors at one time?

    Hi Rick. Thank you for the detailed explanation! Can you please expand on option 3? I do not have management status on SL, would I need to get someone who does?

    Thanks

  • Suggested answer
    Community Member Profile Picture
    Community Member on at
    RE: How do I edit multiply vendors at one time?

    Lisa,

    I can think of 3 ways you could do this.  Which method you use depends on whether you have rights to the SQL Server Management tool or not.  

    You did not indicate whether all the vendors are involved and should be set to the default expense account or not but I am assuming that is not the case.  I am assuming a subset of the vendors are involved and different vendors should be set to different default expense accounts.  If that is an incorrect assumption then there is an easier way than any of the following

    Option 1: using SQL server Management, open the vendor table and edit the ExpAcct field for each vendor.  You will probably want to modify the default query when you open the table to display just the vended, name, and ExpAcct fields.  You may be able to add some sort of where cause to limit which vendors are displayed.  This depends on what you are really trying to do here.  If you are changing expense accounts because you changed your chart of accounts, then filtering on the old expense account would make this easier.

    Option 2: if you have already pushed the vendor ID and default expense account values to an Excel spreadsheet and have then edited the default expense account to what you now want for each vendor you could import that Excel date into a temporary SQL table and then run a SQL update query that joins the vendor table to your imported table updating ExpAcct to the value in you temporary table.  This approach, of course, assumes that you know how to import a Spreadsheet into a SQL table and how to create a SQL update query.  More details can be provided if you want to go with this approach.

    Option 3: you could use transaction import to change the expense account for each vendor using your spreadsheet with the desired expense accounts as the basis for the transaction import CSV file that will be needed.  This approach means you have to know how to run transaction import, how to create the control file for the vendor maintenance screen and how to create the transaction import CSV file.  If you want to go with this approach, more detail can be provided.

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics SL (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans