90 Minds Community

Β View Only

  • 1.  Sage 100 Job Cost new framework question - SQL to get JTDBilled, etc.

    Posted 10-07-2021 17:14
    I'm converting from Job Cost old framework to new framework and trying to figure out the best way to create a SQL field for the JTD, YTD, PTD summary data like JTD Billed amt. 

    Are there any summary fields left to pull from, or do I need to do subqueries and create my own totals?

    Clark Walliser, DataSelf

    ------------------------------
    Clark Walliser
    Senior Consultant
    Dataself Corporation
    San Jose CA
    Clark
    ------------------------------


  • 2.  RE: Sage 100 Job Cost new framework question - SQL to get JTDBilled, etc.

    Posted 10-08-2021 02:18
      |   view attached
    I am not a SQL expert, however I put the attached together in 2018 that may be of assistance when I had to rewrite reports.

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

    Attachment(s)

    xlsx
    Job Cost Old File and Field Names to New.xlsx   27 KB 1 version


  • 3.  RE: Sage 100 Job Cost new framework question - SQL to get JTDBilled, etc.

    Posted 10-08-2021 08:08

    On the Crystal side of things some folks have used subreports to come up with those numbers.  In one of my scenarios we ended up instead leveraging/editing the standard out-of-box J/C Job Transaction Detail Report.  I still would love to know the logic behind Sage's reasoning for eliminating the legacy JTD and YTD information/fields :)  



    ------------------------------
    Brett Zimmerman
    Net at Work
    Greater Boston Area
    ------------------------------

    Original Message


  • 4.  RE: Sage 100 Job Cost new framework question - SQL to get JTDBilled, etc.

    Posted 10-08-2021 11:28

    Logic?

     

     

    Thanks,

    Clark

     

    Clark Walliser

    DataSelf Corp.

    cwalliser@dataself.com

    www.dataself.com

    408-641-9549

     




    Original Message


  • 5.  RE: Sage 100 Job Cost new framework question - SQL to get JTDBilled, etc.

    Posted 10-10-2021 01:11
    Logic = so those fields in Job or Customer master file can't get out of sync! = so everyone should just convert to SQL (where they should provide stored procedures to calculate them)

    ------------------------------
    Dan Burleson
    Software Consultant
    Connex Software
    Corvallis OR
    541-224-6642
    ------------------------------

    Original Message


  • 6.  RE: Sage 100 Job Cost new framework question - SQL to get JTDBilled, etc.

    Posted 10-10-2021 01:17
    Technically logical, yes.
    Practical for simple report writing, no.
    But it doesn't matter because things have changed and they aren't going back. 

    "Adapt or die. So make sure whatever you choose to not adapt to is worth dying for."

    Clark Walliser,
    DataSelf Corp.
    cwalliser@dataself.com
    www.dataself.com
    408-641-9549



    Original Message


  • 7.  RE: Sage 100 Job Cost new framework question - SQL to get JTDBilled, etc.

    Posted 10-09-2021 08:22
    @Brett Zimmerman - so you can struggle to rewrite them just like reports using PTD/YTD figures in the Customer file, etc...​

    ------------------------------
    Beth Bowers
    Mom to Samson, Peanut, ChiChi, Canton, Cagney and Daisy (NO Oxford comma - shriek!)
    Tennessee Software Solutions
    269-445-1625
    ------------------------------

    Original Message


  • 8.  RE: Sage 100 Job Cost new framework question - SQL to get JTDBilled, etc.

    Posted 10-08-2021 08:15
    For JTD you could use {JC_JobBillingHistory.ActualCosts}

    For PTD you could use something like this:
    If {JC_Options.CurrentFiscalYr} = {JC_JobBillingHistory.FiscalYear} and {JC_Options.CurrentPeriod} = {JC_JobBillingHistory.FiscalPeriod} then
    {JC_JobBillingHistory.ActualCosts}
    Else
    0

    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Chauffeur, Chef, and Personal Assistant to Sprinkles
    ------------------------------



  • 9.  RE: Sage 100 Job Cost new framework question - SQL to get JTDBilled, etc.

    Posted 10-09-2021 00:57
    Thanks everyone. I got what I needed to get the job done😊
    90minds rocks! 😎

    ------------------------------
    Clark Walliser
    Senior Consultant
    Dataself Corporation
    San Jose CA
    Clark
    ------------------------------



Related Content