Forum Discussion

Richard_Halsall's avatar
4 years ago
Solved

Salesforce Object requiring an ID

Hi, Please can somebody help. I am attempting to connect to a specific Salesforce object named ContentDocumentLink   However this requires single or multiple id's to be passed so I get the error:...
  • sfraser's avatar
    3 years ago

    I was able to create a query that returns all the ContentDocumentLink records for a given custom object.

     

    To do this, I did the following:

    1. Create a query using a parameter for LinkedEntityId
    2. Create a function from the query in step 1
    3. Create a second query that uses the function in step 2

    Query for step 1 is:

    let
    Source = Salesforce.Data("https://xxx.sandbox.my.salesforce.com", [ApiVersion=48]),
    ContentDocumentLink = Source{[Name="ContentDocumentLink"]}[Data],
    FilterRows = Table.SelectRows(ContentDocumentLink, each ([LinkedEntityId] = #"Linked Entity Id"))
    in
    FilterRows

     Right click on this query and select "Create Function".  You should get the following function created:

    let
    Source = (#"Linked Entity Id" as text) => let
    Source = Salesforce.Data("https://xxx.sandbox.my.salesforce.com", [ApiVersion=48]),
    ContentDocumentLink = Source{[Name="ContentDocumentLink"]}[Data],
    FilterRows = Table.SelectRows(ContentDocumentLink, each ([LinkedEntityId] = #"Linked Entity Id"))
    in
    FilterRows
    in
    Source

     Create another query that uses the function to return the ContentDocumentLink records for a given custom object.  The custom object in this example is named OC_Review__c:

     Source = Salesforce.Data("https://xxx.sandbox.my.salesforce.com", [ApiVersion=48, CreateNavigationProperties=true]),
    OC_Review__c = Source{[Name="OC_Review__c"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(OC_Review__c,{"Id"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Id", "OCR Id"}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "Doc Links", each #"Doc Links For Entity"([OCR Id])),
    #"Expanded Doc Links" = Table.ExpandTableColumn(#"Invoked Custom Function", "Doc Links", {"Id", "LinkedEntityId", "ContentDocumentId", "IsDeleted", "SystemModstamp", "ShareType", "Visibility"}, {"Id", "LinkedEntityId", "ContentDocumentId", "IsDeleted", "SystemModstamp", "ShareType", "Visibility"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Doc Links", each ([Id] <> null))
    in
    #"Filtered Rows"