3 Replies Latest reply on Feb 7, 2017 4:52 PM by AlasdairRobertson

    Removing duplicates through a pick list

    johan.henningsson@tetrapak.com Apprentice

      Is there any way to filter the data in a pick list to only display unique values?

       

      I have data in Excel I would like to import to HEAT as is without any customisation. The document consists of 5 columns of data and roughly 1300 lines. It's a process tree structure where the A column consists of our top 11 processes and column B, C, D and E are sub processes and sub sub processes for each top process, and so on. In Excel it's easy to add filtering and filter just one top process in column A, filter one sub process in column B and so on.

       

      My thought was to set this up as five drop downs where you could do the filtering in HEAT. But importing it straight up would mean the first top drop down would have all values from column A, roughly 1300. Here I only want to display the unique values.

       

      I'm going to use an existing business object and cannot make any changes to it, I can only play with the pick list.

       

      Is there a possibility to use constraints and expressions?

       

      Regards

      Johan

        • 1. Re: Removing duplicates through a pick list
          AlasdairRobertson ITSMMVPGroup

          Yes it is possible but not quite as easy as excel.

           

          You will need an object to hold your spreadsheet data with 5 fields on it.  For the first 4 look up fields you would need individual objects to hold the validation/look up data.  These validation objects would hold the distinct values for the pick lists and then then each field is constrained against the previous field.  For a good example you can look at Service, Category and Sub category for incident (Category is slightly different) or the team and owner relationship.

           

          Desired Result:

             

          BuildingFloorRoom
          London A1123
          London A1456
          London A2789
          London B1444
          London B2657
          London B232
          Coventry A11
          Coventry A12
          Reading C13
          Reading C2CEO Office
          Reading C3HR
          Reading C3

          Technical

           

          Object 1

           

          Building
          London A
          London A
          London A
          London B

           

          Object 2

           

          Floor

          1
          2
          3

           

          Object 3

          BuildingFloorRoom
          London A1123
          London A1456
          London A2789
          London B1444
          London B2657
          London B232
          Coventry A11
          Coventry A12
          Reading C13
          Reading C2CEO Office
          Reading C3HR
          Reading C3

          Technical

           

          The validate the fields against each other:

          Building = $(ValidationList]Building)

          Floor = (ValidationList]Floor)

          • 2. Re: Removing duplicates through a pick list
            johan.henningsson@tetrapak.com Apprentice

            But I was hoping to solve this in a single business object, I don't want to create several. It would be so nice if I could take the Excel document and import as is. With above setup I would need to cut it up and import different columns into different business objects.

            • 3. Re: Removing duplicates through a pick list
              AlasdairRobertson ITSMMVPGroup

              You can do the import from the same excel spreadsheet, when you import you set an import key for updates on each table so object 1 and 2 have a single key and a clustered key on object 3 then you can you one spreadsheet and 3 imports.  Even though there are multiple entries they will just overwrite on import to Objects 1 and 2.

               

              These could even be scheduled, so is easy to maintain.