Skip to Content
avatar image
Former Member

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

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Apr 09 at 10:49 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 09 at 01:42 PM

    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
    
    Add comment
    10|10000 characters needed characters exceeded

  • Apr 10 at 06:56 AM

    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

    Add comment
    10|10000 characters needed characters exceeded