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
|
|
How do I add customers? |
Author |
Message |
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-02-25 20:28:46 |
If you need to create a customer, you can use a format similar to this:
For USA Editions of QuickBooks: INSERT INTO "customer" ("Name","LastName","FirstName","BillAddressAddr1", "BillAddressAddr2","BillAddressCity","BillAddressState","BillAddressPostalcode") values('Zuniga, Daphne','Zuniga','Daphne','Daphne Zuniga','561 W 4th St.','Carlsbad','CA','92009')
For Non-USA Editions of QuickBooks: INSERT INTO "customer" ("Name","LastName","FirstName","BillAddressAddr1", "BillAddressAddr2","BillAddressCity","BillAddressCounty","BillAddressPostalcode") values('Zuniga, Daphne','Zuniga','Daphne','Daphne Zuniga','561 Smith St.','Lysterfiled','VIC','3156')
For jobs, add ParentRefListID or ParentRefFullName of the Jobs parent record.
Note: Name must be unique across Customer, Employee, Vendor, and OtherName. You can query the Entity to verify if a name already exists.
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-03-10 08:15:51 |
I have inserted a new customer successfully. The customer shows in QuickBooks, but when I do a query in the VB Demo application the customer record isn't there. What causes that to happen and how do I fix it?
This occurs when your computer's time setting has been changed or when using a QuickBooks sample file. When this happens you can resync your optimized customer table by running:-
sp_optimizefullsync customer
or you can by-pass the optimized table by doing:-
select * from customer unoptimized
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-04-05 08:45:32 |
That's nice but what about if I wanted to create a new Customer including custom fields with values in them?
While you can use the CustomField table, here's the more straightforward method of creating a new customer and updating a custom field. Using the Sample Rock Castle Construction example in QuickBooks 2006, if you do a:
sp_columns customer
You will see that the example has the following Custom Fields: CustomFieldBDay, CustomFieldContract and CustomFieldSpousesName. The only limitation is that we can't insert them in an insert, but updates are not a problem!
To create a new customer, you can use a format similar to this:
INSERT INTO "customer" ("Name","LastName","FirstName","BillAddressAddr1", "BillAddressAddr2","BillAddressCity","BillAddressState","BillAddressPostalcode") values('Zuniga, Daphne','Zuniga','Daphne','Daphne Zuniga','561 W 4th St.','Carlsbad','CA','92009')
To find the ListID of our new Customer just created we run the stored procedure:
SP_LASTINSERTID customer
Now that we have the ListID for our new customer, we can simply update the Custom Field Contract with the contract number by doing:
Update Customer Set CustomFieldContract='QODBCJOB1' where ListID='AB0000-1197756245'
And there you have it! To check that everything actually worked just do:
Select ListID, Name, FullName, CustomFieldContract from Customer where ListID='AB0000-1197756245'
Still don't believe it's that simple? Let's look inside QuickBooks ..... Wow!
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-03-21 09:53:45 |
For QuickBooks 2007 USA Editions you now have access to 5th address line (Notes). The following select statement shows all the default columns (except for any custom columns):
SELECT "ListID","TimeCreated","TimeModified","EditSequence","Name","FullName","IsActive","ParentRefListID", "ParentRefFullName","Sublevel","CompanyName","Salutation","FirstName","MiddleName","LastName", "BillAddressAddr1","BillAddressAddr2","BillAddressAddr3","BillAddressAddr4","BillAddressAddr5", "BillAddressCity","BillAddressState","BillAddressPostalCode","BillAddressCountry","BillAddressNote", "BillAddressBlockAddr1","BillAddressBlockAddr2","BillAddressBlockAddr3","BillAddressBlockAddr4", "BillAddressBlockAddr5","ShipAddressAddr1","ShipAddressAddr2","ShipAddressAddr3","ShipAddressAddr4", "ShipAddressAddr5","ShipAddressCity","ShipAddressState","ShipAddressPostalCode","ShipAddressCountry", "ShipAddressNote","ShipAddressBlockAddr1","ShipAddressBlockAddr2","ShipAddressBlockAddr3", "ShipAddressBlockAddr4","ShipAddressBlockAddr5","Phone","AltPhone","Fax","Contact", "AltContact","CustomerTypeRefListID","CustomerTypeRefFullName","TermsRefListID","TermsRefFullName", "SalesRepRefListID","SalesRepRefFullName","Balance","TotalBalance","OpenBalance","OpenBalanceDate", "SalesTaxCodeRefListID","SalesTaxCodeRefFullName","ItemSalesTaxRefListID","ItemSalesTaxRefFullName", "ResaleNumber","AccountNumber","CreditLimit","PreferredPaymentMethodRefListID", "PreferredPaymentMethodRefFullName","CreditCardInfoCreditCardNumber","CreditCardInfoExpirationMonth", "CreditCardInfoExpirationYear","CreditCardInfoNameOnCard","CreditCardInfoCreditCardAddress", "CreditCardInfoCreditCardPostalCode","JobStatus","JobStartDate","JobProjectedEndDate","JobEndDate", "JobDesc","JobTypeRefListID","JobTypeRefFullName","PriceLevelRefListID","PriceLevelRefFullName" FROM "Customer"
|
|
|
|
|