Selection of Multiple values in UDF with FMS

[manoj_jagadale]

Greetings all..!!

Hope you guys doing well..

This will be my first blog, but certainly not the last. Inspired by the likes of Linus Torvalds and Steve Jobs, I've made the decision to contribute to the community by sharing the knowledge and experiences I've gained over the past few years. I've spent over 5 years working with an SAP Partner, and during this time, I've encountered some unique yet genuine requirements from customers. Leveraging the knowledge I've acquired, I've made every effort to address these requirements to the best of my ability and come as close as possible to meeting their needs.

Where it Started:

About 3 years ago, one of my colleagues encountered a customer requirement. The customer wanted to populate batches at the Sales Order level for informational purposes, without the need for reservations. In response, we provided a solution using FMS (Formula Management System) with a UDF (User-Defined Field) called "U_batch" at the Sales Order row level. However, the challenge arose when they requested that all batches of the item be displayed in the FMS, complete with their quantities, and the ability to select multiple batches, including alternative ones. This is where the need for a Multi-Selection FMS over the UDF came into play. After searching the entire internet and concluding that it wasn't possible, I decided to tackle the problem myself.

And here it landed :

To achieve this, I came up with the idea of concatenating the results. So, I created a UDF called "U_batch" at the row level of the Sales Order.

Now, let's delve into the FMS aspect. Ordinarily, FMS returns a single value in a field, a fact we are well aware of. But what if we click on the same FMS (the magnifying glass icon) and select another value? It replaces the existing value in the field. To meet our objective, we had to concatenate the existing result set with the newly selected value, and that proved to be the key. I came across information stating that the value for FMS selection originates from the first column itself. Therefore, the first column of the query had to be present, albeit hidden manually.

So, Lets get started with the steps to the achieve our requirements,

• Create the UDF for batch.

 

• Align the UDF on required location over document.

 

• Create the FMS
  
  
  
  • I have created the FMS as "ORDR_Batch_MJ_V-2.0".
  
  
  
  

 

• Assign the FMS on Document, in this case its SO.

 

• Now, as seen in trailing image, the highlighted column on the left is the one which consist the value of FMS, which we can not hide with the form settings.

 

• But this will also show all the concatenated values of our result (like below image). Which basically we don't want to.

 

• So, to overcome this, We have to select column edge and drag it to the very end of the left side.

 

• In this way the column will be present on the left but hidden from the view. This will result in showing two lines on the left side, but wont interfere with the values and can go unnoticed.

 

• Here's the FMS code in SQL as well as HANA.

-- Script for SQL---

/*-----------------------------------------------------

Author : MJ

Created On : 21/06/2019 @12:45 PM

Task Name : Fetch multiple values in sigle UDF with FMS.

Latest Version : 2.0

Last Revision : 26/02/2020 @06:30 PM 

-----------------------------------------------------*/

declare @str nvarchar(200)

declare @itm nvarchar(20)

declare @btnm nvarchar(max)

declare @sql nvarchar(max)



set @itm=$[$38.1.0]

set @str= $[$38.U_batch.0]



set @btnm = convert(nvarchar(max),(select concat('''',replace(@str,',',''','''),'''')))



set @sql = 'select (case when '''+@str+'''='''' then B.BatchNum else 

concat('''+@str+''','','',B.BatchNum) end ), B.BatchNum,B.ExpDate,B.Quantity from OIBT B where B.ItemCode='''+@itm+'''

and B.BatchNum not in ('+@btnm+') and B.Quantity > 0.0'



exec(@sql)

-- Script for HANA---

/*-----------------------------------------------------

Author : MJ

Created On : 22/06/2019 @03:33 PM

Task Name : Fetch multiple values in sigle UDF with FMS.

Latest Version : 2.0

Last Revision : 28/02/2020 @11:35 AM 

-----------------------------------------------------*/

declare str nvarchar(200);

declare itm nvarchar(200);

declare btnm nvarchar(200);

declare sql1 nvarchar(7000);



itm := $[$38.1.0];

str := $[$38.U_BatchNo.0];



select( CAST((SELECT (''''|| replace(:str, ',',''',''')|| '''') FROM DUMMY) AS nvarchar(5000))) into btnm from dummy;



sql1 := 'select (case when ''' || :str || '''='''' then B."BatchNum" else 

 (''' || :str || ''' ' || '||' || ''',''' || '||' || ' B."BatchNum" ) end ) as "Selection", B."BatchNum",B."ExpDate",B."Quantity" from OIBT B where B."ItemCode"=''' || :itm || '''

and B."BatchNum" not in (' || :btnm || ')';



EXECUTE IMMEDIATE(:sql1);

 

• And voilà, here's the final result.

 

High's and Low's :

• This may save a extra UDF creation on the forms where just multiple values needs to be stored.


• The stored values in the UDF with comma separation, can be fetched in queries and utilized further in any operation like normal value. (Values can be processed with dynamic query or using string_split() function in query)

So, as nothing is perfect, this workaround does have the limitations as well. Which is,

• You can not select multiple values at the same time, you have to chose one by one.


• If values are too much like more than 10 or so, then using this workaround will be totally depended upon user conveniency as it will be manual recursive work for user.

 

.. And here, the story concludes with the proposed solution. This solution can be applied to various scenarios involving other objects as well.

I hope, this blog may help someone out there, who is looking after the solution like this.

If you need any help with similar things or have suggestions for the blog post, feel free to leave a comment below.

Thanks for your time... see you soon..

Cheerio..!!