Combining text fields

[Angela10]

I am working on a employee evaluation database. The Employee file contains one record per employee. The Evaluations file contains many evalautions filled out for each employee. The files link on the employee ID no.

My question is this, let's say an employee, John Doe, has 7 evaluations. Each evaluation has 9 performance factors (PF). Each PF has a numeric rating and comments. I can average the ratings and give a average per PF, but I would like to combine the comments for each PF into it's own PF total comments field. Right now the comments are sitting in a portal with no hope of sliding.

Can someone give me a pointer to which direction I could go? Looping script, and copy paste into a new field called PF1_allcomments. I sure wish an Aggregate field like Sum (field) w/text result would work.

If anyone has a suggestion or can point me to some threads or even some keywords to search for, I'd really appreciate it.

[Angela10]

Well, I tried the value list trick and it sorta worked. But, the number of characters allowed per value list item must be limited by FMP and the extensive reviewer comments are truncated. So, doesn't work.

Back the drawing board. I don't know if what I'm trying to do is even possible (combining field data from multiple related records into one field in the master file), any thoughts?

[-Queue-]

In FM 6, items have to be 60 characters or fewer, in groups of 20 characters or fewer, or else they are not fully indexed and will truncate, as you've seen.

What you would have to do is, from the Employee file,

If [not IsEmpty(Evaluations::serial)]

Set Field [allcomments, ""]

Go to Related Record [show only related, "Evaluations"]

Perform Script [External, Loop and Set comments]

Refresh Window

In the Evaluations file, the Loop and Set comments script:

Go to Record/Request/Page [First]

Loop

Set Field [Employee::allcomments, Employee::allcomments & "

[Angela10]

Thank you Queue, that was the help I needed. Got it to work perfectly and even created a looping script to run for all the Performance Factors on the evaluation. Yeah!!!

I was stuck on the field definition thing rather than the scripting, so thanks for pointing me in the right direction.

- A

[Angela10]

New problem, records that do not have comments in the field are creating blank lines in the compiled comments field. So, do I need another if statement in the loop and set script? Obviously not my strong suit, these loops and ifs.

If anyone can throw me a bone, thank you. I've got to get reports to the boss by noon today.CST.

Thanks - A

[-Queue-]

Change the Loop and Set comments script to be

Go to Record/Request/Page [First]

Loop

If [not IsEmpty(Trim(comments))]

Set Field [Employee::allcomments, Employee::allcomments & "

[Misho]

Hi All,

I found useful this script in combining my date fields from related table to a field in the main table ( FM7 ) but I have the following problem

* I'm filling the related table records using a portal

* I'm showing the combined text field in the same layout

How can I make the combined field to synchronize using the script when I go outside of the portal ?

Is there any way in FM7 to use aggregate and concatenate my dates using field type "calculation" in stead of calling scripts ( like I do with sum aggregate )?

Thank You for your help ( as it is always fast and accurate )

Misho

[-Queue-]

The first step after nullifying the allcomments field is to go to the related records. Whether or not you use a portal should be irrelevant since it will go to a related layout during this step.

When you are in the related table, you then set the parent record's allcomments field. So putting the field on the same layout as the parent's portal should be fine also.

You will have to trigger the script using a plug-in such as EventScript or use an Update button manually activated by your users.

There are no aggregate date functions, but you could create a value list based on related dates, starting from the current table, and use an unstored calc of ValueListItems( Get(FileName); "relateddates" ) to display them as a return-delimited list.