SQL Query for Removing and Re-Adding Service Agreements on Account Records
Hello, we've found when we open a case and set the account, the service agreement will default to which ever one has the oldest created on date. We want it to default to Non-SLA, however many accounts have that as the newest service agreement.
Is there a query that can be written and used in the SQL query console to remove the 5 older service agreements from an account and re-add them so they have a newer created on date?
In other words, I want to do what I am doing in this video for all accounts at once with a query instead of having to go through each account 1 by 1 and do this. Any ideas? Maybe someone could at least help me with what table I would find the accounts service agreements in?
Like
Thank you Ryan,
In the future, is there a recommended way for me to find which tables certain types of data live in? Instead of having to ask in these community articles?
Mitch Kaschub,
Yes. This is how you can find the name of any table.
- Open the configuration by going to https://mybpmnonlineurl/0/dev
- Search for the section or entity name, make sure you have "Title" checkbox checked. This would allow you to search for the name "service agreement".
- You'll see the object that returns in the results is named ServicePact that has a title of "Service agreement"
- The table name is always the same as the entity name, in this case "ServicePact"
Similarly, you can double-click on that object to open it, expand the Columns to see the names of the fields on that table as well, since they will be the same as the property names on that object (not to be confused with their titles).
Ryan
Ryan is there a table that shows which service agreements are added to each account? If I select * from ServicePact it just shows the 7 service agreements but doesn't show which accounts are using them. I tried to run the Accounts table too and I don't see the service agreements in that table either.
Mitch Kaschub,
The table that connects Service Pacts and Accounts is called ServiceObject. Connection happens using AccountId and ServicePactId columns. So if you need to delete service pacts for some particular account you can use this table and "CreatedOn" column, since records are added/deleted there once any record is added/deleted in "Service agreement" detail.
Best regards,
Oscar
Oscar Dylan,
Perfect, that should be all I need. Thanks Oscar!