Getting unique stats from Form Submission API

[zohaib]

Hi All

We are running into a problem, we have to migrate form submissions from Hubspot to Dynamics 365, but we are unable to find any unique field or column from hubspot on which we can set up a lookup and get just new records. We are usnig this API

Get submissions for a form

https://api.hubapi.com/form-integrations/v1/submissions/forms/583d14ff-91af-403d-b94a-c8b5d954da6f?h...

Any help will appriciated.

Many Thanks

[IsaacTakushi]

Hi, @zohaib.

To clarify, are you trying to filter the results from this endpoint so that you don't have to parse through all submissions each time?

Could you record the last submittedAt timestamp you retrieve and then the next time you make a GET request, have your app stop calling when it sees the same submittedAt value?

Please let me know if I have misunderstood your question.

[zohaib]

hi @IsaacTakushi 

Thank you so much for your response.

1- Yes,I am using your mentioned endpoind. We have to get the submissions on daily basis, first time i will move all the submissions then next day i need to move only new submissions. But there is an issue

i am trying to get the stats using Json Source in SSIS (Sql Server Integration Services),  it is providing me three different outputs, Issue is timestamp is in different Output Object and the my required fields in other output object, there is not any same column in both obects on which i can merge them, please see attached.

 

2-  Yes, i can save submittedAt timestamp, if i can get the submittedAt timestamp and other fields in the same output object.

Please ask me if there is anything need to more explain.

Thanks

[IsaacTakushi · Accepted solution]

Hi, @zohaib.

Thank you for clarifying. I believe I now grasp the issue: the submittedAt values are going into different tables than the submission fields and you can't use a MERGE statement since you have no primary key to tie the two tables together. Is that correct?

Assuming a single form submission produces a table containing the submittedAt timestamp and another containing the submission values, could you use an INSERT INTO SELECT statement to copy the submittedAt timestamp column into the table containing the submission values?

According to this article, this statement does not required a primary key to match tables.