Journals
Query Journals in Sage Business Cloud Accounting.
Table Specific Information
Query the available Journals
Select
The driver uses the Sage Accounting API to process search criteria that refer to UpdatedOrCreatedSince, DeletedSince columns. The driver processes other filters client-side within the driver.
- UpdatedOrCreatedSince supports the '=' operator.
- DeletedSince supports the '=' operator.
For example, the following queries are processed server side:
SELECT * FROM Journals WHERE UpdatedOrCreatedSince = '2018-12-15' SELECT * FROM Journals WHERE DeletedSince = '2018-12-15'
Insert
- Insert a journal with aggregate value.
INSERT INTO [Journals] ( JournalLineAggregate, date, reference) VALUES ('{"ledger_account_id": "1fcc2765741311ea8d3c022ace1a91e2", "debit": 12,"credit": 0,"details": "details","tax_reconciled": true,"cleared": true,"bank_reconciled": true},{"ledger_account_id": "1fcc2765741311ea8d3c022ace1a91e2","debit": 0,"credit": 12,"details": "details","tax_reconciled": true,"cleared": true,"bank_reconciled": true}', '2019-07-12', 'Journal Reference')
- Insert a journal using temp tables.
INSERT INTO [JournalLineItemsAggregate#TEMP] (credit, debit, details, ledgerAccountId) VALUES ('10', '0', 'details1', '6035f3d39d7511e9a2b70e070fead9d0') INSERT INTO [JournalLineItemsAggregate#TEMP] (credit, debit, details, ledgerAccountId) VALUES ('0', '10', 'details2', '6035f3d39d7511e9a2b70e070fead9d0') INSERT INTO [Journals] (JournalLineAggregate, date, reference) VALUES ('JournalLineItemsAggregate#TEMP', '2019-07-12', 'Reference2')
Delete
You must specify the Id of the Journals to delete it.
DELETE FROM Journals WHERE id = '123'
Columns
Name | Type | ReadOnly | References | Description |
Id [KEY] | String | False |
The unique identifier for the item | |
CreatedAt | Datetime | True |
The datetime when the item was created | |
DisplayedAs | String | False |
The name of the resource | |
JournalLineAggregate | String | False |
The journal lines | |
TransactionTypeId | String | False |
The unique identifier for the item | |
TransactionId | String | False |
Transactions.Id |
The unique identifier for the item |
Date | Datetime | True |
The date of the journal | |
Reference | String | False |
A reference for the journal | |
Migrated | Bool | False |
Indicates if the journal was migrated from another system. | |
UpdatedAt | Datetime | True |
The datetime when the item was last updated | |
LegacyId | Int | False |
The legacy ID for the item | |
DeletedAt | Datetime | True |
The datetime when the item was deleted | |
Total | String | False |
The total for the journal | |
Description | String | False |
A description of the journal | |
JournalCodeId | String | False |
JournalCodes.Id |
The unique identifier for the item |
DeletedSince | Datetime | True |