Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jan 2015
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Date Calculations between Sharepoint Library and Word Doc

    I have a sharepoint library where I intend to keep track of an ordering process. Part of the process is that there is a dropdown entry for "type" and a "follow up date" which changes according to the "Type"; e.g.

    Type = Zone 1, Follow Up = Last Modified + 1 week
    Type = Zone 2, Follow Up = Last Modified + 2 weeks

    The orders need to be kept in word docs but they also need version control, so the answers "don't bother with the word docs, just use a SP list" and "don't bother with SP, just use the word docs and folders" aren't good unfortunately.

    Simple enough to do that in Sharepoint on its own using calculated columns, or in the Word doc on its own using Formfield date calculations, but I don't want the users to have to enter the same information twice.

    The problem I've come across is this:
    I can put the "Type" from Sharepoint in the document as a Quickpart Ok.
    Quickparts apparently require VBA to do calculations.
    If I calculate the date in Sharepoint, I can't use that calculated column as a Quickpart in the Word Doc.
    If I calculate the date in Formfields in the Word Doc, I can't push it out to Sharepoint.
    VBA in the word docs isn't an option, and I don't have access to SP Designer.

    Is it possible to refer to the value of the "Type" Quickpart in a formfield calculation, the same way that you'd use a {QUOTE} or {SET} bookmark?

    If I use the document property "Last Modified" as part of a formfield date calculation, will that change if the Word doc is emailed and re-saved elsewhere?

    Word 2010 and Sp07 if that's relevant.

  2. #2
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts
    I would recommend you look at the Date Calculations pinned thread here and the document you can download from that. If you can get a date into a Word document, you can use a calculation field to manipulate it.

    As for version controls, look at Graham Mayor's site for his code and Add-In which may help.
    Charles Kyle Kenyon
    Madison, Wisconsin

  3. #3
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    It appears you are using the word 'quickpart' to mean a content control. A quickpart in Word is actually a particular category of autotext entry. The quickparts could include one or more content controls so perhaps this is what is causing some of the confusion. A content control can be linked to document metadata which means that changes to the metadata will change the displayed value in the content control and vice versa.

    If you need to change the value of a document metadata field when the document is saved without resorting to a macro or human intervention then I would need to think more about how you might do this. Can you explain why 'VBA in the word docs isn't an option'. Does it matter if the VBA is contained in an attached template and not the Word document itself?
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #4
    New Lounger
    Join Date
    Jan 2015
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    @Charles I've used macropod's date calculations from that document before, and as mentioned doing the date calculation "all in fields" would be fine. I can't seem to get the Word document to recognise the value of the Content control/quickpart which pulls in the "Type" from Sharepoint however. It doesn't appear to have a bookmark name which a date calculating field can reference, and placing a bookmark around the control doesn't appear to work either (unless there's a trick I'm missing).

    @Andrew The word docs need to be self contained. VBA isn't allowed in this environment so it's not an option. Even assuming the VBA were held within an external template, then manual intervention would be required to run the update so that Sharepoint metadata and word fields matched. Since end-users will be running this without support, trusting them to do so is.... "optimistic".

  5. #5
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts
    I don' use SharePoint nor do I input dates using a content control pulling in a "type" from something else. How is it that this is a date? You can bookmark a content control so it will have a name, but you still need to update the calculation field somehow.
    Charles Kyle Kenyon
    Madison, Wisconsin

  6. #6
    New Lounger
    Join Date
    Jan 2015
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I think that's the nub of my problem. The option to place a bookmark on Content Control text is greyed out, so I can place a bookmark on the text surrounding it. A REF field will pick up that bookmark and reproduce the text. However this means that there is extra text (spaces, whatever else) either side of the content control and an IF or COMPARE field doesn't recognise the text.

    It's one of those "if only" moments where I wonder if I'm just missing some syntax for the fields which would make this work.

  7. #7
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    It it not overly difficult to get a bookmark to include the content control and nothing but the content control. I did it this way...

    1. Create two empty paragraphs and in the first one add your content control so that is all it contains
    2. Select the first paragraph (the one containing the CC) and add a bookmark. This bookmark includes the CC and a paragraph mark
    3. Now go to the second paragraph and press the backspace key.

    This deletes the paragraph mark inside the bookmark so the bookmark now contains only the content control.

    Can you post a sample document that shows what you have tried to date?
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  8. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    Sheerpoint (2015-01-29)

  9. #8
    New Lounger
    Join Date
    Jan 2015
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Brilliant, that works!

    As usual, the answer is simple after days of fiddling around trying to guess whether there's some undocumented reference that allows one to get the necessary data.

    Now to work around the fact that "Last Modified" changes when the document is saved off sharepoint to a mapped network drive.....

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •