Hi everyone

I'm looking to add a row to a reference data set in Vendor Insight. To save myself time I'm trying to add this row within Apptio rather than in the source application before uploading my reference data every month.

Is there way I can achieve this internally within Apptio? I can't seem to do it using formulas (circular reference errors) so instead I looked into using an editable or enriched table. The editable table allows me to add this row but I can't find a way to append it to the table containing the rest of the data. I' tried using an enriched table but this doesn't allow me to add an additional row so seems to also be redundant.

Any input is greatly appreciated. Thank you !!

@Patrick Graham can you help? 

You can do this using an editable table. The trick is that when you create the Editable Table initially, you also have to create a "normal" table where the source is the editable table that you created. Once creating that second table, you can then append that one to the reference data set that you need it in.

1. Create editable table ("Table Name Editable Table") and configure columns/data

2. Create "normal" table ("Table Name"); source = "Table Name Editable Table"

3. Append "normal" table to reference data set

Hope this helps!

Hi @Patrick Graham

Thank you for your reply and thank you to @Renee Daignault for tagging Patrick in this

I've only just managed to get to reading your suggestion so I'll give it a go and will revert back with an update. Wish me luck!

Francis

Hi again @Patrick Graham

I managed to get things to work which is awesome, but I now face another issue. I'm not sure if there is a way to accommodate for this in Apptio as opposed to in the raw data but I'll pose the question nonetheless.

All of this relates to my month-end process and management of Vendor Insight. The issue I'm now facing is this:

The model allocates AP data (with a PO number that has a matching Contract number) from the Contracts object to the Vendors object by matching the Normalised Vendor Name between both master data tables. I've noticed that the vendor names in our Contracts Register (uploaded to the Contracts Raw table and make up the Normalised Vendor Name in the Contracts master data table) are not identical to the vendor names in our NAV application (ERP) (uploaded to the Vendors Raw table and make up the Normalised Vendor Name in the Vendors master data table). When uploading data into the Contracts Raw table the vendor names in our Contracts Register are populated in the vendor column. For reasons not worth mentioning, the only way to automate aligning vendor names is to do it in Apptio and not in the source data table. To do so I have tried the following:

-uploading a mapping table of various vendor name spelling/format etc with the approved name as the final column, and then editing the existing vendor column in the formula step for Contract Raw by nesting a lookup to the mapping table to return the correct vendor name. This gives me a circular reference error

-alternatively I have instead tried tried renaming the original vendor column in the Contracts Raw table, then adding lookups, test etc using formulae, and then adding a new column called vendor to which the lookup results are fed, with the aligned name as the output being populated in this column. When doing so, I get an error for this column stating that the vendor column doesn't exist (I presume this is in reference to the original/out-of-the-box vendor column that I have renamed and re-purposed?). At this point, the vendor columns remains blank.

My alternative to automating this in Apptio is manually correcting the vendor names in the Contracts Register output before uploading this into Apptio. I can confirm that having done this for the month-end just passed, this is an unnecessarily time consuming and mundane task I don't want to have to do every month (there are multiple vendors with with multiple contracts so it was such a drain on time). Of course, if there is a way for me to accomplish this within Apptio that would be awesome!

Any help from yourself or anyone else would be greatly appreciated

Thank you.

Francis

Hi Francis,

In this situation I would recommend trying the TableMatch function

This formula acts as a conditional if statement/mapping table like you've already tried, however, it is a little more efficient. You'll need to add all the variations that you may see within the raw data so that Apptio recognizes it, and converts it to the proper format. Converting everything to upper case (using the Upper function ) will help as well. In the end, it's still a table that you will need to update and maintain, but hopefully if you can get a lot of the variations up front, it should get easier over time.

This is something that we do pretty frequently to normalize columns between data sets to assist in allocations.