Simple (?) arithmetic

[dnwarr]

Hello!  I recently purchased Visio because it seemed like the right software to help me build a planning tool that I am trying to develop.  The tool seems straightforward conceptually, but is proving to be very complicated (at least to me) to implement, and I am hoping that someone can help.  The goal is to produce an interactive tool to assist in planning the allocation of people to projects, and the idea is to have one group of boxes (representing people, and storing a certain number of available hours), another group of boxes (representing projects with a collection of "slots" representing team members), and any number of connections that link people to projects, each of which represents a specific level of commitment measured in hours.  A person with 40 available hours, for example, could be manually connected to two projects with needs, at 20 hours each, thereby depleting that person's available hours.

Here's where I've gotten:

1) I think I want an "entity relationship" diagram, so began working with the Crow's Foot Database Notation template.
2) I've figured out how to configure entities and attributes the way I want them.
3) I've figured out how to use Shape Data to "store" hours in the staff and project entities as well as the connections.
4) I am now stuck at getting the math to work.  If I create a connector that represents 20 hours, I'd like it to reduce a staff members available hours by 20 when I glue it to a staff member's entity shape, and add 20 hours to a project's entity shape when I glue it to a project.

I am new to VBA (as well as Visio), and have struggled to emulate several similar examples that I've found online with no luck.  I would greatly appreciate it if someone could help me get over the hump.  Apologies for the lengthy post, and thanks in advance to anyone who can assist!

[Nikolay]

It looks like you might have bought a wrong product for project planning?
Microsoft's product for that is Microsoft Project

Anyways, regarding arithmetic.
First, if you want to do it in Visio, you'll need some programming skills (may be VBA).
I could think of the following approach: you handle the "connection added" / "connection deleted" events. In the even handler, for each person, you find all projects that are connected to that person and update person's availability respectively.

[dnwarr]

Nikolay, thank you very much for the quick response.  I also use Project, but am more interested in visualizing the staff-to-project relations in a format other that a Gantt chart, and am still convinced that Visio is the right tool for this.  I think I can figure out how to use the Event Handler to trigger the operation in the way you describe, but any help with coding the actual function (i.e. summing the data values of glued connections) would be much appreciated.  I tried creating a function to do this, and adding a field to each of my shapes to call the function, but got an error that the function is not recognized.  Here is the code for the function (again, I am brand new to VBA, so apologies in advance for any cringe-inducing errors):

Function Addup(Shape) As Integer
    Dim i As Integer
    Dim j As Integer
    Dim shpIDs() As Long
   
    j = 0
   
    shpIDs = Shape.GluedShapes(visGluedShapesAll1D, "")
    For i = 0 To UBound(shpIDs)
        j = j + ActivePage.Shapes(shpIDs(i)).Hours
    Next
    Addup = j
End Function

And then added a field to each shape set to a "Custom Formula" of "Addup()."

After further research, I think I understand that this is not the right approach, but don't understand why it doesn't work or what the correct approach is.  Again, thank you in advance for any help.

[Yacine]

http://visguy.com/vgforum/index.php?topic=1775.0

[dnwarr]

Thank you, Yacine.  I've downloaded and taken a look.  A very useful and clever planning tool, but not quite what I had in mind (I don't need to account for duration or sequencing, just allocating resources to projects that can be understood as "static").  Thanks, nonetheless, for a great example--I will spend some time trying to "reverse engineer" this and learn more about Visio!  Any additional help would be appreciated as well.

[dnwarr]

Ok, here is hopefully a clearer description of the basic functionality I'm looking for:

1) I have a collection of shapes that represent staff members with a certain amount of available time (say 40 hours), that is stored as shape data.
2) I have a separate collection of shapes (actually grouped shapes or containers) that represent projects with "slots" representing team member needs, with corresponding time commitments.
3) Connections connect staff members to team member "slots" on projects.
4) When a connection is made, the available hours in the "staff" shape is reduced by the number of hours in the team member "slot," so we go from a state that looks like this.  Or, since one staff member can be connected to multiple projects, the "available hours" for any staff member is 40 minus the sum of the hourly needs of all of the team member "slots" that he/she is connected to.  As illustrated by the "before" and "after" attachments.

Seems straightforward enough, but I'm having enormous difficulty implementing!  Any help appreciated.  Thank you.