none
Outlook field as a formula on fly? RRS feed

  • Question

  • I want to have on my custom contact form a read only multi-line field which combines both the contact's Full Name along with their Business Address, Home Address or Other Address so that the user can simply copy and paste from it in one place.  I tried adding as a new field, set the initial value of this field to

    [Full Name] Chr( 13 ) Chr( 10 ) [Business Address]

    and calculate this formula automatically but it doesn't seem to work as expected after I published my form from a blank form.  Please advise how I can get this done.  I'm trying to avoid macros or something complicated for an otherwise simple request.

    Monday, October 22, 2012 1:46 PM

Answers

  • Going to have correct the original post with a significant qualification which I neglected to check prior to answering

    Adding the Formula field as I mentioned will work on a custom form when the a contact is newly created - the UDF will reflect the correct values. The problem comes in when a change is made to the Address field, the new value will show on the detail screen but the new value in that field isn't saved - at least not in some quick tests on the system I'm currently on. Similarly, changing the message class to the custom form with the Formula field results in the field showing "#Error". Right now not certain whether that's a bug in Outlook in terms of a text formula not recalculating or an issue related to the system/Outlook version/test data in use even though the UDF formula is set to recalculate automatically

    One thing that is certain - the formula you originally entered

    [Full Name] Chr( 13 ) Chr( 10 ) [Business Address]

    won't work since for a formula - you'd need the "&" operator between the fields otherwise it does result in a date value appearing. 


    Karl Timmermans - The Claxton Group
    Outlook Import/Export Hints/Tips
    Contact import/export/data management tools for Outlook '2000/2010 - ContactGenie.com

    Thursday, October 25, 2012 8:55 PM

All replies

  • You would need to use form code to populate the custom field as you want it to be formatted with the data you want.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "jfalberg" <=?utf-8?B?amZhbGJlcmc=?=> wrote in message news:ae1f8260-5933-4a63-aee7-51302699c1da...

    I want to have on my custom contact form a read only multi-line field which combines both the contact's Full Name along with their Business Address, Home Address or Other Address so that the user can simply copy and paste from it in one place.  I tried adding as a new field, set the initial value of this field to

    [Full Name] Chr( 13 ) Chr( 10 ) [Business Address]

    and calculate this formula automatically but it doesn't seem to work as expected after I published my form from a blank form.  Please advise how I can get this done.  I'm trying to avoid macros or something complicated for an otherwise simple request.


    Ken Slovak MVP - Outlook
    Monday, October 22, 2012 2:28 PM
    Moderator
  • So is form code possible in a formula editor or will I need to make an add-in or perhaps a form region in order for this to work?  I was hoping to make this as simple as possible without needing VBScripting or extra.

    Monday, October 22, 2012 3:37 PM
  • Form code for a custom form is VBScript code that is embedded in the form. It runs on events that fire in Outlook, so you could use the Load, Save, Write or other events to populate the field. A custom form must be published before code in it will run.
     
    See the forms information at www.outlookcode.com for more information on forms code.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "jfalberg" <=?utf-8?B?amZhbGJlcmc=?=> wrote in message news:700ba002-bc87-45c0-ad3a-d0d97afd8072...

    So is form code possible in a formula editor or will I need to make an add-in or perhaps a form region in order for this to work?  I was hoping to make this as simple as possible without needing VBScripting or extra.


    Ken Slovak MVP - Outlook
    Monday, October 22, 2012 3:40 PM
    Moderator
  • So if I understand correctly, the vbscript is embedded in the form once published and can be distributed with the form across the network.  I tried right-clicking on my desired field and going to advanced properties but didn't see how I would get to my events such as Load, Save, Write like I would in VSTO c#
    Monday, October 22, 2012 3:49 PM
  • As an update this seems quite promising as I got the following accomplished at least in my script editor upon loading:

    Function Item_Open()
      Item.UserProperties("CustAddr1") = Item.FullName & Chr(13) & Chr(10) & Item.BusinessAddress   

      Item.UserProperties("CustAddr2") = Item.FullName & Chr(13) & Chr(10) & Item.HomeAddress

      Item.UserProperties("CustAddr3") = Item.FullName & Chr(13) & Chr(10) & Item.OtherAddress

    End Function

    One thing that bugs me is when I close a contact after opening it, it prompts me to save even though I didn't make any changes.  Any way around this, or perhaps a way to use a field that won't trigger that somehow?

    Monday, October 22, 2012 4:36 PM
  • You need to view the form code, and add your event handlers there. See http://www.outlookcode.com/article.aspx?id=41 for more information.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "jfalberg" <=?utf-8?B?amZhbGJlcmc=?=> wrote in message news:5083f804-1d0c-4b6d-95dc-5dab461bae40...
    So if I understand correctly, the vbscript is embedded in the form once published and can be distributed with the form across the network.  I tried right-clicking on my desired field and going to advanced properties but didn't see how I would get to my events such as Load, Save, Write like I would in VSTO c#

    Ken Slovak MVP - Outlook
    Monday, October 22, 2012 4:58 PM
    Moderator
  • Of course you made changes, you set user property values. Why wouldn't you think you made any changes? If you want that data to be there when the form is opened again you need to save the item.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "jfalberg" <=?utf-8?B?amZhbGJlcmc=?=> wrote in message news:42c65436-7c8a-45fd-ad7a-8dfe856d36c9...

    As an update this seems quite promising as I got the following accomplished at least in my script editor upon loading:

    Function Item_Open()
      Item.UserProperties("CustAddr1") = Item.FullName & Chr(13) & Chr(10) & Item.BusinessAddress   

      Item.UserProperties("CustAddr2") = Item.FullName & Chr(13) & Chr(10) & Item.HomeAddress

      Item.UserProperties("CustAddr3") = Item.FullName & Chr(13) & Chr(10) & Item.OtherAddress

    End Function

    One thing that bugs me is when I close a contact after opening it, it prompts me to save even though I didn't make any changes.  Any way around this, or perhaps a way to use a field that won't trigger that somehow?


    Ken Slovak MVP - Outlook
    Monday, October 22, 2012 4:58 PM
    Moderator
  • Basically the goal I had in mind was to have read-only fields so the user can copy and paste from.  Are there any other field types besides the Item UserProperties or fields that trigger changes I could use for this purpose?
    Monday, October 22, 2012 5:23 PM
  • If there's a way to save after I set these fields I can accept so long as it doesn't bring up any prompts.  I tried the Item.Saved = true method but that field came back to me as read-only.
    Monday, October 22, 2012 6:01 PM
  • Saved is a read-only property telling you if the item was saved. To save an item call the Item.Save() method.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "jfalberg" <=?utf-8?B?amZhbGJlcmc=?=> wrote in message news:8aa5bb49-32bb-4828-9e2c-2bac718a2c08...
    If there's a way to save after I set these fields I can accept so long as it doesn't bring up any prompts.  I tried the Item.Saved = true method but that field came back to me as read-only.

    Ken Slovak MVP - Outlook
    Monday, October 22, 2012 6:10 PM
    Moderator
  • It looks like the

      Item.Save()

    method worked for me as desired.  Hopefully few other things I need to accomplish using the script editor will do the trick for me, one if which is pressing a button to open a new email message with the To field pre-populated with the contact's email address.

    Monday, October 22, 2012 6:25 PM
  • In getting back to this.  The problem I have with using "Item.Save()" is that it blows an error for those with read-only access, and sometimes if the user clicks on any other field it still prompts to save.

    So is there perhaps any scripting possible, perhaps on closing, to not prompt to save?  Particular if the user should have read-only access to this folder.

    Thursday, October 25, 2012 3:36 PM
  • If you change the item you will be prompted to save on closing the item unless the item is:
     
    1) Saved already.
    2) Changes discarded.
     
    There are no other options available. You can try to save and handle the error, but that's about it unless you just discard any changes.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "jfalberg" <=?utf-8?B?amZhbGJlcmc=?=> wrote in message news:01c9d95d-0386-42b8-be09-3e5283a8e0df...

    In getting back to this.  The problem I have with using "Item.Save()" is that it blows an error for those with read-only access, and sometimes if the user clicks on any other field it still prompts to save.

    So is there perhaps any scripting possible, perhaps on closing, to not prompt to save?  Particular if the user should have read-only access to this folder.


    Ken Slovak MVP - Outlook
    Thursday, October 25, 2012 3:41 PM
    Moderator
  • If there's a way to make the changes automatically discarded without prompting on form closing then that would be desireable.
    Thursday, October 25, 2012 3:58 PM
  • Your code would have to call either MailItem.Close(OlInspectorClose.olDiscard) or Inspector.Close(OlInspectorClose.olDiscard).
     
    In the case of form code, where Item is an intrinsic object you could call Item.Close(OlInspectorClose.olDiscard)

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "jfalberg" <=?utf-8?B?amZhbGJlcmc=?=> wrote in message news:e956105f-5963-45bb-b465-17d13a06d8a3...
    If there's a way to make the changes automatically discarded without prompting on form closing then that would be desireable.

    Ken Slovak MVP - Outlook
    Thursday, October 25, 2012 5:44 PM
    Moderator
  • Hmm, I tried

    Function Item_Close()
    'Close the custom form without saving the message
    Item.Close(OlInspectorClose.olDiscard)
    End Function

    The problem I am having is now I get the following popup message:

    <Script Error>
    Object required: 'OlInspectorClose'
    Line No: 18

    Since I'm in script editor, anything differently I would need to do in order to make this work?

    Thursday, October 25, 2012 7:39 PM
  • Set the UDF as a <Formula> with the following

    [Full Name] & Chr(13) & Chr(10) & [Business Address]

    and it should work without any issues


    Karl Timmermans - The Claxton Group
    Outlook Import/Export Hints/Tips
    Contact import/export/data management tools for Outlook '2000/2010 - ContactGenie.com

    Thursday, October 25, 2012 7:39 PM
  • So if I understand correctly, I can have a UDF field that simply works as a calculation and won't prompt me nor my users to save?  I tried adding a direct formula but it only seemed to populate with a date and time value somehow.
    Thursday, October 25, 2012 7:54 PM
  • VBScript only knows the VBSript constants, not ones for Outlook or Office. Use the value from the OlInspector enum, either as the value or define a constant for the value.
     
    OlInspectorClose.olDiscard = 1.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "jfalberg" <=?utf-8?B?amZhbGJlcmc=?=> wrote in message news:37aa8924-76cc-4eb0-886a-d61279e392b4...

    Hmm, I tried

    Function Item_Close()
    'Close the custom form without saving the message
    Item.Close(OlInspectorClose.olDiscard)
    End Function

    The problem I am having is now I get the following popup message:

    <Script Error>
    Object required: 'OlInspectorClose'
    Line No: 18

    Since I'm in script editor, anything differently I would need to do in order to make this work?


    Ken Slovak MVP - Outlook
    Thursday, October 25, 2012 7:59 PM
    Moderator
  • That simply did the trick, 1 down few more fires to put out...
    Thursday, October 25, 2012 8:34 PM
  • Going to have correct the original post with a significant qualification which I neglected to check prior to answering

    Adding the Formula field as I mentioned will work on a custom form when the a contact is newly created - the UDF will reflect the correct values. The problem comes in when a change is made to the Address field, the new value will show on the detail screen but the new value in that field isn't saved - at least not in some quick tests on the system I'm currently on. Similarly, changing the message class to the custom form with the Formula field results in the field showing "#Error". Right now not certain whether that's a bug in Outlook in terms of a text formula not recalculating or an issue related to the system/Outlook version/test data in use even though the UDF formula is set to recalculate automatically

    One thing that is certain - the formula you originally entered

    [Full Name] Chr( 13 ) Chr( 10 ) [Business Address]

    won't work since for a formula - you'd need the "&" operator between the fields otherwise it does result in a date value appearing. 


    Karl Timmermans - The Claxton Group
    Outlook Import/Export Hints/Tips
    Contact import/export/data management tools for Outlook '2000/2010 - ContactGenie.com

    Thursday, October 25, 2012 8:55 PM