Forum Discussion
Salesforce Object requiring an ID
- 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:
- Create a query using a parameter for LinkedEntityId
- Create a function from the query in step 1
- 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
FilterRowsRight 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
SourceCreate 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"
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:
- Create a query using a parameter for LinkedEntityId
- Create a function from the query in step 1
- 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"