Sage 300 2022 Intelligence Report Data Table Names

Hi Sage 300 Community,

How do I export or get a list of data table names so that I can build out specifics in Intelligence Report?

Is it simply going through our database server and doing a generic query?

If so, is there a quick query that anyone has already that I could use and modify it to fit our database?

If not, is there a tutorial or user guide to Intelligence Report module?

Thank you,

Vanessa

  • If you search in the Sage Knowledgebase for "Object Model" or ID 19194 you can download the list of tables and fields.  You might need to have Internet Explorer kicking around to view the files because of how the files are generated and how modern browsers handle XML files.

  • in reply to Django

    Thank you so much! I'll give this suggestion a go! =)

  • in reply to Django

    I found this about Application Object Model (AOM). I downloaded the 2022 zipped file, but there seems to be no user guide on what to do with the files, unless it is some sort of import thing I have to do to our database. Would you happen to know how to incorporate this into Intelligence Reporting?

    Link: support.na.sage.com/.../viewdocument.do

    Thank you!

  • in reply to Vanessa Le

    You just unzip the files into their own folder.  The Advantage.xml file is the main index.  In general there are two file files for each table in Sage.  For example, OEORDD.xml gives you the names and descriptions of the fields in the table of the same name. For some fields you'll also see what the field values mean.  The OE0500.xml file shows you the business logic layer that lives on top of the table. You'll see more fields in there but those other fields are not in that table - they're calculated or come from other related tables.

    So it's just a catalog.  Now Intelligence has created catalogs that overlay the Sage tables to make things easier but at least you'll have some sense of what tables are holding what data.

  • in reply to Django

    Going to extract and give all of this a try! It seems easy enough...but I'm new to Sage 300 so it's a bit of a beast to work with until I get more familiarized and better trained up. I appreciate your time and response. Thank you so much! 

  • in reply to Vanessa Le

    No problem.  If you're looking for specific data - those questions are easier for us to answer. e.g. Where can I find the customer payments or purchase order detail lines,etc.

  • in reply to Django

    Oh I see. So does this mean this forum is only for specific data, features / functions and settings related or errors related only? I'm so sorry if I'm not asking the right questions if my questions have been a bit too generic for anyone to help answer. Thank you so much for responding though. :)

  • in reply to Vanessa Le

    No - you're in the right place and asking perfectly acceptable questions.  I'm just saying that you now have a list of hundreds and hundreds of tables. Once you've started to play around with the system and you want to know where the data for a given screen or report comes from we'll be able to give you a more concise answer.

  • in reply to Django

    Oh ok, I understand now. :) Perfect! Thank you so much! I'm so glad I found this forum / community. 

  • in reply to Vanessa Le

    It takes a bit to muddle thru what goes where and best way to grab.   If you have questions, somewhere he likely already found it.    I think the best way to ask is "I see this this place in the Sage interface, what table do I go to?"    Most of the time its pretty logical as most everything is done in a header/details type arrangement. 

  • in reply to BellScott

    There are so many AOMs / fields! It's quite intimidating to be honest, but that's a good tip to help me submit any future questions on this topic. Thank you so much! :)

  • in reply to Vanessa Le

    Indeed, so very many and some you might not have if you don't subscriber to that module.  Eg, if you don't subscribe to Order Entry, anything with OE is not helpful.  

    Some tricks that I used to learn:

    1) I downloaded the supplemental reports and then opened their containers in Connector and reports in Report Manager.    I'd look at those to find out how they were written and what fields they used.   By way of example, Purchase Master has stuff for Order Entry, we don't use that, so I just made a copy, yanked out those fields, and now I have a purchases (POs) report.    It also pointed me in the correct direction for what tables to use allowing me to roll my own from scratch.  

    2) Expport a transaction from a module to a file.  Example, Export just shipment document number SHP0001234.   The headers on the worksheets often correspond to the field names.   You can then use a program like Notepad++, search for that field in all the XML files, and then open that XML file to see the table(s).   

  • in reply to BellScott

    Yeah so, so many. Mostly right now, they want me to dig into OE and so I'm trying to get that worked out. I'm copying and pasting what you said and what Django said into word to document this while I go through some of the AOMs for OE. Thank you so much. :)