Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #31310

    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

    #33019

    William
    Keymaster

    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.

    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.

    #33020

    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

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.