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