Confirm

Expand all | Collapse all

Estimated value field

Jump to Best Answer
  • 1.  Estimated value field

    Posted 06-23-2019 07:12
    I am writing a report and I'm trying to find the estimated value (total cost) in the jobs screen of maintenance management. I've looked at the relevant schemas I can think of with no luck.  Can someone please advice where is this field.

    ------------------------------
    Afzal
    Knowledge Community Shared Account
    ------------------------------


  • 2.  RE: Estimated value field

    Pitney Bowes
    Posted 06-23-2019 22:13
    Hi Afzal,

    In Confirm® the Estimated Value of a Job will derive from the Target Rate entered in the Schedule of Rates (SOR) Item library (if one is entered) or if Preferred Contract is entered, from the Contract Rates (however a Job need not be raised under any particular Contract).

    If no Target Rates are present and no Preferred Contract is entered, the Estimated Value will be blank.

    Estimated Value is not stored in a singular Confirm database field. It has to be calculated in a report as it would be in the application itself.

    Thanks,
    Saurabh Mathur

    ------------------------------
    Saurabh Mathur
    Pitney Bowes Australia Pty Ltd
    Melbourne
    ------------------------------



  • 3.  RE: Estimated value field

    Posted 06-24-2019 07:54
    Hello Saurabh, I am trying to write a report where it shows a job with total cost for for example Job 1, £5000, job 2 £500 and i dont want individual SOR's or their rates, just the total cost (estimated value).  I have exported a built in report and re-imported and i run it i get the error (see attached).  I also tried to work around it but keeps giving me duplicated entries.  Can you please advise on where i am going wrong.

    ------------------------------
    Afzal Hussain
    Knowledge Community Shared Account
    ------------------------------

    Attachment(s)



  • 4.  RE: Estimated value field

    Posted 06-28-2019 10:03
    Hi Afzal,
    If you remove the fields from the tables job_item, sor_item & contract_boq from the SELECT & GROUP BY, this will remove the duplicate lines, and remove the details at SOR level and retain it at job level.
    Regards
    Haf

    ------------------------------
    Hafezur Rahman
    Confirm Systems Administrator
    London Borough Of Richmond Upon Thames
    Twickenham
    ------------------------------



  • 5.  RE: Estimated value field

    Posted 06-24-2019 06:32
    Hi Afzal,

    Depending on what the state of the job and what value you are looking for, the following is a list of the tables you need to look at to calculate the values.

    Job Item
    Order Item
    Claim Item
    Variation Item
    Payment Item

    If you were looking for just a job estimate, where the job is not on an order, you will need the Job Item table.

    If the job was on an order and uncommitted, you can use Order Item or Job Item.

    Hope this helps.

    Haf

    ------------------------------
    Hafezur Rahman
    Application Specialist
    Richmond and Wandsworth Councils
    ------------------------------



  • 6.  RE: Estimated value field

    Moderator
    Posted 08-05-2019 01:32
    Hi Afzal,

    Greetings!
    I hope you got the resolution of your query.
    Please mark the best answer by selecting "Make Best Answer" in the Reply menu.
    It will help us and other users save their time.

    For more information click here.

    Regards

    ------------------------------
    Aakash Singh
    Pitney Bowes Software India Pvt. Lt
    NOIDA
    ------------------------------



  • 7.  RE: Estimated value field
    Best Answer

    Posted 10-17-2019 08:55
    Hi,

    Try this code if you have not already sorted your issue :

    Barry
    =====================================================================================
    SELECT
    job.job_number,
    TO_CHAR (SUM( job_item.job_item_quantity * contract_boq.contract_rate), 'L9,999,999.00' ) Estimated_Value

    FROM job, job_item, contract_boq
    WHERE
    job.job_number = job_item.job_number
    AND contract_boq.contract_code = job.contract_code
    AND contract_boq.sor_item_code = job_item.sor_item_code
    AND contract_boq.price_factor_code = job.price_factor_code
    AND job.job_number = '17041009'

    GROUP BY job.job_number
    =====================================================================================

    ------------------------------
    Barry Jones
    Cheshire West and Cheshire
    Winsford
    ------------------------------



  • 8.  RE: Estimated value field

    Posted 10-17-2019 09:11
      |   view attached
    Thank you Barry,

    When i tried this, i received the attached error.  We are on Microsoft SQL server and it seems the TO_CHAR code is not recognised by Microsoft


    ------------------------------
    Afzal Hussain
    London Borough of Barking & Dagenha
    Dagenham
    ------------------------------



  • 9.  RE: Estimated value field

    Posted 10-17-2019 09:16

    Hi again,

     

    Then try and replace with just SUM( job_item.job_item_quantity * contract_boq.contract_rate) Estimated_Value

     

    Barry

    Barry Jones

    Contract and Systems Engineer

     

    Cheshire West and Chester Council

    Tel: 01244 9 77941

    Email: barry.l.jones@cheshirewestandchester.gov.uk

    Location: Guilden Sutton Highways Office, Guilden Sutton Lane, Chester CH3 7EX 

    Visit: cheshirewestandchester.gov.uk

     

     

     






  • 10.  RE: Estimated value field

    Posted 10-17-2019 09:23
    Thank you, it worked

    ------------------------------
    Afzal Hussain
    Specialist Application Support Officer
    London Borough of Barking and Dagenham
    ------------------------------



  • 11.  RE: Estimated value field

    Posted 10-17-2019 09:26

    Excellent.  Glad to be of assistance.

     

    Speak soon...

     

    Barry

    Barry Jones

    Contract and Systems Engineer

     

    Cheshire West and Chester Council

    Tel: 01244 9 77941

    Email: barry.l.jones@cheshirewestandchester.gov.uk

    Location: Guilden Sutton Highways Office, Guilden Sutton Lane, Chester CH3 7EX 

    Visit: cheshirewestandchester.gov.uk