I’m evaluating the QuickBooks Destination SSIS connector and have a specific use case of inserting checks into QuickBooks from an OLEDB source query. When mapping the fields from the query, I noticed that the QuickBooks Destination depicts the Check Amount field as read only.
Is there a workaround that would allow me to insert new check information into QuickBooks Desktop Premier 2020 to include the check amount?
Best answer by Ethem Q
Hi
I discussed with the team and there is indeed a reason why the Amount column is read only in the Checks table. As you already know Checks is a parent table and as a big table it should normally have more columns. However, in favor of a better design and better performance for this table our devs have decided to list certain columns like ItemAggregate and ExpenseItemAggregate as aggregate data where different line items data (that would be part of the same Check entry) are defined in XML format. This means that the Amount column is actually the amount calculated internally by Quickbooks from all the ItemAmounts specified for all of the LineItems. The correct way to insert the Amount data via the Checks table would be by specifying the ItemAmount or ExpenseAmount along with other XML aggregate data for ItemAggregate and ExpenseItemAggregate data accordingly as shown in the example below:
<CheckLineItems>
<Row><ItemName>Repairs</ItemName><ItemQuantity>1</ItemQuantity><ItemAmount>100</ItemAmount></Row>
<Row><ItemName>Removal</ItemName><ItemQuantity>2</ItemQuantity><ItemAmount>100</ItemAmount</Row>
</CheckLineItems>
For this reason, we have also exposed the CheckLineItems and CheckExpenseItems child tables separately as it is easier for users to insert data into these tables directly.
Feel free to reach out to the support team at [email protected] if you have any other questions.
Hi
Indeed, we ran a few tests here and it would seem that the Amount column of the Checks table is defined as a read-only. I am not sure if it is reported the same by the Quickbooks SDK API but I will be sure to check with the developers team.
In the meantime, as a workaround you can try inserting checks via the CheckLineItems using the ItemAmount colum or the CheckExpenseItems using the ExpenseAmount column which are not read-only. Basically, these are child tables of the Checks table so you should be able to see the inserted records in the Checks table as well. You can find more information about these tables in the help doc links below:
https://cdn.cdata.com/help/RQJ/rssis/pg_table-checkexpenseitems.htm
https://cdn.cdata.com/help/RQJ/rssis/pg_table-checklineitems.htm
If you encounter any other issues, feel free to reach out to our support team at [email protected].
Hi
Thanks for your response. We will review your proposed workaround. Since we are evaluating the entire suite, it would be helpful to find out if the read only issue is specific to the tools your team have developed or a limitation of the SDK itself. Please let me know what your team learns so that I can accurately set expectations with decision makers.
Take Care,
Sid
Hi
I discussed with the team and there is indeed a reason why the Amount column is read only in the Checks table. As you already know Checks is a parent table and as a big table it should normally have more columns. However, in favor of a better design and better performance for this table our devs have decided to list certain columns like ItemAggregate and ExpenseItemAggregate as aggregate data where different line items data (that would be part of the same Check entry) are defined in XML format. This means that the Amount column is actually the amount calculated internally by Quickbooks from all the ItemAmounts specified for all of the LineItems. The correct way to insert the Amount data via the Checks table would be by specifying the ItemAmount or ExpenseAmount along with other XML aggregate data for ItemAggregate and ExpenseItemAggregate data accordingly as shown in the example below:
<CheckLineItems>
<Row><ItemName>Repairs</ItemName><ItemQuantity>1</ItemQuantity><ItemAmount>100</ItemAmount></Row>
<Row><ItemName>Removal</ItemName><ItemQuantity>2</ItemQuantity><ItemAmount>100</ItemAmount</Row>
</CheckLineItems>
For this reason, we have also exposed the CheckLineItems and CheckExpenseItems child tables separately as it is easier for users to insert data into these tables directly.
Feel free to reach out to the support team at [email protected] if you have any other questions.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
