Combing All Notes For a Record

[Frink1234]

I have a database with clients and each client has a "Notes" field that contains notes from a conversation on a particular date. Those notes show-up in a portal on the individual client screen - the Notes field is contained in a related table "Client Notes".

My goal is to combine all the individual notes for a particular client number into one field for export to a separate text file.

I have 2 thoughts on how I can achieve this.

Option A:

1. Perform Find to retrieve all records for a particular client number within the Client Notes table

2. Go to first record

3. Select and Copy contents of Notes Field

4. Go to "All Notes" Field <===created to hold the combined notes

5. Paste to All Notes field

6. Go to next record

Continue the above script adding each successive notes field to the the All Notes field until the last record for a particular client has been reached. This step I'm not sure how to do - ending the script when the last record for a client has been pasted into the All Notes field, so if this sounds logical I could use some help on this step.

Option B:

Can I make a calculated field that equals the combination of the "Notes" field for all records for a particular client? That would seem like a more elegant solution, I'm just not sure how to get there.

Thanks.

[TheTominator]

Option B:

Can I make a calculated field that equals the combination of the "Notes" field for all records for a particular client? That would seem like a more elegant solution, I'm just not sure how to get there.

If your profile of Dev 7 is accurate, then you can't do the following calc using the List() function. But if you are using a newer version (I'm not sure when List() became available, definitely in FM 8.5) you can use

List(Client Notes::Notes)

Edited November 17, 2010 by Guest

[comment]

My goal is to combine all the individual notes for a particular client number into one field for export to a separate text file.

Option C:

1. Perform Find to retrieve all records for a particular client number within the Client Notes table;

2. Export Records

[Frink1234]

Unfortunately, for this database I am using FM7. Although I do have FM10. The problem is that the database became corrupt and I cannot open it in anything but FM7. There is a possibility I could export the Notes records and import them into a new FM10 database which would allow me to perform the calculated field function - I'll check into that.

Otherwise, I would be stuck doing Option A. Can you help with the last part of the script? I think it needs a line like:

If exists next record ==> continue with script, else end.

[TheTominator]

If you want one export file per client, use comment's Option C above.

If your requirement is to export client info and the consolidated notes data all at once for many clients (and you are not skilled at XSLT), then I can recommend the following pseudocode. (I hope I didn't use any functions unavailable in FM7)

Create a layout named "Only Notes" based on the Notes table. Put only the Notes field on the layout.

Go to Layout [Client Info]

Go to record/request[first]

Loop

if [Count(Client Notes::Notes)] > 1

Go to related record[show only related records; From table "Client Notes::Notes"; Using layout: "Only Notes" (Notes)]

Copy All Records

Go to Layout [Client Info]

Paste[select; Clients::All Notes]

End If

Go to record/request[next; exit after last]

End Loop

[comment]

The problem is that the database became corrupt

Whoa, stop right there. If your file is corrupted, you should perform recover (using the latest version of Filemaker), then try to export ALL your data from the recovered copy - and nothing else. Attempting to work with the corrupted file is playing with fire.

[Frink1234]

Believe me - I have tried the recover utility with several different versions of FM and the file is unrecoverable. It works fine in FM7 though. Fortunately, this database is being phased-out so if I can just consolidate the individual client notes into an exportable text file, I will then be able to import this into the new (unfortunately non-FM) database in a separate tab called "Old Notes".

I am going to try the script by TheTominator as that seems to be what I need for now.

Thanks.

[TheTominator]

Whoa, stop right there. If your file is corrupted, you should perform recover (using the latest version of Filemaker), then try to export ALL your data from the recovered copy - and nothing else. Attempting to work with the corrupted file is playing with fire.

I second this recommendation.

And while I'm at it, I third it.

Export all of the data to plain text files (Merge format so that it has the field names) so that you can import it into a FM10 database as-is.

Then add the simple relationships and scripting you need to consolidate the Notes.

Adding more info in the form of Scripts and field data may expose and expand an underlying problem that will make your file unreadable even with FM7.

Edited November 17, 2010 by Guest

[Frink1234]

It is not worth doing the export to bring into an FM10 database seeing as this database is being retired. It has 28 tables and I would have to do an export and import for each - and since I can't open the database in FM10, I would have to re-write it. The main table alone has over 200 fields, many of them calculated. I just need to get the data out of that one field.

[Frink1234]

Ok, I have created a layout "Summary of all Notes" and created a script that lets me perform a find for all notes of a particular client number and export that as a file. That's pretty much all I need to do.

Thanks for the help. ;-)