13 Replies Latest reply on Aug 27, 2019 11:39 AM by Nicolas Roome

    Filling a new linked field

    Vern Meyer
      Share This:

      We're adding more information to our contact records. One of these peices of information, we'd like to go back and add to Service Requests (just until the first of the year). I am able to import the contacts to give them all the correct info, but im having issues filling the new linked field in their SRs.

       

      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.

       

      Second thought; An import is recorded as an update. So a rule in SR with a trigger of "on linked item update" has the Set field vaule "copy from linked" action. So if I set up that rule before import, will the import trigger that rule and update it's SR?

           - Server stopped responding when attempting an import woth that rule in place.

       

      Any ideas?

        • 1. Re: Filling a new linked field
          Nicolas Roome

          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.

          • 2. Re: Filling a new linked field
            Nicolas Roome

            And I should point out.. that's regardless of whether it is FootPrints, SDE, Remedyforce, or something else I am working on

            • 3. Re: Filling a new linked field
              Vern Meyer

              I'll reach out to our SQL SA, as I don't know enough about SQL to accomplish that.

              • 4. Re: Filling a new linked field
                Nicolas Roome

                If you shoot me some screenshots of what you're trying to do I can help piece a quick update query.

                • 5. Re: Filling a new linked field
                  Vern Meyer

                  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.

                  • 6. Re: Filling a new linked field
                    Nicolas Roome

                    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

                    • 7. Re: Filling a new linked field
                      Nicolas Roome

                      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

                      2 of 2 people found this helpful
                      • 8. Re: Filling a new linked field
                        Vern Meyer

                        Awesome, thanks Nicolas! I'll pass this up to the SQL SAs.

                         

                        I really need to learn SQL, it seems so easy.

                        • 9. Re: Filling a new linked field
                          Nicolas Roome

                          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.

                          • 10. Re: Filling a new linked field
                            Linda Kirkpatrick

                            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. 

                            • 11. Re: Filling a new linked field
                              Vern Meyer

                              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.

                               

                              Thanks Linda!

                              • 12. Re: Filling a new linked field
                                Linda Kirkpatrick

                                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
                                • 13. Re: Filling a new linked field
                                  Nicolas Roome

                                  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.

                                  1 of 1 people found this helpful