Sage 100

 View Only

  • 1.  Record Count by Module

    Posted 08-15-2021 14:53
    I know you can get a record count by table, is there a way to get a record count by module?

    ------------------------------
    [Michele] [Herzog] [CPA,CITP, CGMA]
    [Overland Park] [KS]
    [816-520-1365]
    ------------------------------


  • 2.  RE: Record Count by Module

    Posted 08-16-2021 11:56
    Meaning?  Every table for that folder ( ..\mas_xxx\apXXX) you would like a report (Crystal ?) listing the DFDM record count?

    ------------------------------
    Jeff Schwenk
    FORMER 90M Board Member -140,000,000 is a TERRIBLE thing to waste!!!
    Bottomline Software, Inc.
    Waynesboro VA
    540-221-4444
    ------------------------------



  • 3.  RE: Record Count by Module

    Posted 08-16-2021 12:04
    All the tables with data and yes I am looking for a different way than going thru DFDM.

    ------------------------------
    [Michele] [Herzog] [CPA,CITP, CGMA]
    [Overland Park] [KS]
    [816-520-1365]
    ------------------------------

    Original Message


  • 4.  RE: Record Count by Module

    Posted 08-16-2021 12:09
    This seems to work... you'd just need to get a table list, and add up the values.


    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------

    Original Message


  • 5.  RE: Record Count by Module

    Posted 08-16-2021 16:02
    I have not used the ODBCview as you are showing.  Would you mind telling me how to use that?

    ------------------------------
    [Michele] [Herzog] [CPA,CITP, CGMA]
    [Overland Park] [KS]
    [816-520-1365]
    ------------------------------

    Original Message


  • 6.  RE: Record Count by Module

    Posted 08-16-2021 16:31
    Any ODBC query tool will work.  ODBCView is just a freebie 3rd party tool recommended to me years ago, but if you have another you prefer, that is fine.  (I understand ScanForce uses SqlDBX, which I have been meaning to try out).
    Basically you choose your Datasource... (like SOTAMAS90) then type / run your query.

    The specific query I used was just a text label and row count, one after the other... nothing fancy.


    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------

    Original Message


  • 7.  RE: Record Count by Module

    Posted 08-19-2021 16:10
    Thank you so much @Steve Iwanowski!  I forgot they had the DSD SQL enhancement and the query you provided worked perfect!  @Kevin Moyes thank you for the new tip​ on an ODBC View.​

    ------------------------------
    [Michele] [Herzog] [CPA,CITP, CGMA]
    [Overland Park] [KS]
    [816-520-1365]
    ------------------------------

    Original Message


  • 8.  RE: Record Count by Module

    Posted 08-16-2021 15:21

    If it's Premium, you can use the following SQL query:

    SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY 
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO


    ------------------------------
    Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯
    ------------------------------

    Original Message


  • 9.  RE: Record Count by Module

    Posted 08-16-2021 16:05
    Thanks Steve, they are on advanced.

    ------------------------------
    [Michele] [Herzog] [CPA,CITP, CGMA]
    [Overland Park] [KS]
    [816-520-1365]
    ------------------------------

    Original Message


Related Content