Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

Buy Support
Incidents

If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:

Click Here
If you can't login and post questions or you are having trouble viewing forum posts:
Click Here
Callback
Support

If you live in USA, UK, Canada, Australia or New Zealand, you can leave us details on your question and request us to call you back and discuss them with you personally  (charges apply).

Click Here
 
Buy Support
Incidents
If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:
Click Here

Forum : UK VAT Issues on BillExpenseLineSearch Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC v9 Forum

 New Topic 
 
 Post Reply 
[1]  
 UK VAT Issues on BillExpenseLine 
 Author   Message 
  clueless 
  
 Group: Members 
 Posts: 5 
 Joined: 2008-11-01 
 Profile
 Posted : 2008-12-13 02:03:06

This is really doing my head in.

I am trying to move from IIF files import into QB2006 to using QODBC into QB2008 UK.

I try to keep things simple.  I don't track stock items and all bills are entered summarised into one 'vatable' total and once 'Non Vatable total. 

This sometimes presents a situation where the VAT on the bill I enter does not agree with the VAT on the actual Invoice received.  It may be a couple of pence out.

This was easy to handle in IIF and QB2006 as the VAT amount was held per line, whatever I entered into the IIF file was correctly imported.

QB2008 handles things a little differently but I can't work out how to 'fool' it when adding a bill through inserts into BillExpenseLine.  The actual Vat amount for each line is not available in the table.  However, if I enter an identical bill manually, I can use the 'edit' buttonnext to the total tax field and can them edit the individual tax amounts for each line.  It works and they can be recalled and changed again.  Anyone know who to access these fields through ODBC? 

An example might help:

Line 1       Non-Vatable Food Purchases         Z  (0.00%)        £ 100.00         (Vat = £0.00)

Line 2       Vatable Food Purchases                  S (17.5%)          £100.00        (Vat=£17.50)

Now say the actual bill received has VAT at £17.51.

How would I add this bill through QODBC?

Thanks

 

 

 

 

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-12-14 17:04:51

See: Creating a Bill with Odd Ball Tax 

 

  Top 
  clueless 
  
 Group: Members 
 Posts: 5 
 Joined: 2008-11-01 
 Profile
 Posted : 2008-12-16 02:18:46

OK, I read that and on the face of it, it seems to relate to my problem but I am still struggling to 'see' it all.

Let me explain.  I have added a bill which has two lines as above:

Line 1       Non-Vatable Food Purchases         Z  (0.00%)        £ 100.00         (Vat = £0.00)
Line 2       Vatable Food Purchases                  S (17.5%)          £100.00        (Vat=£17.51)

Note that with VAT set at 17.5% Line to tax total should be £17.50.

The screen shot below show you how I would use the QB UI to enter and modify the tax amounts.  It only allows me to change the overall tax amounts for each VAT code, not the tax amounts for each line.

Now,  if I do a query on the BillExpenseLine table there are no valules in any of the TAX columns, even the rates:

Dump of BillExpenseLine    
Column 1 2
TxnID BCD-1230728130 BCD-1230728130
TimeCreated 55:30.0 55:30.0
TimeModified 38:31.0 38:31.0
EditSequence 1230734311 1230734311
TxnNumber 265 265
VendorListRefID 8000004C-1230727817 8000004C-1230727817
VendorRefFullName 3663 3663
APAccountRefListID 80000027-1184631976 80000027-1184631976
APAccountRefFullName Accounts Payable Accounts Payable
TxnDate 01/01/2009 01/01/2009
TxnDateMacro All All
DueDate 01/01/2009 01/01/2009
AmountDue 1105.01 1105.01
RefNumber mr-12345678 mr-12345678
TermsRefListID 80000001-1184631975 80000001-1184631975
TermsRefFullName Net 15 Net 15
Memo    
IstaxIncluded 0 0
LinkToTxnID1    
Is Paid 0 0
ExpenseLineClearExpenseLines    
ExpenseLineSeqNo 1 2
ExpenseLineTxnLineID BCF-1230728130 BD0-1230728130
ExpenseLineAccountRefListID 80000047-1230727992 80000048-1230728017
ExpenseLineAccountRefFullName Cost of Sales:Non Vat Food & Supplies Cost of Sales:Vatable Food & Supplies
ExpenseLineAmount 400 600
ExpenseLineMemo    
ExpenseLineCustomerRefListID    
ExpenseLineCustomerRefFullName    
ExpenseLineClassRefListID 80000006-1230727913 80000006-1230727913
ExpenseLineClassRefFullName meadowhall1 meadowhall1
ExpenseLineSalesTaxCodeRefListID    
ExpenseLineTaxCodeRefListID    
ExpenseLineSalesTaxCodeRefFullName    
ExpenseLineTaxAmount    
ExpenseLineTax1Amount    
Tax1Total    
Tax2Total    
ExchangeRate    
AmountIncludesVAT    
ExpenseLineBillableStatus    
FQSaveToCache 0 0
FQPrimaryKey BCD-1230728130 BCD-1230728130

So I can't see where the modified tax amounts are stored and therefore cannot see how to create them, enquire on them or modify them.

To be honest, at this stage all I want to do is create them as I want to replace an existing Import routine from a web server which used IIF.

Anyone shed any light for me?

Thanks.

 

 

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-12-16 10:02:47
Looking at the query dumps for the BillExpenseLine table I don't believe you are using QODBC v9.0.0.253. If that is the case please update QODBC, see: How can I get the latest version of QODBC?  

  Top 
  clueless 
  
 Group: Members 
 Posts: 5 
 Joined: 2008-11-01 
 Profile
 Posted : 2008-12-17 00:15:28

Thanks for that.  We may be getting somewhere now.  The tax codes are now present in the BillExpenseLine but I still don;t see where the tax amounts come from if they are 'adjusted' as in the example above.

Here is a new dump of BillExpenseLine for the above:

Dump of BillExpenseLine
Coumn Line 1 Line 2
TxnID BCD-1230728130 BCD-1230728130
TimeCreated 55:30.0 55:30.0
TimeModified 07:13.0 07:13.0
EditSequence 1230728833 1230728833
TxnNumber 265 265
VendorRefListID 8000004C-1230727817 8000004C-1230727817
VendorRefFullName 3663 3663
APAccountRefListID 80000027-1184631976 80000027-1184631976
APAccountRefFullName Accounts Payable Accounts Payable
TxnDate 01/01/2009 01/01/2009
TxnDateMacro All All
DueDate 01/01/2009 01/01/2009
AmountDue 1105.01 1105.01
RefNumber mr-12345678 mr-12345678
TermsRefListID 80000001-1184631975 80000001-1184631975
TermsRefFullName Net 15 Net 15
Memo    
IsTaxIncluded 0 0
SalesTaxCodeRefListID    
SalesTaxCodeRefFullName    
TaxCodeRefListID    
TaxCodeRefFullName    
LinkToTxnID1    
IsPaid 0 0
ExpenseLineClearExpenseLines    
ExpenseLineSeqNo 1 2
ExpenseLineTxnLineID BCF-1230728130 BD0-1230728130
ExpenseLineAccountRefListID 80000047-1230727992 80000048-1230728017
ExpenseLineAccountRefFullName Cost of Sales:Non Vat Food & Supplies Cost of Sales:Vatable Food & Supplies
ExpenseLineAmount 400 600
ExpenseLineMemo    
ExpenseLineCustomerRefListID    
ExpenseLineCustomerRefFullName    
ExpenseLineClassRefListID 80000006-1230727913 80000006-1230727913
ExpenseLineClassRefFullName meadowhall1 meadowhall1
ExpenseLineSalesTaxCodeRefListID 80000003-1184631977 80000001-1184631977
ExpenseLineTaxCodeRefListID 80000003-1184631977 80000001-1184631977
ExpenseLineSalesTaxCodeRefFullName Z S
ExpenseLineTaxCodeRefFullName Z S
ExpenseLineBillableStatus    
ExpenseLineTaxAmount    
ExpenseLineTax1Amount    
Tax1Total    
Tax2Total    
ExchangeRate    
AmountIncludesVAT    
FQSaveToCache 0 0
FQPrimaryKey BCD-1230728130|BCF-1230728130 BCD-1230728130|BD0-1230728130

Here is dump of Bill record for this transaction:

Dump of Bill
Column Value
TxnID BCD-1230728130
TimeCreated 39813.53854
TimeModified 39813.54668
EditSequence 1230728833
TxnNumber 265
VendorRefListID 8000004C-1230727817
VendorRefFullName 3663
APAccountRefListID 80000027-1184631976
APAccountRefFullName Accounts Payable
TxnDate 39814
TxnDateMacro All
DueDate 39814
AmountDue 1105.01
RefNumber mr-12345678
TermsRefListID 80000001-1184631975
TermsRefFullName Net 15
Memo  
IsTaxIncluded 0
SalesTaxCodeRefListID  
SalesTaxCodeRefFullName  
TaxCodeRefListID  
TaxCodeRefFullName  
LinkToTxnID1  
IsPaid 0
Tax1Total  
Tax2Total

 

 

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-12-17 10:02:38
This appears to be a bug, ExpenseLineTaxAmount is showing NULL when it should be showing the VAT amount in each BillExpenseLine. I've reported the bug to the QODBC Software Engineer under QSupport Ticket ID: BR00000096. When a resolution or workaround is available a response will be posted here. 

  Top 
  clueless 
  
 Group: Members 
 Posts: 5 
 Joined: 2008-11-01 
 Profile
 Posted : 2008-12-17 20:17:28
Thanks, Tom.  Much appreciated. 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to