Skip to Content
0

UDF in SAP B1 with default value from FMS and possibility to select other values.

Apr 09 at 08:30 AM

46

avatar image

Dear all,

After several months reading so many helpful answers in this community I need to ask a question which I haven't been able to find an answer for.

We are a small company using SAP B1 9.2 SQL and my knowledge in SAP programming is pretty poor since I am not a programmer.

This is my scenario:

I need a UDF in OCRD table which is filled by users with a BP code selected from a list. This UDF also exists in marketing documents and its default value must be the same as UDF in OCRD table, but it may be changed if necessary and chosen from a list of valid values.

My proposal:

- I have created a UDF both in OCRD table and in marketing documents.

- UDF value in marketing documents is set by a FMS as follows:

  1. If UDF in OCRD table is null, a list of valid values is shown.
  2. If UDF in OCRD table is not null, UDF in marketing docs takes this value.

With these conditions,

if UDF in OCRD table is null, a list of valid values according to FMS is shown and any value can be selected (This is OK) but

if UDF in OCRD table is not null, I get its value in marketing documents, as desired, but when trying to change it, no valid values are listed, nothing happens. It's here where I need your help:

How can I modify the FMS for UDF in marketing docs to get this functionality?

That is the FMS I use:

IF 
(SELECT OCRD.[UDFName] FROM OCRD WHERE OCRD.CARDCODE=$[ORDR.CardCode]) IS NULL 

begin 
(SELECT OCRD.[CardCode], OCRD.CardName, OCRD.CardFName FROM OCRD)
end


ELSE


begin
(SELECT OCRD.[UDFName] from OCRD WHERE OCRD.CARDCODE=$[ORDR.CardCode])
end

I hope to be clear enough with my need.

Thank you for your help.

David.

**For info purposes: As you may notice, the values for UDF in this thread are BP codes. Apparently this makes no sense, since BP Channel already exists in SAP (OCRD.ChannelBP). The problem is that this field cannot be populated with Leads, and for our company a field where a lead is linked to any BP is necessary. However, BPChCode in document marketings (OINV.BPChCode) does allow for leads codes... SAP misteries.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Agustin Marcos Cividanes Apr 09 at 10:49 AM
0

Hi

the support note

1763739 - Select Lead type BP as a related partner in Sales Opportunities

explains your situation. The solution is not feasible in this versión.

To solve you can create all leads as customer and use a property to identify the leads. You can add your validation in SP Transaction Notification to check the BP selected has this property active.

Kind regards

Agustín.

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi Agustín,

Thanks for the info. So it's a know issue.

In any case, I don't think it is a good idea to create leads as customers and that's why I created UDF. Using my previous FMS I am able to select leads, so that's not really a problem now.

The point of my question concerns the way I have to modify my FMS so that I can select a value when condition 2 applies.

0

Hi

I think you are choosing a BP code for a lead; and when you add or update the SBO validation checks the BP code as Lead and it shows the message error.

Kind regards

Agustín

0
Danilo Kasparian Apr 09 at 01:42 PM
0

As a suggestion you could check if the UDF in your marketing document is already filled.

IF 
(SELECT OCRD.[UDFName] FROM OCRD WHERE OCRD.CARDCODE=$[ORDR.CardCode]) IS NULL OR $[$UDFITEM.0.0] <> ''


begin 
(SELECT OCRD.[CardCode], OCRD.CardName, OCRD.CardFName FROM OCRD)
end




ELSE




begin
(SELECT OCRD.[UDFName] from OCRD WHERE OCRD.CARDCODE=$[ORDR.CardCode])
end
Show 4 Share
10 |10000 characters needed characters left characters exceeded

Hi Danilo,

Thanks for your suggestion. I have tried it but it does not seem to make any difference in the results.Once the UDF in marketing docs takes the value from UDF in OCRD, clicking in the FMS button again does not show any opo-up window with values.

0

I tried here with no problem, the only thing I had to do is to put the focus in another field

0

Hi Danilo,

I think I misunderstood something. I've checked again and I may have done something wrong because now it does work !!!

The UDF field in marketing docs populates with the value in OCRD table if it exists and now, I can change it by selecting from pop up window. That's exactly what I wanted.

Sorry for my first misunderstanding and thanks a lot for your solution!

0

Great! could you please close the thread then?

0
Johan Hakkesteegt Apr 10 at 06:56 AM
0

Hi David,

You should link both UDFs to the OCRD table. In that case your FMS query does not need the ELSE clause. Because the fields are linked, you will get a drop down menu, instead of a pop-up window. Your query will work the same, but it will always show all BPs.

I am not sure if linking a UDF to a system table already works in 9.2, but it should in 9.3.

Alternatively you can create a User Defined Table, and fill in all the necessary BPs. This table you can then link to the UDF. Because it is a User Defined Table, you can even create a job in MS SQL Server Management Studio, to populate this table automatically (Please note, only User Defined Tables and/or fields. Using direct sql on any system tables or fields will void your support!). Please let me know if you need help with this.

Regards,

Johan

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi Johan,

That was my first choice but unfortunately by linking UDFs to OCRD table (it is really possible in 9.2 version) there is no possibility to select leads (see note in Agustin's previous message).

It is a good idea the User Defined Table you propose, even though it means duplicating a big amount of data. I will keep it in mind but will wait for any solution to my specific need, which in any case could be applied in other FMS in UDFs.

Thanks!

0

Hi,

The SAP note refers to versions 9.2 and older. An upgrade from 9.2 to 9.3 should be reasonably straightforward.

Regards,

Johan

0