cancel
Showing results for 
Search instead for 
Did you mean: 

FMS Query that Can create dependency between fields in the system.

Former Member
0 Kudos

Hello Everybody,

I want help in creating the FMS Query that can create dependency between fields in the system,  for example, the value in field X influences the value in field Y. Both the fields are in same Table of database. I did some FMS but below specific case is making bit problem for me.

Basically the both fields i m working on are UDFs and both are Drop down lists, One having around 6 values and other one has 10 values , and on selecting value1 from field1 it will have to select value1 in field2.

This case is making problem, Can I get solution of it??

Can I get an example query on this??

Thanks in Advance

Accepted Solutions (0)

Answers (1)

Answers (1)

Johan_H
Active Contributor
0 Kudos

Hi Mohsin,

You will need to link your drop down UDFs to user tables. One table will be primary, and the other secondary.

In the secondary user table you will have to create a UDF and link this field to your primary table. This will create the dependency.

Then you can add a simple FMS with query to your second UDF:

SELECT T0.[Code], T0.[Name] /* instead of name you can also choose some other field */

FROM [dbo].[@SECONDARY_TABLE]  T0

WHERE T0.[U_ParentCode] =$[XXXX.U_Primary_UDF]

Regards,

Johan

Former Member
0 Kudos

Both UDFs are in table name OOPR. I have created UDT as well for both the UDFs, But why we have to do that. Can't we do it on main table (OOPR) check out the below query Its only picking one value can you generalize it??

SELECT CASE T0.[U_CRF09]

WHEN '01' THEN '100'

WHEN '02' THEN '113'

WHEN '03' THEN '120'

WHEN '04' THEN '128'

WHEN '05' THEN '150'

WHEN '06' THEN '260'

WHEN '07' THEN '300'

END

FROM OOPR T0 WHERE T0.[U_CRF09]='02'

In where clause I gave a condition but its only picking up that condition but I want it to pick all values, Can you generalize this condition?? as its form no=-320,item=U_CRF09

Johan_H
Active Contributor
0 Kudos

Hi Mohsin,

Your solution would work, but it is static. The user tables are there to make sure that you do not have to adapt that FMS query every time a value needs to be added or changed. Not next week, and also not two years from now.

On the other hand, if you are 100% sure that the values in your first UDF (U_CRF09 ?)  will never ever change, and that nobody will ever want to add any new values, and that the relationships between different values will never ever change, then it is not necessary to use user tables for either UDF, and your solution is the better one.

Then the query simply becomes:

SELECT CASE $[OOPR.U_CRF09]

WHEN '01' THEN '100'

WHEN '02' THEN '113'

WHEN '03' THEN '120'

WHEN '04' THEN '128'

WHEN '05' THEN '150'

WHEN '06' THEN '260'

WHEN '07' THEN '300'

END

FROM OOPR T0

Regards,

Johan

Former Member
0 Kudos

Its giving me an error... that "Incorrect Syntax near OOPR.U_CRF09, the blanket agreement OOAT could not be prepared"

Johan_H
Active Contributor
0 Kudos

Did you just run the query in the Query Generator or did you test it in the FMS ?

Former Member
0 Kudos

I ran it in Query Generator..

Johan_H
Active Contributor
0 Kudos

Hi Mohsin,

Ok, queries that have dynamic variables like $[OOPR.U_CRF09] will always throw an error in the query generator.

You will need to attach it to the FMS and test it that way.

Regards,

Johan

Former Member
0 Kudos

Hello Johan,

   Chk this query it is giving me an error "The multi part identifier could not be bound".Below is the query.

IF (OOPR.U_CRF09='01')

SELECT T1.CODE,T1.NAME

FROM NEW1 T1

ELSE IF (OOPR.U_CRF09='02')

SELECT T1.CODE,T1.NAME

FROM NEW2 T1

Please help me out, I am stucked.

I want that if user select from drop down list (U_CRF09=01) then some UDT (NEW1) is selected else other UDT (NEW2).

Please help me out..

Thanks in advance

KennedyT21
Active Contributor
0 Kudos

Hi Mohsin Idrees

Try This

IF (OOPR.U_CRF09='01')

SELECT T1.CODE,T1.NAME

FROM NEW1 T1

ELSE IF (OOPR.U_CRF09='02')

SELECT T2.CODE,T2.NAME

FROM NEW2 T2

Hope helpful

Regards

Kennedy