Selected limited columns in List Rows Action of Dataverse Connector using Power Automate

Sanjaya Pradhan Power Automate November 8, 2022 | 0

Post Views: 12,887

You can use the List rows action to retrieve multiple rows/records at once from Microsoft Dataverse with a structured query either using Fetch XML or OData queries.

In the List Rows by default all columns are retrieved if you do not mention the columns you want to retrieve explicitly. if the flow returns all the columns then the performance of the flow is impacted. So it is always a best practice and a performance tweak to mention the columns explicitly in the List Rows action Select Columns field.

In the below example, you can see the Select Columns only contains name and amount field for Loan entity. So it will only retrieve the records with only these two columns and other columns will not be retrieved in the output.

CAUTION:

Remember that, if you are mentioning column list in Select Columns section then you can only use those columns which are mentioned in the list in next action steps of the power automate, you cannot use other columns which are not retrieved. For this you need to add other columns as needed.

{
   "statusCode":200,
   "headers":{
      "Vary":"Accept-Encoding",
      "x-ms-service-request-id":"630c9c3b-cd50-41f3-8e8f-4cc2076afdd9,4f98d9a2-65fd-4647-8ae6-8440c52d9003",
      "Cache-Control":"no-cache",
      "Set-Cookie":"ARRAffinity=252eae343940cd1bf67de5c4a4c4b130ec573989011b24307c766bccec174dc315134d20c556b0b34b9b6ae43ec3f5dcdad61788de889ffc592af7aca85fc1c508DAC16F334ED87F644879877; domain=hdfcdev.crm.dynamics.com; path=/; secure; HttpOnly,ReqClientId=44b2ec40-7442-4cf8-a48d-ce91e4b8b21d; expires=Tue, 08-Nov-2072 09:24:17 GMT; path=/; secure; HttpOnly,ARRAffinity=252eae343940cd1bf67de5c4a4c4b130ec573989011b24307c766bccec174dc315134d20c556b0b34b9b6ae43ec3f5dcdad61788de889ffc592af7aca85fc1c508DAC16F334ED87F644879877; domain=hdfcdev.crm.dynamics.com; path=/; secure; HttpOnly",
      "Strict-Transport-Security":"max-age=31536000; includeSubDomains",
      "REQ_ID":"4f98d9a2-65fd-4647-8ae6-8440c52d9003",
      "AuthActivityId":"1caad2a9-0f88-47bc-abbe-bc5cf555794e",
      "x-ms-dop-hint":"4",
      "x-ms-ratelimit-time-remaining-xrm-requests":"1,200.00",
      "x-ms-ratelimit-burst-remaining-xrm-requests":"7999",
      "OData-Version":"4.0",
      "Preference-Applied":"odata.include-annotations=\"*\"",
      "X-Source":"671231431184662957526591489118414919430194175224611071812121202181621411701494442186,227106200160183224139100214183135772081351871251261246972221082136523315013023419623811755",
      "Public":"OPTIONS,GET,HEAD,POST",
      "Date":"Tue, 08 Nov 2022 09:24:18 GMT",
      "Allow":"OPTIONS,GET,HEAD,POST",
      "Content-Type":"application/json; odata.metadata=full",
      "Expires":"-1",
      "Content-Length":"8060"
   },
   "body":{
      "@odata.context":"https://hdfcdev.crm.dynamics.com/api/data/v9.1/$metadata#hdfc_loans(hdfc_name,hdfc_amount)",
      "#Microsoft.Dynamics.CRM.DeleteMultiple":{
         "title":"DeleteMultiple",
         "target":"https://hdfcdev.crm.dynamics.com/api/data/v9.1/hdfc_loans/Microsoft.Dynamics.CRM.crmbaseentity/Microsoft.Dynamics.CRM.DeleteMultiple"
      },
      "@Microsoft.Dynamics.CRM.totalrecordcount":-1,
      "@Microsoft.Dynamics.CRM.totalrecordcountlimitexceeded":false,
      "@Microsoft.Dynamics.CRM.globalmetadataversion":"5576879",
      "value":[
         {
            "@odata.type":"#Microsoft.Dynamics.CRM.hdfc_loan",
            "@odata.id":"https://hdfcdev.crm.dynamics.com/api/data/v9.1/hdfc_loans(1de8a830-f350-ed11-bba1-000d3a37754a)",
            "@odata.etag":"W/\"5223697\"",
            "@odata.editLink":"hdfc_loans(1de8a830-f350-ed11-bba1-000d3a37754a)",
            "hdfc_name":"Personal Loan for Aman",
            "hdfc_amount@OData.Community.Display.V1.FormattedValue":"₹4,500,000.00",
            "hdfc_amount@odata.type":"#Decimal",
            "hdfc_amount":4500000.0,
            "_transactioncurrencyid_value@OData.Community.Display.V1.FormattedValue":"Rupee",
            "_transactioncurrencyid_value@Microsoft.Dynamics.CRM.associatednavigationproperty":"transactioncurrencyid",
            "_transactioncurrencyid_value@Microsoft.Dynamics.CRM.lookuplogicalname":"transactioncurrency",
            "_transactioncurrencyid_value@odata.type":"#Guid",
            "_transactioncurrencyid_value":"04d63d09-5243-ed11-bba2-0022480b4e0f",
            "hdfc_loanid@odata.type":"#Guid",
            "hdfc_loanid":"1de8a830-f350-ed11-bba1-000d3a37754a",
            "transactioncurrencyid@odata.associationLink":"https://hdfcdev.crm.dynamics.com/api/data/v9.1/hdfc_loans(1de8a830-f350-ed11-bba1-000d3a37754a)/transactioncurrencyid/$ref",
            "transactioncurrencyid@odata.navigationLink":"https://hdfcdev.crm.dynamics.com/api/data/v9.1/hdfc_loans(1de8a830-f350-ed11-bba1-000d3a37754a)/transactioncurrencyid"
         },
         {
            "@odata.type":"#Microsoft.Dynamics.CRM.hdfc_loan",
            "@odata.id":"https://hdfcdev.crm.dynamics.com/api/data/v9.1/hdfc_loans(95e26d08-df54-ed11-bba2-00224806fb4a)",
            "@odata.etag":"W/\"5223689\"",
            "@odata.editLink":"hdfc_loans(95e26d08-df54-ed11-bba2-00224806fb4a)",
            "hdfc_name":"Loan for Aman",
            "hdfc_amount@OData.Community.Display.V1.FormattedValue":"₹490,000.00",
            "hdfc_amount@odata.type":"#Decimal",
            "hdfc_amount":490000.0,
            "_transactioncurrencyid_value@OData.Community.Display.V1.FormattedValue":"Rupee",
            "_transactioncurrencyid_value@Microsoft.Dynamics.CRM.associatednavigationproperty":"transactioncurrencyid",
            "_transactioncurrencyid_value@Microsoft.Dynamics.CRM.lookuplogicalname":"transactioncurrency",
            "_transactioncurrencyid_value@odata.type":"#Guid",
            "_transactioncurrencyid_value":"04d63d09-5243-ed11-bba2-0022480b4e0f",
            "hdfc_loanid@odata.type":"#Guid",
            "hdfc_loanid":"95e26d08-df54-ed11-bba2-00224806fb4a",
            "transactioncurrencyid@odata.associationLink":"https://hdfcdev.crm.dynamics.com/api/data/v9.1/hdfc_loans(95e26d08-df54-ed11-bba2-00224806fb4a)/transactioncurrencyid/$ref",
            "transactioncurrencyid@odata.navigationLink":"https://hdfcdev.crm.dynamics.com/api/data/v9.1/hdfc_loans(95e26d08-df54-ed11-bba2-00224806fb4a)/transactioncurrencyid"
         },
         {
            "@odata.type":"#Microsoft.Dynamics.CRM.hdfc_loan",
            "@odata.id":"https://hdfcdev.crm.dynamics.com/api/data/v9.1/hdfc_loans(09b1e9e3-ff54-ed11-bba2-00224806fb4a)",
            "@odata.etag":"W/\"5224008\"",
            "@odata.editLink":"hdfc_loans(09b1e9e3-ff54-ed11-bba2-00224806fb4a)",
            "hdfc_name":"pl for aman",
            "hdfc_amount@OData.Community.Display.V1.FormattedValue":"₹2,070,000.00",
            "hdfc_amount@odata.type":"#Decimal",
            "hdfc_amount":2070000.0,
            "_transactioncurrencyid_value@OData.Community.Display.V1.FormattedValue":"Rupee",
            "_transactioncurrencyid_value@Microsoft.Dynamics.CRM.associatednavigationproperty":"transactioncurrencyid",
            "_transactioncurrencyid_value@Microsoft.Dynamics.CRM.lookuplogicalname":"transactioncurrency",
            "_transactioncurrencyid_value@odata.type":"#Guid",
            "_transactioncurrencyid_value":"04d63d09-5243-ed11-bba2-0022480b4e0f",
            "hdfc_loanid@odata.type":"#Guid",
            "hdfc_loanid":"09b1e9e3-ff54-ed11-bba2-00224806fb4a",
            "transactioncurrencyid@odata.associationLink":"https://hdfcdev.crm.dynamics.com/api/data/v9.1/hdfc_loans(09b1e9e3-ff54-ed11-bba2-00224806fb4a)/transactioncurrencyid/$ref",
            "transactioncurrencyid@odata.navigationLink":"https://hdfcdev.crm.dynamics.com/api/data/v9.1/hdfc_loans(09b1e9e3-ff54-ed11-bba2-00224806fb4a)/transactioncurrencyid"
         },
         {
            "@odata.type":"#Microsoft.Dynamics.CRM.hdfc_loan",
            "@odata.id":"https://hdfcdev.crm.dynamics.com/api/data/v9.1/hdfc_loans(b01cd54a-7456-ed11-bba2-00224806fb4a)",
            "@odata.etag":"W/\"5242945\"",
            "@odata.editLink":"hdfc_loans(b01cd54a-7456-ed11-bba2-00224806fb4a)",
            "hdfc_name":"Personal loan for aman",
            "hdfc_amount@OData.Community.Display.V1.FormattedValue":"₹800,000.00",
            "hdfc_amount@odata.type":"#Decimal",
            "hdfc_amount":800000.0,
            "_transactioncurrencyid_value@OData.Community.Display.V1.FormattedValue":"Rupee",
            "_transactioncurrencyid_value@Microsoft.Dynamics.CRM.associatednavigationproperty":"transactioncurrencyid",
            "_transactioncurrencyid_value@Microsoft.Dynamics.CRM.lookuplogicalname":"transactioncurrency",
            "_transactioncurrencyid_value@odata.type":"#Guid",
            "_transactioncurrencyid_value":"04d63d09-5243-ed11-bba2-0022480b4e0f",
            "hdfc_loanid@odata.type":"#Guid",
            "hdfc_loanid":"b01cd54a-7456-ed11-bba2-00224806fb4a",
            "transactioncurrencyid@odata.associationLink":"https://hdfcdev.crm.dynamics.com/api/data/v9.1/hdfc_loans(b01cd54a-7456-ed11-bba2-00224806fb4a)/transactioncurrencyid/$ref",
            "transactioncurrencyid@odata.navigationLink":"https://hdfcdev.crm.dynamics.com/api/data/v9.1/hdfc_loans(b01cd54a-7456-ed11-bba2-00224806fb4a)/transactioncurrencyid"
         },
         {
            "@odata.type":"#Microsoft.Dynamics.CRM.hdfc_loan",
            "@odata.id":"https://hdfcdev.crm.dynamics.com/api/data/v9.1/hdfc_loans(4e4792c0-5e5a-ed11-a81b-002248092db4)",
            "@odata.etag":"W/\"5361322\"",
            "@odata.editLink":"hdfc_loans(4e4792c0-5e5a-ed11-a81b-002248092db4)",
            "hdfc_name":"Alex Personal loan for 60k",
            "hdfc_amount@OData.Community.Display.V1.FormattedValue":"$60,000.00",
            "hdfc_amount@odata.type":"#Decimal",
            "hdfc_amount":60000.0,
            "_transactioncurrencyid_value@OData.Community.Display.V1.FormattedValue":"US Dollar",
            "_transactioncurrencyid_value@Microsoft.Dynamics.CRM.associatednavigationproperty":"transactioncurrencyid",
            "_transactioncurrencyid_value@Microsoft.Dynamics.CRM.lookuplogicalname":"transactioncurrency",
            "_transactioncurrencyid_value@odata.type":"#Guid",
            "_transactioncurrencyid_value":"99fe69e8-2443-ed11-bba2-0022480b4357",
            "hdfc_loanid@odata.type":"#Guid",
            "hdfc_loanid":"4e4792c0-5e5a-ed11-a81b-002248092db4",
            "transactioncurrencyid@odata.associationLink":"https://hdfcdev.crm.dynamics.com/api/data/v9.1/hdfc_loans(4e4792c0-5e5a-ed11-a81b-002248092db4)/transactioncurrencyid/$ref",
            "transactioncurrencyid@odata.navigationLink":"https://hdfcdev.crm.dynamics.com/api/data/v9.1/hdfc_loans(4e4792c0-5e5a-ed11-a81b-002248092db4)/transactioncurrencyid"
         },
         {
            "@odata.type":"#Microsoft.Dynamics.CRM.hdfc_loan",
            "@odata.id":"https://hdfcdev.crm.dynamics.com/api/data/v9.1/hdfc_loans(aeec6074-605a-ed11-a81b-002248092db4)",
            "@odata.etag":"W/\"5361520\"",
            "@odata.editLink":"hdfc_loans(aeec6074-605a-ed11-a81b-002248092db4)",
            "hdfc_name":"alex 900k",
            "hdfc_amount@OData.Community.Display.V1.FormattedValue":"$54,545.00",
            "hdfc_amount@odata.type":"#Decimal",
            "hdfc_amount":54545.0,
            "_transactioncurrencyid_value@OData.Community.Display.V1.FormattedValue":"US Dollar",
            "_transactioncurrencyid_value@Microsoft.Dynamics.CRM.associatednavigationproperty":"transactioncurrencyid",
            "_transactioncurrencyid_value@Microsoft.Dynamics.CRM.lookuplogicalname":"transactioncurrency",
            "_transactioncurrencyid_value@odata.type":"#Guid",
            "_transactioncurrencyid_value":"99fe69e8-2443-ed11-bba2-0022480b4357",
            "hdfc_loanid@odata.type":"#Guid",
            "hdfc_loanid":"aeec6074-605a-ed11-a81b-002248092db4",
            "transactioncurrencyid@odata.associationLink":"https://hdfcdev.crm.dynamics.com/api/data/v9.1/hdfc_loans(aeec6074-605a-ed11-a81b-002248092db4)/transactioncurrencyid/$ref",
            "transactioncurrencyid@odata.navigationLink":"https://hdfcdev.crm.dynamics.com/api/data/v9.1/hdfc_loans(aeec6074-605a-ed11-a81b-002248092db4)/transactioncurrencyid"
         }
      ]
   }
}

The output looks as below.

Hope this helps.

Follow my blog for more trending topics on Dynamics 365, Azure, C#, Power Portals and Power Platform. For training, Courses and consulting, call to us at +91 832 886 5778 I am working more for community to share skills in Dynamics 365 and Power Platform. Please support me by subscribing my YouTube Channel. My YouTube Channel link is this : https://www.youtube.com/user/sppmaestro

dataverse select columns power autoamte tutorial restrict list by columns dataverse power autoamte select columns in list rows power automate