Empty value

Question 1

State Verified Answer
Locked Locked
Replies 4 replies
Subscribers 1310 subscribers
Views 1703 views
Users 0 members are here

Question 2

Empty value

Offline JEMT over 3 years ago

I modified my report that can see the ConFirmedDate and ApproveDate in workflow history

here my SQL query.

select 
		max(CASE WHEN APPROVALSTATUS = 40 then DATEADD(hh, 8, CREATEDDATETIME) else '' end) as [CONFRIMED DATE],
		MAX(CASE WHEN APPROVALSTATUS = 30 then DATEADD(hh, 8, CREATEDDATETIME) else ''  end) as [APPROVED DATE]
		
		
from AGSSALESTABLEWORKFLOWHISTORY 
where DATAAREAID = 'inf' and APPROVALSTATUS != 10
group by SALESID,CREATEDBY

and translate into X++ code.

what's wrong with this code?

select maxof(CreatedDateTime), CreatedBy from workFlowHistory
    group by SalesId, CreatedBy
    where workFlowHistory.SalesId == salesTable.SalesId;

    if (workFlowHistory.ApprovalStatus == VersioningDocumentState::Confirmed)
    {
        _tmpWizPSDDSDR.ConfirmedBy = workFlowHistory.ApproverName();
        _tmpWizPSDDSDR.ConfirmedDate = workFlowHistory.createdDateTime;
    }
    if (workFlowHistory.ApprovalStatus == VersioningDocumentState::Approved)
    {
        _tmpWizPSDDSDR.ApproveBy = workFlowHistory.ApproverName();
        _tmpWizPSDDSDR.ApproveDate = workFlowHistory.createdDateTime;
    }

I am getting empty value.

can you help on this.

Thanks

0 Offline JEMT over 3 years ago

Why this idea came up, its because i getting this problem. Please see below image with the red line box

it should be a single row. like this

SO	ITEM	Confirmed by	Confirmed date	Approve by	Approve date
SO001	001	Mikey mouse	2/15/2017 5:40:38 PM	Mini Mouse	2/15/2017 5:55:42 PM
SO002	002	Bugs Bunny	2/16/2017 6:40:38 PM	Road Runner	2/16/2017 6:55:48 PM

how will i do it a single row ?

Thank you.

0 Offline metin emre over 3 years ago

You have to use ApprovalStatus field at group by or "select ... from" clause. Your approvalstatus field will came blank. Unfortunately it's a trick of X++. SQL server would be give you an error but X++ give blank field if you even don't use at select command.
Cancel
Up 0 Down

Cancel

+1 Offline JEMT over 3 years ago in reply to metin emre

Hi metin,

Thanks for your reply. i Solve my problem .
see below code.

public void getWorkflowHist(SalesId _salesId)
{
    AGSSalesTableWorkflowHistory        workFlowHistory;

    while select workFlowHistory
        order by CreatedDateTime asc
    where workFlowHistory.SalesId == _salesId //'INF-SO-000101502'
    && workFlowHistory.ApprovalStatus == VersioningDocumentState::Confirmed
    {
        _tmpWizPSDDSDR.ConfirmedBy = workFlowHistory.ApproverName();
        _tmpWizPSDDSDR.ConfirmedDate = workFlowHistory.createdDateTime;

        select CreatedDateTime, CreatedBy from workFlowHistory
        where workFlowHistory.SalesId == _salesId // 'INF-SO-000101502'
        && workFlowHistory.ApprovalStatus == VersioningDocumentState::Approved;
        _tmpWizPSDDSDR.ApproveBy = workFlowHistory.ApproverName();
        _tmpWizPSDDSDR.ApproveDate = workFlowHistory.createdDateTime;
    }

}

+1 Offline metin emre over 3 years ago in reply to JEMT

Hi Jemt,

I just meant that:

select maxof(CreatedDateTime), CreatedBy from workFlowHistory
group by SalesId, CreatedBy,ApprovalStatus
where workFlowHistory.SalesId == salesTable.SalesId;

or, if you just wanted to max record of approved:

select maxof(CreatedDateTime), CreatedBy from workFlowHistory
group by SalesId, CreatedBy
where workFlowHistory.SalesId == salesTable.SalesId &&
workFlowHistory.ApprovalStatus == VersioningDocumentState::Confirmed;
Cancel
Up 0 Down

Cancel

Empty value

Microsoft Dynamics 365 User Groups

Microsoft Dynamics User Groups