SQL Tables

[daytrader85]

Hello,

I'm trying to find the SQL table where I can find the Revenue Account in a Batch. From exporting a Batch, I have found out that the Column Heading is, "IDACCTREV".

Any help would be greatly appreciated.

Thanks,
Kartik

 

[Jay Converse]

ARIBD

 

[daytrader85]

Thanks Tuba2007. That has been helpful. Is there a "map" of sorts that show which SQL Table house which fields?


Thanks,
Kartik

 

[Jay Converse]

https://partners.sagenorthamerica.c...e Accpac ERP/Ungated Customers/210-30254.html

 

[daytrader85]

Is there an easy way to find out which table a Data Item is housed. For example, if I wanted to know what table the data field, INVCDESC, was housed, how would I do that?

 

[Jay Converse]

We already told you:

https://partners.sagenorthamerica.c...e Accpac ERP/Ungated Customers/210-30254.html

 

[ettienne]

Deductive reasoning... invoice description is in the invoice header on the data entry screen, so it is going to be in the invoice header table. Assuming AR then the invoice header table is ARIBH, AP will be APIBH.

 

[ramam1]

The AOM is *the* reference for the Accpac DB. If you want to know where a particular field in the native Accpac GUI comes from you can use the Accpac UI Info tool to do so. Note that for calculated fields (e.g. the output of an AR Age Trial Balance report) it won't necessarily do what might want as the data in that table is ephemeral.

 

[DjangMan]

Sometimes you can get lucky running a query like below. It can be helpful for tracking down where else a column exists in the database. *WARNING* The naming conventions are not consistent in Sage's database so one table's customer number field is CUSTOMER and another might have IDCUST.

SELECT COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%MyName%'

Just for completeness - this query will locate fields that are referenced in a stored procedure:

SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%MyName%'