Need task table and assigment table info together

[K Major]

First, my understanding is that there is no way to export fields from more
than one table (Task, Resource, Assignment) and combine them into one Excel
worksheet (i.e. you can't cross reference tables). If so, just curious, why
not?

Second, since I believe the above to be true... during an export that
references the assignment table only, why can't you apply a filter (like you
can on the task table and resource table)?

My point is that I think I can export the data that I really am after here
via the assignment table but I can't filter it down (ex: completed tasks
only). Any ideas or am I missing something?

Help!

 

[John]

First, my understanding is that there is no way to export fields from more
than one table (Task, Resource, Assignment) and combine them into one Excel
worksheet (i.e. you can't cross reference tables). If so, just curious, why
not?

Second, since I believe the above to be true... during an export that
references the assignment table only, why can't you apply a filter (like you
can on the task table and resource table)?

My point is that I think I can export the data that I really am after here
via the assignment table but I can't filter it down (ex: completed tasks
only). Any ideas or am I missing something?

Help!

K major,
You can export fields from more than one table but you will need to do
it in steps (i.e. export first table, then the next, etc.). And yes, it
will require you to do some manipulation in Excel to get all the data on
one Worksheet. The reason only one table can be exported at a time is
because a view table defines the fields in that view, whether it is a
Task view, Resource view or Assignment view, and that single table is
the basis for mapping Project fields to Excel cells. However, there is
another solution (see last paragraph below).

To answer you second question, assignment filters cannot be applied
because there are no assignment filters. Assignments are a bridge
between tasks and resources. When in a Task view, assignments are the
resources. When in a Resource view, assignments are the tasks. Think of
it this way, tasks and resources are "tangible" entities in Project and
each can be uniquely identified in a filter. Assignments are a blend of
the two and cannot be uniquely identified in a filter.

If you only want completed tasks included in the export, simply apply
the "Completed Tasks" filter.

Personally I don't use export maps because often I need to export data
that is made up of fields from tasks, resources and assignments. Equally
as often, I need to create data that is not contained in any Project
field (i.e. custom calculated data). To get this type of information to
Excel I use VBA. It gives total flexibility and allows me to do whatever
I need.

Hope this helps.
John
Project MVP

 

[K Major]

John,

Thanks - a couple of questions.

1) When I am creating an export map based on the assignments table, I have
an option to select a Text1, Text2, etc. field in the list provided. These
seem to be customizable fields (just like in Tasks and Resources). Are they
really? If so, how do I get info into them? My point is that when I began
this process, I had entered some info into text fields in the task entry
table, but couldn't "get" to that info because it was over in the task table
while I was in the assignments table. I'm just wondering if these text
fields in the assigment table are a good compromise for my purposes (as long
as I can relate the text fields back to the actual tasks in the task entry
table).

2) When using VBA and extracting info from all three tables - is there some
sort of sequence number that connects the right records with the right
records? If so, what is it?

Thanks for all of your help,
Kevin

 

[K Major]

John,

Thanks - a couple of questions.

1) I saw text1, text2, etc. fields in the assignment table (when creating an
export map) - can you customize these fields? If so, how do you get info
into them? Can you tie them back to the actual tasks each assignment relates
to?

2) On the VBA topic - is there a sequence number that you use to tie each
record in each table together so the right records go with the right records?

Thanks, Kevin

(just like in Tasks and Resources).

 

[John]

John,

Thanks - a couple of questions.

1) I saw text1, text2, etc. fields in the assignment table (when creating an
export map) - can you customize these fields? If so, how do you get info
into them? Can you tie them back to the actual tasks each assignment relates
to?

2) On the VBA topic - is there a sequence number that you use to tie each
record in each table together so the right records go with the right records?

Thanks, Kevin

(just like in Tasks and Resources).

Kevin,
First understand that Tasks, Resources and Assignments all have spare
fields but those spare fields are not the same. In other words, Task
Text1 is not the same as Resource Text1 or Assignment Text1. Task and
Resource spare fields can be customized (e.g. insert a formula or value
list) but Assignment spare fields cannot. As far as putting data into a
spare Assignment field it is probably more easily explained with an
example. Go to the Resource or Task Usage view. Insert Text1. For the
Resource (or Task) rows, the spare field is the Resource (or Task) Text1
field. For the Assignment rows, the spare field is Assignment Text1

When working with VBA, there are generally no "tables" involved. Rather
VBA code is used to access Project objects and their properties directly
whether the object is a Task object, Resource object or Assignment
object. For example the collection of Task objects is:
ActiveProject.Tasks
The collection of Resource objects is:
ActiveProject.Resources
And, the collection of Assignment objects for Task 1 is:
ActiveProject.Tasks(1).Assignments

There are many ways to gather the needed data from Project for exporting
to Excel (or other VBA compatible application). It all depends on what
you want/need to do.

If you are interested in learning more about VBA, go to the MVP website
at: http://www.mvps.org/project/links.htm
At the bottom of the page you will find a link for "Project 98 Visual
Basic Environment Training Materials". Even though it says it is for
Project 98, the training materials are equally applicable to all current
versions of Project. The only real difference is that later versions of
Projects have enhanced the functionality (i.e. more objects in the
object library).

For several examples of VBA code, go to Jack Dahlgren's website at:
http://masamiki.com/project/macros.htm
One of his macros (export hierarchy to Excel) demonstrates export of
Project data to Excel.

Hope this helps.
John
Project MVP

 

[K Major]

Good information.

Thanks for all of your help!

Kevin