Create computed column in view or data entity with Sql statement

 



  public static server str getHasBreakupDetails()

    {

        str expression;

        

        expression = '(select count(bl.RecId) from EMSChargebackBreakupLines bl join EMSChargebackBreakupHeader bh on bl.BreakupHeaderRecId = bh.RecId and bh.ChargebackRecId = ' +

            SysComputedColumn::returnField(tableStr(SAKChargebacksInquiryView),identifierStr(SAKChargebacks),fieldStr(SAKChargebacks,RecId)) + ')';

// If condition & literal with numbers 

        return SysComputedColumn::if(

            SysComputedColumn::equalExpression(

            expression,

            SysComputedColumn::comparisonLiteral(0)), SysComputedColumn::returnLiteral(0), SysComputedColumn::returnLiteral(1));


    }


Other examples


public static server str getCBNum(TableName _viewName,

        str _DataSourceParty,

        str _DataSourcePartyField)

    {

        str expression;

        expression = 'select displayvalue from dimensionattributevaluecombination where recid = ' +

            SysComputedColumn::returnField(_viewName,

            _DataSourceParty,

            _DataSourcePartyField);


        return expression;

    }



with real computed column

    public static str  getPendingAllocationQty()

    {

        #SRSModel


        str contractLineRecIdValue =

        SysComputedColumn::returnField(tableStr(NXRExistingContractLineView),identifierStr(EDF_ContractLine),fieldStr(EDF_ContractLine,RecId));


        str contractQty = SysComputedColumn::returnField(tableStr(NXRExistingContractLineView),identifierStr(EDF_ContractLine),fieldStr(EDF_ContractLine,ContractQty));


        str expression = '(select sum(ContractQty) from NXRMASTERWEIGHINGPURCHALLOCATIONTABLE where  NXRMASTERWEIGHINGPURCHALLOCATIONTABLE.ProcessStatus <> 3  and '+

     'exists( select * from NXRMasterWeighingTable  where NXRMasterWeighingTable.BFSIndependentWeighID = NXRMASTERWEIGHINGPURCHALLOCATIONTABLE.BFSIndependentWeighID '+

     'and  NXRMasterWeighingTable.Status <> 7) and NXRMASTERWEIGHINGPURCHALLOCATIONTABLE.EDF_ContractLine = ' +


            contractLineRecIdValue + ')';


        expression = SysComputedColumn::if(SysComputedColumn::isNullExpression(expression),SysComputedColumn::returnLiteral(0.00),expression);


        return SysComputedColumn::cast( SysComputedColumn::subtract(contractQty, expression), #smFloat);


    }

Comments

Post a Comment

Popular posts from this blog

Ledger dimensions and financial Dimensions X++

   //  Get individual dimensions from ledger dimensions         if (accEntry.LedgerDimension)         {             LedgerDimensionPreviewTmp   ledgerDimensionPreviewTmpLocal;             // Build the preview data.             ledgerDimensionPreviewTmpLocal = LedgerDimensionPreviewTmp::buildLedgerDimensionPreviewTmp(accEntry.ledgerDimension);             select firstonly ledgerDimensionPreviewTmpLocal                 where ledgerDimensionPreviewTmpLocal.Name == "Funds";             fundValue = ledgerDimensionPreviewTmpLocal.Value;             select firstonly ledgerDimensionPreviewTmpLocal                 where ledgerDimensionPreviewTmpLocal.Name == "MainAccount"...
Read more

Logistics address, postal address, address phone fax contact information

// Find primary address by roles  LogisticsPostalAddress postalAddr;  postalAddr = DirParty::findPostalAddressByRole(DirPartyTable::find(companyInfo::find().PartyNumber).RecId,LogisticsLocationRoleType::Invoice); Get Description/ name of location LogisticsLocation::find(postalAddrInvoice.Location).Description // Find contact information (Generic one) DirPartyContactInfoView contactInfoView; contactInfoView = DirPartyContactInfoView::find(partyRecId, _location); // Find primary address by roles regardless primary or not public  LogisticsPostalAddress getPostalAddressByType(DirPartyRecId _party, LogisticsLocationRoleType _type)     {         DirPartyLocation        partyLocation;         DirPartyLocationRole    partyLocationRole;         LogisticsLocation       location;         LogisticsLocationRole   locationRole;   ...
Read more

Consume odata/web api d365fo and parsing json content

// Two types of content type url will have usually , //1. normal URL encocoding(means adding parameters in url link)   //2. multiform-data(values needs to specify on webforms to accesss data ) //Check RetailCommonWebAPI and do find references how they are consuming odata API    class ALMMenaiHRMSIntegrationService {     str accessToken,responseJsONData;     RecordInsertList recIns;     public void  getAuthToken()     {         RetailCommonWebAPI  webApi;         str                 rawResponse;         RetailWebResponse   response;         URL loginURL;         str contentType;         Map docFields = new Map(Types::String, Types::Class);         webApi = RetailCommonWebAPI::construct();         loginU...
Read more