Confirm

Expand all | Collapse all

Regarding showing Features & Defect/Job Document Links

  • 1.  Regarding showing Features & Defect/Job Document Links

    Posted 10 days ago

    I am writing a data source for our insurance team and I want the tables when you open the report to also open the correct Feature when you drill down & have any defect/job documents/pictures attached that allow themselves to also be available when drilled down. 

    My question is:

    1) How do you make it so when you open the report and drill down the corresponding feature appears in the linked tables (currently when I drill down it only shows, defect, job & Central Site as linked tables)

    2) How do defect/job pictures(documents) link to the defect/job themselves and could an example be given of this?

    Any help would be greatly appreciated.

    The SQL I have so far is:

    SELECT
    feature.central_asset_id,
    (defect_attribute.param_string_value*1) as InsuranceID,
    defect.defect_number,
    JOB.JOB_NUMBER,
    job.job_notes,
    central_site.site_code,
    central_site.site_name,
    defect.defect_date,
    job.actual_start_date,
    job.actual_comp_date,
    document_link.document_name
    FROM
    central_site,
    defect,
    priority,
    feature,
    feature_type,
    job,
    defect_attribute,
    document_link
    WHERE
    JOB.JOB_NUMBER = DEFECT.JOB_NUMBER AND
    feature_type.feature_type_code = feature.feature_type_code AND
    feature.site_code = defect.site_code AND
    feature.plot_number = defect.plot_number AND
    central_site.site_code = defect.site_code AND
    priority.priority_code = defect.priority_code AND
    defect.defect_status_flag = 'N' AND
    feature.feature_type_code IN ('TREE','GTRE','TRH') AND
    defect.defect_number = defect_attribute.defect_number AND
    defect_attribute.param_type_code IN 'TRIC' AND
    defect_attribute.param_value_code IS NOT NULL



    ------------------------------
    Louis Johnston
    Hull City County Council
    HULL
    ------------------------------


  • 2.  RE: Regarding showing Features & Defect/Job Document Links

    Posted 10 days ago
    Hi Louis

    The primary key for the feature table is a combination of site_code and plot_number.  You have site_code in the SELECT part of your SQL and although it uses central_site.site_code, that will be the same as feature.site_code.  You do not have feature.plot_number though, so add that to the SELECT.  On the Linked Tables tab you can then,
    1. Click the Add button
    2. Chose 'Feature' in the Linked Table dropdown
    3. Select 'Site Code' for the Site Code field
    4. Select 'Asset Number' for the Asset Number field (Asset Number and Plot Number are the same thing)

    Run the Data Source and you will be able to drill down to the Feature.


    Document Links
    The article Using SQL to show Document Links in Confirm shows the document_link table is used for Defect and Job Document Links.  Select the correct Entity Type for Defects and Jobs.
    • Defect (entity_type = 'DEFECT', entity_key corresponds to defect.defect_number)
    • Job (entity_type = 'JOB', entity_key corresponds to job.job_number)

    I hope that helps you.

    ------------------------------
    Chris Wareham
    Principal Software Support Analyst
    Confirm
    ------------------------------



  • 3.  RE: Regarding showing Features & Defect/Job Document Links

    Posted 10 days ago
    Edited by Louis Johnston 10 days ago

    That is honestly really useful especially for future reports.

    I was wondering after applying the above how to set it up with document links. I tried using the linked table and it kept crashing even after adding document number which is a requirement of the linked table.

    I was also wondering how it would work if I wanted it to show Job photos as well as defect photo's is this possible. As if I put in the where entity_type = 'DEFECT' and entity_type = 'JOB' how would it specify/split that in the data source to show i.e. Entity type - Defect - Photos then Entity type - Job - Photos for example? - if that makes sense?

    Furthermore, now if the Defect doesn't have a photo - it wont appear on the report how would I rectify this?

    New data source if this helps is:

    SELECT
    feature.central_asset_id,
    feature.plot_number,
    defect_attribute.param_string_value,
    defect.defect_number,
    JOB.JOB_NUMBER,
    job.job_notes,
    central_site.site_code,
    central_site.site_name,
    defect.defect_date,
    job.actual_start_date,
    job.actual_comp_date,
    document_link.entity_type,
    document_link.document_name
    FROM
    central_site,
    defect,
    priority,
    feature,
    feature_type,
    job,
    defect_attribute,
    document_link
    WHERE
    JOB.JOB_NUMBER = DEFECT.JOB_NUMBER AND
    feature_type.feature_type_code = feature.feature_type_code AND
    feature.site_code = defect.site_code AND
    feature.plot_number = defect.plot_number AND
    central_site.site_code = defect.site_code AND
    priority.priority_code = defect.priority_code AND
    defect.defect_status_flag = 'N' AND
    feature.feature_type_code IN ('TREE','GTRE','TRH') AND
    defect.defect_number = defect_attribute.defect_number AND
    defect_attribute.param_type_code IN 'TRIC' AND
    defect_attribute.param_string_value IS NOT NULL AND
    document_link.entity_key = defect.defect_number AND
    entity_type = 'DEFECT'



    ------------------------------
    Louis Johnston
    Hull City County Council
    HULL
    ------------------------------



  • 4.  RE: Regarding showing Features & Defect/Job Document Links

    Posted 9 days ago
    Hi Louis

    From the Confirm Explorer menu you cannot open a Document Links screen directly and you have to go via the Job or Defect screen.  Instead of drilling down from the SQL results to the linked table for Document Links, drill down to the Defect or Job first them open the Document Links from there.

    ------------------------------
    Chris Wareham
    Principal Software Support Analyst
    Confirm
    ------------------------------