Hi Jiri,
Yes the database is Oracle 10g. I am aware that you cannot create view forms from an underlying view table and tried the 1st option you mentioned before and it did not work and that seems to be a better option. Is there something that I am not doing right or something that I am missing? Regards, D ________________________________ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Jiri Pospisil Sent: Thursday, December 14, 2006 10:36 AM To: [email protected] Subject: Re: Distinct result set on a table field Hi, you did not mention the type of your database, but I believe you should be able to create a database view on the table from another schema. The view will provide only distinct values and can then be used as the source for your view form. As a guess, I would say in ORACLE the view script may look something like: CREATE OR REPLACE VIEW Distinct_Contacts_View AS (SELECT DISTINCT Client_ID, First_Name, Last_Name, Email, SMS FROM OTHER_DB_SCHEMA.CONTACT_TABLE_NAME) If you prefer not to venture to SQL, you can amend your workflow to record the previous Client ID and compare it with the Client ID of the current record in the table. If the two IDs match, then you do not need to amend your result string and go straight to the next record in the table. You just need to make sure that the table field is sorted by the Client ID value. Hope this helps. Regards Jiri Pospisil Technology Support Systems ▪T▪ ▪ ▪Mobile UK▪ -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Darshana Jivan [MTN Network Solutions] Sent: 14 December 2006 07:47 To: [email protected] Subject: Distinct result set on a table field Hi, I have a View form “Contacts” that reads from an underlying table from another schema in the database. I have a Table Field on a form “Notify” that displays the Contact information. I then loop through the table to get the sms/email values and set it as a delimited string. This is where I am having a problem; the contact can be associated to many branches and therefore the contact information will be duplicated many times. I need to be able to do a Distinct or and extract to be able to get the distinctive value, or else the contact would be notified one to many times depending on how many branches they are associated with. Eg. Notify Request Id First Name Last Name Email SMS Client ID Branch ID 1 Mark Brindley [EMAIL PROTECTED] 2783452012 3 123 2 Mark Brindley [EMAIL PROTECTED] 2783452012 3 456 3 Jakos Smith [EMAIL PROTECTED] 2783656565 10 963 4 Jacques Vermeulen [EMAIL PROTECTED] 2701010233 20 321 5 Jacques Vermeulen [EMAIL PROTECTED] 2701010233 20 654 6 Jacques Vermeulen [EMAIL PROTECTED] 2701010233 20 987 Temp field that gets set Email ; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] SMS ; 2783452012; 2783452012; 2783656565; 2701010233; 2701010233; 2701010233 I need to get a distinct email and sms value to send a notification to the relevant contacts. Any suggestions? Regards, Darshana Jivan MTN Network Solutions NOTICE AND DISCLAIMER: This email (including attachments) is confidential. If you have received this email in error please notify the sender immediately and delete this email from your system without copying or disseminating it or placing any reliance upon its contents. We cannot accept liability for any breaches of confidence arising through use of email. Any opinions expressed in this email (including attachments) are those of the author and do not necessarily reflect our opinions. We will not accept responsibility for any commitments made by our employees outside the scope of our business. We do not warrant the accuracy or completeness of such information. __20060125_______________________This posting was submitted with HTML in it___
