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.

  • Szang - thank you again for your design and clear instructions.  I just completed this for my intent and it worked perfectly.

Reply Children
No Data