Reckon Accounts Hosted Connection Details¶

Introduction¶

Connector Version

This documentation is based on version 23.0.8804 of the connector.

Get Started¶

Reckon Accounts Hosted Version Support

The connector supports v2 of the Reckon Premier, Professional, Enterprise, and Simple Start APIs. All Reckon editions are supported.

Establish a Connection¶

Connect to Reckon Accounts Hosted¶

Reckon Accounts Hosted supports OAuth authentication only. To enable this authentication from all OAuth flows, you must set AuthScheme to OAuth.

The following subsections describe how to authenticate to Reckon Accounts Hosted from three common authentication flows:

• Desktop: a connection to a server on the user's local machine, frequently used for testing and prototyping. Authenticated via either embedded OAuth or custom OAuth.
• Web: access to data via a shared website. Authenticated via custom OAuth only.
• Headless Server: a dedicated computer that provides services to other computers and their users, which is configured to operate without a monitor and keyboard. Authenticated via embedded OAuth or custom OAuth.

For information about how to create a custom OAuth application, and why you might want to create one even for auth flows that have embedded OAuth credentials, see Creating a Custom OAuth Application.

For a complete list of connection string properties available in Reckon Accounts Hosted, see Connection.

Desktop Applications¶

provides an embedded OAuth application that simplifies authentication at the desktop; that is, in situations where the user is using a local server not connected to the internet.

You can also authenticate from the desktop via a custom OAuth application, which you configure and register at the Reckon Accounts Hosted console. For further information, see Creating a Custom OAuth Application.

Before you connect, set these properties:

• InitiateOAuth: GETANDREFRESH. Used to automatically get and refresh the OAuthAccessToken.
• Custom OAuth applications only:
  • OAuthClientId: The client ID assigned when you registered your custom OAuth application.
  • OAuthClientSecret: The client secret assigned when you registered your custom OAuth application.
  • CallbackURL: The redirect URI defined when you registered your custom OAuth application.

When you connect, the connector opens Reckon Accounts Hosted's OAuth endpoint in your default browser. Log in and grant permissions to the application.

After you grant permissions to the application, the connector completes the OAuth process:

1. The connector obtains an access token from Reckon Accounts Hosted and uses it to request data.
2. The OAuth values are saved in the path specified in OAuthSettingsLocation. These values persist across connections.

When the access token expires, the connector refreshes it automatically.

Automatic refresh of the OAuth access token:

To have the connector automatically refresh the OAuth access token, do the following:

1. The first time you connect to data, set these connection parameters:
   • InitiateOAuth: REFRESH.
   • OAuthClientId: The client ID in your application settings.
   • OAuthClientSecret: The client secret in your application settings.
   • OAuthAccessToken: The access token returned by GetOAuthAccessToken.
   • OAuthSettingsLocation: The path where you want the connector to save the OAuth values, which persist across connections.
2. On subsequent data connections, set:
   • InitiateOAuth
   • OAuthSettingsLocation

Manual refresh of the OAuth access token:

The only value needed to manually refresh the OAUth access token is the OAuth refresh token.

1. To manually refresh the OAuthAccessToken after the ExpiresIn period (returned by GetOAuthAccessToken) has elapsed, call the RefreshOAuthAccessToken stored procedure.
2. Set these connection properties:
   • OAuthClientId: The Client ID in your application settings.
   • OAuthClientSecret: The Client Secret in your application settings.
3. Call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken.
4. After the new tokens have been retrieved, set the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken. This opens a new connection.

Store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Create a Custom OAuth Application¶

Create a Custom OAuth Application¶

embeds OAuth Application Credentials with branding that can be used when connecting to Reckon Accounts Hosted via a desktop application or a headless machine. If you want to use the embedded OAuth application, all you need to do to connect is to:

• set AuthScheme to OAuth,
• get and set the OAuthAccessToken, and
• set the necessary configuration parameters.

(For information on getting and setting the OAuthAccessToken and other configuration parameters, see the Desktop Authentication section of "Connecting to Reckon Accounts Hosted".)

However, you must create a custom OAuth application to connect to Reckon Accounts Hosted via the Web. And since custom OAuth applications seamlessly support all three commonly-used auth flows, you might want to create custom OAuth applications (use your own OAuth Application Credentials) for those auth flows anyway.

Custom OAuth applications are useful if you want to:

• control branding of the authentication dialog;
• control the redirect URI that the application redirects the user to after the user authenticates; or
• customize the permissions that you are requesting from the user.

Procedure¶

To register a custom OAuth application and obtain the OAuthClientId and OAuthClientSecret:

1. Got to the ReckonAccountsHosted Developer Portal.

2. To create a developer account, click Sign Up.

   The Reckon Accounts Hosted Developer site displays a series of prompts that you can use to register a custom OAuth application.

3. Complete the registration. If this will be a web application, set CallbackURL to a trusted URL where users return after they authorize your application.
   Note the redirectURI for future use.

4. Submit the application form.
   The Reckon Accounts Hosted Developer site sends an email containing the Client ID and Client Secret to the user whose login was specified during application creation. The Client ID and Client Secret are used to configure the OAuthClientId and OAuthClientSecret properties.

Important Notes¶

Configuration Files and Their Paths¶

• All references to adding configuration files and their paths refer to files and locations on the Harmony Agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features¶

This section details a selection of advanced features of the Reckon Accounts Hosted connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using Private Agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Reckon Accounts Hosted and then processes the rest of the query in memory (client-side).

User Defined Views¶

The Reckon Accounts Hosted connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File¶

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Customers WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements¶

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View¶

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View¶

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View¶

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views¶

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views¶

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

Insert Parent and Child Records¶

Use Case¶

When inserting records, often there is a need to fill in details about child records that have a dependency on a parent.

For instance, when dealing with a CRM system, Invoices often cannot be entered without at least one line item. Since invoice line items can have several fields, this presents a unique challenge when offering the data as relational tables.

When reading the data, it is easy enough to model an Invoice and an InvoiceLineItem table with a foreign key connecting the two. However, during inserts, the CRM system requires both the Invoice and the InvoiceLineItems to be created in a single submission.

To solve this sort of problem, our tools offer child collection columns on the parent. These columns can be used to submit insert statements that include details of both the parent and the child records.

For example, let's say that the Invoice table contains a single column called InvoiceLineItems. During the insert, we can pass the details of the records that must be inserted to the InvoiceLineItems table into Invoice record's InvoiceLineItems column.

The following subsection describes how this might be done.

Methods for Inserting Parent/Child Records¶

The connector facilitates two methods for inserting parent/child records: temporary table insertion and XML aggregate insertion.

Temporary (#TEMP) tables¶

The simplest way to enter data would be to use a #TEMP table, or temporary table, which the connector will store in memory.

Reference the #TEMP table with the following syntax:

TableName#TEMP

#TEMP tables are stored in memory for the duration of a connection.

Therefore, in order to use them, you cannot close the connection between submitting inserts to them, and they cannot be used in environments where a different connection may be used for each query.

Within that single connection, the table remains in memory until the bulk insert is successful, at which point the temporary table will be wiped from memory.

For example:

INSERT INTO InvoiceLineItems#TEMP (ReferenceNumber, Item, Quantity, Amount) VALUES ('INV001', 'Basketball', 10, 9.99)
INSERT INTO InvoiceLineItems#TEMP (ReferenceNumber, Item, Quantity, Amount) VALUES ('INV001', 'Football', 5, 12.99)

Once the InvoiceLineItems table is populated, the #TEMP table may be referenced during an insert into the Invoice table:

INSERT INTO Invoices (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', 'InvoiceLineItems#TEMP')

Under the hood, the connector will read in values from the #TEMP table.

Notice that the ReferenceNumber was used to identify what Invoice the lines are tied to. This is because the #TEMP table may be populated and used with a bulk insert, where there are separate lines for each invoice. This enables the #TEMP tables to be used with a bulk insert. For example:

INSERT INTO Invoices#TEMP (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', 'InvoiceLineItems#TEMP')
INSERT INTO Invoices#TEMP (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV002', 'Jane Doe', 'InvoiceLineItems#TEMP')
INSERT INTO Invoices SELECT ReferenceNumber, Customer, InvoiceLines FROM Invoices#TEMP

In this case, we are inserting two different Invoices. The ReferenceNumber is how we determine which Lines go with which Invoice.

Direct XML Insertion¶

Direct XML can be used as an alternative to #TEMP tables. Since #TEMP tables are not used to construct them, it does not matter if you use the same connection or close the connection after insert.

For example:

[
  {
    "Item", "Basketball",
    "Quantity": 10
    "Amount": 9.99
  },
  {
    "Item", "Football",
    "Quantity": 5
    "Amount": 12.99
  }
]

OR

<Row>
  <Item>Basketball</Item>
  <Quantity>10</Quantity>
  <Amount>9.99</Amount>
</Row>
<Row>
  <Item>Football</Item>
  <Quantity>5</Quantity>
  <Amount>12.99</Amount>
</Row>

Note that the ReferenceNumber is not present in these examples because the XML, by its nature, is passed against the parent record in full per insert. Since the complete XML must be constructed and submitted for each row, there is no need to provide something to tie the child back to the parent.

Now insert the values:

INSERT INTO Invoices (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', '{...}')

OR

INSERT INTO Invoices (ReferenceNumber, Customer, InvoiceLines) VALUES ('INV001', 'John Doe', '<Row>...</Row>')

Example for Reckon Accounts Hosted¶

For a working example of how temp tables can be used for bulk insert in Reckon Accounts Hosted, please see the following:

// Insert into Invoices table
INSERT INTO InvoiceLineItems#TEMP (ItemName, ItemQuantity) VALUES ('Repairs','1')
INSERT INTO InvoiceLineItems#TEMP (ItemName, ItemQuantity) VALUES ('Removal','2')

INSERT INTO Invoices (CustomerName, Memo, ItemAggregate) VALUES ('Abercrombie, Kristy', 'NUnit Memo', 'InvoiceLineItems#TEMP')


// Insert into InvoiceLineItems table
INSERT INTO InvoiceLineItems#TEMP (CustomerName, Date, ShipMethod, ShipDate, Memo, Message, DueDate, Other, ItemName, ItemQuantity, ItemRate) VALUES ('Abercrombie, Kristy', '2011-01-01', 'UPS', '2011-01-02', 'NUnit Memo', 'We appreciate your prompt payment.', '2011-01-03', 'Some other data', 'Repairs', '1', '3.50')
INSERT INTO InvoiceLineItems#TEMP (CustomerName, Date, ShipMethod, ShipDate, Memo, Message, DueDate, Other, ItemName, ItemQuantity, ItemRate) VALUES ('Abercrombie, Kristy', '2011-01-01', 'UPS', '2011-01-02', 'NUnit Memo', 'We appreciate your prompt payment.', '2011-01-03', 'Some other data', 'Removal', '2', '3.50')

INSERT INTO InvoiceLineItems (CustomerName, Date, ShipMethod, ShipDate, Memo, Message, DueDate, Other, ItemName, ItemQuantity, ItemRate) SELECT CustomerName, Date, ShipMethod, ShipDate, Memo, Message, DueDate, Other, ItemName, ItemQuantity, ItemRate InvoiceLineItems#TEMP

SSL Configuration¶

Customize the SSL Configuration¶

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model¶

The Reckon Accounts Hosted connector models entities in the Reckon Accounts Hosted API as tables, views, and stored procedures. There are three parts to the Data Model: Tables, Views, and Stored Procedures. These are defined in schema files, which are simple, text-based configuration files.

API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

The Reckon Accounts Hosted connector models the data in Tables so that it can be easily queried and updated.

Views

Views are tables that cannot be modified. Typically, read-only data are shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

Tables¶

The connector models the data in Reckon Accounts Hosted as a list of tables in a relational database that can be queried using standard SQL statements.

Reckon Accounts Hosted Connector Tables¶

Accounts¶

Create, update, delete, and query Reckon Accounts.

Table Specific Information¶

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

Reckon Accounts Hosted allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for Accounts are Id, Name, Type, IsActive, and TimeModified. TimeModified may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. Name may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM Accounts WHERE Name LIKE '%Bank%' AND TimeModified > '1/1/2011' AND TimeModified < '2/1/2011'

Insert¶

To add an Account, specify the Name and Type fields.

Columns¶

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.

BillExpenseItems¶

Create, update, delete, and query Reckon Bill Expense Line Items.

Table Specific Information¶

Bills may be inserted, queried, or updated via the Bills, BillExpenseItems, or BillLineItems tables. Bills may be deleted by using the Bills table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

Reckon Accounts Hosted allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for Bills are Id, Date, ReferenceNumber, VendorName, VendorId, AccountsPayable, AccountsPayableId, IsPaid, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM Bills WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert¶

You can also use Bills and BillExpenseItems to insert a Bill.

To add a Bill, specify a Vendor, Date, and at least one Expense or Line Item. All Expense Line Item columns can be used for inserting multiple expense Line Items for a new Bill transaction. For example, the following will insert a new Bill with two Expense Line Items:

INSERT INTO BillExpenseItems#TEMP (VendorName, Date, ExpenseAccount, ExpenseAmount) VALUES ('Cal Telephone', '1/1/2011', 'Utilities:Telephone', 52.25)
INSERT INTO BillExpenseItems#TEMP (VendorName, Date, ExpenseAccount, ExpenseAmount) VALUES ('Cal Telephone', '1/1/2011', 'Professional Fees:Accounting', 235.87)
INSERT INTO BillExpenseItems (VendorName, Date, ExpenseAccount, ExpenseAmount) SELECT VendorName, Date, ExpenseAccount, ExpenseAmount FROM BillExpenseItems#TEMP

Columns¶

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.

BillLineItems¶

Create, update, delete, and query Reckon Bill Line Items.

Table Specific Information¶

Bills may be inserted, queried, or updated via the Bills, BillExpenseItems, or BillLineItems tables. Bills may be deleted by using the Bills table.

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

Reckon Accounts Hosted allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for Bills are Id, Date, ReferenceNumber, VendorName, VendorId, AccountsPayable, AccountsPayableId, IsPaid, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM Bills WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert¶

You can also use Bills and BillExpenseItems to insert a Bill.

To add a Bill, specify a Vendor, Date, and at least one Expense or Line Item. All Line Item columns can be used for inserting multiple Line Items for a new Bill transaction. For example, the following will insert a new Bill with two Line Items:

INSERT INTO BillLineItems#TEMP (VendorName, Date, ItemName, ItemQuantity) VALUES ('Cal Telephone', '1/1/2011', 'Repairs', 1)
INSERT INTO BillLineItems#TEMP (VendorName, Date, ItemName, ItemQuantity) VALUES ('Cal Telephone', '1/1/2011', 'Removal', 2)
INSERT INTO BillLineItems (VendorName, Date, ItemName, ItemQuantity) SELECT VendorName, Date, ItemName, ItemQuantity FROM BillLineItems#TEMP

Columns¶

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.

BillPaymentChecks¶

Create, update, delete, and query Reckon Bill Payment Checks.

Table Specific Information¶

BillPaymentChecks may be inserted, queried, or updated via the BillPaymentChecks or BillPaymentChecksAppliedTo tables. BillPaymentChecks may be deleted by using the BillPaymentChecks table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

Reckon Accounts Hosted allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for BillPaymentChecks are Id, Date, ReferenceNumber, PayeeName, PayeeId, AccountsPayable, AccountsPayableId, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM BillPaymentChecks WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert¶

To add a BillPaymentCheck, specify a Payee and BankAccount. The Payee must match the Vendor associated with the Bill you are adding a payment for. The AppliedToAggregate column may be used to specify an XML aggregate of AppliedTo data. The columns that may be used in these aggregates are defined in the BillPaymentChecksAppliedTo table and it starts with AppliedTo. For example, the following will insert a new BillPaymentCheck with two AppliedTo entries:

INSERT INTO BillPaymentChecks (PayeeName, BankAccountName, AppliedToAggregate)
VALUES ('Vu Contracting', 'Checking',
'<BillPaymentChecksAppliedTo>
<Row><AppliedToRefId>178C1-1450221347</AppliedToRefId><AppliedToAmount>20.00</AppliedToAmount></Row>
<Row><AppliedToRefId>178C1-1450221347</AppliedToRefId><AppliedToAmount>51.25</AppliedToAmount></Row>
</BillPaymentChecksAppliedTo>')

AppliedToRefId is a reference to a BillId and can be found in Bills, BillLineItems, or BillExpenseItems.

Columns¶

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.

BillPaymentChecksAppliedTo¶

Create, update, delete, and query Reckon Bill Payment AppliedTo aggregates. In a Bill Payment, each AppliedTo aggregate represents the Bill transaction to which this part of the payment is being applied.

Table Specific Information¶

BillPaymentChecks may be inserted, queried, or updated via the BillPaymentChecks or BillPaymentChecksAppliedTo tables. BillPaymentChecks may be deleted by using the BillPaymentChecks table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

Reckon Accounts Hosted allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for BillPaymentChecks are Id, Date, ReferenceNumber, PayeeName, PayeeId, AccountsPayable, AccountsPayableId, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM BillPaymentChecksAppliedTo WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert¶

To add a BillPaymentCheck entry, specify the Payee and BankAccount fields. The Payee must match the Vendor associated with the Bill you are adding a payment for. All AppliedTo columns can be used to explicitly identify the Bills being paid. For example, the following will insert a new BillPaymentCheck with two AppliedTo entries:

INSERT INTO BillPaymentChecksAppliedTo#TEMP (PayeeName, BankAccountName, AppliedToRefId, AppliedToAmount) VALUES ('Vu Contracting', 'Checking', '178C1-1450221347', 20.00)
INSERT INTO BillPaymentChecksAppliedTo#TEMP (PayeeName, BankAccountName, AppliedToRefId, AppliedToAmount) VALUES ('Vu Contracting', 'Checking', '881-933371709', 51.25)
INSERT INTO BillPaymentChecksAppliedTo (PayeeName, BankAccountName, AppliedToRefId, AppliedToAmount) SELECT PayeeName, BankAccountName, AppliedToRefId, AppliedToAmount FROM BillPaymentChecksAppliedTo#TEMP

AppliedToRefId is a reference to a BillId and can be found in Bills, BillLineItems, or BillExpenseItems.

Columns¶

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.

BillPaymentCreditCards¶

Create, update, delete, and query Reckon Bill Payments.

Table Specific Information¶

BillPaymentCreditCards may be inserted, queried, or updated via the BillPaymentCreditCards or BillPaymentCreditCardsAppliedTo tables. BillPaymentCreditCards may be deleted by using the BillPaymentCreditCards table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

Reckon Accounts Hosted allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for BillPaymentCreditCards are Id, Date, ReferenceNumber, PayeeName, PayeeId, AccountsPayable, AccountsPayableId, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM BillPaymentCreditCards WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert¶

To add a BillPaymentCreditCard, specify a Payee and CreditCard. The Payee must match the Vendor associated with the Bill you are adding a payment for. The AppliedToAggregate column may be used to specify an XML aggregate of AppliedTo data. The columns that may be used in these aggregates are defined in the BillPaymentCreditCardsAppliedTo table and it starts with AppliedTo. For example, the following will insert a new BillPaymentCreditCard with two AppliedTo entries:

INSERT INTO BillPaymentCreditCard (PayeeName, CreditCardName, AppliedToAggregate)
VALUES ('Vu Contracting', 'CalOil Credit Card',
'<BillPaymentCreditCardsAppliedTo>
<Row><AppliedToRefId>178C1-1450221347</AppliedToRefId><AppliedToAmount>20.00</AppliedToAmount></Row>
<Row><AppliedToRefId>881-933371709</AppliedToRefId><AppliedToAmount>51.25</AppliedToAmount></Row>
</BillPaymentCreditCardsAppliedTo>')

AppliedToRefId is a reference to a BillId and can be found in Bills, BillLineItems, or BillExpenseItems.

Columns¶

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.

BillPaymentCreditCardsAppliedTo¶

Create, update, delete, and query Reckon Bill Payment AppliedTo aggregates. In a Bill Payment, each AppliedTo aggregate represents the Bill transaction to which this part of the payment is being applied.

Table Specific Information¶

BillPaymentCreditCards may be inserted, queried, or updated via the BillPaymentCreditCards or BillPaymentCreditCardsAppliedTo tables. BillPaymentCreditCards may be deleted by using the BillPaymentCreditCards table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

Reckon Accounts Hosted allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for BillPaymentCreditCards are Id, Date, ReferenceNumber, PayeeName, PayeeId, AccountsPayable, AccountsPayableId, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM BillPaymentCreditCardsAppliedTo WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert¶

You can also use BillPaymentCreditCards to insert a BillPaymentCreditCard.

To add a BillPaymentCreditCard, specify a Payee and CreditCard. The Payee must match the Vendor associated with the Bill you are adding a payment for. All AppliedTo columns can be used to explicitly identify the Bills being paid. For example, the following will insert a new BillPaymentCreditCard with two AppliedTo entries:

INSERT INTO BillPaymentCreditCardsAppliedTo#TEMP (PayeeName, CreditCardName, AppliedToRefId, AppliedToAmount) VALUES ('Vu Contracting', 'CalOil Credit Card', '178C1-1450221347', 20.00)
INSERT INTO BillPaymentCreditCardsAppliedTo#TEMP (PayeeName, CreditCardName, AppliedToRefId, AppliedToAmount) VALUES ('Vu Contracting', 'CalOil Credit Card', '881-933371709', 51.25)
INSERT INTO BillPaymentCreditCardsAppliedTo (PayeeName, CreditCardName, AppliedToRefId, AppliedToAmount) SELECT PayeeName, CreditCardName, AppliedToRefId, AppliedToAmount FROM BillPaymentCreditCardsAppliedTo#TEMP

AppliedToRefId is a reference to a BillId and can be found in Bills, BillLineItems, or BillExpenseItems.

Columns¶

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.

Bills¶

Create, update, delete, and query Reckon Bills.

Table Specific Information¶

Bills may be inserted, queried, or updated via the Bills, BillExpenseItems, or BillLineItems tables. Bills may be deleted by using the Bills table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

Reckon Accounts Hosted allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for Bills are Id, Date, ReferenceNumber, VendorName, VendorId, AccountsPayable, AccountsPayableId, IsPaid, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM Bills WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert¶

You can also use BillLineItems and BillExpenseItems to insert a bill.

To add a Bill, specify a Vendor, Date, and at least one Expense or Line Item. The ItemAggregate and ExpenseAggregate columns may be used to specify an XML aggregate of Line or Expense Item data. The columns that may be used in these aggregates are defined in the BillLineItems and BillExpenseItems tables and it starts with Item and Expense. For example, the following will insert a new Bill with two Line Items:

INSERT INTO Bills (VendorName, Date, ItemAggregate)
VALUES ('Cal Telephone', '1/1/2011',
'<BillLineItems>
<Row><ItemName>Repairs</ItemName><ItemQuantity>1</ItemQuantity></Row>
<Row><ItemName>Removal</ItemName><ItemQuantity>2</ItemQuantity></Row>
</BillLineItems>')

To insert subitems, set the ItemName field to the FullName of the item; for example, '<Row><ItemName>Subs:Carpet</ItemName><ItemQuantity>0</ItemQuantity></Row>'

Columns¶

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.

BuildAssemblies¶

Delete and query Reckon Build Assembly transactions.

Columns¶

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.

BuildAssemblyLineItems¶

Create and query Reckon Build Assembly transactions.

Columns¶

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.

CheckExpenseItems¶

Create, update, delete, and query Reckon Check Expense Line Items.

Table Specific Information¶

Checks may be inserted, queried, or updated via the Checks, CheckExpenseItems, or CheckLineItems tables. Checks may be deleted by using the Checks table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

Reckon Accounts Hosted allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for Checks are Id, Date, ReferenceNumber, Payee, PayeeId, Account, AccountId, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM CheckExpenseItems WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

SELECT * FROM CheckExpenseItems WHERE Date >= '2020-01-07' AND Date < '2020-01-10'

SELECT * FROM CheckExpenseItems WHERE [Date] = '2020-01-07' 

Insert¶

To add a Check, specify an Account, a Date, and at least one Expense or Line Item. All Expense Line Item columns can be used for inserting multiple Expense Line Items for a new Check transaction. For example, the following will insert a new Check with two Expense Line Items:

INSERT INTO CheckExpenseItems#TEMP (Account, Date, ExpenseAccount, ExpenseAmount) VALUES ('Checking', '1/1/2011', 'Utilities:Telephone', 52.25,)
INSERT INTO CheckExpenseItems#TEMP (Account, Date, ExpenseAccount, ExpenseAmount) VALUES ('Checking', '1/1/2011', 'Professional Fees:Accounting', 235.87)
INSERT INTO CheckExpenseItems (Account, Date, ExpenseAccount, ExpenseAmount) SELECT Account, Date, ExpenseAccount, ExpenseAmount FROM CheckExpenseItems#TEMP 

Columns¶

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.

CheckLineItems¶

Create, update, delete, and query Reckon Check Line Items.

Table Specific Information¶

Checks may be inserted, queried, or updated via the Checks, CheckExpenseItems, or CheckLineItems tables. Checks may be deleted by using the Checks table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

Reckon Accounts Hosted allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for Checks are Id, Date, ReferenceNumber, Payee, PayeeId, Account, AccountId, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM CheckLineItems WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

SELECT * FROM CheckLineItems WHERE Date >= '2020-01-07' AND Date < '2020-01-10'

SELECT * FROM CheckLineItems WHERE [Date] = '2020-01-07' 

Insert¶

To add a Check, specify an Account, a Date, and at least one Expense or Line Item. All Line Item columns can be used for inserting multiple Line Items for a new Check transaction. For example, the following will insert a new Check with two Line Items:

INSERT INTO CheckLineItems#TEMP (Account, Date, ItemName, ItemQuantity) VALUES ('Checking', '1/1/2011', 'Repairs', 1)
INSERT INTO CheckLineItems#TEMP (Account, Date, ItemName, ItemQuantity) VALUES ('Checking', '1/1/2011', 'Removal', 2)
INSERT INTO CheckLineItems (Account, Date, ItemName, ItemQuantity) VALUES Account, Date, ItemName, ItemQuantity FROM CheckLineItems#TEMP

Columns¶

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.

Checks¶

Create, update, delete, and query Reckon Checks.

Table Specific Information¶

Checks may be inserted, queried, or updated via the Checks, CheckExpenseItems, or CheckLineItems tables. Checks may be deleted by using the Checks table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

Reckon Accounts Hosted allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for Checks are Id, Date, ReferenceNumber, Payee, PayeeId, Account, AccountId, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM Checks WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

SELECT * FROM Checks WHERE Date >= '2020-01-07' AND Date < '2020-01-10'

SELECT * FROM Checks WHERE [Date] = '2020-01-07' 

Insert¶

To add a Check, specify an Account, a Date, and at least one Expense or Line Item. The ItemAggregate and ExpenseAggregate columns may be used to specify an XML aggregate of Line or Expense Item data. The columns that may be used in these aggregates are defined in the CheckLineItems and CheckExpenseItems tables and it starts with Item and Expense. For example, the following will insert a new Check with two Line Items:

INSERT INTO Checks (Account, Date, ItemAggregate) VALUES ('Checking', '1/1/2011',
'<CheckLineItems>
<Row><ItemName>Repairs</ItemName><ItemQuantity>1</ItemQuantity></Row>
<Row><ItemName>Removal</ItemName><ItemQuantity>2</ItemQuantity></Row>
</CheckLineItems>')

To insert subitems, set the ItemName field to the FullName of the item; for example, '<Row><ItemName>Subs:Carpet</ItemName><ItemQuantity>0</ItemQuantity></Row>'

Columns¶

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.

Class¶

Create, delete, and query Reckon Classes.

Table Specific Information¶

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

Reckon Accounts Hosted allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for the Class table are Id, Name, and IsActive. Name may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax.

Insert¶

To insert a Class, specify the Name field.

Columns¶

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.

CreditCardChargeExpenseItems¶

Create, update, delete, and query Reckon Credit Card Charge Expense Line Items.

Table Specific Information¶

CreditCardCharges may be inserted, queried, or updated via the CreditCardCharges, CreditCardChargeExpenseItems, or CreditCardChargeLineItems tables. CreditCardCharges may be deleted by using the CreditCardCharges table.

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

Reckon Accounts Hosted allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for CreditCardCharges are Id, ReferenceNumber, Date, TimeModified, AccountName, and AccountId. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM CreditCardChargeExpenseItems WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert¶

To add a CreditCardCharge, specify an Account and at least one Expense or Line Item. All Expense Line Item columns can be used for inserting multiple Expense Line Items for a new CreditCardCharge transaction. For example, the following will insert a new CreditCardCharge with two Expense Line Items:

INSERT INTO CreditCardChargeExpenseItems#TEMP (AccountName, ExpenseAccount ExpenseAmount) VALUES ('CalOil Credit Card', 'Job Expenses:Job Materials', 52.25)
INSERT INTO CreditCardChargeExpenseItems#TEMP (AccountName, ExpenseAccount ExpenseAmount) VALUES ('CalOil Credit Card', 'Automobile:Fuel', 235.87)
INSERT INTO CreditCardChargeExpenseItems (AccountName, ExpenseAccount, ExpenseAmount) SELECT AccountName, ExpenseAccount, ExpenseAmount FROM CreditCardChargeExpenseItems#TEMP

Columns¶

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.

CreditCardChargeLineItems¶

Create, update, delete, and query Reckon Credit Card Charge Line Items.

Table Specific Information¶

CreditCardCharges may be inserted, queried, or updated via the CreditCardCharges, CreditCardChargeExpenseItems, or CreditCardChargeLineItems tables. CreditCardCharges may be deleted by using the CreditCardCharges table.

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

Reckon Accounts Hosted allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for CreditCardCharges are Id, ReferenceNumber, Date, TimeModified, AccountName, and AccountId. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM CreditCardChargeLineItems WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert¶

To add a CreditCardCharge, specify an Account and at least one Expense or Line Item. All Line Item columns can be used for inserting multiple Line Items for a new CreditCardCharge transaction. For example, the following will insert a new CreditCardCharge with two Line Items:

INSERT INTO CreditCardChargeLineItems#TEMP (AccountName, ItemName, ItemQuantity) VALUES ('CalOil Credit Card', '1/1/2011', 'Repairs', 1)
INSERT INTO CreditCardChargeLineItems#TEMP (AccountName, ItemName, ItemQuantity) VALUES ('CalOil Credit Card', '1/1/2011', 'Removal', 2)
INSERT INTO CreditCardChargeLineItems (AccountName, ItemName, ItemQuantity) SELECT AccountName, ItemName, ItemQuantity FROM CreditCardChargeLineItems#TEMP

Columns¶

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.

CreditCardCharges¶

Create, update, delete, and query Reckon Credit Card Charges.

Table Specific Information¶

CreditCardCharges may be inserted, queried, or updated via the CreditCardCharges, CreditCardChargeExpenseItems, or CreditCardChargeLineItems tables. CreditCardCharges may be deleted by using the CreditCardCharges table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

Reckon Accounts Hosted allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for CreditCardCharges are Id, ReferenceNumber, Date, TimeModified, AccountName, and AccountId. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM CreditCardCharges WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert¶

To add a CreditCardCharge, specify an Account and at least one Expense or Line Item. The ItemAggregate and ExpenseAggregate columns may be used to specify an XML aggregate of Line or Expense Item data. The columns that may be used in these aggregates are defined in the CreditCardChargeLineItems and CreditCardChargeExpenseItems tables and it starts with Item and Expense. For example, the following will insert a new CreditCardCharge with two Line Items:

INSERT INTO CreditCardCharges (AccountName, ItemAggregate)
VALUES ('CalOil Credit Card',
'<CreditCardChargeLineItems>
<Row><ItemName>Repairs</ItemName><ItemQuantity>1</ItemQuantity></Row>
<Row><ItemName>Removal</ItemName><ItemQuantity>2</ItemQuantity></Row>
</CreditCardChargeLineItems>')

To insert subitems, set the ItemName field to the FullName of the item; for example, '<Row><ItemName>Subs:Carpet</ItemName><ItemQuantity>0</ItemQuantity></Row>'

Columns¶

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.

CreditCardCreditExpenseItems¶

Create, update, delete, and query Reckon Credit Card Credit Expense Line Items.

Table Specific Information¶

CreditCardCredits may be inserted, queried, or updated via the CreditCardCredits, CreditCardCreditExpenseItems, or CreditCardCreditLineItems tables. CreditCardCredits may be deleted by using the CreditCardCredits table.

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

Reckon Accounts Hosted allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for CreditCardCredits are Id, ReferenceNumber, Date, TimeModified, AccountName, and AccountId. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM CreditCardCreditExpenseItems WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert¶

To add a CreditCardCredit, specify an Account and at least one Expense or Line Item. All Expense Line Item columns can be used for inserting multiple Expense Line Items for a new CreditCardCredit transaction. For example, the following will insert a new CreditCardCredit with two Expense Line Items:

INSERT INTO CreditCardCreditExpenseItems#TEMP (AccountName, ExpenseAccount, ExpenseAmount) VALUES ('CalOil Credit Card', 'Job Expenses:Job Materials', 52.25)
INSERT INTO CreditCardCreditExpenseItems#TEMP (AccountName, ExpenseAccount, ExpenseAmount) VALUES ('CalOil Credit Card', 'Automobile:Fuel', 235.87)
INSERT INTO CreditCardCreditExpenseItems (AccountName, ExpenseAccount, ExpenseAmount) SELECT AccountName, ExpenseAccount, ExpenseAmount FROM CreditCardCreditExpenseItems#TEMP

Columns¶

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.

CreditCardCreditLineItems¶

Create, update, delete, and query Reckon Credit Card Credit Line Items.

Table Specific Information¶

CreditCardCredits may be inserted, queried, or updated via the CreditCardCredits, CreditCardCreditExpenseItems, or CreditCardCreditLineItems tables. CreditCardCredits may be deleted by using the CreditCardCredits table.

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

Reckon Accounts Hosted allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for CreditCardCredits are Id, ReferenceNumber, Date, TimeModified, AccountName, and AccountId. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM CreditCardCreditLineItems WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert¶

To add a CreditCardCredit, specify an Account and at least one Expense or Line Item. All Line Item columns can be used for inserting multiple Line Items for a new CreditCardCredit transaction. For example, the following will insert a new CreditCardCredit with two Line Items:

INSERT INTO CreditCardCreditLineItems#TEMP (AccountName, ItemName, ItemQuantity) VALUES ('CalOil Credit Card', '1/1/2011', 'Repairs', 1)
INSERT INTO CreditCardCreditLineItems#TEMP (AccountName, ItemName, ItemQuantity) VALUES ('CalOil Credit Card', '1/1/2011', 'Removal', 2)
INSERT INTO CreditCardCreditLineItems (AccountName, ItemName, ItemQuantity) SELECT AccountName, ItemName ItemQuantity FROM CreditCardCreditLineItems#TEMP

Columns¶

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.

CreditCardCredits¶

Create, update, delete, and query Reckon Credit Card Credits.

Table Specific Information¶

CreditCardCredits may be inserted, queried, or updated via the CreditCardCredits, CreditCardCreditExpenseItems, or CreditCardCreditLineItems tables. CreditCardCredits may be deleted by using the CreditCardCredits table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

Reckon Accounts Hosted allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for CreditCardCredits are Id, ReferenceNumber, Date, TimeModified, AccountName, and AccountId. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM CreditCardCredits WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert¶

To add a CreditCardCredit, specify an Account and at least one Expense or Line Item. The ItemAggregate and ExpenseAggregate columns may be used to specify an XML aggregate of Line or Expense Item data. The columns that may be used in these aggregates are defined in the CreditCardCreditLineItems and CreditCardCreditExpenseItems tables and it starts with Item and Expense. For example, the following will insert a new CreditCardCredit with two Line Items:

INSERT INTO CreditCardCredits (AccountName, ItemAggregate)
VALUES ('CalOil Credit Card',
'<CreditCardCreditLineItems>
<Row><ItemName>Repairs</ItemName><ItemQuantity>1</ItemQuantity></Row>
<Row><ItemName>Removal</ItemName><ItemQuantity>2</ItemQuantity></Row>
</CreditCardCreditLineItems>')

To insert subitems, set the ItemName field to the FullName of the item; for example, '<Row><ItemName>Subs:Carpet</ItemName><ItemQuantity>0</ItemQuantity></Row>'

Columns¶

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.

CreditMemoLineItems¶

Create, update, delete, and query Reckon Credit Memo Line Items.

Table Specific Information¶

CreditMemos may be inserted, queried, or updated via the CreditMemoLineItems table. CreditMemos may be deleted by using the CreditMemos table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

Reckon Accounts Hosted allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for CreditMemos are Id, ReferenceNumber, Date, TimeModified, CustomerName, CustomerId, AccountsReceivable, and AccountsReceivableId. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM CreditMemoLineItems WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert¶

To add a CreditMemo, specify a Customer and at least one Line Item. All Line Item columns can be used for inserting multiple Line Items for a new CreditMemo transaction. For example, the following will insert a new CreditMemo with two Line Items:

INSERT INTO CreditMemoLineItems#TEMP (CustomerName, ItemName, ItemQuantity) VALUES ('Abercrombie, Kristy', 'Repairs', 1)
INSERT INTO CreditMemoLineItems#TEMP (CustomerName, ItemName, ItemQuantity) VALUES ('Abercrombie, Kristy', 'Removal', 2)
INSERT INTO CreditMemoLineItems (CustomerName, ItemName, ItemQuantity) SELECT CustomerName, ItemName, ItemQuantity FROM CreditMemoLineItems#TEMP

Columns¶

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.

CreditMemos¶

Create, update, delete, and query Reckon Credit Memos.

Table Specific Information¶

CreditMemos may be inserted, queried, or updated via the CreditMemoLineItems table. CreditMemos may be deleted by using the CreditMemos table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

Reckon Accounts Hosted allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for CreditMemos are Id, ReferenceNumber, Date, TimeModified, CustomerName, CustomerId, AccountsReceivable, and AccountsReceivableId. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM CreditMemos WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert¶

To add a CreditMemo, specify a Customer and at least one Line Item. The ItemAggregate column may be used to specify an XML aggregate of Line Item data. The columns that may be used in these aggregates are defined in the CreditMemoLineItems tableand it starts with Item. For example, the following will insert a new CreditMemo with two Line Items:

INSERT INTO CreditMemos (CustomerName, ItemAggregate)
VALUES ('Abercrombie, Kristy',
'<CreditMemoLineItems>
<Row><ItemName>Repairs</ItemName><ItemQuantity>1</ItemQuantity></Row>
<Row><ItemName>Removal</ItemName><ItemQuantity>2</ItemQuantity></Row>
</CreditMemoLineItems>')

To insert subitems, set the ItemName field to the FullName of the item; for example, '<Row><ItemName>Subs:Carpet</ItemName><ItemQuantity>0</ItemQuantity></Row>'

Columns¶