In situations such as these, I generally avoid doing anything on the application side, and rather go straight to the database and do the update there. It's typically much easier, faster, and safer.
And I should point out.. that's regardless of whether it is FootPrints, SDE, Remedyforce, or something else I am working on
I'll reach out to our SQL SA, as I don't know enough about SQL to accomplish that.
If you shoot me some screenshots of what you're trying to do I can help piece a quick update query.
Not sure which parts I should take screenshots of, but the field is called "EmploymentType" in both contacts, and service requests. Linked by a Ticket/Contact relationship. Our unique key field is "Email address" in the conacts.
Let me know what I should take sceenshot of, the rule? The fields? I'm so SQL dumb i don't know what'll help haha.
Try something like this. You'll have to fix the table names and the unique ID for what you need it to be.
update a set a.NEW_FIELD = b.NEW_FIELD
from fpscdb001_ws_001.incident a
inner join fpscdb001_ab_001.contact b on a.domain_name = b.domain_name
2 of 2 people found this helpful
Actually this is better, using your names (the names may be different in the database, but your SQL guy can fix it easily). Also I know you're an older FP install so I think the schema should be right, but the table may still need to be fixed:
update a set a.EmploymentType = b.EmploymentType
from fpscdb001_ws_001.service_request a
inner join fpscdb001_ab_001.contact b on a.email_address = b.email_address
Awesome, thanks Nicolas! I'll pass this up to the SQL SAs.
I really need to learn SQL, it seems so easy.
It's pretty easy to get to an intermediate level (what I consider myself). I'm self taught and Google comes in real handy. In fact its a skill I strongly encourage any system admin to have if for nothing more than situations like this, or for 3rd party reporting.
Hey this is random and overdue, but i solved this issue but doing a time based rule that used generic linking and then copied the values from the address book to the ticket. its interesting to me that this worked because of your comment:
First thought; A rule that runs off hours that updates the record after I import them!
- Nope, cannot pull from linked items with a time based rule.
the trigger i used was time based,
criteria was generic linking ticket/contact and then contact id=any value
action was to copy fields from linked record
and then it backfilled the fields the way i expected.
Oh geeze, all I needed to do was add "Generic Linking" into the criteria and I now get the "from linked" methods for setting a field value action. I was focusing on identifying the blank fields that needed to be filled.
1 of 1 people found this helpful
You are welcome! i have been taught to avoid touching the DB if at all possible, its outside a typical ITSM consultants wheelhouse. Not to mention if i wipe out someones DB my engagement comes to a screeching halt.
1 of 1 people found this helpful
Honestly I'm the opposite. I was also part of those also trained in FP that the DB is something you don't touch, it's a black box that you should never concern yourself about. Never been a fan of that philosophy since it cripples the administrator (how do you do backups and restores if you don't understand the DB? How do you do 3rd party reporting? Custom development? etc). I came from the SDE world where that wasn't the case, so I brought that mentality over. Learned the FP DB, how it works, what can and can't be done. Biggest thing is always take a backup. So many times I find I can fix a problem or do something that cannot be done, by touching the DB. And if it breaks, a DB restore gets you back up and running in 10 minutes.
Even our standard admin training includes an overview and discussion on the DB.