Prev
Next
Forums Performance Management Lawson Business Intelligence/Reporting/Crystal

DME Syntax

 2 Replies
 0 Subscribed to this topic
 22 Subscribed to this forum
Sort:
Author
Messages
Petter
Basic Member
Posts: 7
Basic Member
8/12/2008 1:25 PM

    Hello,

    I am a Lawson query user. When I set values for indexes, I cannot "skip" a line in the index set. For example, I want to list all open invoices so I go to AROITEMS and use AROSET13 with my company code, process level and for a specific transaction type (invoices). However, I want to do it for all customers, I don't want to choose a customer number. If I leave customer blank, it will not filter for process level and transaction type. So I need something like a " * " so I get all customers.

    I get the data via web query to excel and use the DME syntax.

     

    Peter

     

     

    John Henley
    Posts: 3353
    8/12/2008 1:54 PM
      As you've discovered, you can't skip index elements with DME calls to simulate a wildcard. You will need to use another index which doesn't have customer before the trans-type, such as AROSET9, and then use &SELECT (e.g. &SELECT=PROCESS_LEVEL=xxx) to filter based on the process level. Not as fast as an index, but should achieve your desired result.
      Thanks for using the LawsonGuru.com forums!
      John
      jeremy.zerr
      Advanced Member
      Posts: 23
      Advanced Member
      10/23/2008 4:58 PM
        Yes, I agree, there probably isn't a wildcard character.  However, you do know that ranges, work.  And really, the function of a wildcard is really just an infinitely big range.  We can emulate that within practical limits.

        So why don't you just pick a sufficiently large range for one of the inner indices.

        For example:
        I want to pull all employees in our HR company that are in any process level, but a specific department.

        From the EMPLOYEE table, I see EMPSET2 is:

        COMPANY
        PROCESS-LEVEL
        DEPARTMENT
        EMPLOYEE

        So I'm in the same situation you are.  I need a wildcard for PROCESS-LEVEL, but a specific for DEPARTMENT.

        Well, to do a range in the Query Wizard, you use the little arrow thing with a dash and a greater than sign, like this:

        ->

        So if I know that all my PROCESS-LEVELs are between 00 and 99, I can just set up the indices like this:


        COMPANY                      1
        PROCESS-LEVEL        00->99
        DEPARTMENT               1001
        EMPLOYEE

        The 00->99 effectively acts as a wildcard, because you know with your system, that there won't be any higher or lower.  I think you can see how to apply this to pretty much any situation.

        If the field is a character field, it gets a little trickier, but you can figure it out.  Its hard to figure out what the values of characters are.  It actually depends on the database behind the scenes.  However, a little testing can get you there.

        Usually, numbers are a lower value than alpha characters.  And sometimes, special characters like _, or even *, or just a blank space, have certain values.  See an example of an ASCII table.  But, this ASCII table doesn't represent the specific ASCII table of values that you particular database is using.  I actually had a ticket with Lawson on this issue that opened my eyes up to this.

        So anyways, sorry to be rambling on.  But if you had an alpha field that you wanted to emulate a wildcard using a range, you could do this:

        000000->ZZZZZZ

        I think you get the picture.

        Jeremy Zerr
        http://www.zerrtech.com