How to filter in nGetResultSets or GetResultSets

I am working on getting data from sage100 2016. I need to know how can I apply filter to data that I am getting using nGetResultSets.
The documentation Sage100-2018 has shown what the function needs:
FUNCTION GetResultSets(descColumn$, keyColumn$, dropBoxLoad$, dropBoxTranslate$, filter$, begin$, end$) 

Problem is that I could not find exactly how the filter is applies. There seems to be no documentation for it.

I need to know how to apply filters such as ==, !=, >, <, >=, <=, not null, is null ect. for string values as well as numerical. 

  • 0

    Often it is easier to PULL data from Sage 100 using ODBC.  You can easily pull data into SQL or Access.  Can you explain the project a bit more.  Are you pulling into a web site or portal?

  • 0

      

    I am just told to prepare sample code that demonstrates how to pull data from sage100. I am using nGetResultSets function.

    object[] getResultSetParams = new object[] { $"{columns}", $"{keyColumn}", "", "", "CustomerName$=\"Joy Tony\"", "", "" };

    ar_cust_svc.GetType().InvokeMember("nGetResultSets", System.Reflection.BindingFlags.InvokeMethod, null, ar_cust_svc, getResultSetParams, pMods1, null, null);

    1. How to have multiple column filter? (e.g. col1='someVal' and col2!=61)]

    2. How to use 'and' and 'or' operators and multiple of them? (e.g. col1='someval' and col2=65 or col3=684)

    3. How to filter 'not null' or 'is null'?

    4. How to use operators like 'LIKE'? (e.g. col1 LIKE '%value%') 

    I tried something like 'POS(\"VALUE\"=col1$)>0' that someone suggested in some other post but did not work

    I believe this four questions will suffice my needs. Please answer me this four questions with an example.

  • 0 in reply to Dvs

    As BigLouie said, use ODBC for reading Sage 100 data from outside of Sage 100.  Working with business objects is a lot more restricted and difficult in many ways.  ODBC allows SQL like queries.

    Syntax for dates is a bit odd, and the types of table joins you can use is limited, but otherwise this is much easier than learning how to work with Sage business objects.

  • 0 in reply to Kevin M

    Yes,  , it is true that using ODBC would be much easier but my superior told me he does not want to use it for some reasons. I did not pry into details as my job is to simple provide sample code.

    So far I have figured out first 3 questions. The only question is how to do what operator LIKE can do.

    Please help me with this one question.