How to report on links in the Attachment section?

We are on SBM 12.1 and would like to know if it is possible to report on links between items. The two forms are linked in the attachment section. We have 2 forms that are linked on creation. The problem comes when one of the item closes, the other does not automatically close. So is there a report function I can use that would allow me to see 

  • Suggested Answer

    0  

    It is possible to do for a listing report and advanced pass-through SQL queries for your Search Filter.  The pass-through query will look like the following where UBG_ISSUES represents the database table name for your process app.  You can find this in Composer by looking at the table and take the database name with the prefix value.  In this case UBG is the prefix value.  The value for TS_BASETYPE is figured out from the database schema guide: 16 is a file attachment and 64 is an item attachment.

    @WHERE UBG_ISSUES.TS_ID IN (
      SELECT a.TS_CASEID FROM TS_ATTACHMENTS a
      JOIN TS_TABLES t ON a.TS_SRCTABLEID = t.TS_ID AND t.TS_DBNAME = 'UBG_ISSUES'
      WHERE a.TS_BASETYPE = 64
    )

    You can see more about this query in our documentation at https://docs.microfocus.com/doc/Solutions_Business_Manager/12.2/usingqbeandadvancedsql.

  • 0   in reply to   

    Rather than reporting on items where the linked item is closed and the other is not closed, consider making this automatic.  When one item is closed, the other is automatically closed too. There are two ways to do this. This would guarantee that both are closed and eliminate the need for reporting.

    TIP: Some people get intimidated to make big changes to their process app without knowing the results. (I don't blame them; testing can be messy.) So, I would recommend setting up a small test application to try both options. After you decide which you like better, you can make the changes to your real process app.

    Option 1: (my favorite) Using relational fields

    This option uses relational fields to link the items together, making the automation easier. There is a tutorial with all the steps in the documentation here: Tutorial: Defining Child-Driven Actions - Solutions Business Manager

    Option 2: Using triggers

    You said that users are linking items in the Attachments section, so this might work for you too. This is a little more manual, in my opinion, but it works.

    First, to setup the trigger and the automation, follow the steps here: Setting up a New Trigger - Solutions Business Manager

    Then, when users link items, they will be able to set the "link type" like this:

    Note: There are several link types available. You can decide the best one for your situation. Each option is described here: ItemLink-Type - Solutions Business Manager

    After the item is transitioned (e.g. when it is closed), the trigger will fire. The second item will get the trigger and will also close.

    Hope that helps,

    Vickie