Identifying Newly Created Record

[LaRetta]

Well, I love the feature which allows 'copy value from last record' but it's causing a problem. Staff enters service in the Service db on a form. I need to use a form because so much information needs to be displayed about each Client, their documenting requirements, Contracts, allowable procedures, remaining balance etc.

Most of the data is the same as the last record. Most times, only one field changes (Date, ClientCode, Units or Location) so I have all fields set to "use last value". Problem: When I 'add new record' it looks like nothing happened. I DO have a record count, but I don't think users will be looking at that each time. I thought of creating a calc to display in red "NEW RECORD" identifying it, but I can't think of how. Maybe I could take advantage of 'modification time?" I can't figure how to put this calc together and what it should key on. Any ideas out there?

[spb]

>> Most of the data is the same as the last record. Most times, only one field

>> changes (Date, ClientCode, Units or Location) so I have all fields set to

>> "use last value".

You could pick the field most likely to be changed by the user (ClientCode, Units, or whatever), and take away its "use last value" function. This would force the user to always fill in that field, but if this field is never normally empty, it would only be empty in the case of a new record. Then you could make a calc field where the calc is something like:

If (IsEmpty(ClientCode), "This is a new record", "")

Your new field with have the phrase only if it's a new record, otherwise it'd be empty. You could make this field huge and cover your layout (perhaps arranged behind your entering fields, or in some large white area, if any), 72 point red text. Whenever a new record is created, the text would show until the ClientCode field was filled in, then it would go away.

Steve Brown

[LaRetta]

Thanks for the idea Steve. I thought of that, but there were so many fields and since most remained the same, I didn't want to take away the auto-fill.

But, Live Oak's answer to another question of mine gave me an idea and it works great! I placed a calc field under a rectangle button which says "New Record". I then set the tab order on the layout to tab to it first. When the user creates a new record, it tabs to the rectangle and the field New Record shows in large red. Then, when the user tabs to the data entry portion, it disappears again behind the rectangle. I've also been reviewing ideas of John Osborne (Scriptology) for hiding fields & changing field background colors on tab into or out of. Thanks you all!

[CobaltSky]

Hello LaRetta,

I suspect there is a drawback with the solution you are envisaging, because if the calc field retains its value, then when users press the tab key on first entering any record, the 'New Record' sign will appear (since that field is first in the tab order).

It will become rather self-defeating if users frequently see the New Record sign flashing up on records which are decidedly elderly.

Another alternative you could consider would be to create an unstored calculating field with the formula:

Case(Status(CurrentRecordModificationCount) < 1, "NEW RECORD")

---

This will produce the flag when the record has not been modified in any way (and will continue to display it until the user leaves the record and comes back again, at which time the unstored calculation will refresh).

If you want to use this approach, but want the 'New Record' sign to disappear the instant the user modifies any field, then you will have to include trigger references to each of the fields on the layout in the formula, thus:

Case(YourField1 + YourField2 + YourField3... and Status(CurrentRecordModificationCount) < 1, "NEW RECORD")

---

...where you include a full list of the fields that the user can change on the layout where I've put YourField1 and YoutField2 etc (it doesn't matter if they are text, number or time fields, so long as the 'sum' of them all will never be zero). With this approach, your large bold 'New Record' text will disappear the instant anything on the layout is changed the first time.

If you want to get really sophisticated, you could use this approach in conjunction with the visibility trick where the unstored calculation produces (instead of the 'NEW RECORD" text) the primary key for the relationship which makes the visibility portal show. Or you could reference a graphic in a container, and size it to fill it in the background so that new record layouts are always a different color or 'watermarked'. Whatever

[LaRetta]

THANK YOU! I knew "New Record" would display during browsing of other records, etc. I copied the layout and thought I'd just have this layout only for data entry of new records -- until I learned more and could fix it properly. I've used 'inserting KEY fields in globals to display graphics', etc. in Approach but didn't think of it, and still wouldn't know how to apply it in this situation. I will use your second example until I learn a lot more. The data entry person was just going crazy ... we're back to using 5.0 (had 6.0 trial) and it doesn't have portals (?). I wanted to show a Self portal so she could see what records were just entered. I had to solve it quickly. Bless ya!

[LaRetta]

It worked but not quite. Maybe I'm missing something.

Code I used:

Case(ClientCode + StaffCode + ServiceDate + ProcedureCode + Units + Location and Status( CurrentRecordModificationCount) < 1,"New Record")

The New Record stays lit throughout the entry (not really a problem) and New Record stays lit when I arrow back through records until I get to a record entered before the current data entry period. Also, not really a problem. Any way I can fine-tune it or do I have my formula tweaked?

[CobaltSky]

Hmmn... that's right - the modification count doesn't increment until you exit the record, and the initial data entered counts as modification 0.

What would help is, if your users use a button (rather thane the keyboard command) to create new records, and if you attach it to a script which includes an 'Exit Record/Request' step and then a 'Go To Field' step after the 'New Record/Request' step. This will at least ensure that the flag will disappear the first time the user exits the record (because their first change to the data will then count as modification 1).

As regards your move back to v5, it definitely does support portals (you'll find them in FileMaker versions right back to v3.0). Self-join portals are a common feature of many of the FMPv5.x databases I've worked on.

[LaRetta]

Thanks, I'll add a button to add new record and attach the scripts. Where do I find the add portal command? In 6.0 it's on the Insert menu. I've hunted everywhere!

[CobaltSky]

From v5.5, the Portal option was included on the Insert menu when you are in layout mode. In v5 and earlier, the easiest way to get a portal is by selecting the portal tool in the status bar (it is next to the button tool and ithas four kind of square bracket corners as its symbol [ ] ).

Click on that to select it and then drag with the mouse across part of your layout to form a rectangle where you want your portal and you're away.

[LaRetta]

I had only searched the drop-downs and didn't find it. Thought it was like Format Painter ... not in 5.0 version. It's the pits to be ignorant! After you said it was there, I did a silly thing ... I went to HELP and found it!

[LaRetta]

I wrote a script: New Record/Request; Exit Record/Request;Go To Field [staffCode] -- and attached it to a 'New Record' button (with above formula). It still kept flagging new record throughout the entry. So I re-checked that all fields were in the above formula. New record stayed lit.

However, since I now know I can use portals in 5.0 (smile) maybe I'll just place a self-portal above the data entry line. I also tried Live Oaks (and your) suggestions about using a global container as background. Maybe I'll keep this simple portal solution until I get my Scriptology book and learn what I'm doing Really wish I coulda done this tho! I even selected all calcs, etc. as a last try. Thanks for your time on this; the good news is that even though I failed, I learned a bunch (what are global containers, calc containers, Case and more)!

[CobaltSky]

Hi LaRetta,

OK, well I have come up with an alternative approach which is a little more work to set up, but works much more smoothly and instantly updates. It works perfectly in all cases when the new record is generated by script/button, plus it also works if a new record is created using a keyboard or menu command, provided the user is on the most recent record. If they are part way through the file and they make a new record without using the button, then the New Record flag won't appear with this method.

If that sounds like it will serve your purposes and you want to give it a try, you'll need to brace yourself because it requires several extra fields, a relationship plus a few extra script steps.

To enable you to see how it works and get a feel for what is involved, I have adapted one of the demo files from my web site to provide a working example for you. If you want to download the demo file and take a look at it (it is password-free), you can do so by clicking on the following link:

http://www.nightwing.com.au/FileMaker/NewRecordAlert.zip

[LaRetta]

Thank you so much for the file. IT WORKS! My formulas & scripts were correct but it still wouldn't work. I then noticed major differences in how the fields (gStoredRecordID, RecordID, LastRecordID and PreviousRecordID) incremented between your file and mine. You began with zero records. I began with 4,000 and THEN applied your process. When I signed on to each db for the first time, the mis-match was obvious:

YOURS:------------------Sign on-----1st New----2nd New

gStoredRecordID--------109-----------92------------111

RecordID--------------------92----------111------------112

LastRecordID----------------0------------92------------111

PreviousRecordID---------91-----------92-------------111

MINE

gStoredRecordID------33623-----------36----------33625

RecordID---------------------36-------33623----------33626

LastRecordID----------------0------------36-----------33625

PreviousRecordID---------35------------36-----------33625

The 36 records were the new 'test add records.' I then deleted all records from Service db and the NewRecord.flag SHOWED! Why? Hmmm, the gStoredRecordID was the problem. I deleted the amount from it and, lo and behold ... I have the perfect "New Record Alert" solution!!! Bless you for all your help (and I sure learned a BUNCH from this!

One final question ... why is my RecordID number so high and why doesn't it reset in a db with no records? Is there a compact or reindex function I can't find? Some db's, even when they delete a record, hold the space ... is that why the number is so large? Regardless, I'm really tickled

[CobaltSky]

Status(CurrentRecordID) is a unique internally generated record identifier rather than a serial number or record number, and can be non-contiguous. The advantage of ther RecordID and the Status(CurrentRecordID) function is that it remains constant regardless of changes in the file - including re-sorting and deleting of records.

There is an article on the FMI kbase which explains why the numbers are so high in your database. It is at http://www.filemaker.com/ti/104663.html

The method used in the sample file I provided should work regardless of the size of the number or whether there are gaps in the sequence - it is being used only as a record identifier.

In this respect it is in contrast to the Status(CurrentRecordNumber) function which returns the position of the record in the file - and changes with deletion of records, sorting or even find/omit changes to the file.

The idea is that, as with the sample file, the additional fields (gStoredRecordID, RecordID, LastRecordID and PreviousRecordID) won't appear on any layouts, so it shouldn't matter that the numbers don't start at 1. If you want a number showing, then it would be best to create either a serial number or a calc using Status(CurrentRecordNumber) - depending on whether you want the number to reflect the current find/sort order or not.

Meanwhile I'm glad you got the solution to work and that it is now doing what you want!