Using Project and Job Costing with Microsoft Project

Sage 300 Project and Job Costing data works with Microsoft Project to let you schedule time and resources with Microsoft Project, while accounting for projects using Project and Job Costing.

The PJC Data wizard links MS Project to Project and Job Costing. The wizard, which you use in Microsoft Project, lets you import Project and Job Costing contracts and export Microsoft Project contracts to Project and Job Costing. It also lets you update contracts in Project and Job Costing with end dates and quantities that you adjust in Microsoft Project.

Typically, you create a contract as a task in Microsoft Project, entering projects as subtasks, assigning resources to the subtasks, and assigning end dates and quantities to the resources. You use the PJC Data wizard to export the contract to Sage 300, where you use Project and Job Costing's sophisticated tools to account for and report on the contract.

You can continue to use Microsoft Project to manage resources and schedules for the duration of the contract.

If you revise end dates and quantities in Microsoft Project, you use the wizard to "synchronize" the dates and quantities in Sage 300 Project and Job Costing. If the contract has an Estimate status in Project and Job Costing, the wizard updates quantities for the estimates. If the contract is open, the wizard posts Revise Estimate transactions.

Note: If you use security in your Sage 300 system and you do not have security rights to revise estimates, the quantities are not updated in Sage 300. If you change only the dates in Microsoft Project, however, the contract is updated.

Adding the PJC Data Wizard to Microsoft Project

The PJC Data Wizard is available as an add-in for Microsoft Project. You must add it to Microsoft Project before you can use it to export contracts to Project and Job Costing or to synchronize information between Microsoft Project and Project and Job Costing.

To add the PJC Data Wizard to MS Project:

  1. In Microsoft Project, click File > Options > Add-Ins.
  2. From the Manage list, select COM Add-ins, and then click Go.
  3. In the COM Add-Ins screen:
    1. Click Add.
    2. Browse to the Sage 300 Project and Job Costing program folder, select PJCDatawizard.dll, and then click OK.

      Note: Typically, the program folder is located at C:\Sage\Sage300\PMxxA, where "xx" represents the program version.

    3. Ensure that the PJC Data Wizard is selected in the Add-Ins Available box, and then click OK.

    The PJC Data Wizard is now included as a project option on the View and Manage Microsoft Office Add‑ins screen under Active Application Add-ins. It is also available on the Add-Ins tab for projects.

Setting Up a Contract for Export from Microsoft Project

One way to use Microsoft Project with Sage 300 Project and Job Costing is to set up the contract in Microsoft Project, then use the PJC Data wizard to export the contract to Sage 300. This lets you take maximum advantage of the relative strengths of each program. (We recommend that you export contracts from Microsoft Project, rather than importing them from Sage 300.)

The exported contract in Project and Job Costing:

  • Does not include tasks. Project and Job Costing does not keep track of tasks. You use Microsoft Project for scheduling tasks and managing resources, while Project and Job Costing provides the accounting backbone for your contracts.
  • Uses only the first word of the names you give projects and resources in Microsoft Project.

    You can enter long, descriptive names for projects and resources in Microsoft Project. If you use a long name, such as "TRAINING - End User" in Microsoft Project, only the first word of the name, in this case "TRAINING," will be used for the project name in Project and Job Costing.

  • Does not use cost information that you enter in Microsoft Project. You maintain budgets and estimates, and track revenues and costs separately in Project and Job Costing.

Integration works best with standard projects Because Microsoft Project is used to manage resources, it is most valuable when working with Project and Job Costing contracts that include resources - that is, with standard projects.

Setting Up Categories for a Microsoft Project

When you set up a contract in Microsoft Project for integration with Project and Job Costing, you begin by defining four optional fields to use as categories. You will assign these optional fields to the project-level tasks that you set up.

To set up the categories:

  1. In Microsoft Project, on the Tools menu, click PJC Data Wizard. The PJC Data Wizard appears.
  2. Click Setup Custom Fields, and then click Finish.

    The wizard sets up the fields for you, displaying a message when finished.

  3. Click Done.
  4. Continue to set up the contract in Microsoft Project, keeping in mind the following requirements:
    • Any cost information that you enter in Microsoft Project will not be exported to Project and Job Costing.
    • You cannot assign a resource with the same category to more than one task on a project. However, you can assign the same resource category to different projects.
    • You cannot assign staff members using the same name as a subcontractor name.
    • You cannot assign an equipment code that uses the same code as an item.
    • In Microsoft Project, task levels represent the structure of a project and its tasks.

    For successful export to, and integration with, Project and Job Costing, you must set up the project in Microsoft Project using a hierarchy of tasks that is consistent with the structure of a contract Project and Job Costing.

    The following list shows Microsoft Project tasks with the corresponding Project and Job Costing contract levels in brackets:

    • Level 0 task (Contract)

      You should have only one level 0 task for a project in Microsoft Project. The task name you assign should conform to a contract structure code that you use in Project and Job Costing.

    • Level 1 task (Contract Project)

      You can have multiple level 1 tasks, representing the various projects within a contract in Project and Job Costing.

      You must assign only projects that exist in Project and Job Costing. The first word of the task description must be a project name that you use in Project and Job Costing.

    • Level 2 task (Project Category)

      You use level 2 tasks to assign resources to each level 1 task. You must assign only staff, subcontractors, equipment, and material resources that you have set up in Project and Job Costing, using codes that you have set up in Project and Job Costing. For example, to assign an employee, you use the employee number.

      For each resource you assign to a project, you assign a category using one of the custom fields. You must assign categories that you have set up in Project and Job Costing, and the category type must be consistent with the resource. For example, if you assign Sean Blake to Project A, you would assign a labor category using the "Labor Category" custom field.

      When you export the contract, the PJC Data wizard will match resources with your Project and Job Costing employee, subcontractor records, and equipment records, and with Inventory Control items depending on the optional field you assigned to the resource in Microsoft Project:

      • The entry in the optional field TEXT1 specifies the labor category to use for the resource in Project and Job Costing.
      • The entry in the optional field TEXT2 specifies the material category to use for the resource in Project and Job Costing.
      • The entry in the optional field TEXT3 specifies the equipment category to use for the resource in Project and Job Costing.
      • The entry in the optional field TEXT4 specifies the subcontractor category for the resource in Project and Job Costing.

    • Level 3 task (not used in Project and Job Costing)

      You can use task levels 3 and above for other tasks that you manage using Microsoft Project.

    • On standard Project and Job Costing contracts, the quantity you enter for a task resource in Microsoft Project will be used for the resource category in Project and Job Costing. (Quantity is not updated on basic projects.)
    • The start and end dates you enter for a task resource in Microsoft Project will be used for the resource category in Project and Job Costing.

Exporting from Microsoft Project to Sage 300

The PJC Data wizard lets you export a contract that you have set up in Microsoft Project, creating a similar contract in Project and Job Costing.

The following restrictions regarding the data that you export from Microsoft Project:

  • Costs that you enter in Microsoft Project are not exported to Project and Job Costing.
  • You enter and track costs in Project and Job Costing. The costs you enter in Sage 300 do not affect the project in Microsoft Project.

The following instructions for exporting a contract to Sage 300 assume that you have set up the contract in Microsoft Project according to the instructions in "Setting Up a Contract for Export from Microsoft Project."

To export a project from Microsoft Project to Sage 300 Project and Job Costing:

  1. With the project open in Microsoft Project, on the Tools menu, click PJC Data Wizard.

    The PJC Data Wizard appears.

  2. Click the option Export This Project To Sage 300, and then click Next.
  3. On the screen that appears, enter your password, and then click Next.

    The wizard displays a message showing that it is connecting to the Sage 300 database. If the wizard encounters problems matching projects, categories, and resources in Project and Job Costing, it displays a message explaining the problem. If you receive such a message, click Cancel to stop the export process, correct the problem in Microsoft Project, and then restart the wizard.

    The wizard displays a message if the matching process is successful.

  4. Use the drop-down list for the structure code, customer number, account set, contract manager, and contract style fields, to select the correct entries from your Sage 300 database.
  5. Click Finish.

    The wizard displays a screen telling you that export is finished, and indicating the projects added. If there were problems creating the contract in Project and Job Costing, the message says so.

  6. Click Done.

Importing from Sage 300 to Microsoft Project

When you import a contract from Project and Job Costing, the wizard automatically sets up category fields for you.

For basic projects, the wizard creates tasks in Microsoft Project for each project category. Because no resources are assigned to them, quantities are not imported.

For standard projects, the wizard creates a new task for each resource category on a project, assigning the resource to that task, and setting the category field (Text1-4). Quantities are treated as hours.

Only staff, subcontractors, equipment, and material resources and categories are imported. Overhead and miscellaneous charges are not used, since they are not normally scheduled resources.

Note: When importing from Sage 300, quantities must be in hours.

To import a contract from Project and Job Costing:

  1. In Microsoft Project, on the Tools menu, click PJC Data Wizard.

    The PJC Data Wizard appears.

  2. Click the option Import A Contract From Sage 300, and then click Next.
  3. On the screen that appears, enter your password, and then click Next.

    The wizard displays a message showing that it is connecting to the Sage 300 database.

  4. On the next screen that appears, from the drop-down list on the Import Contract field, select the contract you want to import.
  5. If you do not want to import a particular contract project, click it on the Import These Projects list, and then click Remove.

    If you make a mistake or change your mind, select the project on the Do Not Import These Projects list, and then click Add.

  6. When you have finished selecting the contract and its projects that you want to import, click Finish.

    The wizard displays messages showing the progress of the import.

  7. On the next screen that appears, click Done.

The contract is now displayed as a project in Microsoft Project.

Synchronizing Microsoft and Sage 300 Projects

If you change resource quantities or dates for a project in Microsoft Project or in Project and Job Costing, you need to run the PJC Data wizard to update the contract to keep the two programs synchronized.

When you run the wizard, you indicate which program contains the correct data. If the dates or quantities are different between the two programs, the wizard uses the data you indicated is correct to update the contract in the other program, as follows:

  • If the quantities are different and you are updating Project and Job Costing data:
  • If the project has an Estimate status, the wizard updates the original estimate in Project and Job Costing.
  • If the project has an Open status and you have security authorization to approve and post revise estimate transactions, the wizard creates a revised estimate transaction in Project and Job Costing. You can also choose to post the revise estimate transactions automatically.
  • If the project has an Open status and you do not have authorization to approve and post revise estimate transactions, the wizard you can choose to update only the dates.
  • If only the dates are different and you are updating Project and Job Costing, the wizard updates the contract, or contract project.
  • If you are updating a task in Microsoft Project, you can choose to update the percentage complete for the task.

To synchronize Sage 300 and Microsoft projects:

  1. With the project displayed in Microsoft Project, on the Tools menu, click PJC Data Wizard.

    The PJC Data Wizard appears.

  2. Click Next.
  3. On the next screen that appears, type your password in the Password field, and then click Next.

  4. Choose synchronization options as follows:

    • Synchronize With Contract. Select the Project and Job Costing contract you want to synchronize with the selected Microsoft project.
    • Use Values From. Indicate whether Microsoft Project or Sage 300 Project and Job Costing contains the correct quantities and dates.

      If you are trying to update an open Project and Job Costing contract that has different project quantities from those used for the project in Microsoft Project, the wizard will only update quantities only if you have authorization to enter revise estimates in Project and Job Costing. If you do not have authorization, the wizard will update the dates, only.

      If a project has an Estimate status, the wizard will update the original estimate.

    • Update Percentage Complete In Microsoft Project. If the contract in Project and Job Costing has projects that use a percentage complete accounting method, indicate whether to update the percentage complete for the projects in Microsoft Project.
    • Post Revise Estimates. Select this option only if you have security authorization to post revise estimates in Project and Job Costing and you want to revise estimates for quantities that you have changed in Microsoft Project.
  5. When you have entered the options you need, click Finish.
  6. Click Done.