13 Replies Latest reply on Jan 2, 2012 7:19 PM by rkevwill

    Simple calculation for a text box.



      Simple calculation for a text box.


      I have a scrolling text box, that will be used quite a lot for contact notes. Here's what I want to do in Laymans Language. Tried to check out the tools, but apparently I don't understand some of them.

      I am making a button. Clicking the button will select the text box. Then, a cursor goes to the very top left of any text thats in the box. Then we have two returns, to make a double space. Next step is the cursor goes back all the way to the top, places the current date, and does one return, ending with a blinking cursor.

      In summary. I want the newest entry on top, with the date first.

      I did this in the old style Filemaker, but not proficient (yet) to do it at present. Tips please?


        • 1. Re: Simple calculation for a text box.

          Another solution to think about would be to add a separate Notes table with date and text fields .  Then have a portal on your layout to the Notes table.  That will give you a separate record for each entry into Notes and can be sorted by date.

          • 2. Re: Simple calculation for a text box.

            Actually, in this particular use, I prefer to have a scrolling notes field, right on the same layout. However, I do have use for a notes table, on another related layout, and have a draft of that. I may need to "polish" that layout after I finish this scrolling notes function on this layout.

            I may change my mind on the scrolling notes function, but there is a reason for this process. In this particular field, there is no reason to search by date, as there most certainly is in the related layout I mentioned.

            • 3. Re: Simple calculation for a text box.

              Set Field [ yourField ; "¶¶" & yourField ]
              Set Selection [ start: 0  ; end: 'blank' ]
              Insert Calculated Result [ Get ( CurrentDate ) & ¶ ]
              Set Selection [ start:  yourField ; Position ( YourField  ; ¶ ; 1 ; 1 ) + 1 ; end: 'blank' ]

              • 4. Re: Simple calculation for a text box.

                I also agree with Mark.  I can not tell you how many times I have tried the 'one field' technique only to change it.  I use one table for NOTES and it attaches to all of my tables which contain notes.  The Notes table would contain a field for each parent ID and relate to the parent with 'allow created' so notes can be added (via portal on parent layout or via script to track a process, such as emailing someone; notes can also be a list of actions.  One of the large benefits is that FileMaker loads all fields when serving up information.  By offloading notes to another table, it improves speed.  But it also makes it easy to search notes no matter which table spawned it and this is powerful when a User wants to find something they said and they do not remember if they put the note in one specific invoice or whether they put it on the customer table.

                rkevwill, if you find you need to then move this data to proper fields (using notes table), you will find yourself in a major nightmare because Users have free rein on that text and they can change the dates (and mess them up making them invalid).  I have handled large migrations where this has occurred and I can tell you from vast experience that parsing this type of data will be your worst nightmare.  If you think the Users will keep your pretty carriage returns, you will be surprised how they can mess it up.

                It takes about the same amount of time and effort to set it up right (using another small table) as it does to set it up in less than opimum manner.  And if you use the same notes table for all your tables then it is even easier than easy.  ;-)

                ADDED:  Also, by giving a User free rein to other entries, they can delete and change other people's notes.  Just things to consider as you move forward.

                • 5. Re: Simple calculation for a text box.

                  With a portal, you get a scrolling list of notes right on your layout and you can specify a sort order that places the most recent note at the top of the list.

                  With a portal, the script looks like this:

                  Freeze window
                  Set variable [$ID ; OriginalLayouttable::IDfield ]
                  Go to Layout [portal table's layout]
                  New Record/Request
                  Set Field [Portaltable::IDfield ; $ID ]
                  Go to layout [original layout]

                  A date field in this portal table would auto-enter the current date and be displayed with a text field in your portal.

                  The script you describe would work like this:

                  Set Field [LayoutTable::NotesField ; Get ( CurrentDate ) & "¶¶¶" & LayoutTable::NotesField ]
                  Set Selection [ LayoutTable::NotesField ; Start position: Length ( Get ( CurrentDate ) + 2 )]

                  But personally, I'd use the portal to a related table, much less messy way to manage notes in my opinion...

                  • 6. Re: Simple calculation for a text box.

                    This will not be used my multiple users, just me. HOWEVER......far be it for me to contradict anything you guys are suggesting. I just want to make sure the last few notes are showing on my main form in Browse. I'm unable to spend time on it tonight (cause if I get started, I just know I'm not gonna stop, and I need to go to bed sooner than later). Actually, I'm having a ball re-learning all this, from so many years ago, but the new version is SO much more feature rich than the mid 90's versions.

                    Anyway, I will do the portal, and once I get started, I am sure I will be back with questions. Things are actually coming together.

                    • 7. Re: Simple calculation for a text box.

                      Well, I couldn't resist playing with it a bit before bed. Here's where the terminology starts the brick wall. I've included a screen capture, of what the main form is going to look like. I tried the Portal field, (to replace the Notes field you see). When creating the portal field to replace the notes, its asking for the related field. Thats where I'm lost. Do I put the portal field on another layout? The one thing that i must have, is the list of notes (from the portal I assume) right where the Notes field is currently. (I had taken it out, but replaced it to show you where I must have it.

                      Lets make the question more concise, to get me to the next step.

                      1. Do I put the portal field on a separate layout, and if on this layout, where do I put the related notes? Just take me through this step, and if possible, use the field names I have here on the jpg.

                      BTW, I have heard mention several times on parent ID's / serial numbers. I have not created that yet, I assume you gentlemen suggest having that with each parent record. There are several more fields I need to put on this form, already placed on other layouts, like emails, bdays, website, etc. I'll get to them when I finish the important things.

                      • 8. Re: Simple calculation for a text box.

                        First you do need an ID field for the parent table, TEst1.  Just create a new field, type = number, options = auto enter serial number.  If you already have a bunch of records in this table the ID field will be blank for them, you can place the serial number field on the layout, go to browse mode- go to the first record - click in the ID field - select records/replace field contents and select the serial number option to populate all the records.  You can then go back to the field definition and set the serial number to start after the last number, you will also want to specify that it is a unique value.

                        Now when you set up your notes table you will want to include Notes::TEst1ID as a number field and Notes::Notes as your text field.  Then in the relationship set TEst1::TEst1ID = Notes::TEst1ID.  You could also set up fields as autoentered date or time stamp fields to record when you created each note.

                        Place the portal on your layout in the place of your Notes field with the Notes::Notes field inside it.  If you like you can put the date or time stamp field above the note field so it would look like this:



                        Notes about this converstation



                        Notes about that conversation



                        you can sort the portal in decending order by date so they scroll chronologically from most recent down, using Phils solution above to make the one you are currently entering on top.

                        • 9. Re: Simple calculation for a text box.

                          Note too that you use the Portal Tool to add a portal to your layout instead of using the field tool to add a field to it. This tool will then pop up a dialog box where you can specify the details of the portal--including what fields you want to list in it. After you've dismissed the portal setup dialog, you can ressize and resposition both the portal and the fields with in it. Portal Setup defaults to a table like arrangement of your fields but you can rearrange them after the fact to get the arrangment Mark has described.

                          • 10. Re: Simple calculation for a text box.

                            Just an updated note here. I have made some progress working briefly with the portal tools etc, that you all have been kind enough to provide me. I am wrapped up with end of year work, as well as helping make preparations for our first grandchild.  My guess is I will get back on this full time, week between Christmas and New Years.

                            Be back pestering you guys then!

                            • 11. Re: Simple calculation for a text box.

                              Sorry, have been away for a bit, (cant stay away from this long) and also needed to buy the app, as my trial was running out. Before I took my trip, I did creat the serial number field. I'm trying to picture the portal in my head. When creating the portal, a box comes up to relate the portal to another field. From that comment, am I correct I need to creat a second field, where I enter the information, OR......since I am creating the portal field within the original area where my notes field is (refer to the jpg above), do I enter my notes right there? So, what field to they relate to, when I enter a note? Does it actually become a separate note in another layout? Is this where I place and relate it? I assume they are separate notes then, related by serial number?

                              I will be doing this field, and making sure its exactly what I want, before going on to the much more simple fields I need to ad. BTW, I see that this portal will work for Assistant assignments too, if it works out the way I want. Just repeat the exercise, with a new portal field, on a new layout (I think)

                              • 12. Re: Simple calculation for a text box.

                                Did you set it up as Mark suggested, "Now when you set up your notes table you will want to include Notes::TEst1ID as a number field and Notes::Notes as your text field.  Then in the relationship set TEst1::TEst1ID = Notes::TEst1ID.  You could also set up fields as autoentered date or time stamp fields to record when you created each note."

                                So you related the TestID in both tables on the =.  While there, notice below where it says 'allow creation of related'.  If you want to type into the Notes portal and add a new notes record, you need to check 'allow creation of related' on the Notes side only.  Do not show the Test1ID field in your Notes portal - it isn't necessary.  And when you just begin typing a note itself, FM will insert the current test1ID into the Notes::test1ID field automatically - you do not need to do anything.   :-)

                                • 13. Re: Simple calculation for a text box.

                                  I apologize to you folks, for not being active on your suggestions earlier last month. Year end business issues, plus the birth of our first grandson took me away from this project. I will be on it often this week, and be assured, I will indeed be bothering your folks.I am carefully going to look through the info above again, and get started on the two situations I need that notes portal. Once I do one properly, I will then do the next one. Back sometime this week. I desperately need to bring this database system I used to use, back from the dead.