$(function () { pageContext.i18n.modTalk = 'moderation talk'; pageContext.i18n.replyToComment = 'Reply'; pageContext.i18n.modTalkEmpty = 'moderation talk is empty'; pageContext.url.getModTalk = "/comments/%25ID%25/listModTalk.json"; pageContext.url.possibleCommentRecipients = "/comments/%ID%/possibleRecipients.json"; pageContext.url.commentEdit = '/comments/%25ID%25/edit.html'; pageContext.url.commentView = '/comments/%ID%/view.html'; pageContext.i18n.commentVisibility = { 'full': 'Viewable by all users', 'op': 'Viewable by the original poster', 'mod': 'Viewable by moderators', 'opAndMod': 'Viewable by moderators and the original poster', 'other': 'Advanced visibility', 'dialogTitle': 'Comment visibility', 'selectGroups': 'Visible to groups', 'selectOther': 'Other recipients', 'selectOriginalPoster': 'Original poster', 'selectModerators': 'Moderators', 'selectAssignees': 'Asked to answer users' }; pageContext.i18n.commentMenuLabels = { 'comment-edit': 'comments.menu.edit', 'comment-delete': 'comments.menu.delete', 'comment-convert': 'comments.menu.convert' };pageContext.i18n.answer= { bestAnswer: 'Best Answer', controlBar : { accept: 'Accept', unaccept: 'Unaccept', acceptCommand: 'Accept this answer as correct', cancelAcceptedCommand: 'Remove this answers accepted status' } }; window.croles = { u: false, op: false, m: false, og: false, as: false, ag: false, dc: false, doc: false, eo: false, ea: false }; tools.init({ q: { e: false, ew: false, eo: false, r: false, ro: false, d: false, dow: false, fv: false, c: false, co: false, p: false, tm: false , ms: false, mos: false }, n: { f: false, vf: false, vfo: false, vr: false, vro: false, c: false, co: false, vu: false, vd: false, w: false, wo: false, l: false }, c: { e: false, eo: false, d: false, dow: false, ta: false, tao: false, l: false }, a: { e: false, ew: false, eo: false, d: false, dow: false, a: false, aoq: false, ao: false, tc: false, tco: false, p: false, tm: false }, pc: croles }, { tc: true, nsc: true }); commandUtils.initializeLabels(); }); Skip to Content
avatar image
Former Member

Auto Cost Center FMS Query

Hello Experts!

I'm having trouble with creating a formatted search query in my purchase request.

The scenario is that whenever the requester changes the department (OPRQ.Department), the field in the "Division/Department" (PRQ1.OCRCODE2) would automatically be filled up with either (1) ENNGDEPT, (2) GEN DEPT ot (3) SALES DEPT.

Help guys. I'm currently on our implementation phase in one of our clients. Thank you!

query.jpg (272.9 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Oct 21, 2016 at 10:53 AM

    Hi Nikka,

    In the configuration of your FMS, set the option "Auto Refresh", on the combobox on side of option "Auto Refresh", choose the option 1"When Exiting Altered Column" and below this information choose the column that represent (OPRQ.Department).

    See the image:

    Hope it helps.

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Else try below,

      Declare @Dept as nvarchar (10) 
      Set @Dept=$[OPRQ.department]
      If @Dept in ('-2','3','4')
      Begin
      Select 'SALESDEPT'
      End
      If @Dept in ('1','6','5')
      Begin
      Select 'GEN DEPT'
      End
      If @Dept in ('7','2')
      Begin
      Select 'ENGGDEPT'
      End

      Hope this will work...

      Thanks,

      Rahul

  • Oct 21, 2016 at 01:33 PM

    Hi Nikka,

    A query for your FMS:

    SELECT CASE WHEN (SELECT Name FROM OUDP WHERE Code = $[OPRQ.Department]) IN ('SA', 'SALES', 'SSAD') THEN 'SALESDEPT'
    			WHEN (SELECT Name FROM OUDP WHERE Code = $[OPRQ.Department]) IN ('ACCT', 'MNGT', 'BUSDEV') THEN 'GEN DEPT'
    			WHEN (SELECT Name FROM OUDP WHERE Code = $[OPRQ.Department]) IN ('E', 'ENG') THEN 'ENGGDEPT' END
    

    The config for your FMS

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded