A technical blog about my projects, challenges, and discoveries in the world of data warehousing using SQL Server, Power BI Desktop, DevExpress, and more.

Tuesday, December 5, 2017

Using Two List Boxes to Manage Key Word Tags (or Similar Values)

Recently I started using OutSystems as a web development tool because Microsoft decided to abandon Access Web Apps and replace it with a not-ready-for-prime-time tool called Power Apps. I tried to use Power Apps, but when I found it incapable of using SQL Server views of data to present information to the user, I decided it would not serve my purposes and found OutSystems.

I'm glad I did. OutSystems provides all the functionality I need plus room for growth down the road. It opens up new possibilities rather than restricting my options unreasonably as Power Apps would have done. It is called a "low code" environment as opposed to the "no code" environment Power Apps touts. I guess "low code" is what I need, because even though I have no prior web development experience, I was able to follow their tutorial project without any difficulty, and after having done that, I was able to reproduce all the functionality of the Access Web App I had already written for the Tobacco Farm Life Museum (run by my sister Melody) and then improve upon it.

All of that is preamble to this blog post, which is mainly about one of the ways I was able to improve upon the Access Web App design using the OutSystems tools. In the database, people and firms were associated with "interests" to enable key word searches based on the interests. The table that relates people (or firms) with interests is a bridge table with a many-to-many relationship: one interest can be associated with many people, and one person can be associated with many interests. Originally, the only way to edit the interests of either a firm or a person was to open that one firm or person's record, go to the list of interests for that entity, and then edit the interests list. This meant that adding the same interest to a lot of people or firms was time-consuming. But then I remembered a user tool in the Shelby Systems software that I used to work with that allowed users to assign multiple people to a particular option using two list boxes side-by-side, one list of "available" names and one list of "selected" names. I wondered if I could achieve that same effect using OutSystems, and it turned out that I could. Here is a screenshot of the kind of interface I was able to create.



There are several steps to the process of creating such an interface, and the rest of this blog post is here to document those steps. If you use OutSystems, you can download a sample project from the Forge library that I created to show how to implement this solution. If you don't use OutSystems but want to produce the same effect, hopefully the steps here will help you with the outline of the process, even if you have to implement it differently.

The names in the steps below are generic to represent three database tables (or entities in OutSystems parlance): a MainEntitiy (for the entity that will be associated with one or more categorical tags), a TagEntity (for the simple categorical tag entity), and a BridgeEntity (for the table that links the main entities with their tags).

  1. Create a session variable to hold the entity identifier for the tag entity: TagEntitySearchValue.
  2. Add a blank web screen page to the project and name it appropriately: ManageTagAssignments.
  3. Add a local variable to the page that represents the main entities that are not yet associated with a selected tag entity. It should be a "List of Record" data type that includes the main entity table itself and an additional Boolean value to indicate if the user has selected the entity in the list box: MainEntitiesWithoutTags.
  4. Add another local variable to the page that represents the main entities that are associated with the selected tag. It should also be a "List of Record" data type with the bridge entity plus a Boolean value to indicate if the user has selected the entity in the list box: MainEntitiesWithTags.
  5. Add a third local variable to the page that represents one association of a main entity with a tag entity. It should be defined using the bridge entity: CurrentBridgeEntity
  6. Add a preparation to the web page. It will have four steps between the Start and End points.
    • Add an aggregate to get the list of tag entities. You can make this simply by dragging the tag entity from the Data tab onto the preparation sequence.
    • Add a second aggregate to get the main entities not associated with the selected tag entity: GetMainEntitiesWithoutTags.
      • Include both the main entity and the bridge entity. There are a few additional changes that you need to make to this aggregate.
      • Edit the join condition and add a requirement that the bridge table tag entity identifier value must equal the session variable you set up in Step 1: BridgeEntity.TagEntitiyId = Session.TagEtitiySearchValue.
      • Add a filter so that the aggregate only includes people without a matching tag entity value: BridgeEntity.TagEntityId = NullIdentifier().
      • Add a filter so that the list is empty unless there has been a value assigned to the session variable you set up in Step 1 above: Session.TagEntitySearchValue <> NullIdentifier().
      • Add a new column with a Boolean value of False so that the results of the aggregate will match all the required values of the local variable: IsSelected.
    • Add a third aggregate to get the main entities that are associated with the selected tag entity: GetMainEntitiesWithTags.
      • Include both the main entity and the bridge entity.
      • Add a filter to limit the list to main entities with the selected tag entity: BridgeEntity.TagEntityId = Session.TagEntitySearchValue.
      • Add a filter so that the list is empty unless there has been a value assigned to the session variable: Session.TagEntitySearchValue <> NullIdentifier().
      • Add a new column with a Boolean value of False to represent selected values: IsSelected.
    • Add an assign action to populate two of the local variables with the related aggregate lists:
      • MainEntitiesWithTags = GetMainEntitiesWithTags.List
      • MainEntitiesWithoutTags = GetMainEntitiesWithoutTags.List
  7. Add a title to the new web page.
  8. Add a container to the main body of the web page.
  9. Put a combo box inside the container to list the available "tag" values. Give it an appropriate name. Set the other properties as follows:
    • Variable -- the session variable for the tag entity value.
    • Mandatory - true
    • Source Record List - the prepared aggregate for the list of tag entities.
    • Source Attribute - the attribute from the prepared aggregate that is the plain text value for the tag entity.
    • Special List Value 1 - None
    • Special List Option 1 - No Interest Selected.
  10. Add a button widget to the right of the combo box. Change the label to an appropriate action for making the selected item the active tag. I labeled mine as "Search." Make the destination a new screen action.
  11. Add a refresh data action to the sequence and choose the aggregate in the preparation that contains the unselected entities.
  12. Add a second refresh data action to the sequence and choose the aggregate that contains the selected entities.
  13. Return to the main edit screen for the web page interface.
  14. Add a second container to the main body of the page, underneath the first.
  15. Add four containers inside the second container. Change the width of each to be 6 columns wide. Also change the left margin of all the containers to 0. This will create a two-by-two block of containers. 
  16. Inside the first nested container, add a label with a value describing main entities not associated with the tag. Set the width of the label to the same width as the container, 6 columns. Set the style class to Heading 2.
  17. In the container underneath the label you just made, add an expression. The expression will complete the header begun in the label above it with a lookup of the plain text for the selected tag entity value or a generic description when there is no tag selected: If(Session.TagEntitySearchValue = NullIdentifier(), "Tag", GetTagEntity(Session.TagEntitySearchValue).TagEntity.TagDescription).
  18. Set the style class for the expression you just created to Heading 2.
  19. In the container to the right of the first label, add another label with a value describing the entities associated with the tag.
  20. In the container underneath the second label, add another expression exactly the same as the one in Step 13.
  21. Add another container to the page, underneath the others at the same level as the container you created in Step 10. (The exact hierarchical positions of all these containers is not critical as long as the layout works.)
  22. Add three more containers inside the one you just made, and change their column widths to 5, 1, and 6 respectively. Change the left margin of the far right container to 0 so that it is aligned exactly below the labels above it.
  23. Add a list box to the container on the far left and give it a name designating it for the main entities not associated with the selected tag value: UnassociatedListBox. Assign the properties as listed below:
    • SourceRecordList: Use the local variable for the list of main entities not associated with the selected tag value: MainEntitiesWithoutTags.
    • SourceAttribute: Use the value from the local variable for the plain text value to display: MainEntitiesWithoutTags.MainEntities.FullName.
    • SelectionAttribute: Use the attribute from the local variable for the selection state: MainEntitiesWithoutTags.IsSelected. Even though the value for this attribute is set as "False" initially, the list box will overwrite that value to reflect the current user selections.
    • Width: Match the width of the container. 
    • Height: Choose a reasonably long height to balance scrolling within the list box and scrolling the browser pane.
  24. Add a list box to the container on the far right and give it a name designating it for those entities associated with the selected tag value: AssociatedListBox. Assign the properties as listed for Step 18, except for the SourceRecordList use the local variable for the selected entities: MainEntitiesWithTags.
  25. In the container between the two list boxes, add an icon widget. Give it a name signifying the process to add a new entity: AddMainEntities. Change the Source Web Block / Name property to Entities.IconName.angle_double_right and the Size to Entities.IconSize.Size_3x.
  26. Link the icon widget to a new screen action.
  27. Open the new action to edit it. Rename it appropriately: AddMainEntities.
  28. Drag a "for each" action to the sequence and assign its record list to the list box for entities not associated with the selected tag: UnassociatedListBox.List.
  29. Drag an "if" action beside the sequence near the "for each" action and set its condition property to the IsSelected value of the current item in the list: UnaffiliatedListBox.List.Current.IsSelected. Since this is a Boolean value, it can serve as the condition expression all by itself.
  30. Drag a connection from the "for each" action to the "if" action to create the first step in the "for each" cycle.
  31. Drag an "assign" action beside the sequence just below the "if" condition. Make the following assignments:
    • The main entity id for the local variable representing the "bridge" table should be set to the value of the current list box item for entities not-yet-associated with the tag:  CurrentBridgeEntity.MainEntityId assigned to UnassociatedListBox.List.Current.MainEntities.MainEntityId.
    • The tag entity id for the local variable representing bridge entity should be set to the value of the currently selected tag item: CurrentBridgeEntity.TagEntityId assigned to  Session.TagEntitySearchValue.
    • The bridge entity identifier for the local variable should be set to a null value so that a new entry will be created for the relationship in the next step: CurrentBridgeEntity.BridgeEntityId associated with NullIdentifier().
  32. Drag a connection from the "if" condition to the "assign" condition to define the first action if the condition is true.
  33. Drag a connection from the "if" condition back to the "for each" condition to close the loop when the condition is false and trigger the next iteration of the cycle.
  34. Drag a "run server action" action beside the sequence below the "assign" action. Select the "create" action under the bridge entity: CreateBridgeEntity. As the source for this action, choose the local variable for the current bridge entity: CurrentBridgeEntity.
  35. Drag a connection from the "assign" action to the "run server action" action to define the cycle  step in the cycle when the "if" condition is true. Then drag a connection from the "run server action" action to the "for each" action to close the loop when the condition is true and trigger the next iteration of the cycle.
  36. Add a "refresh data" action to the sequence after the "for each" action and choose the aggregate that contains the list of main entities not associated with the selected tag. This will refresh the related list box and the local variable tied to it.
  37. Add a second "refresh data" action to the sequence and choose the aggregate that contains the list of main entities associated with the selected tag. This will refresh the related list box and the local variable tied to it.
  38. Return to the main edit screen for the web page.
  39. Add another icon widget underneath the one to add entities to the tag. Set this one's name appropriately and then set the Source Web Block Name to Entities.IconName.angle_double_left and its size to Entities.IconSize.Size_3x.
  40. Link the new icon widget to a new screen action.
  41. Open the screen action and name it appropriately: RemoveMainEntities.
  42. Add a "for each" action to the sequence and set the record list to the list of the box containing the names already associated with the tag: AssociatedListBox.List.
  43. Drag an "if" action beside the sequence near the "for each" action and set its condition to the IsSelected value of the current item in the list of entities associated with the tag: AssociatedListBox.List.Current.IsSelected.
  44. Drag a connection between the "for each" action and the "if" action to define the first step in the cycle.
  45. Drag a "run server action" action beside the sequence underneath the "if" action. Set it to the server action to delete the relationship between the main entity and the tag from the bridge table: DeleteBridgeEntity. Set the Id property of the action to the entity Id value for the current row of the list of entities associated with the tag: AssociatedListBox.List.Current.BridgeEntity.BridgeEntityId.
  46. Drag a connection between the "if" action and the "run server action" action to define the next step of the loop if the condition is true. 
  47. Drag a connection between the "if" action and the "for each" action to close the loop when the condition is false and move to the next iteration of the cycle.
  48. Drag a connection between the "run server action" action and the "for each" action to close the loop when the condition is true and move to the next iteration of the cycle.
  49. Drag a "refresh data" action to the sequence after the "for each" action and choose the aggregate that contains the list of main entities not associated with the selected tag.
  50. Add a second "refresh data" action to the sequence after the "for each" action and choose the aggregate that contains the list of main entities already associated with the selected tag.
  51. Add a link from a web page in your main flow to the new web page.

2 comments:

  1. do you have the source code?

    ReplyDelete
    Replies
    1. I recently posted a project to the Forge server with sample code in it. I have updated the original post, but I will include the link here too: https://www.outsystems.com/forge/component/3955/dual-list-box-demo/

      Delete

Followers