Autofill field using field content from previous record

[db_tragic]

I want to give the user an option to copy visit notes from the previous dated visit, into the current 'visit notes' field (useful if the content will be very similar, or identical).

 

I plan to have this as a script attached to a button next to the 'visit notes' field. It is an optional function only(i.e. not to occur automatically when new notes for that client are entered).

 

Basically, I'm not sure how to set up ( a calculation I presume) to basically copy the visit notes from "visit date -1" (and only if there is content in that field).

 

TIA

[Klypto]

With just throwing untested suggestions out there:

 

 

Two instances of the notes table, with the first one being connected to the field entry on your layout.  

Notes Table 1's client ID field connects to Notes Table 2's client ID field and has a "does not equal" connection between the note id in table 1 and the note id in table 2 .

The relationship is sorted by ID's or time created /entered to ensure that the latest record is first.

You should now have access to the latest visit entry for that client that does not include the current entry that you are about to enter.

 

Then use a script to import that information from instance 2 to instance 1 when they click the button.

[LaRetta]

It is so much easier and more flexible if each note is a record. By combining notes, you open possibility of a User scrambling someone else's entry. And searching, sorting by date, printing by date range or User - all become impossible. It is far easier to create it properly using a notes table ... if you wish to consider it just ask and we will help you through it.

[Lee Smith]

Basically, I'm not sure how to set up ( a calculation I presume) to basically copy the visit notes from "visit date -1" (and only if there is content in that field).

 

TIA

There are a couple of ways to accomplish this. Do a search for Site: FMForums.com  FileMaker Notes in Google.

 

ps, I agree with LaRetta suggestion.

[LaRetta]

Anyway if you wish to copy value from prior record then try button with

GetNthRecord ( tableName::VisitNote ; Get ( RecordNumber ) -1 )

[Klypto]

Anyway if you wish to copy value from prior record then try button with

GetNthRecord ( tableName::VisitNote ; Get ( RecordNumber ) -1 )

 

But wouldn't that get everything confused?

 

Like Susan visits today and they wrote the note "Susan dropped off a package" in susan's visit notes.  Then tomorrow Tommy visits and the autofill in the note for Tommy's client record notes says "Susan dropped off a package", but tommy only ever comes to pick up packages and his name isn't Susan.

[LaRetta]

AUTO-FILL and PREVIOUS in the title of the post suggests that db_tragic wants value from prior notes field. You are correct that some of the wording would need to be changed. If OP wants User to go select a specific record to replicate, or if OP wants prior record for only that client ( for example ) then they will need to clarify. :-)

As mentioned, a notes table is better choice and this request would be simple Duplicate Record.

[db_tragic]

Hi folks, loving the discussion here! Apologies for not getting back sooner. The construct is this (just to clarify the scenario). I have Clients, who own Horses, and I treat those horses (I'm a human/animal chiropractor). Notes are recorded in the Visit Notes table, in one of several fields that live there - in this case, 'TxNotes' field. Often, a subsequent visit requires the same treatment protocol as the previous visit. So when I am visiting a horse, and I open up a new record in that horses related Visit Notes, I want a button next to the TxNotes field, which when tapped, simply copies the information in the TxNotes field, (for that horse) from the previous visit. Hopefully that should clarify what I am aiming to achieve. Thankyou for all your input.

[Klypto]

Just replace the word "client" in my first post with "horse" then.

[LaRetta]

 So when I am visiting a horse, and I open up a new record in that horses related Visit Notes

 

What does this mean?  Are you on a Horses layout and view/add Visits in a portal?  Or do you go to the Visits table and add a new Horse record there and set the HorseID in the Visits table when you do?

 

If you are in a portal row then script will handle it fine as:

 

Go to Portal Row [ Previous ]

Set Variable [ $value ; Visits::TxNote ]

Go to Portal Row [ Next ]

Set Field [ Visits::TXNote ; $value ]

 

If you are viewing a list of Visits from the Visits table then we need to know whether you are viewing the record you want to copy and is it always the record immediately prior (have you Narrowed your found set using GTRR or find to only that Horse)?

 

Hey Klypto, I'm not dis'ing your concept but for something like this, adding schema probably isn't necessary so I'd like to explore those aspects first.

[db_tragic]

Hi Laretta,

 

I start on a Client layout, which has a portal showing a list of horses owned by that client. I then tap on the horse in the portal list that is being treated that day.

 

This takes me to the Horse Details layout, which includes a portal containing a list of dates corresponding to previous appointment dates for that horse (based on related records in the Visit Notes table).

 

There is also a button below that portal titled "Create New Visit", which then opens up a layout (based on the related "Visit Notes" table), in this case containing empty fields, from the Visit Notes table (related to that horse of course), where I can then enter information into the relevant fields (fields include; Date, Pre-Tx Notes, Tx Notes,Trigger Points, Stretching, Recommendations amongst others).

 

So to clarify, I will have just created a new Visit Notes record for that horse, and decide that I want to duplicate the "TxNotes" field only, from the previous visit (and only the previous visit) into today's new "TxNotes" field for that horse. I don't usually need to duplicate any other of the fields from the pervious visit's notes for that horse - just that one field in particular. 

 

When I chose the word "Autofill" in the title of the post, it was in the context of having a script that would automatically fill the TxNotes field in the newly created Visit Notes record for that horse (as described above), when a button that had the script associated with it, was tapped, not automatically when a new Visit Notes record for that horse was created. Sorry for the confusion.

[MartieH]

sounds to me that you need a script that would find the all notes records for the horse in question, the go to the last record minus one (since the newly created record would be the last record), set a variable with the value form the TxNotes field and then go to the new record (the last one) and set the TxNotes field with the variable. if you have already started to enter values into the TxNotes field for the newest record, you should use insert text rather than set field so that you don't lose what you have already entered.

 

Martie

[LaRetta]

Another option would be simply to look through the Notes and find the one you want.  I would assume that, if you were viewing Note #6 for this horse and you wanted to duplicate the last treatment, it would be looking to the horse's 5th note.  So new notes would look at the last note for horse always (except the one you are currently creating, no matter when or why you are on different records.  If that would be okay then a single script step should work nicely along these lines ... User clicks a button and script would be (and I simplified your table/field names to make it easier for me):

 

Set Field [ Notes::TXNote ;

Let ( values =
    ExecuteSQL (
    "SELECT TxNote
    FROM Notes
    WHERE horseID =? and noteID <?" ;
    "" ; ""; Notes::HorseID ; Notes::NoteID )
;
GetValue ( values ; ValueCount ( values ) )
)

[LaRetta]

]
.... sorry once in a code I can't type anything below it.  This forum breaks that way periodically.  But I couldn't leave it without an ENDING BRACKET for the script step, LOL

 

I am no MySQL expert so some of you might tweak that further.  It would be nice to isolate only the next-to-last value to begin with instead of requiring further evaluation on a list.

[db_tragic]

OK the MySQL stuff is way out of my league at the moment.

 

I did try this script based on some of the above suggestions, however I clearly have something wrong. The steps seemed to make sense to me, but I guess scripting isn't exactly an intuitive art form!

 

My thinking was this:

1. Set the Variable

2. Get the Tx Notes field contents from the previous (n-1) visit as the variable

3. Set the variable into the current (most recent, new) Tx Notes field

 

 

Set Variable[$TxNotes]

If [GetNthRecord (Visit Notes::Tx Notes; $TxNotes) -1)]

Set Field [Visit Notes::Tx Notes; $TxNotes]

End If

 

Only included the "If" component cause I couldn't seem to find a way to just enter the GetNthRecord part on it's own.

 

The user could of course just open up the last visit notes from the portal in the Horse Details layout, view the earlier notes, and create a new set of notes for the current visit (possibly include a copy/past scenario), but I'd prefer the original idea I'm chasing a solution for.

[LaRetta]

All you need to do is create your new record then if you want to use the prior treatment note, run that single-step script I gave you except change the names to your names.

 

If it did not work then please post your table, field names and I will give you the correct syntax or zip and attach your file?

It is 'Set Field [ your current treatment notes field in your new record '  with the following except replace with your field names:

 

Set Field [ Notes::TXNote ;

Let ( values =
ExecuteSQL (
"SELECT TxNote  (the red should be your treatment note field without the table name in front of it (so drop the Notes:: part off)
FROM Notes (this is the name of your table)
WHERE horseID =? and noteID <?" ; (the horseID and NoteId should be your field names for those IDs but drop the table name off again)
"" ; ""; Notes::HorseID ; Notes::NoteID ) (here you need to include the table name)
;
GetValue ( values ; ValueCount ( values ) ) (nothing needs to be done here)
)

]

 

Really, it is a single step script.  Understanding it would be worthwhile.

Edited March 23, 2013 by LaRetta

[db_tragic]

Hi LaRetta, 

 

Thankyou for that. I'll be giving it a go in the next couple of days - though I was hoping it would be a bit simpler than all this (famous last words!)

 

Cheers

 

Chris

All you need to do is create your new record then if you want to use the prior treatment note, run that single-step script I gave you except change the names to your names.

 

If it did not work then please post your table, field names and I will give you the correct syntax or zip and attach your file?

It is 'Set Field [ your current treatment notes field in your new record '  with the following except replace with your field names:

 

Set Field [ Notes::TXNote ;

Let ( values =
ExecuteSQL (
"SELECT TxNote  (the red should be your treatment note field without the table name in front of it (so drop the Notes:: part off)
FROM Notes (this is the name of your table)
WHERE horseID =? and noteID <?" ; (the horseID and NoteId should be your field names for those IDs but drop the table name off again)
"" ; ""; Notes::HorseID ; Notes::NoteID ) (here you need to include the table name)
;
GetValue ( values ; ValueCount ( values ) ) (nothing needs to be done here)
)

]

 

Really, it is a single step script.  Understanding it would be worthwhile.

[db_tragic]

Hi Laretta,

 

So much for "...in the next couple of days....."!!

 

Was having some difficulty with this script - the horse ID (actually "_pk_Patient" in my database wasn't showing as found. Thought I'd upload a copy of the db for you to have a look at if you wouldn't mind,

 

Thanks,

 

Chris

 

[edit - db temporarily removed]

 

 

All you need to do is create your new record then if you want to use the prior treatment note, run that single-step script I gave you except change the names to your names.

 

If it did not work then please post your table, field names and I will give you the correct syntax or zip and attach your file?

It is 'Set Field [ your current treatment notes field in your new record '  with the following except replace with your field names:

 

Set Field [ Notes::TXNote ;

Let ( values =
ExecuteSQL (
"SELECT TxNote  (the red should be your treatment note field without the table name in front of it (so drop the Notes:: part off)
FROM Notes (this is the name of your table)
WHERE horseID =? and noteID <?" ; (the horseID and NoteId should be your field names for those IDs but drop the table name off again)
"" ; ""; Notes::HorseID ; Notes::NoteID ) (here you need to include the table name)
;
GetValue ( values ; ValueCount ( values ) ) (nothing needs to be done here)
)

]

 

Really, it is a single step script.  Understanding it would be worthwhile.

 

[LaRetta]

You removed the file?  No problem ... if you still are stuck just let us know and we'll help you through it. 

[Lee Smith]

 

 

[edit - db temporarily removed]

What do you mean, temporarily removed? It usually isn't necessary to remove an attachment, just post your revised file to a new Reply to your Thread.