Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Combine Key/Value Lists from Web Response

Hi


I am trying to get entity record counds from dynamics using its API and the function RetrieveTotalRecordCount.

 

I am making a call similar to this

https://foobar.crm.dynamics.com/api/data/v9.1/RetrieveTotalRecordCount(EntityNames='salesorder','salesorderdetail','invoice','invoicedetail'])

which returns data as follows

 

{
  "@odata.context": "https://foobar.crm.dynamics.com/api/data/v9.1/$metadata#Microsoft.Dynamics.CRM.RetrieveTotalRecordCountResponse",
  "EntityRecordCountCollection": {
    "Count": 4,
    "IsReadOnly": false,
    "Keys": [
      "salesorder",
      "salesorderdetail",
      "invoice",
      "invoicedetail"
    ],
    "Values": [
      6679,
      10547,
      17568,
      59618
    ]
  }
}
 

What i would like is a table with the table name (key) and the correcpoding rowcount(value)  ie

Table NameRow Count
salesorder6679
salesorderdetail10547
invoice17568
invoicedetail59618

 

As you can see the response returns 2 key/value lists. 

however the only way i can find to do this currently is to execute the query twice and expand each of the lists separately into a table adding an index field to each then joining the 2 tables back up together on this index column.  This seems a rather clunky way of doing to so was wondering if anyone could suggest a smarter way?

 

thanks in advince

 
Need Help
  • Smauro's avatar
    Smauro
    5 years ago

    Anonymous
    You could try something like

     

        Json = Json.Document(Response)[EntityRecordCountCollection],
    toTable = Table.FromList(List.Zip({Json[Keys], Json[Values]}),(x) => {x{0}, x{1}}, {"Table Name", "Row Count"}),
    tableType = type table [ #"Table Name" = Text.Type, #"Row Count" = Int64.Type],
    Final = Value.ReplaceType ( toTable, tableType)

     

    where Response is the response.

     

    Cheers

2 Replies

  • Smauro's avatar
    Smauro
    Solution Sage
    5 years ago

    Anonymous
    You could try something like

     

        Json = Json.Document(Response)[EntityRecordCountCollection],
    toTable = Table.FromList(List.Zip({Json[Keys], Json[Values]}),(x) => {x{0}, x{1}}, {"Table Name", "Row Count"}),
    tableType = type table [ #"Table Name" = Text.Type, #"Row Count" = Int64.Type],
    Final = Value.ReplaceType ( toTable, tableType)

     

    where Response is the response.

     

    Cheers

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you Smauro 

      With your example i was able to get it working with the following code

      let
    Source = OData.Feed("https://xxxxxx.crm11.dynamics.com/api/data/v9.1/RetrieveTotalRecordCount(EntityNames=['contact','lead','serviceappointment','task'])", null, [Implementation="2.0"]),
    EntityRecordCountCollection = Source[EntityRecordCountCollection],
    toTable = Table.FromList(List.Zip({EntityRecordCountCollection[Keys], EntityRecordCountCollection[Values]}),(x) => {x{0}, x{1}}, {"Table Name", "Row Count"})
in
    toTable