Batch update after the items are imported?

[fxding]

I wonder if there is a way to do data batch update for a [WS]? Something similair to a SQL statement:

UPDATE [WS]Product

SET mktgrp = 'Lateral Systems'

WHERE mktgrp = 'LAT';

Thanks!

Frank

[michelle.stone]

You can use the import tool to make mass updates - just make sure you match on fields so it knows to update records.  Help here in the Wiki - scroll down to the "Update and Insert New Records" section.

Michelle

[fxding]

Once I use the 'mktgrp' column as "Match On" column, I cannot use it as a column to do the data update. In another word, I cannot use import/update approach to do selected update on one column?

Thanks,

Frank

[jpiggee]

Frank,

In order to do an update to a batch of pre-existing records in a workspace, you need to make sure to choose a unique value to do the match on. This unique value has to exist in every record, but be unique unto the record.

Unfortunately if this is not an available option, the next best option would be to update the spreadsheet, purge the workspace and re-import all records.

[michelle.stone]

Do you have an autonumber field you can match on instead?  I did a quick check and created a 'Product' ws w/mktgroup field.  Only created a handful of records, but it shows the idea.

Ran a report only grabbing the 'LAT' entries that I could use for the import:

Then created the import and matched on my autonumber (read this tip about using them - very useful!):

End result:

Hope that helps.

[fxding]

Thanks Joe and Michelle! Now I understand  what PLM 360's import/update approach means.

My original SQL statement is essentially a selected update, which means search/update on centain records.matching some criteria on the column to be updated.

Does server-side sciprting allows passing a SQL statement to the back-end database?

Thanks,

Frank

[jpiggee]

Frank,

SQL scripting is only allowed to work with the current item you are in, in the current workspace you are working with.

Java scripting is the sever side scripting that allows you to use and change information not only in the current item and workspace, but across the entire tenant.

A good way to think about it is SQL is cosmetic scripting or “math / display” scripting, whereas Java is the “workhorse.” 

[fxding]

Hi Joe,

Thanks for the tip! I'm an old school now.

Is the server side scripting available for end users to do this kind of selected batch update using Java scripting?

If so, how can this be done?

Thanks

Frank

[jpiggee]

Frank,

Unfortunately no, the best ways to do a mass update would be the methods Michelle and I posted earlier, sorry.