$(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
0

Auto Cost Center FMS Query

Oct 21, 2016 at 07:20 AM

198

avatar image

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)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

DIEGO LOTHER Oct 21, 2016 at 10:53 AM
0

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


sem-titulo.png (50.6 kB)
Show 8 Share
10 |10000 characters needed characters left characters exceeded

Hi Diego,

I'm having problems with my formatted search query.

0
Nikka Paula Castillo

Hi Nikka,

Could you explain more your problem?

Kind Regards,

Diego Lother

0

Hi Nikka,

Is there any relation defined somewhere between Department and OCRCode2?

Thanks,

Rahul

0

Hi Diego and Rahul,

Whenever we create a PR, the requester would have to choose from the drop down list which department she comes from.

I would like to create a FMS that would automatically choose a "Division/Department" column in PRQ. When we choose departments SA, SALES or SSAD it should automatically put SALESDEPT, for ACCT, MNGT and BUSDEV = GEN DEPT , E and ENG = ENGGDEPT. I need help with the creation of the the query.

Thank you very much.

1.jpg (69.4 kB)
2.jpg (146.6 kB)
0

Hi Nikka,

When you move cursor over Department, you can see the internal numbers, like when you select 'SA' and move cursor to this field you can see internal number at the bottom when you enable System Information. You have to provide those numbers with its value in order to build the FMS else if you have your own FMS please post here to check.

Thanks,

Rahul

1

Hi Rahul,

Thanks for the quick response. Below is the data generated from table OUDP.

I haven't started creating the FMS query yet because I'm not quite sure how to begin building it.

Thank.

3.jpg (34.5 kB)
0

Hi Nikka,

Try to use FMS provided by diego below, Just replace values with Internal codes and then let us know the result.

Thanks,

Rahul

0

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

0
DIEGO LOTHER Oct 21, 2016 at 01:33 PM
0

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


capturar.png (52.5 kB)
Share
10 |10000 characters needed characters left characters exceeded