Can I populate an acronym list that varies by user

We have different offices that want different acronyms to show up on our log key.   There are over 30 acronyms that I currently have hard coded into the log key as text entities. Can I build an acronym list from a Lookup table maybe, so the user can select certain acronym list groups, depending on their needs?  I want them to be alphabetical, so it is hard to just tack onto the end.  I don't want to make 5 different versions of the log key template.

Parents
  • Not sure this is exactly what you are looking for and there are probably other ways to do this. I offer the following example:

    I set up a sample library Table called LEGEND-TEST2.  It has a key field of Abbreviation and fields for Definition, A, B, C.

    The A, B, C fields are Boolean and in this case they represent each office location that wants different items in the legend. You can substitute actual names for them as appropriate.  A sample library data table is shown in the picture below.

    Note that the Boolean fields have check marks for each abbreviation and definition that is to be included in the legend for each office. You can sort this table by any of the fields to facilitate easy editing, but the sort order does not matter.

    I set up a simple report template of the type Graphic Text Document.  I added a report variable named "Office" to the Template to allow selection of the office at output time but you could also use a field in the project table to control the office that gets printed. Since I used office names of A, B, C in the table, I used office names of A, B, and C for Output.

    The actual legend entry is generated from Two text entities that use the wrap and text columns properties to print the legend items in columns as shown in your example above. 

    The report design is shown in the picture below

    The main text expression uses a case function to generate a separate SqlList function for each office.  The SqlList function extracts a list of items that have the Boolean field for that office checked, orders them by abbreviation, and separates them with a carriage return.  This can be expanded to as many offices as you have.

    The wrap tab of the entity is set to the width of the abbreviation column as shown below.

    The Text columns tab is set to the column spacing that that represents the width of the adjacent definition entity and an appropriate height.

    The Definition entity is set up similar to the above except the SqlList functions are set to return the definition field and the wrap and column spacing is set to space each column in-between the abbreviation columns.

    Sample output for office "A" selected at output time is shown below.

    Sample output with office "B" selected at output time is shown below.

    Note the output lists are sorted alphabetically by abbreviation. If you edit the data table and check different office boxes the items included in each list will be changed appropriately.

    Some comments:

    1. If you enter an abbreviation or description that is longer than the width of the column, it will wrap to the next line and offset all remaining items in the list.

    2. If you leave an abbreviation field or definition field blank, again the items will be offset in the output, thus I have made the fields required.

    3. If you have more items than will fit in the various spaces defined by the number of columns that fit on the page and the height of the column and the line spacing, they will overrun to the right of the page and will not print.

    There are probably better ways to do this.  I tried using a single Text field to indicate what office each legend item was for with entries such as A, or 'A', 'C' and then using an IN operator in the WHERE clause but I could not get the SqlList function to pass this to the access database engine correctly and I was unable to return any entries that were to be used for more than one office. I am not an expert in SQL so I'm sure I had something screwed up syntax wise. If someone wants to pursue this, I can provide what I tried. It would be a simpler text expression and would not have to be edited when the number of offices expand.   

    Hope this helps. If this is not what you were looking for let me know.

  • I realize this is after the fact but an alternative way to accomplish this is as follows:

    I created a data table in the library similar to above but instead of having Boleen fields for each office I set up a single user field as shown below.  In the user field I have entered the office/username of each user that wants each definition separated by commas:

    IN this case each user/office is represented by a single letter but they can be any unique text string such as "Paul at western regional office"

    The text entity that creates the legend listing is similar to what was used above except the text property is simplified to what is shown below

    The definition entity is similar.

    This has the following advantages:

    1. You can have an unlimited number of users/offices each with their own legend and you do not have to modify the data table to add a field for that user.  You also do not need to modify the report design to add a case to the text entity for the new user.

    2. It is simpler and less likely to generate errors.

    The disadvantage of this set up is that maintaining the library table becomes a bit hard as you have to enter each unique office/user name the exact same throughout the table and you cannot sort on a single office. Note that the comma separators are not required but make it easier to understand.

    Note in both alternates the selected user must match exactly the user name used in either the case statement or the user field of the data table.

    Just an alternative way of doing it that might appeal to others that have a lot of users each with their own specific desires.

    I have tested this and the results for both were Identical.  You would have to modify to use your data structure set up and table/field names.

Reply
  • I realize this is after the fact but an alternative way to accomplish this is as follows:

    I created a data table in the library similar to above but instead of having Boleen fields for each office I set up a single user field as shown below.  In the user field I have entered the office/username of each user that wants each definition separated by commas:

    IN this case each user/office is represented by a single letter but they can be any unique text string such as "Paul at western regional office"

    The text entity that creates the legend listing is similar to what was used above except the text property is simplified to what is shown below

    The definition entity is similar.

    This has the following advantages:

    1. You can have an unlimited number of users/offices each with their own legend and you do not have to modify the data table to add a field for that user.  You also do not need to modify the report design to add a case to the text entity for the new user.

    2. It is simpler and less likely to generate errors.

    The disadvantage of this set up is that maintaining the library table becomes a bit hard as you have to enter each unique office/user name the exact same throughout the table and you cannot sort on a single office. Note that the comma separators are not required but make it easier to understand.

    Note in both alternates the selected user must match exactly the user name used in either the case statement or the user field of the data table.

    Just an alternative way of doing it that might appeal to others that have a lot of users each with their own specific desires.

    I have tested this and the results for both were Identical.  You would have to modify to use your data structure set up and table/field names.

Children
No Data