Concatenating the text fields from every portal row into a text field in the parent record.
In my CRM database I want to automatically generate a brief description of what is in the related records for a particular customer phone call event.
Rough database layout is
Contact table ( holds customer details)
Activity Table ( related by contact nuber to the contact table) Holds details of type of activity. ie Technical call, Place order, Price and Delivey
Line Item Table (related by Activity number to the Activity table) Holds the details of each item that was requested for price and availability. This table is only used when the enquiry is regarding price and availability. There are other table used to hold the specific information relevent to other types of enquiry. The problem and solution is comon to all the tables so the answer to one can be applied multiple times.
The senario is customer calls wanting to know the price and availability of 3 products.
Sales person selects add new P&D enquiry, which adds a record to the activity table and places the time, date, etc in a notes field. This notes field is shown in a portal under the contacts record (activities Tab) and is a brief summary of what the enquiry was about.
After adding the new activity record, FM opens a layout bassed upon the activity table that has a portal to the Line items. as the part numbers are entered into the line items portal (adding related Line item records) FM completes the record adding the price stock levels etc.
This layout then is a complete record of the enquiry but is too large to be shown in a tab on the contact layout.
What I want to do is have a save button on this layout that when clicked will add the part number from each portal row to the notes field on the activity.
I can do this by finding all line item records related to this enquiry(Activity) then looping through each one a use Note = note & ", " & Part_number field.
The problem with this is when someone looks at that Activity record later if they click the save button and not the return button then the Note field will duplicate itself etc.
Is there a nicer way to do this? Could the list of products from the related field be calculated as opposed to scripted. so that regardless of how may times the record is viewed and saved, the note field would allways equal the original time and date (saved in a seperate field) plus the concatenation of the part numbers.
in advance for your help and ideas.