Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Working with Material Consumption

9 replies [Last post]
Bogdan Leonte
User offline. Last seen 9 weeks 16 hours ago. Offline
Joined: 18 Aug 2012
Posts: 194

When I create the 3 scenarios for a project I usualy have to change the Material Consumption [per Unit Volume] for each material assign an activity. This is very time consuming since I usualy have 100+ materials for a project. When I asign materials to activities I use either the activity properties box or the activities tab in the materials properties box. Is there a faster way to modify the material consumption other than using the activity properties box or the activities tab in the materials properties box?

For example when I want to modify the productivity of resources for the 3 scenarios I use a formula:
Productivity = Productivity * x, where x<1 or x>1.



we shall improve this transfer but today you shall do the following:

In first reference-book menu select Document/Add from other Document then Add, select the document (other reference-book) stored on disk, Add rows. Then select the columns that shall be added (material requirements in particular) and Add Columns. This way you will create necessary columns and rows but you shall delete the values in the added columns manually or using formulas (this we shall change and it will be done soon).

Now open second reference-book and transfer data to the first reference-book (full transfer) but don't forget to add all necessary fields in the transfer options.

Best Regards,


Bogdan Leonte
User offline. Last seen 9 weeks 16 hours ago. Offline
Joined: 18 Aug 2012
Posts: 194

Thank you for the answers,

All have been very helpful, but I still have one concern. Is there a way to transfer materials from one Activiti cost and material requirements reference-book to another.

Let's say that Activiti cost and material requirements reference-book A has Materials A and B and Activiti cost and material requirements reference-book B has Materials C,E,F,G.
How do I transfer both the assignments and materials coresponding to those assignments from Reference-book A to Reference-book B? 

The data to transfer shall be selected in the Data Transfer Options, then Add (in the right field).

You may select what to transfer or delete unused that will be highlited and have zero value in the calculated Planned Quantity field. So applying filter Planned Quantity is equal to zero, selecting and deleting all you will keep only those resources that are used, the same with materials.

Rafael Davila
User offline. Last seen 2 hours 33 min ago. Offline
Joined: 1 Mar 2004
Posts: 4977

Working with table rows.

Double click on the row number invokes the Object properties dialog box, where the user can set its characteristics (e.g. in the Activities table double click on the row number invokes Activity properties dialog box).

To highlight a table row, click with the left mouse button on its row numer. To highlight several rows, keep Ctrl button pressed. The user can move highlighting using ‘Up Arrow’, ‘Down Arrow’, ‘PageUp’ and ‘PageDown’ buttons. To cancel the highlighting, click on the row number with the left mouse button, keeping Ctrl button pressed.

To highlight all rows of the table, choose menu item Edit–>Select all.

The user can delete a row from a table using one of the following methods:

  1. Highlight the row and press Delete.

  2. Invoke the shortcut menu clicking on the number of row being deleted and choose menu item Delete.

  3. Highlight the row and choose menu item Delete.

In the tables of Resources, Cost Components, Materials, Multi-resources, Resource Skills, Calendars and Weeks the user can highlight rows with objects that are not used in the project using menu item Edit–>Select Unused Objects. For example, to highlight rows with unused resources, choose menu item Edit–>Select Unused Objects in the Resources table.

If any data in the table are reflected in blue, they cannot be edited (the corresponding message will appear).

Every table has Main menu and a toolbar.

Try selective purging on your tables, it is not always desirable to delete all unused objects. Better than using selective highlighting prone to error consider creating a user defined field on your table for easier filtering and selection of rows to delete.

Bogdan Leonte
User offline. Last seen 9 weeks 16 hours ago. Offline
Joined: 18 Aug 2012
Posts: 194

In order to transfer material and resource assignments I have to transfer first the material and resource reference-books, only then I can transfer the assignments. If I have very large material and resources reference-books (lets say 500+ items) then some of these items will not be used, these are the materials and resources I want to eliminate after I transfer the asignments into my project.

Also I have been trying to transfer one Activity cost and material requirements reference-book to another but I can't figure out the setting to transfer the materials too. I manage to transfer only the type, unit of measure and activity type name.

Here are the settings I have tried:


And here are my results:



As you can see the activity assignments have been transfered but not the materials.


It is possible to transfer reference-book data not only to projects but also to other documents (reference-books). This way you can add materials or resources from one reference-book to another.

Besides one project can be linked with multiple reference-books if you will add them to Corporate Reference-books table. Transfering data from the reference-books listed in this table does not require opening them. Just select and choose Transfer data in the pop-up menu that appears by right mouse click on the number of one of selected rows.

But the easiest way to merge data from two projects is just by inserting one project into another and then creating standard reference-books from this united project.

The data from the reference-books are transferred only to objects that have the same types.

Besides you can transfer only filtered or selected data. So select those assignments that you want to transfer. You don’t need to delete unwanted assignments, just don’t select them.



Bogdan Leonte
User offline. Last seen 9 weeks 16 hours ago. Offline
Joined: 18 Aug 2012
Posts: 194


I have been experimenting with reference-books for a couple of days now. I managed to create an example based on the steps you have provided, very useful. This being said I am now stuck on another problem.

Lets say I have a Project X with the following activities:

Activity A  of Type a-> Resource/Multi-Resource/Resource Skill 1 and Material A

Activity B  of Type b-> Resource/Multi-Resource/Resource Skill 2 and Material B

Activity C  of Type c-> Resource/Multi-Resource/Resource Skill 3 and Material C

For wiich I create the following reference-books:

  1. Resources, Multi-Resource, Resource Skills;
  2. Materials;
  3. Activity Cost and Material Requirements;
  4. Resources, Multi-Resource, Resource Skills Assignments.

Now let's consider a Project Y with the following activities:

Activity D of Type d-> Resource/Multi-Resource/Resource Skill 4 and Material D

Activity E  of Type e-> Resource/Multi-Resource/Resource Skill 5 and Material E

Activity F  of Type f-> Resource/Multi-Resource/Resource Skill 6 and Material F

For which I create the same reference-books as for Project X. 

How can I merge the reference-books into a single one so that later I can just filter the data and transfer it in another project? 

Also I will have to transfer all the resources and materials but only part of the assignments, how can I delete at once all the materials and resource/multi-reources/resource skills I will not be using?

Rafael Davila
User offline. Last seen 2 hours 33 min ago. Offline
Joined: 1 Mar 2004
Posts: 4977

You may enter material requirements per volume unit directly in the columns of Gantt Chart and formulas can be a way to edit them instead of using a direct entry. Because you have 100 materials then it means a lot of columns will be needed.


Productivity = Productivity * x


Mat Consumption = Mat Consumption * X

something like

m_tim_A =XA *m_tim_A , m_tim_B = m_tim_B * XB ...

Because you have 100 materials then it translates into a 100 material formulas. Try using a script to run the 100 formulas with a single click after changing the value of X using a User Defined Field. Value of X can be single one or can be independent for each row and columns, it is up to your needs.

You can display a column to display material consumption for every defined material, here you can also enter data either directly for Mat Consumption or for X values.

Remember always to keep original values by storing on additional UDFs as required.

Try it with a sample job of say 5 activities and 3 material resources and 3 UDF columns, one for each X factor if different for each material. You shall need only 3 formulas for your script. Suggest using a combination of record script and editing on the script window.

Beware that if you repeat the script without restoring original values you will be iterating the formula, something at times I do on purpose if need be. Another script might be needed to restore original values.

Reference Books is a great way to transfer data from one file into another, can be different versions of the same job or even different jobs. Once you get the logic on Reference Books it becomes easy and powerful. Instead of multiple UDF for each material, say 100 materials x 4 options/scenarios = 400 columns you can store them in four reference books. Combining formulas with reference books will keep all data (including your factors) very efficiently.

In this specific case of only 3 scenarios for "what if" I prefer to use separate files and compare schedules.

Best regards,



if you will click right mouse button on any column header you will see pop-up menu where you may select Show Columns, then necessary materials and what is called Expenditure type Per Volume Unit.

You may enter material requirements per volume unit directly in the columns of Gantt Chart.

But we usually do this differently.

If you will create activity types that have the same material requirements per volume unit then you may create a reference-book where these material requirements will be set as corporate standards. Later it will be sufficient just to enter activity type to get these requirements from the reference-book.

I suggest to create small project with several activities and materials. Assign activity types, assign material requirements per volume unit and then select in the Project menu Create Standard Reference-book and then Activity Cost and Material Requirements (per volume unit). By this example you will understand this reference-book structure.

Another standard reference-book I suggest to create is Materials - you will see that your materials will be there.

Now create new project with several activities and enter their types that are the same as in previous project. Now transfer data from your reference-books, materials first, then material requirements.

If a set of reference-books is created it is sufficient to enter activity and resource types, all other information shall be transfered from the reference-books.