Forum Discussion

CharlotteMorin's avatar
CharlotteMorin
Qrew Cadet
19 days ago

Dropdown list with Ratings?

I have a user request to track software skills for team members.

They want a list of items, for example software packages, in a drop-down list.

But - they also want a rating for each skill, from 1-5.  So for example, they don't want to just know that John has experience with "QuickBooks" and "Xero" - they want to say he has "QuickBooks - Level 1 experience," and "Xero - Level 5 experience."  And of course, they want to then be able to get a report of all team members with, for example, Xero experience level 3 and above.

The list of systems could have 20 or so items, and I'm thinking there must be a  better way to implement this than to have 20x5 items in the dropdown.  Is it possible to list the 20 software packages, then a separate 1-5 ratings field, and somehow combine them to get a complete list of software experience, with the correct ratings attached, for each person?

3 Replies

  • You will want to set up a "Join Table".  Also called a Many to many relationship.

    So One Employee has many Employee Software Experience records.

    One Software Name has Many Employee Software Experience records.

     

    If you do not use this approach, your reporting will be a nightmare.

    If you have a concern that the data entry is difficult to add many Employee Software Experience records, then we can dicsuss to make that process easier.

     

    Feel free to post if you have any questions about the setup of the Many to many relationships.

  • OK, so in that case is this right -

    the M:M could look like:

    Employee ID      Software      Rating             Formula
    -----------------------------------------------------------------------

    25                        Xero              5                      Xero-5

    25                        QB                 3                      QB-3

    32                        QB                4                       QB-4

     

    And I could then pull Formula up into the employee table for reporting, using a text summary field.

    To do the data entry, I could have an editable report on a tab in Team Member, which would allow me to create the necessary number of records for each person, picking from single-choice dropdown lists for Software and Rating.

     

     

     

    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend

      Yes you were on the right track. A couple of tips.  

      Once you roll up the combine text summary field you can make it much more attractive for reporting on a report by doing this in a formula text field.

      SearchandReplace([My Combined text summary field]), " ; ", "\n")

      That will make a nice clean vertical field.

      Also Grid Edit on New Form to add records does not work (ie to add children to the Team member record) .  LMK if you need a work around to that.