web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Get Highest Unit Selli...
Microsoft Dynamics AX (Archived)

Get Highest Unit Selling Price

(0) ShareShare
ReportReport
Posted on by Mohammed Altaf 435

Hi there,

            I am creating a Job Which gives me the Highest Selling Price of a Item and SO Number from CustInvoiceTrans table.

I wrote the Query some thing like this

CustInvoiceTrans   _cit;

select maxOf(LineAmount) from _cit where _cit.ItemId == 'XXXXXXX'

But if any Sales Order is having a quantity more than 1 then its not giving me the exact value.

i need a query to get the maxOf(LineAmount) divided by its quantity and SO Number.

Can you please guide me how to get the above details.

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Rohin Profile Picture
    Rohin 4,624 on at

    I would go for creating a View  and add computed column acc. to your requirement.

  • Mohammed Altaf Profile Picture
    Mohammed Altaf 435 on at

    Hi Visvash Walia

    Thanks for your Valuable reply.

    I created on Job which gives me Amount Divided by Quantity i.e.

       CustInvoiceTrans  _cit;

       real              _lineAmount,_maxAmount;

       Qty               _SQty;

       SalesId           _salesID;

       while select * from _cit where _cit.ItemId == 'LI-0003697'

       {

           _salesID       =    _cit.OrigSalesId;

           _lineAmount    =    _cit.LineAmount;

           _SQty          =    _cit.Qty;

           _maxAmount     =    _lineAmount / _SQty;

        info(strFmt("Line Amount :- %1,Sales Qty :- %2,Max Amount :- %3,Sales ID :- %4",_lineAmount,_SQty,_maxAmount,_salesID));  

       }

    Here i need Highest or Maximum Value from "_maxAmount".

    Can you guide me how to get the Highest Value.

  • Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at

    Hi,

    There are many ways to achieve this, but I modified your job to get the maxAmount/highest value amount for a given item. Please check and let me know if anything is missing.

    static void MaxAmount(Args _args)
{
    CustInvoiceTrans  _cit;
    real              _lineAmount,_maxAmount;
    Qty               _SQty;
    SalesId           _salesID;
    real              maxValue = 0;
    
    while select * from _cit where _cit.ItemId == "XYZ"
    {
       _lineAmount    =    _cit.LineAmount;
       _SQty          =    _cit.Qty;
       _maxAmount     =    _lineAmount / _SQty;
        
        if (maxValue <= _maxAmount)
        {
            maxValue = _maxAmount;
            _salesID  = _cit.OrigSalesId;
            _SQty    = _cit.Qty;
            _maxAmount = _lineAmount / _SQty;
        }        
    }
    
    info(strFmt("Line Amount :- %1,Sales Qty :- %2,Max Amount :- %3,Sales ID :- %4",_lineAmount,_SQty,_maxAmount,_salesID));  
}
  • Rohin Profile Picture
    Rohin 4,624 on at 
    static void Job22(Args _args)
{
  CustInvoiceTrans    buffer;
    real        maxamount,test =0;

    ;
    
    while select buffer where buffer.ItemId == "xxx"
    {
        
        maxamount = buffer.LineAmount/buffer.Qty;
        if(test <= maxamount)
        {
            test = maxamount;
           
        }
       
    }
    
       
       
        info(strFmt("%1",test));
    
  
    
}

    please try this

  • Mohammed Altaf Profile Picture
    Mohammed Altaf 435 on at

    Thanks @Chaitanya Golla

    Thanks @Visvash Walia

    Thanks for your help, it solved my problem.

    @Visvash Walia as you said have created a View and added a new Computed Column to it i.e;

    FinalLineAmount  =  Line amount from CustInvoiceTrans Table + CalculatedAmount from

                                       MarkUpTransTable.

    Can you please gudie me how to divide the new Created Computed Column by Qty Field?

  • Rohin Profile Picture
    Rohin 4,624 on at

    you can add computed Column to your view and write static method to view and then link the computed column to method.

    please go through this for more information:

    msdn.microsoft.com/.../gg845841.aspx

  • Mohammed Altaf Profile Picture
    Mohammed Altaf 435 on at

    Thanks @Vishvash

    I have Created a View and a Static Method i.e.

    0827.View.jpg

    public static server str getFinalLineAmount()

    {

       return SysComputedColumn::add(

           SysComputedColumn::returnField(

               tableStr(om_CampaignDiscount_MarkupTrans),

               identifierStr(custinvoicetrans_1),

               fieldStr(CustInvoiceTrans, LineAmount)),

           SysComputedColumn::returnField(

               tableStr(om_CampaignDiscount_MarkupTrans),

               identifierStr(MarkupTrans_1),

               fieldStr(MarkupTrans, CalculatedAmount)));

    }

    I have added a New Computed Column and  Linked with the Above Method.

    Here i need to Divide the Newly Created Computed Column with the Qty Field.

    so how can i use a Computed Column inside  Static Method of View?

  • Suggested answer
    Rohin Profile Picture
    Rohin 4,624 on at

    it would be the same, refer to your view computed field in method...

  • Mohammed Altaf Profile Picture
    Mohammed Altaf 435 on at

    I have modified my method but its not giving me exact value.

    method Code

    public static server str FinalUnitAmount()

    {

     str   _FinalUnitAmount,_finalLineAmount,_qty;

       // get lineamount + calculated amount

     _finalLineAmount = SysComputedColumn::add(

           SysComputedColumn::returnField(

               tableStr(om_CampaignDiscount_MarkupTrans),

               identifierStr(custinvoicetrans_1),

               fieldStr(CustInvoiceTrans, LineAmount)),

           SysComputedColumn::returnField(

               tableStr(om_CampaignDiscount_MarkupTrans),

               identifierStr(MarkupTrans_1),

               fieldStr(MarkupTrans, CalculatedAmount)));

       // get quantity

     _qty =  SysComputedColumn::returnField(

               tableStr(om_CampaignDiscount_MarkupTrans),

               identifierStr(custinvoicetrans_1),

               fieldStr(CustInvoiceTrans, Qty));

       _FinalUnitAmount  = _finalLineAmount +"/"+_qty;

       return _FinalUnitAmount;

    }

    am i missing something in my above method?

  • Verified answer
    Mohammed Altaf Profile Picture
    Mohammed Altaf 435 on at

    Thanks

    I have solved it by changing the above code.

    public static server str FinalUnitAmount()

    {

     str   _LineAmount,_FinalUnitAmount,_finalLineAmount,_qty;

     _LineAmount = SysComputedColumn::add(

           SysComputedColumn::returnField(

               tableStr(om_CampaignDiscount_MarkupTrans),

               identifierStr(custinvoicetrans_1),

               fieldStr(CustInvoiceTrans, LineAmount)),

           SysComputedColumn::returnField(

               tableStr(om_CampaignDiscount_MarkupTrans),

               identifierStr(MarkupTrans_1),

               fieldStr(MarkupTrans, CalculatedAmount)));

     _qty =  SysComputedColumn::returnField(

               tableStr(om_CampaignDiscount_MarkupTrans),

               identifierStr(custinvoicetrans_1),

               fieldStr(CustInvoiceTrans, Qty));

       return SysComputedColumn::divide(_LineAmount,_qty);

    }

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the March Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
CP04-islander Profile Picture

CP04-islander 16

#2
GiacomoRovai Profile Picture

GiacomoRovai 4

#3
Douglas Noel Profile Picture

Douglas Noel 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans