GetFieldName script trigger question

[the_kid]

Howdy,

I'm trying to create a script trigger. I have several fields and associated "comment" fields. Like this:

abc

abc_comment

defg

defg_comment

The trigger would be if the abc_comment field has text in it, put a "Y" in the abc field.

I'm sure it can be done, but I'm not smart enough to figure out how to read just the first part of the field name and parse out the "_comment" part. I'm currently using the function below to get the name of the active field.

GetFieldName ( Evaluate(Get(ActiveFieldName)))

Any help is greatly appreciated.

P.S. I guess I could rename all the fields so that they have the same length before the "_comment" and use the LEFT function, but if there is a simpler way...

[Vaughan]

if the abc_comment field has text in it, put a "Y" in the abc field

Wouldn't a calculation field be better for this?

The calculation would be:

Case( not isempty( abc_comment ) ; "Y" )

[comment]

The description is too abstract; my impression is that the structure is flawed and that each pair of fields should be a record in a related table.

[LaRetta]

I have several fields and associated "comment" fields.

When you find yourself creating several 'like' fields ... stop. It usually means they should be related records. In this instance, you would probably have two fields in a Comments table - Type and Comment. The Type would probably hold what you now name your field - abc or defg. The table would of course also have a unique auto-enter serial CommentID (smile) and creation timestamps, UserName (who created the comment) etc.

[the_kid]

Thanks to all for your input. I'm intrigued with the relational table suggestion because it may well solve a looming reporting requirement later down the road.

However, I'm not grasping how it would help in my situation. Please see the attached file. The "required" layout is to be used like a checklist for the data input people. All input fields must be visible because they will act as a visual cue to check the contract for certain clauses. I understand that I can put these elements in a value list and create a related table, but a dropdown or menu in a "portal" arrangement is not sufficient.

One deliverable is to present the client with the full checklist ("required" layout) showing him/her that we checked the 20+ elements on the layout against their contract and that only those with a checkmark and comment are contained therein.

The problem with my approach (and they may be numerous) is that a future deliverable is to send the client a followup e-mail that contains only those elements that have comments - not the entire checklist. Example from the attached table:

Dear Sir, your contract <<contract>> has a <<field name that is checked>> clause which states <<field name that is checked_comment>>. (addressing all checked fields)

I can see where a related table would solve this problem, but can it also address the "full checklist" requirement?

Again, many thanks for taking a look and offering your opinions. I really value your help.

test.zip

[LaRetta]

All input fields must be visible because they will act as a visual cue to check the contract for certain clauses.

So are the comments generic to the Type? Or are the comments entered by Staff (against each Type) when viewing the contract? Is there only one Type = Humans with its one unique comment and one Type = Animals with its unique comment?

[the_kid]

Hi LaRetta,

The comments are specific to the type. Staff will enter one comment per type, where appropriate. That is, if there are humans involved in the contract there will only be one unique "human" comment. Same goes for all the other types.

Thanks.

[LaRetta]

I did not ask the question very well (where is Comment when I need him, LOL), my apology ...

So Staff is reviewing a Contract and they have a checklist with the types - Animal, Human ... and they type in a comment on Human which is their own wording about how 'human' is used in this particular contract.

OR

Do you predetermine the comment for Human and by checking Type = Human, that predetermined comment is assigned to the contract.

[the_kid]

No apologies necessary. I really appreciate that you're taking the time to look into this.

It is the first example you gave. Our staff review a contract and if they find something that has "humans" in it, they will enter free form text in the human_comment field. An example might be "Contract requires human subject protocol approval by an approved reviewing committee." Once done, (and back to my original question) a check mark automatically displays in the adjacent field (human). BTW, Vaughan came up with a nice solution to this, although I had to change the field type from text to calculation for every instance... Not a big deal.

Conversely, if in their review of the contract they find nothing to do with animals, then both the animal and animal_comment fields remain blank.

I hope this provides some insight. Again, thanks loads for the help.

[LaRetta]

I would use an approach such as this (attached). All Checklist items appear in the portal whenever you add one to the Checklist table. Comments are typed next to those that apply, ignoring those that don't. In the new related AwardsItems table, records are created only when staff types into the comment. Note there is a script trigger on the layout (OnRecordLoad). This sets the global in the Checklist table with the AwardsID whenever you change records.

And now, in addition to you having a table with only selected checklist items, you also have each comment Type as a record so you can create sub-summary reports by Type (sorry, I misnamed it Item but you know what I mean, LOL). Is this getting you closer? Structurally, it is sound and that is the most important place to start. The rest can be worked through from there.

testMOD.zip

Edited March 1, 2012 by LaRetta

[the_kid]

LaRetta,

This is wonderful! I had one of those "ah-ha" moments the instant I looked at the relationship graph. I am glad I asked the question, but am now embarrassed because it falls into the "why didn't I think of that" category.

A hearty thanks and a zesty dancing chili pepper for you. :chili:

[LaRetta]

I ADORE the red dancing chili pepper! I'm glad it worked out for you. :^)