iSolutions Client Tracker Problem

[RalphL]

Can you explain the need for the auto-enter calculation for the ContactID in Notes and how it works. Looks like an invalid relationship is being used.

[Fenton]

That's not really my thing. I thought it was odd also. It's from the original. It is a trick, which I first saw being used by Matt Petrowsky, but I don't know if he originated it. I believe it was also possible in 6 using lookups. Its purpose is to work-around showing the IDs in a drop-down list.

The same field, ContactID is being used in 2 relationships, one to Contact::namefull (which seems invalid, but is merely odd), and a regular one to the Contacts::ContactID. He's attached a value list of the full name to the ID field. When you choose the full name from the value list, the relationship to the full name is valid, if just for a moment, and is used to auto-enter, with replace, the full name you choose with the related ContactID. It's a switcheroo.

It is bypassing the relational integrity of a unique ID by using the name; it would always return the 1st ID in the case of a duplicate name. But, for this use, in a small filtered value list it seems fine. You'd have to have 2 people with the exact same name working for the same company for it to be a problem; and that would be a problem anyway, from a non-database viewpoint.

Because they've used long "random" IDs* it seems appropriate to use the name in the value list. I don't use random IDs; I just do the imports and reset the serial; but there's been times when I wished I had.

*He's using a mix of the current time, padded with a random number, concatenated with a random number. Likely to be unique.

[Fenton]

Shrubman, looking at your file there is one critical difference. In mine there is only 1 Notes table. It is represented by 2 TOs, but it's only 1 table. This is what allows the sharing of the notes between Contacts and Projects.

I marked the original ProjectLog table as obsolete. It is not on the relationship graph anymore. I should have deleted it entirely. As I said earlier: "The Projects Log table is still there, but is no longer being used, nor on the graph; can be deleted."

[RalphL]

Thanks, I missed the value list part, all I could see was the strange relationship.

[Shrubman]

Hi gentlemen,

I've spent a few hours today working on this thing, and I wish I could say differently but I'm not making much progress.

Fenton, I have removed the secondary log you mentioned. I had, indeed, overlooked your note about its obsolessence. I have currently done everything I know to set up the project log layout, using your file as a template. Though I think I have done evrything right, there are two obvious issues. First, in the upper pane of the layout, the contacts list does not show up. Not only do no records show up, but even the drop-down icon and trash icon disappear (as compared to layout mode).

In the lower pane of the window (the projectLog portal) hitting the button that should launch the project note script does nothing but clear all the fields in the layout.

Please be patient with me, both of you, and try to look over my file and see where I've gone wrong. I have not yet created the necessary script to create new entries in the log from the contact window. No sense in me creating anymore new code 'til I can get the first one working...right? Or do I need them both before things will work, for some reason.

You are both very helpful, and I know it may be hard for you to appreciate my novice level of understanding, but if I could make a request it would be to make your answers a little more detailed. So far, some of the replies have seemed to be meant to "jog my memory" about a concept that I really haven't learned.

Thanks again, guys!

Evan

Clone3.fp7.zip

[Fenton]

A quick glances shows that you deleted the obsolete "log" table, but did not reassign the existing portal and fields, on the Contact layout, and in the New Contact Log script, to the other Log Contact table occurrence (TO); you did reassign it correctly on the relationship graph (which the most important place). If you look at that portal in Layout mode you will that it is not assigned to any table occurrence. Reassign it to the LogContact TO.

Also, the ONLY layout for notes has no table occurrence assigned; it must have belonged to the one you deleted. I assigned to ProjectLogs, which has the most going on and needs it. The ContactsLog TO can share it.

I went through the file and fixed everything I could find. There were several errors. I created my own value lists for the 2 critical ones (one of which is referenced in my script by name).

You'd sometimes used ContactProjects instead of ProjectContacts (it matters).

It is very difficult for me to translate what you have done, and the names you've given things, to what I have done, which follow my own naming conventions, different from either yours or theirs.

This template is not really for beginners, mostly because of that trick for choosing the name instead of the ID. There is no way that I could document all the changes that I made. You did a good job considering.

Clone4.zip

[RalphL]

I have looked at Fenton's latest file. It seems to be coming together. I have a few comments:

The project_form layouts are not consistant. Only the contact log has a portal for contacts. I would add that to the other 3. This portal is not consitant with other portals in the database. The relationship is set to allow creation of new related records. All others have a button to create related records. The delete symbol is different from the other portals. These are minor points but they are things that could confuse the user.

In the project_form_details(assignments) layout the field zk_F_stafID.t should be added under the employee full name field. This means that the trick for choosing the name instead of the ID needs to be used here also. That will require another TO on the graph.

[Shrubman]

Fenton,

I'm very grateful for your efforts..on a holiday no less! I just took a quick look and things seem to be doing what I want. Just a quick note on my naming conventions: I have tried to follow the model of the template. abc_def.g would use abc as the TO through which the information is viewed and def.g is the table and field supplying the data. I think. : )

Anyway, I'll look at things more thoroughly a little later. Thanks again for the help.

And thanks to you, Ralph, for your input as well.

[RalphL]

I have made the changes I suggested. They are in the attachment.

[Fenton]

Yes, I guess it is confusing. I was not fond of their naming techinique, because they put the entire TOG name at the first, which I haven't done, yet (too long). I expect to see the name of the "target" TO right away, with perhaps a TOG abbreviation first. Either way is fine. I probably should have stuck to their method, as it's more explicit.

I also made a rather silly cosmetic mistake, which is that I rearranged the relationship graph a bit, not realizing that the fields were not sorted by name, which is how I almost always leave them. So you'll need to drag them around again. It's good practice anyway, getting to know them better :-]

And Ralph is correct, there are several layouts that need work. We have only been working on the 2 "log" layouts. I was responding to Shrubman's request for help. I wasn't revamping the entire solution. I have my own version of the file, with my own layouts, which are slightly different. I consider some of the layout elements of these FileMaker templates (and that's what this came from) to be extreme; they waste too much space unnecessarily; some text is huge, some is tiny, too much difference.

And I think my little trash is cuter than their bright orange/red X :-) It is less garish, and more explicit.

[Shrubman]

I have made the changes I suggested. They are in the attachment.

After running smoothly for a while, the contact log entries are not going well. I cannot get the drop down list to stick in the projectlog portal, and the "go to contact" button in the upper part of the project log window doesn't function. Could you please look at my file and figure out what has been changed. I have been all over it and can't see any differences from you last upload. I've even copied and pasted the entire layout from your file but things are a no go.

Thanks much,

Evan

As usual username and password are "admin."

Clone(July13).fp7.zip

[RalphL]

I will look at it this evening.

[RalphL]

After much testing, I am convinced that the problem is the relationship "project_contact.nameFull". I have tried to repair this relationship, but I am not able to do it. The relationship looks just like the one in the file I posted, but it doesn't work. I think the file must be damaged.

Do you know if the file was mistreated?

I suggest that you go back to an earlier version and import the data from the current file into the working version.

[Shrubman]

Hmmm...

Mistreated? Not sure exactly what you mean other than the fact that it's a work in progress that I've been constantly tweaking.

Earlier version? Uh oh... I guess yours could act as the earlier version...

Thanks!

[RalphL]

Computer crash, force quit, copy open file other than using FileMaker Copy command.

It is a good idea to quit after making changes and make .sit or .zip file to use if you need it.

[Shrubman]

Well, I guess it's possible. Is there any type of debugger or something? What about the restore command? I guess I really haven't made any structural changes since your last file. I've changed some value lists and added a field or two, but other than that I think copying into your version will be my best bet.

[Fenton]

[Attachment has been moved further down the line :-]

PROJECTS

A. Contact name in portal should be based on ContactID relationship; NOT on "full name" relationship.

Full name relationships are ONLY used as part of a trick, to temporarily match the name, then use it to auto-enter the ID. The real relationship is based on the ID. It is an extra level of abstraction, adding an extra non-intuitive table occurrence every time it's used. It is cute though :-)

B. The Go to Contact arrow works for me.

C. It is not really necessary to use a button & script to add a new contact. It could just be "allow creation." But then you have to hide the buttons on the extra row (showing the buttons causes no harm, but looks odd). You can create calculations to only show the buttons on existing records, using a test on the ID. It's a pain, but looks good.

CONTACTS

A. Contacts Notes: Project name was based on Projects TO, whose path from the portal is: Contact -->Client -->Project. This will always show only the 1st Project for them. And it will show BEFORE you've actually chosen a Project ID. In this case the actual ProjectID in the log table would still be EMPTY. You want them to choose a project and populate that ID.

The project name in that portal should be: contact_project.projectID::projectTitle. The TO is already on the graph.

CLIENTS

A. It seems a little odd to me that there is no button for a new Contact on the Client form. Yes, you'd want them to finish the Client data first. But if there are hundreds or thousands of clients it will be awkward to force them to choose from a client drop-down list in the Contact form.

Drop-downs work well in templates, with only few records. I don't much like them in real situations with thousands of choices; unless they're filtered to a reasonable number. It's better to start at the top, in Clients, to add a contact.

[Shrubman]

Thanks for the input. I'll need some time to digest what you've written. In summary are you saying that you DON'T think my file is corrupt?

TTYL,

Evan

[Fenton]

Well, I don't know if you've "mistreated" it, crashed it, etc.. But the problems were caused by using the wrong table occurrence; which is easy to do when you have these weird ID=FullName relationships. Those relationships are only used to auto-enter by calculation, using that relationship, to get the real ID. It's not that complex of a trick, but it's kind of backwards thinking. It's also not 100% relationally correct, as it would be broken by an exact duplicate name. But it's safe enough in a filtered situation, choosing from a limited list, where common sense tells you that duplicate names would be a problem (2 contacts working for the same client, etc.).

[Shrubman]

Doh,

I didn't even notice you had an attachment. Downloading it now, thanks!

[RalphL]

The problem still exists with Fenton's file.

[Fenton]

[Moved attachment further down the line (again)]

Good catch Ralph. I originally fixed what I saw to be the problem, but didn't try and change one to see what happened.

There is another tweak needed. The Name -->Contact ID switcheroo really only happens in the environment of the "logProject" table occurrence, NOT the "logContact" table occurrence.

So "logProject" has to be specified in the auto-enter calculation as "Evaluate this calculation in the context of:", also the "current table" is logProject.

If you look at my "New Project Note" script, which operates in the same environment, you'll see that it explicity goes to the "projectLog" layout, as I had this straight in my own version of the file.

Changing the table occurrence of a calculation is not something you often have to do. But in cases where you are using a table for a "dual" purpose, such as notes from 2 different main tables it is sometimes necessary.

[Shrubman]

Is that "gscratch" field that's floating above the project log portal being used? Also, there still seems to be another problem (perhaps related). In the contact log of the projects view, when I use the contact name drop down menu to choose a contact, it does not stick. In fact, even with the two example entries in your file, if I hit the drop down menu to change the name of the contact, it seems to erase the field. It goes blank.

[Fenton]

No, it's not being used. I was just checking quickly that it was getting the right value. I don't know what's going on with your drop-down. Mine is certainly sticking, in my latest file.

[Shrubman]

Hi Fenton,

I'm at home now on a WinXP machine. At work I was on a Mac (Tiger). Just to be sure, I just downloaded your file to my desktop. I then opened the file, immediately hit the contact log tab in the opening window, and then immediately hit the blue triangle in your latest contact log entry. Whether I change the contact or leave it the same, the result is a blank field. I have attached a screenshot.

Perhaps you posted the wrong version of your file?

Thanks again (as always),

Evan

[Fenton]

Yes, you're right. I don't know what happened. It's a heat wave here, my brain is overheated (that's my excuse). Anyway here's a new file, renamed, so there's no confusion.

iSolutions_7_14.zip

[Shrubman]

I thought San Diego was supposed to be the best climate around!

Gracias!

Evan

[Yogesh Nath]

Your solution is great and very helpful.

But what happends once the job is completed and you receive the payments.

How do you close the job etc etc. I have no idea that is why I am asking.

Thank you.