Excel Add-In for Zoho Books

Build 24.0.9060

ExpenseDetails

List, add, update and delete details of an Expense.

Table Specific Information

Select

The add-in uses the Zoho Books API to process WHERE clause conditions built with the following column and operator:

  • ExpenseId supports the '=' and IN operators.

NOTE: ExpenseId is required to query ExpenseDetails.

The rest of the filter is executed client-side in the add-in.

For example: 

    SELECT * FROM ExpenseDetails WHERE ExpenseId = '1894553000000077244'
	SELECT * From ExpenseDetails WHERE ExpenseId IN (SELECT ExpenseId FROM Expenses)
	SELECT * FROM EstimateLineItems WHERE EstimateId IN ('1894553000000077244','1894553000000077245')

Insert

INSERT can be executed by specifying the AccountId, Date, Amount, and PaidThroughAccountId columns. The columns that are not read-only can be inserted optionally. The following is an example of how to insert into this table. 

INSERT INTO ExpenseDetails (AccountId, Date, Amount, PaidThroughAccountId) VALUES ('3285934000000000409', '2023-01-19', '500', '3285934000000259036')

Update

UPDATE can be executed by specifying the ExpenseId in the WHERE Clause. The columns that are not read-only can be updated. For example: 

UPDATE ExpenseDetails SET AccountId = '3285934000000000409', Date = '2023-01-19', Amount = '300', PaidThroughAccountId = '3285934000000259036' WHERE ExpenseId = '3285934000000262014'

Delete

DELETE can be executed by specifying the ExpenseId in the WHERE Clause For example: 

DELETE FROM ExpenseDetails WHERE ExpenseId = '3285934000000262014'

Columns

Name Type ReadOnly References SupportedOperators Description
ExpenseId [KEY] String True

Expenses.ExpenseId

Id of an expense.

AccountId String False

BankAccounts.AccountId

Id of the Bank Account.

AccountName String True

Name of the account.

Amount Decimal False

Amount of the expenses.

ApproverEmail String True

Email of an approver.

ApproverId String True

Users.UserId

Id of an approver.

ApproverName String True

Name of an approver.

AcquisitionVatId String False

This is the Id of the tax applied in case this is an EU - goods expense and acquisition VAT needs to be reported.

BcySurchargeAmount Decimal True

Surcharge amount of Base Currency.

BcyTotal Decimal True

Total Base Currency.

CanReclaimVatOnMileage String False

To specify if tax can be reclaimed for this mileage expense.

CreatedById String True

Users.UserId

Id of a user who has created expense.

CreatedTime Datetime True

Time at which the Expense was created.

CurrencyCode String True

Currency code of the customer's currency.

CurrencyId String False

Currencies.CurrencyId

Currency Id of the customer's currency.

CustomerId String False

Contacts.ContactId

Id of the customer or vendor.

CustomerName String True

Name of the customer or vendor.

CustomFields String False

Custom fields of the contact.

Date Date False

Expense date.

Description String False

Description of the expense.

DestinationOfSupply String False

Place where the goods/services are supplied to. (If not given, organisation's home state will be taken).

Distance String False

Distance travelled for a particular mileage expense where mileage_type is manual.

EmployeeEmail String True

Email Id of an employee.

EmployeeId String False

Employees.EmployeeId

Id of an employee.

EmployeeName String True

Name of an employee.

EndReading String False

End Reading of the Odometer.

ExchangeRate Decimal False

Exchange rate of the currency.

ExpenseItemId String True

Item Id of an expense.

ExpenseReceiptName String True

Receipt name of an expense.

ExpenseReceiptType String True

Receipt type of an expense.

ExpenseType String True

Type of expense.

EngineCapacityRange String False

Engine capacity range for a particular mileage expense. Allowed Values: less_than_1400cc, between_1400cc_and_1600cc, between_1600cc_and_2000cc and more_than_2000cc.

HSNORSAC String False

Add HSN/SAC code for your goods/services.

FcySurchargeAmount Decimal True

Surcharge amount of Foreign Currency.

FuelType String False

Fuel type for a particular mileage expense. Allowed Values: petrol, lpg and diesel.

GstNo String False

GST number used for credit note.

InvoiceConversionType String True

Type of invoice conversion.

InvoiceId String True

Invoices.InvoiceId

Id of an invoice.

InvoiceNumber String True

Number of an invoice.

IsBillable Boolean False

Check if the expense is billable.

IsInclusiveTax Boolean False

Check if the expense is inclusive tax.

IsPersonal Boolean True

Check if the expense is personal.

IsPreGst Boolean True

Check if the pre GST is applied in the expense.

IsRecurringApplicable Boolean True

Check if the recurring is applicable.

IsReimbursable Boolean True

Check if the expense is reimbursable.

IsSurchargeApplicable Boolean True

Check if the surcharge is applicable in this expense.

LastModifiedById String True

Users.UserId

Id of the user last modified.

LastModifiedTime Datetime True

The time of last modification of the expense.

LineItems String False

Line items of an estimate.

Location String True

Location of the expense.

MerchantId String True

Id of the merchant.

MerchantName String True

Name of the merchant.

MileageRate Double False

Mileage rate for a particular mileage expense.

MileageType String False

Type of Mileage.

MileageUnit String False

Unit of the distance travelled.

PaidThroughAccountId String False

BankAccounts.AccountId

Account Id from which expense amount was paid.

PaidThroughAccountName String True

Account name from which expense amount was paid.

PaymentMode String True

Mode through which payment is made.

ProjectId String False

Projects.ProjectId

Id of the project.

ProjectName String True

Name of the project.

ProductType String False

Type of the expense. This denotes whether the expense is to be treated as a goods or service purchase.

PlaceOfSupply String False

The place of supply is where a transaction is considered to have occurred for VAT purposes.

ReferenceNumber String False

Reference number of expense.

ReportId String True

Id of the report.

ReportName String True

Name of the report.

ReportNumber String True

Number of the report.

ReportStatus String True

Status of the report.

ReverseChargeVatId String False

This is the Id of the tax applied in case this is a non UK - service expense and reverse charge VAT needs to be reported.

ReverseChargeTaxId String False

Id of the reverse charge tax.

StartReading String False

Start Reading of the Odometer.

Status String True

Status of the expense.

SubTotal Decimal True

Sub total of expenses.

SourceOfSupply String False

Place from where the goods/services are supplied. (If not given, place of contact given for the contact will be taken).

Tags String True

Details of tags related to expenses.

TaxAmount Decimal True

Amount of tax.

TaxId String False

Taxes.TaxId

Id of tax.

TaxName String True

Name of tax.

TaxPercentage Integer True

Percentage of tax.

Total Decimal True

Total of expenses.

TransactionType String True

Type of the Transaction.

TripId String True

Id of a trip.

TripNumber String True

Number of a trip.

UserEmail String True

Email Id of a User.

UserId String False

Users.UserId

Id of a user.

UserName String True

Name of a user.

VehicleId String True

Id of a vehicle.

VehicleType String False

Vehicle type for a particular mileage expense. Allowed Values: car, van, motorcycle and bike.

VehicleName String True

Name of a vehicle.

VendorId String False

Id of the vendor the expenses has been made.

VendorName String True

Name of the vendor the expenses has been made.

VatTreatment String False

VAT treatment for the estimates.

TaxTreatment String False

VAT treatment for the Estimate.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Receipt String

Expense receipt file to attach. Allowed Extensions: gif, png, jpeg, jpg, bmp, pdf, xls, xlsx, doc and docx.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9060