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

Audit Split and useage through ODBC

Sage 50 general help forum - Free help and support for all general issues
Post Reply
Sam

Audit Split and useage through ODBC

Post by Sam » 09 Jan 2007, 15:15

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.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Post by brucedenney » 10 Jan 2007, 18:37

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
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.

Sam

Post by Sam » 11 Jan 2007, 08:58

OK, got that. Thanks for your help

cdhemant
User
User
Posts: 5
Joined: 09 Jul 2020, 05:59
Sage Version: v2018 24 UK/Europe/Africa

Re: Audit Split and useage through ODBC

Post by cdhemant » 09 Jul 2020, 06:02

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 ??

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Audit Split and useage through ODBC

Post by brucedenney » 09 Jul 2020, 09:00

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.
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.

cdhemant
User
User
Posts: 5
Joined: 09 Jul 2020, 05:59
Sage Version: v2018 24 UK/Europe/Africa

Re: Audit Split and useage through ODBC

Post by cdhemant » 10 Jul 2020, 09:09

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

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Audit Split and useage through ODBC

Post by brucedenney » 10 Jul 2020, 11:31

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.

cdhemant
User
User
Posts: 5
Joined: 09 Jul 2020, 05:59
Sage Version: v2018 24 UK/Europe/Africa

Re: Audit Split and useage through ODBC

Post by cdhemant » 10 Jul 2020, 11:35

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.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Audit Split and useage through ODBC

Post by brucedenney » 17 Jul 2020, 07:30

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?
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.

SomeGuy
User
User
Posts: 17
Joined: 20 Jul 2020, 06:14
Sage Version: Other

Re: Audit Split and useage through ODBC

Post by SomeGuy » 20 Jul 2020, 06:27

The sql you post is invalid, it does not have full table names and the case should be uppercase.
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.
Im using ODBC and with inner join

but still cannot get parent-child kind of data.
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).

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?

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Audit Split and useage through ODBC

Post by brucedenney » 20 Jul 2020, 09:02

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.
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.

SomeGuy
User
User
Posts: 17
Joined: 20 Jul 2020, 06:14
Sage Version: Other

Re: Audit Split and useage through ODBC

Post by SomeGuy » 21 Jul 2020, 06:27

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.
The aliases are part of the SQL:

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.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Audit Split and useage through ODBC

Post by brucedenney » 21 Jul 2020, 14:51

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.
Untitled.png
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.

Post Reply

Who is online

Users browsing this forum: No registered users and 21 guests