5 Replies Latest reply on Feb 22, 2018 2:47 PM by Nicolas Roome

    Table Mapping Assistance

    Erin Mills
      Share This:

      I am trying to find figure out how the Incident Table maps to the assignment table, or team table, or any table for that matter.  Any suggestions?  I am trying to run reports outside of service analytics.  I can map App_user, Contact, team, Agent_team, assgnmt, but nothing seems to map back to Incident.  What am I missing??

        • 1. Re: Table Mapping Assistance
          Don Cholish

          You will want to download the data model here: Data model for FootPrints version 12.01.03 - BMC FootPrints Service Core 12.1 - BMC Documentation

           

          Our database architect was generous enough to carve out some time in his busy schedule to give us some more details that would be useful.  See below:

           

          The HTML Data Model has Logical and Physical layers. Please look at the Logical layers for Subject Areas 46-51.  Then also look at the Physical Layers.

          data.jpg

           

          The Logical Layer has “Logical Only” entities, and the one you want is “Item”.   The definition from the data model is “The Item is the abstract class for Issue, Configuration Item, Address Book entry, and other information items that are the main information entities in FootPrints. The Item structure and behavior is defined by its Definition. The Item is namespaced, identified by, and logically stored in the single parent container. It can be referenced in the same manner both inside and outside the container. The Item is the storage model and the implementation for the Item Definition. There are many important specializations of the Item class, and there even more Template definitions of Container/Item that provide an end-user application experience to the End-User. The Item implementation must implement flexible schema in an easy to manage, well engineered, and high performing way. The item can store user-defined and user-named fields defined in its definition. The Item could implement the nice-to-have features such as an inheritance model for Item subtypes, and have the ability to represent collections of subordinate items. The Item data can be imported, exported, or synchronized with external data sources. The Item data can be linked and synchronized with other items in the same Instance.”.

           

          You should also query the views “fpscdb001_system.v_item_defn”, ““fpscdb001_system. v_cntnr_item_fld”.  Here are two useful queries for you.

           

          Select distinct container_description

          ,cntnr_external_name

          ,item_external_name_en_US

          ,containerSchemaName

          ,table_name

          FROM fpscdb001_system.v_cntnr_item_fld

          order by container_description,cntnr_external_name,containerSchemaName

           

          Select container_description

          ,cntnr_external_name

          ,item_external_name_en_US

          ,field_externalName_en_US

          ,containerSchemaName

          ,table_name

          ,col_name

          FROM fpscdb001_system.v_cntnr_item_fld

          -- sample where clause, change or omit

          where container_description = 'Workspace Container'

          and cntnr_external_name = 'Service Desk' and item_external_name_en_US = 'Incident'

          --

            order by container_description,cntnr_external_name,containerSchemaName

          • 2. Re: Table Mapping Assistance
            Don Cholish

            This may also help:

             

            -- List assignment fields that have data

             

            Select distinct

            1. ca.container_description

            ,ca.cntnr_external_name

            ,f.item_external_name_en_US

            ,f.item_defn_id

            ,ca.schema_name + '.' +ca.table_name as FullTableName

            ,f.field_externalName_en_US

            ,f.fld_defn_id

            FROM fpscdb001_system.v_Item_Fld_Defn f

            join

            (

                   select distinct container_description

                   ,cntnr_external_name

                   ,containerSchemaName

                   ,item_defn_id

                   ,schema_name

                   ,table_name

                   from fpscdb001_system.v_cntnr_item_fld

            ) ca on ca.item_defn_id = f.item_defn_id

            where f.fld_defn_id in

            (

            select distinct field_id

            from fpscdb001_system.asgnmt a

            )

            ;

            • 3. Re: Table Mapping Assistance
              Erin Mills

              Great information Don!  Thank you

              • 4. Re: Table Mapping Assistance
                Craig Sakach

                All very good information but I'm not understanding how the "Change Implementors" and "Change Manager" fields are associated. I'm building a custom Crystal Report with drill-down capability against our Change Management workspace (out of the box wizard) and those 2 fields are alluding me. What would be the easiest way to link to that information?

                • 5. Re: Table Mapping Assistance
                  Nicolas Roome

                  Craig Sakach, read this, it should give you the info you need: Import issues? You can do it manually!