FMS which brings up a list

[Former Member]

Hi!

I have a question concerning a QUERY a FMS and a UDF.

I created a UDF in the AR Marketing Documents, it has a FMS which goes and looks at the Business Partner and should present the user the selection of the Contacts only for that BP.

My QUERY only returns the first Contact in the list,

SELECT T0.[Name] FROM OCPR T0 WHERE T0.[CardCode] =$[$4.0.0]

What do I have to change so that the user can pick from a list in the UDF?

thx Richard

[Former Member · Accepted solution]

Your UDF should be setup under Market Documents Title part. The field property should follow Suda's instruction.

When your mouse is on that UDF under any market documents view user fields, using ShiftAltF2 to bring up FMS defintion. Select Search by saved query. Double clicks on the blank row to bring up the query manager to choice your query.

It should work, I have tested it. Before assigning query to your UDF FMS, you can check the query result when you bring up the marketing document screen. Do not save the query at this testing time.

Thanks,

Gordon

[Former Member]

Thanks for the post Suda.

I have tried that and still get the same results with the different types of queries that haven been proposed to me (either nothing gets displayed or I get an eror messgae).

I must be a bit dumb, but can some one spell out exactly what the UDF should be set up as, the syntax of the Query an how the FMS should be configured.

thank you!!

Richard

[Former Member]

Hi guys,

For all of the proposed solutions I am getting either no response or a system error.

Can it be that I have to match the type of UDF so that I can have a list of the Business Partners Contacts to appear?

I could really use a hand on this and I think it would be of great use to many of the implementations out there where we can get a specific list in repect to some other criterea to be displayed.

Thanks,

Richard,

[former_member583013]

Richard,

The Name from the OCPR is a varchar data type and your field should be

1. Alphanumeric

2. Big enough to hold the complete name (32 chars)

Suda

[former_member186095]

Hi,

This query will help :

SELECT T0.[Name], t0.cardcode 
FROM OCPR T0

but it will display all customers and BPs with their contact persons.

I am not sure if query FMS can solve. Try to use store procedure

Rgds,

[Former Member]

Hi Richard,

You need to use the full table name to make it work. Try:

SELECT T0.Name FROM [DBO].[OCPR] T0 WHERE T0.CardCode = $[$4.0.0]

Thanks,

Gordon

[Former Member]

I have set it not to refresh so that we should be able to get the list of Contacts, but the search does not work, always comes up empty.

Any other ideas?

Richard

[former_member583013]

Your query seems correct. When you have Auto Refresh, then the result will always fetch the first row into the UDF. Does the user press Shift+F2 manually or what is the case.

Please try to Press Shift+F2 and test the results

Let me know