Adding non standard field to report

[ttaylor]

In our Staff Details data entry screen we have a "non standard" data field that was added when our system was first set up back in May 2010. It contains a numeric unique ID number used by the University for official identification.

University of Greenwich ID (Screengrab attached)

I would like to include the details from that field in one of our reports. The report is one that shows Staff by their Security Access Groups

SQL Source is

select staff.id, staff.id as staff_id, staff.knownas as _knownas, staff.username, staff.jobtitle,
staff.accgrp_id, accgrp.name as _access_group, staffcat_id, staffcat.name as _staffcategory
from staff
left join accgrp on staff.accgrp_id=accgrp.id
left join staffcat on staff.staffcat_id=staffcat.id
where (staff.deleted true)

Two questions really:-
1) How do I find the database ID/name for this non standard field?
2) How so I add this field to the report

Regards
Tony Taylor
ESO Maidstone

[William]

Hi Tony

Thanks for the email. The non standard or custom staff field that you are using would be stored in the "custom_staff_fields" table. This means that you would need to perform a left join to this table and then add the custom field above the "from staff". Below is how you would add this field.

Code: Select all

select

staff.id, staff.id as staff_id, staff.knownas as _knownas, staff.username, staff.jobtitle,
staff.accgrp_id, accgrp.name as _access_group, staffcat_id, staffcat.name as _staffcategory,

custom_staff_fields."University_of_Greenwich_ID"

from staff
left join accgrp on staff.accgrp_id=accgrp.id
left join staffcat on staff.staffcat_id=staffcat.id
left join custom_staff_fields on (custom_staff_fields.staff_id=staff.id)

where (staff.deleted true)

Something to note about the SQL above is the line
custom_staff_fields."University_of_Greenwich_ID". You will note that I have put quote marks around the field University_of_Greenwich_ID. You only need to put quote marks around a field name if the field name has any uppercases involved. If the field does not have any uppercases you can just type custom_staff_fields.university_of_greenwich_id instead.

Please can you find out how the way the field name is formatted and enter it into the SQL accordingly. The field name can be different to the display name. To find out the field name please go to the Design tab in the staff editor, click Enter Design Mode button, right click on the field "University_of_Greenwich_ID" and select Edit Field.

Hope this helps.

[ttaylor]

Thank you William for the very clear instructions.

As you say the field name shown in the Design Tab was nothing like the Field Description shown on the form.

I have amended the report and it works exactly as I hoped.

Many thanks for your help

Regards
Tony Taylor