Fuzzy search to UDF

[former_member724095]

Hi,

How to apply fuzzy search logic to UDF(Created in sales order at row level). I don't have any idea. Can anyone guide me?

Thanks in Advance

[edy_simon · Accepted solution]

Hi,
First of all, UserFields is meta data. You only need to create it once per company db.
Not everytime the form opens.
Secondly, to create a userfields, please check this example if you have installed SDK on your PC.
C:\Program Files (x86)\SAP\SAP Business One SDK\Samples\COM DI\CSharp\02.MetaDataOperations

And to answer your question:
The error is saying you cannot convert 'oOrders' object into a 'UserFieldsMd' object.
Quoted from the sample file:

oUserFieldsMD = (SAPbobsCOM.UserFieldsMD) globals_Renamed.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oUserFields);

[former_member724095 · Accepted solution]

Hi,

Thanks for the reply. Now I'm able to see userfield in RDR1 .

[edy_simon]

Hi,
If you are referring to fuzzy search in queries here are examples

--MSSQL
SELECT DIFFERENCE('StringToSearch', T1."U_TEST") Score, T0.DocEntry, T0.DocNum, T1.LineNum, T1.U_TEST
FROM ORDR T0 JOIN RDR1 T1 ON T0.DocEntry = t1.DocEntry
WHERE DIFFERENCE('StringToSearch', T0."U_TEST") >0
ORDER BY Score DESC 


--HANA
SELECT TO_DECIMAL(SCORE(),3,3) AS score, T0."DocEntry", T0."DocNum", T1."LineNum", T1."U_TEST" 
FROM ORDR T0 JOIN RDR1 T1 ON T0."DocEntry" = T1."DocEntry"
WHERE CONTAINS(T1."U_TEST", 'StringToSearch', FUZZY(0.0))
ORDER BY score DESC

Regards

Edy

[PierreBrothier]

Hi Edy,

thanks for the MS SQL trick doesn't know about it

[edy_simon]

Hi Pierre,
We all are still learning.
It's a whole vast sea out there. Impossible to take in everything in one life time.

Unless we can download them directly to our brain, like in the film 'The Matrix'.

[PierreBrothier]

Hi,

in HANA version there's a CONTAINS predicate that can work with fuzzy search

More information in the folloing link :

https://help.sap.com/viewer/691cb949c1034198800afde3e5be6570/2.0.04/en-US/9a4da8f6f23b4ebf95c98f1104...

I don't know is there something equivalent in SQL Server

[Johan_H]

Hi Pierre,

That assumes sql though, right? It could conceivably be used in a combination of a UDF and formatted search then.

Regards,

Johan

[Johan_H]

Come to think of it, I suppose

CONTAINS('my search term')

really just does this:

*my search term*

which would be the same as the LIKE operator:

LIKE '%my search term%'

or how does CONTAINS() work?

[PierreBrothier]

Hi,

ou can use that way :

the FUZZY parameter defines the fuzzy score. It's great to search duplicates

-- exact search
SELECT ... WHERE CONTAINS(col1, 'term1 term2 term3') ...;


-- fuzzy search
SELECT ... WHERE CONTAINS(col1, 'term1 term2 term3', FUZZY(0.7)) ...;

[Johan_H]

Okay, and that would then search term1, term2 and term3 in any order?

So results would include:

- term1
- term2
- term3
- term1 term2
- term1 term2 term3
- term3 term1
- term2 term1
etc.

[Johan_H]

Hi,

It depends on what you mean with "fuzzy search".

In a UDF, you can search with key words and stars as jokers, just like any other field:

*my*search*term*

To my knowledge, this will return results like this:

- this is my search term
- is this my search term
- my searching term this is
- this is my wonderful search term
- my search is good in terms of

but not like this:

- this is my term
- is this search term
- my search this is
- this is my term to wonderfully search for
- term search my
- term
- search
- my

Regards,

Johan

[former_member724095]

Hi Experts,

Thanks for your valuable replies. I'm using SQL not HANA & will try above solution. but before that I have created field by using customization tool and trying to read/get it. it is showing invalid uid. & now when I'm trying to create UDF at row level in sales order using C#, it is giving following error.

{"Unable to cast COM object of type 'System.__ComObject' to interface type 'SAPbobsCOM.UserFieldsMD'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{81D132DE-0549-460D-9BEC-2FEDEAB0A950}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE))."} System.InvalidCastException

My Code:

public static SAPbouiCOM.Form oForm;

public static SAPbouiCOM.Item oItem;

public static SAPbobsCOM.UserFieldsMD oUserfield;

****************************************************************************

private static void SBO_Application_ItemEvent(string FormUID, ref SAPbouiCOM.ItemEvent pVal, out bool BubbleEvent) {

BubbleEvent = true;

if ((pVal.FormType == 139 && pVal.EventType != SAPbouiCOM.BoEventTypes.et_FORM_UNLOAD) && pVal.Before_Action == true)

{

oForm = Application.SBO_Application.Forms.GetFormByTypeAndCount(pVal.FormType, pVal.FormTypeCount);

if (pVal.EventType == SAPbouiCOM.BoEventTypes.et_FORM_LOAD && pVal.Before_Action == true) { AddDataSourceToForm();

AddNewLine();

} } }

public static void AddNewLine() {

try {

oApp = (SAPbouiCOM.Application)Application.SBO_Application;

oForm = (SAPbouiCOM.Form)oApp.Forms.ActiveForm;

oCompany = (SAPbobsCOM.Company)oApp.Company.GetDICompany();

oUserfield = (SAPbobsCOM.UserFieldsMD)oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oOrders); //ERROR

oUserfield.TableName = "RDR1";

oUserfield.Name = "BE_UserField1";

oUserfield.Description = "UserField";

oUserfield.Type = SAPbobsCOM.BoFieldTypes.db_Alpha;

oUserfield.EditSize = 20;

oUserfield.Add();

System.Runtime.InteropServices.Marshal.ReleaseComObject(oUserfield);

GC.Collect();

}

catch (Exception e1) {

}

}

public static void AddDataSourceToForm() {

Odbdatasource = oForm.DataSources.DBDataSources.Add("RDR1"); }

Could you please tell me exact syntax of creating UDF at row level in sales order or what's wrong with my code?

Thanks & Regards

[PierreBrothier]

Hi,

this line doesn't return a UserFieldsMD, but a document object. So the cast error is normal.

(SAPbobsCOM.UserFieldsMD)oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oOrders);

User field should better be created at addon startup.

creation sample taken from the documentation :

Private Sub AddUserField()

'//****************************************************************************
'// The UserFieldsMD represents a meta-data object that allows 
you
'// to add\remove fields from tables or change 
the fields' characteristics
'//****************************************************************************

    Dim oUserFieldsMD As 
SAPbobsCOM.UserFieldsMD

'//****************************************************************************
'// In any meta-data operation there should be no other object 
"alive"
'// but the meta-data object, otherwise 
the operation will fail.
'// This restriction is 
intended to prevent a collisions.
'//****************************************************************************

    '// The meta-data object needs to be initialized with 
a
    '// regular UserFields 
object
    Set oUserFieldsMD = 
oCompany.GetBusinessObject(oUserFields)

    '//**************************************************
    '// When adding user tables or fields to the SAP Business One 
database
    '// use a prefix identifying your 
partner name space
    '// this will prevent 
collisions between the various partners add-ons
    '//
    '// SAP's name space prefix 
is "BE_"
    '//**************************************************

    '// Set the Fields' mandatory 
properties

    oUserFieldsMD.TableName = "OCRD" '// BP 
table
    oUserFieldsMD.Name = "BE_UserField1"
    oUserFieldsMD.Description = "A user field"
    oUserFieldsMD.Type = db_Alpha '// am alphanumeric 
type
    oUserFieldsMD.EditSize = 20

    '// Add the field to the 
table
    oUserFieldsMD.Add

End 
Sub