Scenario: Want to have a list of commitments (subcontracts and purchase orders) by Job without having to run multiple reports.

Requirements: None

Example Solution:

  1. Open the VA Inquiries form
  2. Enter udPMCommitments in the Query Name field
  3. Enter PM Project Commitments (SL & PO) in the title and description fields
  4. Type = SQL
  5. Query Text list at the bottom.
  6. Change Columns as desired in Columns tab
  7. Setup Parameters as follows:
    1. @JCCo,Type=5,Datatype=bJCCO
    2. @Job,Type=4,DataType=bJob
  8. Go to the PMProjectsForm VA Inquiry
  9. On Links tab add udPMCommitments in Related Query Field
  10. Set Display Seq = 1
  11. Double Click the udPMCommitments record to load the Inquiry links form and @JCCo to use Default Value and @Job Matching Column to be Project (see screen shot below)
  12. Open EM Projects Form
  13. Open Form Properties from the Tools menu
  14. Add a tab page Commitments on the Tab Pages tab.
  15. Select Commitments tab and click edit
  16. Set Control Type to 3 and select
    udPMCommitments in the Query Name field.
  17. Save and close/reopen forms.
  18. Test tab to validate functionality.
Step 11 Screen Shot

SQL Query


    SELECT b.SLCo as Co
        ,'SL' as Type
        ,b.SL as Commitment, sum(b.OrigItemCost) as OriginalContract, sum(b.ChangeOrderCost) as Changes, sum(b.OrigItemCost + b.ChangeOrderCost) as CurrentContract
        , sum(b.APTDAmt) as Billed
        , sum(CASE WHEN APTDStatus > 2 THEN b.APTDAmt ELSE 0 END) as Paid 
        , sum(CASE WHEN APTDStatus = 2 and PayType = 4 THEN b.APTDAmt ELSE 0 END) as Retainage 
    FROM brvSLSubContrByJob b
    JOIN SLIT i ON b.SLCo=i.SLCo and b.SL = i.SL
    JOIN SLHD s ON i.SLCo=s.SLCo and i.SL = s.SL
    JOIN APVM v ON s.VendorGroup = v.VendorGroup and s.Vendor = v.Vendor
    WHERE i.JCCo = @JCCo and i.Job = @Job
    GROUP BY b.SLCo,s.VendorGroup,s.Vendor,v.Name,i.JCCo,i.Job,b.SL


        ,'PO' as Type
        ,PO as Commitment
        ,sum(OrigCost) as OriginalContract, sum(ChgTotCost) as Changes, sum(isnull(OrigCost,0) + isnull(ChgTotCost,0)) as CurrentContract
        ,sum(InvAmount) as Billed
        ,sum(PaidAmount) as Paid 
        ,sum(Retainage) as Retainage 
    FROM brvPOPurchStats b
    JOIN APVM v ON b.VendorGroup = v.VendorGroup and b.Vendor = v.Vendor
    WHERE POITJCCo = @JCCo and POITJob = @Job
    GROUP BY POCo,b.VendorGroup,b.Vendor,v.Name,POITJCCo,POITJob,PO