Cannot import new rate lines for a Rate Model

User147276 · ‎01-14-2020

We are using the Rate Model and the documentation says that I can update existing rate lines, via the export/import functionality built in the Rate Model. Documentation also says can create new rate lines that append to the model via the import functionality.

Neither of those work for me. When I export the rate lines and update the end dates on all 164, then upload, only 1 will be updated. The rest of the lines are ignored.

Creating new lines via the import functionality doesn't work either. I get errors. We have 4 attributes and those fields do not get updated when we import new lines.

Has anyone successfully updated multiple existing rate lines via the export/import functionality? Has anyone added new rate lines via the import?

I have had to manually create every line and have 200+ more that need to be added to the model. Please help! Thanks!

User147276 · ‎02-18-2020

Wanted to let everyone know what the solution is for this issue. I opened a HI ticket and worked through this with someone there.

After you set up the rate model, the first time you need to adjust it or append new lines to it, you will need to do the following: (copied from my HI ticket)

Set the system property "glide.import_set_row.dynamically_add_fields" to true. sys_properties table
Specifies whether an import set can add new columns to the staging table (true) or not (false). Instances that contain large numbers of import sets can sometimes become unresponsive when an import adds a column because the instance must alter every row in the staging table. Sometimes the database alter table action causes an outage. Setting this property to false prevents an import set from adding columns to the staging table and produces a log message.

NOTE: This is a really scary property if you have integrations, etc. We have it set to FALSE in our prod instance.

More info from the HI ticket:
Rate Line Import is a combination of update Rate Line and Rate Line Attributes. The staging table 'imp_rate_model_line' is having cloned fields aligned with 'rate_model_line' and the attribute fields needs to be get added dynamically based on exported file's columns. For this reason, 'glide.import_set_row.dynamically_add_fields' property should be TRUE. In case, setting this property to TRUE for all the import is costly, then follow the below workaround.

1. Set the 'glide.import_set_row.dynamically_add_fields' to TRUE.
2. Import Rate Line with the attributes column once (with single row also fine).
3. Set back the 'glide.import_set_row.dynamically_add_fields' to FALSE. This will create the necessary dynamic fields in staging table on the first import.

So, from next time onwards it's not required to have dynamically added fields to staging table. But, if you have any new attributes added for Rate Line Attribute, then the above steps should be executed once again.

NOTE: We set the system property to True, did one update/import and then set it to False. Afterwards, I did daily rate line imports to update existing rate lines and append new rate lines to the table. Worked great every time!

View solution in original post

Noah Drew · ‎01-14-2020

Hi @tcodell !

Have you checked to make sure that your import is not having issue due to one of the points mentioned in the docs? Examples:

Rate lines fail to import in the following circumstances:

Overlapping effective dates for the same set of criteria values.
Criteria values with no matching data in the system. For example, a rate line might have Business Analyst as the Role criteria but that role does not exist in the system.
Rates in non-functional currency but no corresponding budget reference rates for the required period exist in the system.

Also, make sure the "import_rate_line" UI Page has not been customized at all.

Are you importing a file using a .csv, .xls, or .xlsx extension?

Which errors are you receiving?

Hope that helps!

If it did, please mark as Helpful and consider setting this reply as the Correct Answer to this question, thanks!

User147276 · ‎01-14-2020

Yes, I am sure that I have met all the criteria. If you are simply using the export - update via excel - import process, then all the lines should update.

For example, I have 164 rate lines that each have an end date of 12/31/2021. I want to update them all to 12/31/2020, as the precursor to creating new rate lines with a start date of 1/1/2021.

When I export the rate lines (cvs), via the export function, save the file as an excel (xlsx), change all the end dates to be 12/31/2020 and save.

Then import the excel file (xlsx), via the import function, it will only update one rate line. The rest are ignored. These are all valid working rate lines today.

Arvind Singh Ra · ‎01-15-2020

Hi tcodell,

Please make sure that:

If the import file is a CSV file, then the data from the last saved file is considered for import irrespective of value specified in the Sheet Number.
If the import file is an XSLX file, then the data from the specified sheet number is used for import

Also, check the logs after Run transform, you may some errors form there.

All the rate lines in the rate model are exported to the downloaded file. If there is no rate line in the rate model, the file is an empty template containing only the column names in the first row. Each column corresponds to a criteria column in the rate model.

Also, Rate lines are not imported if they have:

Overlapping effective dates for the same set of criteria values.
Values with no matching attribute data in the system. For example, a rate line with Business Analyst in the Role attribute, but that role does not exist in the system.
Rates in non-functional currency but no corresponding budget reference rates are available. For example, a rate line that has a rate for the period November 1–15 in EURO, but the budget reference rate is not available for EURO for November.

For more info:

https://docs.servicenow.com/bundle/madrid-it-business-management/page/product/project-portfolio-suit...

Please do mark correct and helpful based on impact.

Regards,

Arvind

User147276 · ‎01-17-2020

Yes, I have set the XLSX file to have a sheet of 1 and a header row of 1. And, when I use the built in UI action to export the rate lines that currently exist they all show up in the file.

Have you gotten it work in an instance? Importing rate lines to append to an existing rate model or even updating the existing lines via the import feature?