Iterating through Products attached to Deals

[marko.hilzendegen]

Hi there,

I have used the API to pull information to MS PowerBi. I was able to iterate through the pagination of the deals, but with the products I have not found a way. I have cheated and hardcoded a high number into the iteration right now. Do you have any ideas how to pull the last deal id and feed it into the loop?

Starts = List.Generate(()=>1, each _ < 1200, each _ + 1), 
#"Converted to Table" = Table.FromList(Starts, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}), 
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Json.Document(Web.Contents("https://digainfo.pipedrive.com/v1/deals/"&[Column1]&"/products:(id,deal_id,product_id,item_price,sum,currency,add_time,comments,name,quantity)?api_token=xyz",[Query=[api_token="xyz", limit="500", start="0"]])))

Thanks
Marko

[Hem]

Hey @marko.hilzendegen
Welcome to the community

@marko.hilzendegen

I have cheated and hardcoded a high number into the iteration right now. Do you have any ideas how to pull the last deal id and feed it into the loop?

I see the intent however I would request you to consider pagination

Pagination Considerations

Why pagination? Pagination limits the amount of data that you can get in a single call. Rather than obtaining the entire collection/list items in bulk, one can retrieve them in a page-wise manner. This is to avoid excessive load for the product and also for the consuming client. How do I know if the responses are paginated? Most of the lists/item collections are paginated. Within the response’s additional_data object, a pagination object will be set upon pagination. The additional_data.pagina…

You need to fetch entries by pages

[marko.hilzendegen]

Hey @Hem,

pagination does not help her, as pagination yields the count of records, but it does not count deleted ones for example, so technically our count is lower than the highest id, and is the end point is

deals/{id}/products

the count does not help. I would need to find the highest id so that i can wild card the {id} in the iteration.

if i was able to pull all products, pagination would work, but you can only pull the product information (quantity etc) per deal. Another stupid decision made by pipedrive along with the many other stupid decisions.

Here are screenies:

JSON with summary:

Highest id in BI:

Marko

[Hem]

Hey @marko.hilzendegen
I see your frustration and it seems to have a reasonable origin. Couple of questions:

• Which entity are you trying to retrieve - Deals, Products, or both?

  • For Deals, you can use the status query parameter that can retrieve deleted and all_not_deleted entries
  • For Products, you do not have a status query parameter.
    • However, you can listen to deleted.product webhook events. You might need a middleware that keeps track of deleted products.
    • You can also reach out to Pipedrive support for the initial snapshot of deleted products

• Do you rely on deals/{id}/products just for the sake of getting the deleted products? (and thus you have to take a hacky approach) or are you trying to find products associated with deals as the end goal?

  • Deleted products still appear in that endpoint. However, you only need a Deal ID in that case (and from the previous section you can see that you can retrieve all deals, deleted + not deleted)
  • However, It is tricky to build a product master data from that endpoint but again this depends on your end goal.

Overall, I think that you would possibly require a middleware and it may not be a direct approach. I also agree that it is definitely tricky and there’s some room for improvements which I have taken note of It will be shared with the concerned team.