Using SQL Server Views

  • 92Views
  • Last Post 28 February 2020
Ryan Mirto posted this 25 February 2020

I have a daily package that runs and part of it adds any recently Invoiced Orders to a custom module in zoho crm. I have noticed that it misses quite a few orders and I have to do a manual query and then upload into zoho. I have a timestamp field in the view so that Skyvia can know what's new and the view I am using does not exclude anything from my invoice table.

Could the fact I'm pulling information from a view be the reason why some invoices are being missed? If so is there anything I can do to fix it?

Order By: Standard | Newest | Votes
Dmitriy Muzurov posted this 27 February 2020

Hi Ryan,

Thank you for submitting a ticket with us.

Our UPSERT operation is working in the following way: if a Null value is specified for the ID or primary key, UPSERT operation inserts the record, and if a non-null value is specified, UPSERT operation tries to update the record with the specified ID or primary key.

There is a lookup mapping configured for the task of data import from dbo.vCustomerInvoiceSummary to Orders and an option "Set null when no match found" is selected. This is a correct setup, however, since the package throws no errors, it seems there is always some value found in the column ARInvcID in Zoho.

When it is found, Skyvia tries to UPDATE the record with the Id that does not exist yet.

We would recommend matching by some other field, so that comparing fields would return Null from Zoho and let Skyvia perform INSERT instead of UPDATE.

More details about UPSERT can be found here.

Should you have any questions, do not hesitate to contact us back.

Ryan Mirto posted this 28 February 2020

Thanks! I already did a manual upload this morning before I saw this but just made the adjustments so we'll see if it works tonight. I will report back the result.