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.
Original Message:
Sent: 01-11-2021 11:40
From: Louis Johnston
Subject: Regarding showing Features & Defect/Job Document Links
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
Original Message:
Sent: 01-11-2021 07:29
From: Chris Wareham
Subject: Regarding showing Features & Defect/Job Document Links
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,
- Click the Add button
- Chose 'Feature' in the Linked Table dropdown
- Select 'Site Code' for the Site Code field
- 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
Original Message:
Sent: 01-11-2021 05:39
From: Louis Johnston
Subject: Regarding showing Features & Defect/Job Document Links
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
------------------------------