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

How to change calculation to work like I want


IGotIt
 Share

Recommended Posts

I have a solution here in the office and have no idea where it came from. But it has a notes filed that I would like to change to work differently, but I don’t know how to change the calculations to do so. They are shown below.

 

Right now once you enter information and press enter key or tab out of the field. You can no longer make any changes to what you entered. Kind of a pain when you think that you can’t even go back and change a spelling error.

 

The other thing is you cannot clear just one note. You either clear all of them or none of them. Also not a real super idea.

 

I simple want to be able to make changes and clear what I want cleared.

 

Thanks for your help.

 

Milo

 

Here are the calculations.

 

Notes field: Text with Auto-enter Calculation.

 

// See note at bottom of calculation

 

Let (

[

notes_field = Notes; //

notes_compare = NotesCompare; //

 

note_tag = "On " & Get ( CurrentDate ) & " at " & Get ( CurrentTime ) & " - " & Get ( AccountName ) & "¶ ";

note_divider = "¶----------------------------------------¶";

tag_colored = TextColor ( note_tag ; RGB ( 150 ; 150 ; 150 ) );

 

text_color = TextColor ( " " ; RGB ( 0 ; 0 ; 0 ) );

prompt = text_color & TextColor (

"Enter your notes here by simply typing in the information.¶

Older notes cannot be deleted...¶" ; RGB ( 56 ; 117 ; 215 ) );

 

result = prompt & note_divider & tag_colored & notes_field & Substitute ( notes_compare ; prompt ; "" )

];

 

//-------------------------------

 

Case(

LeftWords( notes_field; 2 ) = "clear notes"; "";

Left( notes_field; Length ( prompt ) ) = prompt; notes_compare;

notes_field notes_compare; result;

notes_compare

 

)

//-------------------------------

 

)

 

/*

You must use an auto-enter calculation on the

NotesCompare field. This will allow the execution

of commands like "clear notes"

 

Case(

IsEmpty( Notes ); "";

Notes

)

 

*/

 

 

NotesCompare field: Text with Auto-enter Calculation.

 

Case(

IsEmpty( Notes ); "";

Notes

)

 

Thanks for your help,

 

Milo

Link to comment
Share on other sites

I don't see a way to neatly allow editing the field using a modified version of this calc. There may be one, but....

If you need more flexibility, why not store notes in a separate table? (one note, one record). You can create, view, edit and delete them in a portal, view them by account, whatever.

 

See sample

 

kjoe

 

PS. If i understand correctly, the calculation you posted only works well when the whole field content is selected and cleared (not committed) before typing in new notes text. Otherwise, since "result" adds the current contents of NotesField to NotesCompare, you'll quickly see many duplicate notes. So you'd probably see this field linked to some kind of script.

Link to comment
Share on other sites

Very good "Kjoe-Of-Bird-Dom,

 

Many of you may not know that Kjoe first made this solution for an autobahn society and rather than paying him in cash, no reflection on the quality of his work,smiley-wink they gave him a bird, a vulture to be precise! As it happened the society was sponsoring a bird race that afternoon. So he entered the bird in the race and it won. He was so pleased that he entered it in another race and it won again. The local paper read. Kjoe’s bird is out front.

 

Kjoe's wife was not real pleased with this kind of publicity and told him not to enter the bird in any more races. The next day the local paper headline read. Wife scratches Kjoe’s bird!

 

This was too much for his wife, she told him “Get rid of the bird. So he gives it to FileMaker. The local paper after hearing the news, posted the following headline “Kjoe gives FileMaker the bird”!

 

Sorry Kjoe, it if wasn’t me someone else would have told the story. At least I left out how your bird “Ate up the competition”, literally!smiley-surprised

 

Milo, sorry for horn-in on your thread, but some times the truth must tell...smiley-tongue-out

 

Harry

Link to comment
Share on other sites

no reflection on the quality of his work

ya rub it in man. Shout it from the rooftops thank you very much. My employer's still not on to me. I'd like to keep it that way.

 

kjoe

Link to comment
Share on other sites

Hey Milo,

 

I don’t disagree with your assessment because we all have our likes and dislikes. Some even differ in the flavor of ice cream they like if you can believe that. Hey, some even go so far as posting a Vulture as an avatar and calling it by another bird. So anything can happen!smiley-laughing

 

I for one like the Kjoe method and I’ll tell you why. I like a designated area for each note. My solutions will vary depending on the client, but typically I’ll make a notes area something like 3 to 3 1/2 “ wide and 2” deep. This allows for a portal with three text areas and each text area will hold three lines of Arial 11 point text, Approx. 35 words. I don’t go smaller than 11 point Arial, but that’s up to you.

 

What I like about this method is you can see the last three notes! No matter the amount of text that is entered into each one, you see the last three notes sorted by the most recent on top. This way if Vera Verbose typed in a book report. You can click on the field and read the entire report. But you still have 3 points of history. BTW you want the text field set up so that helf of the 4th row of text is cut in half so you know there is more text to follow. Yah you can set up a tricky colored arrow to show when there is more text, but save yourself a trip!smiley-wink

 

My point is this. Unless you have an unusually large notes field. Using the Vera Verbose theory, the only note you will see is Vera’s. You then have to scroll down past her note to see the next note and if that note is important.

 

Kjoe’s way you see,

 

9/10/2006 11:22 AM By: Jim Beam

Mary’s out with sick husband but will be back

at 2:00 PM this afternoon.

 

9/9/2006 3:02 PM By: Carol Crow

Mary Stork’s is still out of office with sick

Husband.

 

9/8/2006 1:13 PM By: Vernon Vulture

Mary Stork is the only one we can talk to about

Packaging and she is out for awhile tending to

Her sick husband.

 

You see what I mean. It gives three immediate visual notes of history… YeeeeeeeeeeeeeeeHaaaaaaaaaaaaaaaaaa Kjoe!

 

Harry

Link to comment
Share on other sites

That's funny, I never thought of it that way. I saw Kjoe's example and never even thought of it as something smaller, or being able to see "Three immediate visual notes of history…" as you put it.

 

You're right, it is a better way to handle notes. So YeeeeeeeeeeeeeeeHaaaaaaaaaaaaaaaaaa Kjoe!smiley-laughing

 

Milo

Link to comment
Share on other sites

Okay thanks!

 

 

I can't PM or even edit my posts.

that would be because you registered as a guest member. why your profile does not say so, but rather calls you a junior member (which is from the old site) I don't know. If you upgrade your membership (means pay $23 a year) you'll get more functionality, amongst which is the possibility to download attachments.

 

kjoe

Link to comment
Share on other sites

Hi Kjoe,

 

What I’ve tried to do is include you notes system into my solution. Here’s what’s happening.

 

I run the Add Note script and here is what happens.

 

1. The curser enters the NoteContent field and I type in a note. The CreationTimeStamp, ByWhom and other fields are empty.

2. I press the Enter key and I get the dialog “ContactID” is defined to contain unique values only. You must enter a unique value.” When I click the OK button the CreationTimeStamp, ByWhom, DataID and NoteID are filled in. But I’m obviousely not going anywhere until I press the Revert button to clear the record.

 

Here’s what I’ve done.

 

1. I added the fields from your "NotesItems Table" to my "Details Table" except for the DataID table which I assumed would be the same as my ContactID field.

2. I added the field Data from your "Data Table" to my "Contacts Table" (Contacts and Details tables are related by ContactID) and again did not include your DataID for the same reason as above.

3. I created a new relationship to my "Details TO" called Details_contactNotes.

4. I created a new layout called NotesItem to show records from Details_ContactNotes.

5. I added the Details_ContactNotes portal to my contacts layout along with the same fields shown on your Data layout.

6. I added your AddNote script as follows.

 

Set Variable [$ID; Value.Details_ContactNotes::ContactID

Freeze Window

Go to Layout [“NotesItem” (Details_ContactNotes)

New Record/Request

Set Field [Details_ContactNotes::ContactID;$ID

Go to Layout [original layout]

Go to Field [select/perform; Details_ContactNotes::NoteContent]

 

Can you see from what I’ve done where I’ve gone wrong?

 

Thanks Kjoe I sure appreciate your help,

 

Milo

Link to comment
Share on other sites

It sounds like you have validation ("value must be unique") switched on in the Details_ContactNotes table for the field ContactID. As this field is the 'foreign key' to the relationship, it should not be unique. It must only be unique as the primary key in your Contacts table. So my guess is, that you should switch off this validation in the Details_ContactNotes table.

The rest, at first glance, seems OK. Let me know if this helps, if not we'll just dig deeper....

 

kjoe

Link to comment
Share on other sites

Uhh the field "data" in my sample is just there for demo purpose. It does not do anything. Just take care the ID's are in place and the timestamp too, so the portal will work and can be sorted by timestamp descending.

BTW I'm a little worried: did this details table already exist? what's it for? can the notes really piggyback on it? why did you have the contact ID validated?

 

I shall have to learn to think before I post. One of these days.

 

kjoe

Link to comment
Share on other sites

Hi Kjoe,

 

The ContactID should not have been validated and that took care of the validation issue. A couple things I don’t understand. On your solution you only see a new note area of the portal when the AddNote button is clicked. I see under Edit Relationships you only have the Delete records selected. When I set up my Details_ContactNotes relationship like that, it will not give me a new portal record. I have to have the Allow creation of records checked for mine to work and then you have that extra record sitting there which I don't like. Is that because I have the script wrong?

 

As for the Details_ContactNotes. I have as you know a Details TO. This TO has the Street, City, State, FirstName, LastName etc. This is where I put your fields. From this Details TO I have the relationships of Details_ContactAddress, Details_ContactPhone, Details_ContactPeople and Details_ContactNotes. Are you saying I should have a notes relationship with only your fields in it?

 

Thanks Kjoe,

 

Milo

Link to comment
Share on other sites

When I set up my Details_ContactNotes relationship like that, it will not give me a new portal record.

 

Hi Milo,

 

The only thing I can think of is that your "add note" button is inside the portal, it should be on the outside. Inside you put the delete button (delete portal row).

 

Are you saying I should have a notes relationship with only your fields in it?

That would be the best thing to do because contact details such as their phone number are quite different from notes about a contact.

If you're viewing the address details in a portal, you're getting all the note records for that contact ID in that portal. They will have empty adress details fields (same goes for the notes). Of course this can be worked around by creating extra relationship keys but unless you have a very good reason to combine them, I certainly wouldn't in this case and go for a separate notes table.

 

kjoe

Link to comment
Share on other sites

Hi Kjoe,

 

I made a new Notes TO just like yours and everything is working great.

 

I have another question if you don't mind. As you know I have a Details TO. that have the fields ContactID, StreetAddress, City, State,ZipCode, NameFirst, NameLast, NameTitle, Department, Position, PhoneExtension and PhoneNbr.

 

Then I have the relationships off of the "Details Table"

Details_ContactAddress, with a portal in Contacts with the address fields.

Details_ContactPhone, with a portal in Contacts with the Phone fields.

Details_ContactPeople, with a portal in Contacts with the people fields.

 

Would I be smart to do the same thing with these relationships that I did with your Notes and Make a seperite Table for each of them?

 

What do you think? I you think I should go with new TO's, I'd also appreciate you telling me the problem with having it as I do right now.

 

You're the best kjoe and I really appreciate your help,

 

Milo

Link to comment
Share on other sites

You're welcome and no, I don't think so. Are we talking about a main record which holds company data and the details table holds the various persons that work there? If that is so, you could make do with one TO. I don't understand why you want to show only phone numbers (without a person you can reach by it) or only adresses (same). Anyway, the relation for all would be by ContactID. So there really is no need for all the separate TOs that point to the same table.

 

Unless I really misunderstand what you're building here. In that case, please post a screen shot of your main contacts layout that shows all these portals, or post a stripped version of your solution.

 

kjoe

Link to comment
Share on other sites

Hi Kjoe,

 

I don't understand why you want to show only phone numbers (without a person you can reach by it) or only adresses (same).kjoe

 

Yah you hit the nail on the head. I've had nothing but trouble trying to figure out how to do this. I have the Details_ContactPhone portal on my customer layout with the FirstName, LastName and Department and it of course has all of the people who work for, lets say Bozo Circus. Many of the people have four or more numbers like Direct Phone, Cell Phone, Home Phone, Pager, email etc. I'm trying to figure out how to handle this.

 

Milo

Link to comment
Share on other sites

Ok what you need to do is try your hand at an "entity relationship diagram". this is simply a schematic that you make with pen and paper or on a whiteboard (i love those!) or flipover. On it, you make little boxes (that represent "entities", mostly this will turn out to be tables) and arrows, that are relationships between the entities.

 

So there's companies, that have multiple affiliates and these in turn have multiple phone numbers. The companies may have multiple departments, and multiple locations.

Every time you encounter a " X may have multiple Y", draw an arrow from X to Y.

 

In the problem at hand there will be an arrow from companies to employees and an arrow from employees to phonenumbers.

Now you can have two relationships that reflect your ERD: one from companies to employees, one from employees to phonenumbers.

Only there's a snag: you can't view all employee phonenumbers directly in the portal that shows all employees in the companies layout.

 

So, now you face a choice: go on or go back.

 

go back, no separate phone numbers table:

- decide that after all you're not going to need an unlimited amount of phonenumbers for a contact, just, say, five or six tops. Then you can just add these to the employee table, hard-designating six fields to home, work, cell, pager, fax, secretary. And view them without fuss in the one portal (although these portal rows will become a little crowded).

 

go on:

You need not one but two sets of keys, one set for each arrow in the ERD.

I suggest that you rename the fields in all tables that you currently call "contactID" into "companyID", because really that's what you're relating the employees by.

Then define an auto-enter serial number in the contact details table. Call this one ContactID.

 

Then if you don't have one already, create a phone numbers table. give them an auto-enter serial PhoneID. You'll probably never use it but it's good practice to give any table a primary key field, you never know when it will come in handy. More importantly, put a field ContactID in the phone numbers table.

Then create the relationship between "details" and phonenumbers.

 

Now there are at least two ways to get to view all the phonenumbers. One is, put a "go to related records" button in the details portal. This will take you to a full screen layout of each "detail" or employee record. And there you can make a portal to the phone numbers. It would seem a logical step as there could be much more information about this person, for instance, a notes portal :). With a little trickery you could probably set a preferred phone number and view that in the details portal on the companies layout.

 

The second way is to view the phone numbers in a portal in the companies table. please take a look at the DuoPortals sample by CobaltSky (post n# 4 here). Actually that thread may be of broader use to you also.

 

hope this helps.

 

kjoe

Link to comment
Share on other sites

Hi Kjoe,

 

I’m using your Notes solution and I’m having a problem I hope you can help me with.

 

On my Contacts layout the “ContactID” is from the ContactsDetails relationship and that ID is shown correctly on the layout for each contact.

 

When I click the Add Note button nothing happens. However if I go to the Notes layout a record has been made but the ContactsDetail::ContactID field does not have the ContactID number. The ContactID field says “ContactsDetails::ContactID”.

 

Here is my script.

 

Set Variable [$ID; Value: ContactsDetails::ContactID]

Freeze Window

Go to Layout [“Notes” (Notes)]

New Record/Request

Set Field [Notes::ContactID;$ID]

Go to Layout [original layout]

Go to Field [select/perform; Notes::NoteContact]

 

Can you see from the script what’s causing the problem?

 

Thanks Kjoe,

 

Milo

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use