iSolutions Client Tracker Problem

[Shrubman]

Could someone please see if they can duplicate this problem? Go to the contact tab and create a new contact. For company name choose one of the existing companies in clienttracker. Now go to the projects tab and pull up one of the existing projects. Make sure the company name matches that of the new contact you created. Now choose your dummy client from the drop down list. So far so good, right? Now click the little blue arrow next to the contact name while still in the project window. It takes you to the original contact, not the new one you specified, right? How do I fix this?

Thanks,

Evan

[RalphL]

There is no direct relationship between project and contact. It goes thru client. You might try adding another TO for contact and use it for the button.

[Shrubman]

There is no direct relationship between project and contact. It goes thru client. You might try adding another TO for contact and use it for the button.

Please excuse my ignorance, but what does TO stand for?

Evan

[Shrubman]

Ahh, Ok. I found it by searching google: Table Occurrence. I'll see what I can do.

While I have folks attention please consider another thing I am trying to add to my solution. Right now the job assignments are not really tracked and connected to a staff table. I have added a staff table which i want to link to job assignments so that i can pull up a tab in the staff form view that will show project assignments for that employee via a portal. I have been trying to copy the method being used to show procect data in the client view, but I'm having trouble. Any advice. I think it is, again, a TO issue.

Thanks,

Evan

[Shrubman]

There is no direct relationship between project and contact. It goes thru client. You might try adding another TO for contact and use it for the button.

I've looked at things more closely now and I'm having a hard time seeing the disconnect you mention. There are two Contact TO's that directly link the contact ID and contact Name to fields in the project table.

Evan

[RalphL]

The button uses the relationship (TO) contact. The path of this relationship is project -> client -> contact and uses ClientID as the key field. There is no way for FMP to know who the contact is, so it goes to the first one.

Adding a new TO contact 2 and make a relationship from project to contact 2 using ContactID as the key solves the problem.

You could use the TO project_contact.contactID insted of adding another TO. I didn't study the graph enough to see this relationship.

[Shrubman]

...

You could use the TO project_contact.contactID insted of adding another TO. I didn't study the graph enough to see this relationship.

That's all I needed to do it seems. Thanks so much! One more small hurdle cleared.

Evan

[RalphL]

While I have folks attention please consider another thing I am trying to add to my solution. Right now the job assignments are not really tracked and connected to a staff table. I have added a staff table which i want to link to job assignments so that i can pull up a tab in the staff form view that will show project assignments for that employee via a portal. I have been trying to copy the method being used to show procect data in the client view, but I'm having trouble. Any advice. I think it is, again, a TO issue.

I assume your staff table has at least 2 fields, StaffID and Name. In projectAssignment add the field StaffID and remove Name. Make a relationship between Staff and projectAssignment using StaffID aas the key field.

Change the valuelist zProjectAssignmentEmployeeNames to use StaffID and Name.

Now in the Layout project_formView_assignments change the field projectAssignment::Name to projectAssignment::StaffID. Add a new field Staff::Name, place it on top of projectAssignment::StaffID and make it unenterable.

[Shrubman]

I assume your staff table has at least 2 fields, StaffID and Name. In projectAssignment add the field StaffID and remove Name. Make a relationship between Staff and projectAssignment using StaffID aas the key field.

Change the valuelist zProjectAssignmentEmployeeNames to use StaffID and Name.

Now in the Layout project_formView_assignments change the field projectAssignment::Name to projectAssignment::StaffID. Add a new field Staff::Name, place it on top of projectAssignment::StaffID and make it unenterable.

I think I've now done everything you recommended. Unfortunately my New Project Assignment button won't work. When I hit it no new fields open. I've examined the script and I'm guessing some additions are needed to deal with the new functionality, but I don't know.

Thanks again,

Evan

[RalphL]

I tested with the original script and it worked fine. This script is called by button "+" next to the portal in the project_form_assignements layout.

[Shrubman]

I have made a lot of progress but have a new problem. Could someopne please look at the attached clone file and tell me what I need to do to get my contact log to work with inputs from both the contact view and the project view. Right now it's kind of a mess but I'm not sure where the errors are coming from.

Thanks much,

Evan

[Lee Smith]

Do not change or alter the subjects of the threads.

If you want to make a point, make it your first line of the body. You can embellish it using the Gremlins, Instant USB Code and Font Colors enough to call attention your point.

Thanks for your cooperation.

Lee

[RalphL]

After several attempts I was able to get the .sitx file into .fp7. Now it wantts a password and account.

[Shrubman]

Sorry for changing the subject line. When I did so I thought it was going to start a new thread. Anyway...

Regarding the password and account, I meant to include that: account=admin, password=admin

Thanks for trying.

[RalphL]

You have added a new foreign key to contactLog, zk_F_projectID.t. You also have the foreign key zk_F_contactID.t. Both of these must be set when you create a new record in contactLog. Your sciprt only sets zk_F_contactID.t. Add the necessary steps to set zk_F_projectID.t.

In your graph you have a TO named contact_contactLog. It looks redundant.

[Shrubman]

You have added a new foreign key to contactLog, zk_F_projectID.t. You also have the foreign key zk_F_contactID.t. Both of these must be set when you create a new record in contactLog. Your sciprt only sets zk_F_contactID.t. Add the necessary steps to set zk_F_projectID.t.

In your graph you have a TO named contact_contactLog. It looks redundant.

I'm not sure how to modify this script because I'm not fully understanding what it's doing (I didn't write it). I don't know how the scratch field works. In addition, I'm not sure how to set the project ID from within the contact --> contact log panel. Would I have to have a drop down list of all the projects we've done? Could I limit the selections avaiable.

Will these changes account for what seems to be a conflict between the contactID set in the contact log, vs. the contactID set in the contact filed of the upper panel of the project view? I need to be able to have those as possible two or more different people.

Thanks for the help,

Evan

PS: I think that TO you mention was an aborted attempt to solve my problem. I've removed it.

[RalphL]

As you have it set up the name in the display may have no relationship with the person contacted. If you are going to have multiple contacts for a project you need a portal to display the names. Maybe another tab.

[RalphL]

You added some more questions while I was working on this.

The scratch field is a global, you will need a second one. The value of the key field is set into the global, when the new related record is created the value of the global is set to the foreign key.

As far as drop down lists, you can do several things to reduce the size of the value list, you can use a relationship to limit the list to only related records.

[Shrubman]

Ralph,

Thanks for the continuing attention and help. I guess you realize that I'm quite a novice at this stuff.

Anyway, I'm trying to think of how to consolidate my questions and desires for the DB. I'm getting annoyed that the project forms are already getting so congested with tabs and data. As you know, initially the ClientTracker showed the contact log only in the contacts view. We realized we were at least as likely to want to track contacts by project as by contact. It's making this work that is posing the problem because ideally, inputting through the project view would auto-enter the project ID (as usual) and inputting through the contact view would auto enter the contact ID. We would, indeed, like to be able to use multiple contacts per project, but I'm not sure how your suggestion to use a portal would solve this. A portal to what? Do I need a new table altogether? Is my problem a many-to-many relationship?

Thanks for your patience,

Evan

[RalphL]

I haven't fully analyzed this database. I have been trying solve your problems as you bring them up. I just copied the data from the original database to the clone.

I think you have the wrong relationship in the portal that displays the contact log in projects.

You can not add data in the projects view because you do not know the contact. Maybe you could add a field "current contact" to the layout that displays the contact log.

You need a field "current project" to the layout that displays the contact log in contacts.

Both projectID & contactID are entered by the script that creates the new record in contact log.

What I was thinking is a portal that showed only contacts that were on a given project. In the client table you have a portal that shows all contacts for a client.

I don't think that you have a many to many, but more like a many to many to many. Like I said I haven't analyzed this. A client can have many contacts and many projects. A contact has one client and many projects. A project has one client, many contacts and many staff. Staff has many projects. This doesn't cover things like log, milestones, etc.

[Shrubman]

I haven't fully analyzed this database. I have been trying solve your problems as you bring them up. I just copied the data from the original database to the clone.

I think you have the wrong relationship in the portal that displays the contact log in projects.

You can not add data in the projects view because you do not know the contact. Maybe you could add a field "current contact" to the layout that displays the contact log.

[color:"orange"]Does not the drop down list I've created next to the "contact" name in the portal projects--->contact log record the contact? It seems to, but I ran into a problem choosing a different name than that of the upper pane.

You need a field "current project" to the layout that displays the contact log in contacts.

[color:"orange"]Right, now couldn't this go in the same location as the contact name I mentioned above?

Both projectID & contactID are entered by the script that creates the new record in contact log.

What I was thinking is a portal that showed only contacts that were on a given project. In the client table you have a portal that shows all contacts for a client.

I don't think that you have a many to many, but more like a many to many to many. Like I said I haven't analyzed this. A client can have many contacts and many projects. A contact has one client and many projects. A project has one client, many contacts and many staff. Staff has many projects. This doesn't cover things like log, milestones, etc.

[color:"orange"]Hmmm...

Thanks again,

Evan

[Fenton]

Here is a modified version of the original which uses a similar mechanism to the original, but allows for multiple contacts for a project. It uses a join table. I added/modified/renamed a few relationships. I use a different naming method than they do. I also modified the layout for Projects, to make it bigger, and deleted the photos.

I might look at your file also, if you upload it again as a .zip file. I don't want to mess with .sitx.

Your contacts belong to a specific Client, do they not? Can you say again just what the problem is, in an organized fashion?

ClientTracker2.zip

[Shrubman]

Thanks for the example. I have attached a zipped version of the clone file. I'm sorry i couldn't post file with more data but privacy was an issue, as well as file size...

I will try to summarize my issue thusly: I want to be able to have a contact log that interfaces both with projects and contacts so that a contact entry made in one will register in the other as well. The ability to choose from more than one contact per project is also needed.

Thanks!

Evan

Clone.fp7.zip

[Fenton]

Yes, the "shared log." You can make the ProjectID in the Log table a lookup based on the ContactID. Sort the relationship descending by date, in order to get the latest project, in cases of multiple projects. Likewise you could make the ContactID a lookup based on ProjectID.

But this is only going to get the first contact for that project. A more accurate method would be to lookup a ContactIDs value list items of a relationship to the project, to get all contacts for the project (if the note is created from projects). This would be a multi-line field, which would work fine in a relationship to Contacts, allowing all project contacts to see the note.

Since creation of notes would likely be scripted, to enable to sort the portal descending by date, I would forget the lookups and just set the IDs with the script. That way you have more control. (You could also use Go To Related Record [show] and a Loop instead of ValueListItems in this case, to get possible multiple ContactIDs, either method; but I like the VL.)

My logic is that a note created on a specific Contact screen is for that particular contact only; but it is also for their current Project, so it is also visible in the Project's notes.

A note created on the Project screen is for all contacts of the project, visible on each's Contact screen, and also viewable on the Projects screen (obviously).

This allows you to have contact-specific personal notes, as well as overall project notes, all in the same notes table. I changed the name of the table to Notes. It was the Contacts Log. The Projects Log table is still there, but is no longer being used, nor on the graph; can be deleted.

[P.S. I used my version of the file. You'll have to add it to yours, or visa versa.]

ClientTracker3.zip

[Shrubman]

Fenton,

That was very generous of you to work that up for me. Earlier today I visited your site and it seems you, indeed, have a strong history of knowing your stuff about FM.

Having said that (uh oh) I'd like to point out a couple of situations where, if I'm understanding your solution correctly, my needs would remain unmet.

First, you say that an entry made through your "contact log" would record the "current project." What is the current project? We may have more than one job going for a given client, therefore we need to specify.

That's the main issue I have, but I'd also be happier if we could somehow address entries through the "project notes" window to one or all contacts independently.

I don't have the actual DB with me now (home from work) but will examine it against your file tomorrow. In the mean time, thanks a lot for your help and, if you have some time, please consider my above concerns.

Best,

Evan

[RalphL]

I have downloaded Fenton's file and looked at it. The portal he has on the Projects layout for contacts is what I was suggesting.

I think you are on the right track with using the 2 foreign keys in the notes table. The problem is defining the value of the second key. Maybe put the second key in the notes portal.

[Fenton]

OK, here's one that asks you if you want to add a single contact (if there is only one), or multiple contacts, or none, or lets you choose one. That seems to cover it.

[bTW, several of the TOs, the "namefull" ones are just to do that "choose the person without showing the ID in the value list" thing," which I don't normally do, but which the file started with, so might as well continue. It does look better I guess. I generally use shorter IDs myself. You cannot have 2 people with the same name working for the same Client; but that would be highly unlikely.]

ClientTracker4.zip

[Shrubman]

That looks like a winner! I'll let you know how integration goes once I get a chance to try it.

Thanks so much!

Best,

Evan

[Shrubman]

Whoops! I forgot to follow up on my earlier question about a contact's "current project." How do I know what that is and can I specify which project I'm speaking of in the contact log of the contact view?

Thanks again,

Evan

[Fenton]

In my file the "current" project is the latest project that has that ContactID. The relationship to the ProjectContacts is sorted descending by creation date.

You could set up a similar (to the one on the project layout) drop-down choice, in the Contact Notes (Log), for the Project this time, from a relationally filtered list of projects for that contact. But, unless a contact can be working on more than one project at once, this seems overkill.

BTW, the ID field for the drop-down is hidden behind the gradient bar; it's at the back of the layout. That was so the name box could be tranparent. Makes it a little harder to work on, but looks better.

I also turned off the "Show field frames" for the layout. Even though it affects the portal snap, I didn't like the way all the fields seemed to jump when it was on; kind of a personal preference.

[Shrubman]

Fenton,

I devoted most of yesterday towards adapting your methods to our existing database. At this point I believe I have created the necessary tables and TO's and value lists. I have also attempted to convert your new project note script to our table and field references.

I have not yet done the same with the contact log script, but since already things are failing I'd like to have you examine my current setup and see if you can see what's wrong.

I have attached a clone of the latest version of my file. Please let me know if you can help.

Evan

Clone.fp7.zip

[RalphL]

Have you changed password & account. I can not open the file.

[Fenton]

You've changed the password again. Also, you must answer the question of whether a contact can/will be working on 2 projects at the same time. Otherwise there's no point in adding a project choice to the Contact note portal. Is that what you're having trouble with.

In my file there was a subtle point which I didn't describe. Which is that I created another layout, hidden, specifically for the P_Notes_project TO.* The other notes layout is attached to the CL_Notes_contact TO. That whole "Clients" side of the relationship graph could very well end up in a separate TOG eventually, in which case a script using its layout from a Projects layout might fail.

Because of this the auto-enter calculation for the ContactID in Notes specify P_Notes_Project as the TO where evaluation occurs, in calculation dialog, in the field definitions. The auto-enter is not needed if you create a Note from Clients, neither if you allow the script to set it automatically (if there's only 1 contact for that project). It's only for the drop-down choice, which only happens in the P_Notes_project portal. That's my story, and I'm sticking to it -]

*My personal TO naming scheme: P means Project TOG, Notes is the base table, _project means it's directly attached to the Projects TO. I'll often also put in the field used, if it's ambiguous; in this case it isn't. It would then look like: P_Notes_ProjectID_project

[Shrubman]

Sorry guys, I forgot to change the account and privileges. It's fixed now.

Clone.fp7.zip

[Shrubman]

You've changed the password again. Also, you must answer the question of whether a contact can/will be working on 2 projects at the same time. Otherwise there's no point in adding a project choice to the Contact note portal. Is that what you're having trouble with.

In my file there was a subtle point which I didn't describe. Which is that I created another layout, hidden, specifically for the P_Notes_project TO.* The other notes layout is attached to the CL_Notes_contact TO. That whole "Clients" side of the relationship graph could very well end up in a separate TOG eventually, in which case a script using its layout from a Projects layout might fail.

Because of this the auto-enter calculation for the ContactID in Notes specify P_Notes_Project as the TO where evaluation occurs, in calculation dialog, in the field definitions. The auto-enter is not needed if you create a Note from Clients, neither if you allow the script to set it automatically (if there's only 1 contact for that project). It's only for the drop-down choice, which only happens in the P_Notes_project portal. That's my story, and I'm sticking to it -]

*My personal TO naming scheme: P means Project TOG, Notes is the base table, _project means it's directly attached to the Projects TO. I'll often also put in the field used, if it's ambiguous; in this case it isn't. It would then look like: P_Notes_ProjectID_project

I've just read over what you're written and it'll take a little time for me to fully understand what you're saying. Meanwhile, let me say that it IS possible for one contact to be involved with more than one project at a time.

Regards,

Evan