Forum Discussion

Melben's avatar
Melben
Frequent Visitor
3 years ago
Solved

We encountered an error during evaluation. Details: The evaluation with ID is already in progress

Hi all, I'm getting an error in a query that just started a few days ago after having worked flawlessly for a week or so. I am extracting data from HubSpot via API and loading to a table in Dataverse...
Need Help
  • ImkeF's avatar
    ImkeF
    3 years ago
    No worries, you're welcome 🙂
    That code is really bad in many ways:
    1) It uses native recursion (@) instead of List.Generate, which is supposed to be very slow
    2) How the buffers are used will make performance even worse, so it would be better to remove them

    3) For my understanding, the current code should result in an endless loop (which would explain the error-messages):
    This recursive call doesn't include any of the results that have been returned in the previous call:

    //get new req & data
    newReq = Json.Document(Web.Contents(
       baseuri,
        [
         Headers= headers,
        Content= newContent
         ]
          )
    ),
    as the headers are fix and the so called newContent is fix as well, as it doesn't refer any of the previous calls result.

    The general "architecture" for a solution using the faster List.Generate would look like so:

    let
      headers = [
        #"Content-Type" = "application/json",
        Authorization   = "Bearer XXXXXXXXXXXXXXXXXXXXXX"
      ],
      initContent = Text.ToBinary(
        "
          {
            ""filterGroups"": [
              {
                ""filters"": [
                  {
                    ""propertyName"": ""hs_lastmodifieddate"",
                    ""operator"": ""GTE"",
                    ""value"": "                            
          & Text.From(#"Last Update Deals")
          & " // Separate query that returns a Unix datetime stamp of last updated deal
                  },
                  {
                    ""propertyName"": ""pipeline"",
                    ""operator"": ""IN"",
                    ""values"": ["                              
          & Text.From(#"Sales Pipelines")
          & "] // Separate query that returns only the pipelines I'm interested in
                  }
                ]
              }
            ],
            ""properties"": ["                          
          & Text.From(#"Properties Deals")
          & "], // Separate query that returns all properties I need
            ""limit"": 100,
            ""after"": 0
          }
    "
    ),
      initReq = Web.Contents(
        baseuri,
        [
          Headers              = headers,
          Content              = initContent,
          ManualStatusHandling = {400, 401, 403, 404, 500},
          IsRetry              = false
        ]
      ),
      initCall = Json.Document(initReq),
      initData = initCall[results],
      // will generate a list with the results of all calls. Can be expanded using the UI afterwards.
      gather = List.Generate(
          ()=> [request = initCall, result = initData, newOffset = initCall[paging][next][after], Counter = 0],
         
          //Condition, under which the next interation should take place
          each Table.Contains(Record.ToTable(request), [Name = "paging"]) = true
                and [Counter] < 10 // for debugging purposes, delete after everything is running smoothly: Limits to max. 10 recursive steps
                ,
          each [
              // [result] refers to the result-step of the previous call, so that's where the recursive effect is coming from here.
              // But I am not sure, if that actually needs to go into the content parameter here.
              // Probably the newOffset would have to be considered as well? API documentation will tell you.
              request = Json.Document(Web.Contents(baseuri, [Headers = headers, Content = [result]])),
              result = request[results],
              newOffset = request[paging][next][after],
              Counter = [Counter] + 1
          ]
      )
    in
      gather

    But it might not work, as I wasn't sure what needs to go into the Content-parameter. Is it "only" the result (of the previous step) or has the newOffset to be integrated there as well?
    .. sorry for the shi... formatting, somethings wrong with the code editor here in the forum...
     
ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
3 years ago
No worries, you're welcome 🙂
That code is really bad in many ways:
1) It uses native recursion (@) instead of List.Generate, which is supposed to be very slow
2) How the buffers are used will make performance even worse, so it would be better to remove them

3) For my understanding, the current code should result in an endless loop (which would explain the error-messages):
This recursive call doesn't include any of the results that have been returned in the previous call:

//get new req & data
newReq = Json.Document(Web.Contents(
   baseuri,
    [
     Headers= headers,
    Content= newContent
     ]
      )
),
as the headers are fix and the so called newContent is fix as well, as it doesn't refer any of the previous calls result.

The general "architecture" for a solution using the faster List.Generate would look like so:

let
  headers = [
    #"Content-Type" = "application/json",
    Authorization   = "Bearer XXXXXXXXXXXXXXXXXXXXXX"
  ],
  initContent = Text.ToBinary(
    "
      {
        ""filterGroups"": [
          {
            ""filters"": [
              {
                ""propertyName"": ""hs_lastmodifieddate"",
                ""operator"": ""GTE"",
                ""value"": "                            
      & Text.From(#"Last Update Deals")
      & " // Separate query that returns a Unix datetime stamp of last updated deal
              },
              {
                ""propertyName"": ""pipeline"",
                ""operator"": ""IN"",
                ""values"": ["                              
      & Text.From(#"Sales Pipelines")
      & "] // Separate query that returns only the pipelines I'm interested in
              }
            ]
          }
        ],
        ""properties"": ["                          
      & Text.From(#"Properties Deals")
      & "], // Separate query that returns all properties I need
        ""limit"": 100,
        ""after"": 0
      }
"
),
  initReq = Web.Contents(
    baseuri,
    [
      Headers              = headers,
      Content              = initContent,
      ManualStatusHandling = {400, 401, 403, 404, 500},
      IsRetry              = false
    ]
  ),
  initCall = Json.Document(initReq),
  initData = initCall[results],
  // will generate a list with the results of all calls. Can be expanded using the UI afterwards.
  gather = List.Generate(
      ()=> [request = initCall, result = initData, newOffset = initCall[paging][next][after], Counter = 0],
     
      //Condition, under which the next interation should take place
      each Table.Contains(Record.ToTable(request), [Name = "paging"]) = true
            and [Counter] < 10 // for debugging purposes, delete after everything is running smoothly: Limits to max. 10 recursive steps
            ,
      each [
          // [result] refers to the result-step of the previous call, so that's where the recursive effect is coming from here.
          // But I am not sure, if that actually needs to go into the content parameter here.
          // Probably the newOffset would have to be considered as well? API documentation will tell you.
          request = Json.Document(Web.Contents(baseuri, [Headers = headers, Content = [result]])),
          result = request[results],
          newOffset = request[paging][next][after],
          Counter = [Counter] + 1
      ]
  )
in
  gather

But it might not work, as I wasn't sure what needs to go into the Content-parameter. Is it "only" the result (of the previous step) or has the newOffset to be integrated there as well?
.. sorry for the shi... formatting, somethings wrong with the code editor here in the forum...
 
Melben's avatar
Melben
Frequent Visitor
3 years ago

Hi ImkeF  thank you so much you nailed it showing that there was an endless loop. Not sure when, but I had left out the refernce to the 'newOffset' which pulled through the paging 'after' field from the paging API call. This change alone has the query working again.

newContent = Text.ToBinary("
      {
        ""filterGroups"": [
          {
            ""filters"": [
              {
                ""propertyName"": ""hs_lastmodifieddate"",
                ""operator"": ""GTE"",
                ""value"": " & Text.From(#"Last Update Deals") & "
              },
              {
                ""propertyName"": ""associations.deal"",
                ""operator"": ""IN"",
                ""values"": [" & Text.From(#"New Deal List") & "]
              }
            ]
          }
        ],
        ""properties"": [" & Text.From(#"Properties Line Items") & "],
        ""limit"": 100,
        ""after"": " & newOffset & " // RIGHT HERE
      }
"),
 
I'm marking your answer as a solution, but may come back for advice as I'm about to start looking at List.Generate to improve performance.
 
Thanks again,
Ben