Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / SmartList Designer - C...
Microsoft Dynamics GP (Archived)

SmartList Designer - Case Query

(0) ShareShare
ReportReport
Posted on by Heather Roggeveen 9,144
Hi Loving the look of the new SmartList Designer. I thought it might be a nice answer to the age old issue in SmartList of credit documents showing as positives. It is something most of my clients find very frustrating if they want to dump a list of documents. I am not a SQL script minded person - I can read them, but not so great with the writing. I much prefer the "IF, THEN, ELSE" in Excel. I am struggling with the correct form and I don't know whether it is just me, but it seems like the "THEN" option is missing (or don't we need it). Anyway, basically I want: If Document Type >= 7 then Document Amount * -1 else Document Amount If someone is able to tell me how this should look in SmartList Designer, I would greatly appreciate it. Cheers Heather

*This post is locked for comments

  • Dawn Prigmore Profile Picture
    Dawn Prigmore on at
    RE: SmartList Designer - Case Query

    Lyn I came across your post and this was great to know!  Can't believe I never tried just typing the expressions - SO much easier. Thanks!

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SmartList Designer - Case Query

    Hi

    I'm trying to do the same as Heather. Show returns, credit notes and payments as negative in my Smartlist for Sales - Receivables Transactions. I have entered this expression 

    CASE WHEN {RM30101.RMDTYPAL} > = 7 THEN ({RM30101.ORTRXAMT}*-1) ELSE {RM30101.ORTRXAMT} END

    But I get this error

    The calculated field is not valid. 

    Can anyone please help?

  • Lyn Barr Profile Picture
    Lyn Barr on at
    RE: SmartList Designer - Case Query

    FYI, I just had a support case with Microsoft, wherein I complained about having to go back & forth to insert fields into my expression, one at a time.

    In testing, we discovered that you can actually type the entire expression into the Constants tab, as a text field, and it will still validate & return the correct results!

    There is an 80-character limit to text fields added as constants, so if your expression happens to be more than 80 characters, just add two or more text fields.

  • Naga Kiran Profile Picture
    Naga Kiran on at
    RE: SmartList Designer - Case Query

    Smartlist Designer is a new feature and its always nice to have questions on a new feature like this which will help other GP users and I don't think they are silly. In fact you have raised a concern on usability.

    Glad to help you Heather :)

  • Heather Roggeveen Profile Picture
    Heather Roggeveen 9,144 on at
    RE: SmartList Designer - Case Query

    Thanks very much Naga - I have to say - I feel like that person who asks the silly questions right now!  It never occurred to me to just type in the missing options!

    Now we just need a simple configuration option - or the standard in all the queries to show Credits as negatives so we don't have to create all the columns ourselves!

  • Naga Kiran Profile Picture
    Naga Kiran on at
    RE: SmartList Designer - Case Query

    Also the ones in the flower brackets are inserted by double clicking the desired field under 'Table Fields' tab. Don't use Text for that.

  • Verified answer
    Naga Kiran Profile Picture
    Naga Kiran on at
    RE: SmartList Designer - Case Query

    Hi Heather,

    You just need to follow the syntax of Case statement in SQL. Below is something I created which worked.

     

    Note :

    To insert a Space, Relation operators( >=) , Keywords (THEN) you need to use Constants tab, select 'Text' as type enter the value you desire (even a space value) in the Value field to build the expression. Make sure each key word has a space before and after.

    Hope this helps :)

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,321 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans