1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Vlookup in ECM/URM

Discussion in 'Oracle Webcenter Suite (formerly Oracle ECM)' started by jason_m_Longoria, Dec 27, 2010.

  1. jason_m_Longoria

    jason_m_Longoria Active Member

    Messages:
    23
    Likes Received:
    3
    Trophy Points:
    90
    Here is a sticky one. There are several variables involved in this... lets say choice... Here are the variables
    1. DIS
    2. xls
    3. xlsx
    4. vlookup
    5. idcapacheauth

    example 1
    =VLOOKUP(A4,'\\HJ09FS\NS_Technology36\S94\Data\AppData\Stellent\SyndicationCache\urm-dev.companyname.com\F704AD14\[datafile.xlsx]Sheet1'!$A$1:$B$3,2,FALSE) FAILS

    example 2
    =VLOOKUP(A4,'https://urm-dev.companyname.com/idc/idcplg?IdcService=GET_FILE&dDocName=IDC001913&RevisionSelectionMethod=LatestReleased, [datafile.xlsx]Sheet1'!$A$1:$B$3,2,FALSE) FAILS

    example 3
    =VLOOKUP(A2,[datafile.xlsx]Sheet1!$A$1:$B$3,2,FALSE) WORKS


    So here is the scenario. I have created two files one is a data file that holds all the lookup values, then there is a calling file in which the values should change dynamically through the vlookup calculation. I have had one person say this was working but has been unable to demonstrate on any level using ECM. My concern is this, if you have security on a document and you try to pull data from it without checking it out, then you get an error stating that there are links in the document and they have to be updated would you like to update... well that fails for a few reasons, first it is trying to map to a relative path which usually is somewhere on your local machine. But this is just a cached version which is really like a query to the document location. We know that excel will not follow the query to the location of the document but only the relative path that does not really have the document. If the document is in ECM then we know a few things. first in order to use the document you must check out the native file, it will not pull from a .pdf its web viewable read only so that's one of the issues. The next issue is that if you try to add the path to the document to vlookup table array it still gives the same error as its a relative path to a document on a local machine shared drive. here is the example path that is used.

    http://urm-dev.company.com/idc/idcplg/webdav/Contribution Folders/IM Collaborative Area/VLOOKUP Tests/datafile.xlsx

    This is the path per DIS, however it still does not work, if you try to build the url excel complains that there are errors in the calculation... so this does not work. They are getting this path from the WEI address bar. Still we know based on security this should not work anyway because you are not checking it out which means you are getting a read only version. again does not allow you to pull or modify data in the spreadsheet.

    Now me, Im thinking well lets just go ahead and build an HCSP solution with a lookup table we will add to ECM... this is all fine well I start building my calculation and realize that they are not making any request to ECM using the service to modify a document. I go back to excel and start adding the correct url to the document so it can me checked out... so all that works from my HCSP page... but will not work in excel. So after banging my head on the wall for a few hours I decide to become a vlookup expert and within the first five sentences it states that if you use = signs or - in the url its going to fail because excel now looks at your path as part of the calculation... this is not good. well further more it says if you prefix all the =/- with a single ' it will work. I was unable to make this solution work in any fashion.

    Can anyone shed some light on this or perhaps give me an use case where vlookups worked with ECM/URM security? I personally do not think it is possible without circumventing the security. And even if so you would have to manually write out the path to were the document is stored in the system. Am I missing something?

    Thanks in advance to my colleagues
    :welcome
    Jason M Longoria
     
  2. dcell59

    dcell59 Forum Advisor

    Messages:
    103
    Likes Received:
    18
    Trophy Points:
    260
    I'll have to try this one out and see what I can find.

    One thing - you might try encoding the = as %3D. For example, I tried: http://myserver/cs/idcplg?IdcService=GET_DOCPROFILES&IsJava=1 and it worked fine. Similarly, you will want to encode spaces as %20. DIS encodes URL parameters for most server operations because there are too many problems when you don't.
     
    jason_m_Longoria likes this.
  3. dcell59

    dcell59 Forum Advisor

    Messages:
    103
    Likes Received:
    18
    Trophy Points:
    260
    I don't think that the first example will work simply because the cache path could vary from system to system. Will the second example work for other types of web sites? That is, if you had a file on http://blah.com/myfiles/myvlookup.xls, would Excel attempt to pull the data from the server?

    I'm wondering whether or not the Office add-in receives an event when the vlookup gets called. If so, it might be able to pull the data from the server in a more integrated way that would allow the security to be handled correctly.