Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals
Audit Split and useage through ODBC
Audit Split and useage through ODBC
Hi there,
Newbie here, so tip of the hat to y'all.
Boring bits first:
Line 50 version 12
Running on Windows Server 2003
OK, my problem is this. I'm trying to extract various data from Sage into a Data Warehouse. I'm using ODBC with SQL Integration Services, which is working fine so far. Problem is my lack of knowledge of the Sage database structures, and accounting principals in general.
Problem 1 is I want to pull out the records relating to Time taken to Pay Sales Invoices. I see there is a report of this name, that looks at AUDIT_SPLIT and AUDIT_USEAGE. Unfortuantely, I can't see how those tables join.
In very basic terms, if I pull these two tables, and figure out how they join, then I should be able to see when payments were made against invoices, right? Is this a meaningful way of extracting this information, or should I be going to other tables?
Any help gratefully received.
Newbie here, so tip of the hat to y'all.
Boring bits first:
Line 50 version 12
Running on Windows Server 2003
OK, my problem is this. I'm trying to extract various data from Sage into a Data Warehouse. I'm using ODBC with SQL Integration Services, which is working fine so far. Problem is my lack of knowledge of the Sage database structures, and accounting principals in general.
Problem 1 is I want to pull out the records relating to Time taken to Pay Sales Invoices. I see there is a report of this name, that looks at AUDIT_SPLIT and AUDIT_USEAGE. Unfortuantely, I can't see how those tables join.
In very basic terms, if I pull these two tables, and figure out how they join, then I should be able to see when payments were made against invoices, right? Is this a meaningful way of extracting this information, or should I be going to other tables?
Any help gratefully received.
- brucedenney
- Site Admin
- Posts: 4619
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
There are three levels of hierarchy in the Audit trail
Header
Split
Usage
many usage records are linked to one split by the split_number to the tran_number
many splits are linked to one header by header_number to the tran_number
Header
Split
Usage
many usage records are linked to one split by the split_number to the tran_number
many splits are linked to one header by header_number to the tran_number
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.
Re: Audit Split and useage through ODBC
What does it mean
many usage records are linked to one split by the split_number to the tran_number
does it meane
Audit_Header is parent
Audit_Split is child with header_number as foreign key of Audit_Header table ??
And what is Tran_Number for ??
many usage records are linked to one split by the split_number to the tran_number
does it meane
Audit_Header is parent
Audit_Split is child with header_number as foreign key of Audit_Header table ??
And what is Tran_Number for ??
- brucedenney
- Site Admin
- Posts: 4619
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Audit Split and useage through ODBC
Audit_Header is parent
Audit_Split is child with a join on header_number in both tables.
The transaction number is the split number, the header number is the same as the first of its splits.
Audit_Split is child with a join on header_number in both tables.
The transaction number is the split number, the header number is the same as the first of its splits.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.
Re: Audit Split and useage through ODBC
Thanks Bruce for reply.
But what iam seeing after join is that
For one record in Audit_Header there is no multiple records in Audit_Split table even we have entered multiple entries through UI.
For every record in Split table there is unique header_number.
Thanks
But what iam seeing after join is that
For one record in Audit_Header there is no multiple records in Audit_Split table even we have entered multiple entries through UI.
For every record in Split table there is unique header_number.
Thanks
- brucedenney
- Site Admin
- Posts: 4619
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Audit Split and useage through ODBC
What type of join are you using (It needs to be an outer join) and in what context are you using ODBC (eg Excel)?
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.
Re: Audit Split and useage through ODBC
Im using ODBC and with inner join
Select a_h.Header_Number,a_s.Split_Number
From Audit_Header a_h JOIN Audit_Split a_s ON a_h.Header_Number=a_s.Header_Number
but still cannot get parent-child kind of data.
Select a_h.Header_Number,a_s.Split_Number
From Audit_Header a_h JOIN Audit_Split a_s ON a_h.Header_Number=a_s.Header_Number
but still cannot get parent-child kind of data.
- brucedenney
- Site Admin
- Posts: 4619
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Audit Split and useage through ODBC
The sql you post is invalid, it does not have full table names and the case should be uppercase.
Can you post the actual sql you are using?
You have not said what you are using to access SQL. Some products such as the datapilot in open office have specific issues. Are you using MS Query with Excel or what?
Can you post the actual sql you are using?
You have not said what you are using to access SQL. Some products such as the datapilot in open office have specific issues. Are you using MS Query with Excel or what?
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.
Re: Audit Split and useage through ODBC
The SQL is actually valid. The table names use an alias and do not need to be in upper case (upper case is just a commonly used convention.The sql you post is invalid, it does not have full table names and the case should be uppercase.
The SQL is OK and will give the header number from the AUDIT_HEADER table and the split number from any matching records in the AUDIT_SPLIT table, but only where at least one AUDIT_SPLIT record matches with an AUDIT_HEADER (which should always be the case anyway).Im using ODBC and with inner join
but still cannot get parent-child kind of data.
Not sure what your expectations are when you say parent-child kind of data but perhaps all you are entering in the UI is a single split for your header. What are entering as your test data?
- brucedenney
- Site Admin
- Posts: 4619
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Audit Split and useage through ODBC
The SQL could be valid, if there are alias's, but that is not the norm and there was no mention of an alias having been setup or anything else. Adding aliases just adds a layer of obfuscation when trying to debug so not a good idea.
It is not clear what the OP has or has not done in the way of creating aliases.
It is not clear what the OP has or has not done in the way of creating aliases.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.
Re: Audit Split and useage through ODBC
The aliases are part of the SQL:The SQL could be valid, if there are alias's, but that is not the norm and there was no mention of an alias having been setup or anything else. Adding aliases just adds a layer of obfuscation when trying to debug so not a good idea.
It is not clear what the OP has or has not done in the way of creating aliases.
Select a_h.Header_Number,a_s.Split_Number
From Audit_Header a_h JOIN Audit_Split a_s ON a_h.Header_Number=a_s.Header_Number
This gives the AUDIT_HEADER table an alias of a_h and the AUDIT_SPLIT table an alias of a_s.
Use of an alias in SQL is common practice for anyone who writes SQL a lot as it can significantly cut down on the typing and can often aid understanding. It's all down to personal preference though.
The key point is that the SQL is valid and should return results. The question remains as to what data data has actually been entered in the UI as I'd hazard a guess that only 1 split has been entered.
- brucedenney
- Site Admin
- Posts: 4619
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Audit Split and useage through ODBC
So SQL is case insensitive as well.. you learn something everyday.
I just copied and pasted it in to MSquery and it is fine.
I get perfectly good data returned. I suspect you are right, the OP was looking at the beginning of the Audit Trail where it is all opening balances that have only 1 split for each header.
I just copied and pasted it in to MSquery and it is fine.
I get perfectly good data returned. I suspect you are right, the OP was looking at the beginning of the Audit Trail where it is all opening balances that have only 1 split for each header.
Login to access the files attached to this post.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.
Who is online
Users browsing this forum: No registered users and 21 guests