Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Keeps changing name


bigzilla
 Share

Recommended Posts

I’m designing a solution with everything in one file in 8.0. I have a Company TO with fields called CompanyID and CompanyName. This file also has a layout called Company Entry.

 

I have a Letters TO with a layout called Letters Entry associated to the letters table. Both files are related to each other by CustomerID.

 

Right now if ABC Fireworks is the name showing in my company file. When I click on the navigation button that takes me to the letters layout, ABC Fireworks is showing there as well. I understand that because the CompanyName field in my Letters file is displayed from my Company TO.

 

The problem is I’m in my Customer file on ABC Fireworks, I click on the Letters button, go to the letters file and click on new record and write a letter to ABC Fireworks and everything is fine. Then I go back to the Customer file to a customer called XYZ Builders, I click on the letters file button, click on new record in the letters file and the company name is for XYZ Builders just like it should be. That’s great until you recognize that it changed the ABC Fireworks letter I just created to XYZ Builders as well, which is not so great!

 

I like the idea of going from ABC Fireworks in the Company file and see all of the ABC Fireworks letters in the letter file. That seems like a good idea. But when I click on XYZ Builders I want to see the XYZ Builders, letters, not have all the Letters changed to the most recent company name.

 

Did I make myself clear enough that you can help me?

 

Bill

Link to comment
Share on other sites

The problem is I’m in my Customer file on ABC Fireworks, I click on the Letters button, go to the letters file and click on new record and write a letter to ABC Fireworks and everything is fine. Then I go back to the Customer file to a customer called XYZ Builders, I click on the letters file button, click on new record in the letters file and the company name is for XYZ Builders just like it should be. That’s great until you recognize that it changed the ABC Fireworks letter I just created to XYZ Builders as well, which is not so great!

 

You need to explain in more detail what scripts are attached to the buttons you click, and also what relationships and fields are in use on the letters layouts. What does the "Letters" button DO? (post the entire script that's attached to that button) What does "click on new record" mean in the context of the letters screen, is that also a button, and, if so, what script is attached to IT? And at the top of your letters, you have some kind of Company Name field — the field that says "XYZ Builders" in a previous letter where a moment ago it said "ABC Fireworks" — what is the name of that field, what table is it literally in, what is its field definition and type, and what table occurrence does it belong to when you view it on the Letters screen?

Link to comment
Share on other sites

For whatever use it may serve you, here is how I would have set things up:

 

Company file has a scrollable portal of letters; the portal shows a couple fields, such as Creation Date and maybe Status or something. To create a new letter I would scroll down to the always-empty bottom portal row (the relationship between Company and Letters would allow for the creation of related records in Letters), and I would enter today's date in the Creation Date field. Then there would be a button next to the fields in the portal row. The function attached to the button would do this:

 

Go to Related Record [Letters, via layout "Letters"].

 

I would click the button and I would be on a brand new blank Letters record (well, blank except for the creation date). At the top would be all the Company fields that I want to appear at the top of my letters "XYX Company", "100 Address Road", "Cityname, ST, 00000" — these fields would be fields in the Company file, not in the Letters file. They would be set up to not allow the cursor to enter them in Browse Mode. View Only on this layout.

 

The "New Record" button would be renamed to "New Letter Same Client". It would run the following script:

 

Set Variable [$Company ID, Letters::Company ID]

New Record/Request

Set Field [Letters::Company ID, $Company ID]

Commit Record

 

If you want to create a new letter to some other company, you go back to the Company screen and bring up the company and click into its letters portal.

Link to comment
Share on other sites

Hi Ahunter3,

 

What scripts are attached to the buttons you click: Scripts are just simple “Go to Layout” scripts.

 

What relationships and fields are in use on the letters layout” From the Letters elationship the fields BodyText, Subject, MessageName.

 

What does "click on new record" mean in the context of the letters screen, is that also a button, and, if so, what script is attached to IT: It’s just a New Record/Request Script. Same as going under the Records menu to New Record.

 

 

At the top of your letters, you have some kind of Company Name field — the field that says "XYZ Builders": From the Company relationship CompanyName.

 

In a previous letter where a moment ago it said "ABC Fireworks" — what is the name of that field, what table is it literally in: From the Company relationship CompanyName.

 

What is its field definition and type, and what table occurrence does it belong to when you view it on the Letters screen: CustomerName is an indexed Text field in the Customer TO.

 

Does this help?

 

Bill

Link to comment
Share on other sites

OK. Now...how is the Company table related to the Letters table? If you're in the Letters table and you just create a new Letter record (button = New Record/Request), that Letters record would not ordinarily be related to ANY Company table. The fields at the top for Company Name, which you said belong to the Company table, would be blank... not only that, but you would not be able to type into them, you'd get an error message.

 

So there must be something unusual about how the relationship is set up.

Link to comment
Share on other sites

Hello again AHunter3,

 

RE: "If you're in the Letters table and you just create a new Letter record (button = New Record/Request), that Letters record would not ordinarily be related to ANY Company table. The fields at the top for Company Name, which you said belong to the Company table, would be blank... not only that, but you would not be able to type into them, you'd get an error message":

 

When I click New Record/Request the CompanyName is there and I can type into it! Any change I make there is also shown in the CustomerName field on the Customer layout.

 

RE: "How is the Company table related to the Letters table":

 

I find there is no direct relation between them. The Company table is related by the "Letters Log" relationship, which is a duplicate of the Letters table and they are related by ContactID. There is no link between the "Letters Log" relation and the Letters table!

 

I have a feeling we just got somewhere? True!

 

Bill

Link to comment
Share on other sites

Go to the Letters layout. The Letters layout has, as its native table-occurrence (the one you see when you go to Layout Setup, where it says "Show records from:"), the table occurrence also called "Letters", right?

 

At the top of this screen, you have fields that describe the company, like company name, etc, yes? In Layout Mode, double-click Company Name. At the top of the "Field/Control Setup" dialog box that comes up, on the right where it says "Display data from:", what table occurrence is it displaying data from? It either is or it is not "Company". Is it?

 

If it is, click the actual word "Company". You should get a pop-up menu that lists possible table occurrences, "Company" being among them. Is it listed under "Related Tables", or is it listed under "Unrelated Tables"?

 

If it is listed under unrelated tables, it should not display field contents; it should instead display "". (Unless "Company Name" was a field set to global storage, globals in unrelated tables are still accessible). Since you say you actually see the Company Name, though, I'm assuming you'll find it listed under related tables instead.

 

If it is listed under related tables, the two tables (table occurrences, to be precise) are related. It may not be a direct relationship. Company may be related to this Letters Log table (the purpose of which I don't understand), which in turn is related to something else which is related to something else which is related to Letters, or something like that. If you can't trace the relationship, take a screen shot of your relationship diagram and post it here.

 

But your problem lies in how your relationship is constructed -- by what pathway Letters is "seeing" related Company data.

Link to comment
Share on other sites

Hi AHunter3,

 

I guess my level of forum user is not able to post a screen shot. If you would give me an email address I would send it to you that way. My address is BigZilla@myway.com.

 

In the time being here are the answeres to your other questions.

 

Go to the Letters layout. The Letters layout has, as its native table-occurrence (the one you see when you go to Layout Setup, where it says "Show records from:"), the table occurrence also called "Letters", right? YES AND IT SAYS LETTERS.

 

At the top of this screen, you have fields that describe the company, like company name, etc, yes? In Layout Mode, double-click Company Name. At the top of the "Field/Control Setup" dialog box that comes up, on the right where it says "Display data from:", what table occurrence is it displaying data from? It either is or it is not "Company". Is it? YES, THE DISPLAY DATA FROM READS: CUSTOMER AND THE FIELD SELECTED IS COMPANY NAME

 

If it is, click the actual word "Company". You should get a pop-up menu that lists possible table occurrences, "Company" being among them. Is it listed under "Related Tables", or is it listed under "Unrelated Tables"? IT IS LISTED UNDER RELATED TABLES!

 

Thanks again AHunter3,

 

Bill

Link to comment
Share on other sites

My email is ahunter3@earthlink.net

 

 

If it's a small file and/or you don't have confidentiality issues, you can just email me the file. Then I can look for myself instead of grilling you! :)

 

But if that doesn't sound appealing just send me the screen shot.

Link to comment
Share on other sites

Thanks for the file attachment. I'm posting it here for the benefit of anyone else following along.

 

Companies is related to Letters via the join of each of them to the Navigation TO which is offscreen in your screen shot but which you indicate is related to Letters and to Company.

 

That's your screwup. You need a direct relationship between Company and Letters. You need to create a field in Letters, "Company_ID" and connect Company and Letters via an "=" relationship that allows for the creation of related records via that relationship on the Letters side. When you do that, you'll be prompted to give a name to a new Table Occurrance for Letters — let's call it "Company Letters".

 

Then you need to change the home TO for the Letters layout to "Company Letters", and double-click all the fields EXCEPT those in Company and switch their relationship at the top of the Select Field dialog from "Letters" to "Company Letters".

 

Finally, go back to my post, #3 in this thread, and implement your creation of new letters per each company using that strategy.

 

(I don't know what fields are linked to connect Company to Navigation TO and Letters to Navigation TO but it can't possibly be good)

Link to comment
Share on other sites

Just to clarify, Company should be related to Letter by:

 

Company Letter =

Company::CompanyID = Letter::CompanyID

 

In this structure, each Company has its own list of Letters, and there's no chance of mixing them up.

 

If you're doing mass-mailings, then you may find a merge letter technique a little more useful, where a merge letter is generated (in a field of a Letter table), and you then select Companies or Contacts that should receive that Letter. This was my approach here, although there are methods that don't require custom functions:

 

http://fmforums.com/forum/showpost.php?post/165807/

Link to comment
Share on other sites

Hi AHunter3.

 

I can see from the changing of the server that some posts were lost. But here is what you last sent me and what I've done.

 

FROM AHunter3: " For whatever use it may serve you, here is how I would have set things up:

 

Company file has a scrollable portal of letters; the portal shows a couple fields, such as Creation Date and maybe Status or something. To create a new letter I would scroll down to the always-empty bottom portal row (the relationship between Company and Company Letters would allow for the creation of related records in Company Letters), and I would enter today's date in the Creation Date field. Then there would be a button next to the fields in the portal row. The function attached to the button would do this:

 

Go to Related Record [Letters, via layout "Company Letters"].

 

I would click the button and I would be on a brand new blank Letters record (well, blank except for the creation date). At the top would be all the Company fields that I want to appear at the top of my letters "XYX Company", "100 Address Road", "Cityname, ST, 00000" — these fields would be fields in the Company file, not in the Company Letters file. They would be set up to not allow the cursor to enter them in Browse Mode. View Only on this layout."

 

I PERFORMED THE ABOVE AND IT WORKS GREAT!

 

I've had trouble with the following.

 

FROM AHunter3: "The "New Record" button would be renamed to "New Letter Same Client". It would run the following script:

 

Set Variable [$Company ID, Company Letters::Company ID]

New Record/Request

Set Field [Company Letters::Company ID, $Company ID]

Commit Record

 

HERE'S WHAT I DID:

 

Set Variable: Name: $CustomerID:

Value: "Set Variable [$CompanyID, Company Letters::CompanyID]" Repetition: 1

New Record/Request

Set Field [Company letters::CustomerID; $CustomertID]

Commit Records/Request []

 

This does not work. It makes a new record but the record is blank. If we can get this working I think we’ve got it. What do I need to do Ahunter3?

 

Bill

Link to comment
Share on other sites

Congratulations on getting the first part working! Now let's look at the script you're still having problems with:

 

Set Variable: Name: $CustomerID:

Value: "Set Variable [$CompanyID, Company Letters::CompanyID]" Repetition: 1

New Record/Request

Set Field [Company letters::CustomerID; $CustomertID]

Commit Records/Request []

 

a) The first script step (the part in red) is unnecessary. You don't have to declare your variables up-front before you do things with them later, like you do in some scripting environments.

 

b) Look at the parts I bolded. You've got $CustomerID, then $CompanyID, and then finally $CustomertID. As I said, the first occurrence is unnecessary, but the variable that you set to the value Company Letters::CompanyID has to be spelled exactly the same way as the variable that you use as the value to set Company Letters::CustomerID to after creating the new record, and that's not what you did.

 

c) Look at the field names in blue. Those should also be the same. I'm going to guess that you don't really have a CustomerID and also a CompanyID field in the Letters table (& therefore not in the Company Letters table occurrence) and this is just a typo, but the field does have to be the same.

 

Your final result should look like this:

 

Set Variable: [$CompanyID, Company Letters::CompanyID]" Repetition: 1

New Record/Request

Set Field [Company letters::CompanyID; $CompanyID]

Commit Records/Request []

Link to comment
Share on other sites

Hi AHunter,

 

My script now reads exactly as yours and the same thing is happening. I run the script and get a new record thats blank except for the ActionID and CreationDate. If I try to leave the record I get the dialog box "This operation cannot be performed etc.

 

Bill

Link to comment
Share on other sites

In the Company relation the CompanyID field is Number - Indexed, Auto-enter Serial, Unique.

 

In the Letters relation CompanyID is a Text field - Indexed

 

Bill

Link to comment
Share on other sites

This may seem like an unprofessional way to do it. But what about making a script that duplicates the record and then clears the 5 fields where new information will be entered? Doing it this way saves the Company name and makes a new "ActionID", "CreationDate" and "CreatedBY" and best of all It works. Is there a reason I should know about to not do it this way?

 

Bill

Link to comment
Share on other sites

Hi AHunter,

 

I don't mean to be a smart alick, I'm just trying to find something that works and keeps my boss of my back. I can't seem to get what you mentioned working and my boss doesn't want me to send the file. So I'm between the "Rock and a Hard Place" they talk about. I just wanted to know if there is a reason not to do the duplicate and clear field idea I mentioned earlier. Other than it being a rather "Ham & Egg" way of doing it.

 

I hope you didn't take me wrong, I very much appreciate all the time you've taken to help me and the last thing I want to have is for you think I don't care. As I said, I just want it to work and not have it mess my file doing it that way!

 

Any other ideas you have I will be more than happy to try. I guess you don’t think much of my duplicate idea!

 

Thanks AHunter,

 

Bill

Link to comment
Share on other sites

There's nothing inherently wrong about duplicate-and-clear; I've used it as a strategy myself when the folks I work for want so much of an existing record cloned that it makes more sense to duplicate it and nuke the parts they want empty than to make a new record and fill the fields they want to be the same.

 

Things you want to watch out for with dupe-and-clear:

 

a) Every time you add a new field to the table and make it available for data entry, you have to add it to your duplicate-and-clear script.

 

b) Any field that has auto-enter options may need to have additional script steps if you want the duplicated version to carry those values forward, because the auto-enter options will overwrite the values of the record being duplicated.

 

c) Any field that is supposed to contain unique values or values highly specific to the record, but which don't get those values via auto-enter options, have the opposite problem and need to be cleared out after duplication. Example: modifier, modification date, date letter sent, that kind of thing.

 

 

The method I instructed you in works (generically) and should work in your solution (specifically) if you've followed the instructions correctly and your database structure is as you've described it.

 

Since your boss does not wish you to email the file itself, consider printing the scripts out to PDF and sending me the PDF. Field defs also. There's a lot about your file structure that looks awful (sorry to say but true)...there may be a valid reason to have a relationship between a "Navigation TO" and other TOs but I'm suspicious that you've got relationships where they don't belong (we already know you had the absence of relationships where they did belong), and I'm also just curious to see what's bollixed up at this point (this is probably not a healthy curiousity). My guess is that you've pointed Set Field to the wrong table occurrence (perhaps the wrong table entirely) or that there's something odd and ill-considered about the field def of CompanyID in Letters (validation formula or some such thing).

 

This is a train wreck. I've been on the other side of train wrecks (you should see the thread in which I first tried to set up an X11 environment from source code in the days before Apple's own X11), so don't take it personally, but the chaff to wheat ratio of communication between us has been abysmally bad.

Link to comment
Share on other sites

From the Customer file “ABC Crane Co.” is in the CompanyName field. I enter a date into the DateCreated field (Company Letters Portal) and click on the button, discussed earlier. And a new record is created in the Company Letters layout for “ABC Crane Co.”

 

The problem is it goes to the first record in the Company Letters layout and not to the new “ABC Crane Co.” record. I have to go to the last record to get to the “ABC Crane Co.” record. What do I need to do to go directly to the newly created record?

 

Thanks again for your help,

 

James

Link to comment
Share on other sites

From the Customer file “ABC Crane Co.” is in the CompanyName field. I enter a date into the DateCreated field (Company Letters Portal) and click on the button, discussed earlier. And a new record is created in the Company Letters layout for “ABC Crane Co.”

 

The problem is it goes to the first record in the Company Letters layout and not to the new “ABC Crane Co.” record. I have to go to the last record to get to the “ABC Crane Co.” record. What do I need to do to go directly to the newly created record?

 

Thanks again for your help,

 

James

 

Hello James...are you working on the same project as bigzilla / Bill?

 

What function do you have assigned to the button, and where is the button on the layout?

 

Button should do this: Go to Related Records ["Company Letters" show only related records, using layout "Company Letters", match current record only]

 

Button should be located: inside the portal, appearing in the top portal row when viewed in Layout Mode (and in every valid portal row when viewed in Browse Mode). Button can be to the left of the fields in the portal (as long as it's still entirely within the portal row) or to the right, or between fields; or the button can BE a field (although not the field you're reserving for typing into, which was Creation Date in the example given)

Link to comment
Share on other sites

Hey AHunter3

 

I know Bill from here on the forum but I don't have anything to do with his project. I've been following along as it is something i've wanted to add to the solution i'm working on. Sorry, I did kind of jump into the middle.

 

James

Link to comment
Share on other sites

Hey again AHunter3,

 

Yes sir that did it. I didn't have the "match current record only" checked. This stuff is real easy when you know what you're doing!

 

Thank you for your help,

 

James

Link to comment
Share on other sites

Hi Ahunter3

 

Since we're on a roll I thought I'd ask another question. Is there a way to set up the “Company Letters” portal in the Customer layout so that the button is not in the portal row, but above the portal?

 

What I would ideally like is to click the button above the portal and have today’s date auto entered into the next available DateCreated field. I'd then like to put a field into the portal called “ActionType” which is a drop-down-list to select Letter, Email, Call, etc.

 

This is what I'd like to happen. When I click the button, today’s date is auto entered and the curser goes into the “ActionType” field brings up the pop up list. Then once a selection is made from the pop up list, you automatically go to the new record in the Customer Letters layout. I think I'd want it set up so that a selection must be made from the pop up list to get a new record.

 

Then after a record is created. You could either click a button of the portal rows in the Customer layout, or click the portal row, whichever you think best and it would take you to that previously created record.

 

How would you do that?

 

Thanks a lot,

 

James

Link to comment
Share on other sites

What I would ideally like is to click the button above the portal and have today’s date auto entered into the next available DateCreated field. I'd then like to put a field into the portal called “ActionType” which is a drop-down-list to select Letter, Email, Call, etc.

 

The field you enter data into to generate the related record need not be DateCreated. ActionType would do just fine. Just scroll down-portal to the blank record at the bottom, enter the ActionType, then click the button to go to the resultant newly-created record.

 

But if you really wanted a script to do it all for you from a button up above the portal:

 

Version 1:

 

Go to Field [PortalRelationship::Anyfield in Portal]

Go to Portal Record [Last, no select]

Go to Field [PortalRelationship::ActionType]

Allow User Abort [Off]

Pause [indef]

Go to Related Record [PortalRelationship, current rec only, showing related only, using layout CompanyLetters]

 

 

Version 2:

 

Set Field [Anytable::GlobalField X, ""]

Show Custom Dialog ["Enter Action Type", fields: Anytable::GlobalField X, "OK", "Cancel"]

Go to Field [PortalRelationship::ActionType]

Go to Portal Record [Last]

Set Field [PortalRelationship::ActionType, Anytable::GlobalField X]

Set Field [Anytable::GlobalField X, ""]

Go to Related Record [PortalRelationship, current rec, show rel only, layout CompanyLetters]

 

Version 2a:

 

Same as Version 2 except you actually put GlobalField X on the layout with a dropdown value list you want to use for ActionType and a "Go" button next to it:

 

Go to Field [PortalRelationship::ActionType]

Go to Portal Record [Last]

Set Field [PortalRelationship::ActionType, Anytable::GlobalField X]

Set Field [Anytable::GlobalField X, ""]

Go to Related Record [PortalRelationship, current rec, show only, CompanyLetters]

Link to comment
Share on other sites

Hi again AHunter,

 

I tried version 1 and 2 and neither of them worked. Version 3 didn’t seem like what I wanted so I didn’t try it. Here’s what I did (using Bigzilla’s naming convention).

 

Version 1:

 

Go to Field [Company Letters::Date Created]

Go to Portal Row [Last]

Go to Field [Company Letters::ActionType]

Allow User Abort [Off]

Pause/Resume Script [indefinitely]

Go to Related Record: Company Letters

Show record using layout: Letters Entry

Check marked “Show only related records” and “Match current record only. This is the same “Go to Related Record” I used for the button in the portal row and it worked perfectly.

 

Here’s what happens after I click the above button.

 

1. The ActionType pick list comes forward

2. I make a selection from Pick list and the selection is displayed, as is the Date Created.

3. After making the above selection nothing happens.

4. I then pressed the Enter key (Remembering the pause step) and nothing happens. I manually went into the Letters Entry layout and a new record was created but the script did not take me there and it was not on the Company Name selected.

 

I don’t like the pause idea if it requires you to click the Enter key. It just seems like an unnecessary step and complicates things

 

I don’t remember exactly what happened when I tried version 2 but it didn’t work either. But it didn't created a letter. I also did not like the Custom Dialog. It just seamed like a pain if you used it a lot.

 

I can stay with the button on the portal row if need be, but would much rather have it the other way.

 

I know, you wish I stayed away right?

 

James

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use