Skip to Content
author's profile photo Former Member
Former Member

ORANGE ARROW

Hi Experts,

I tried to like the orange arrow in crystal report as below. However, I am just wondering how to link for ORIN Document No.

'http://$b1$/link?table=OINV&key=' + totext({Command.DocNum},0,"","")

I would be appreciated if anyone could advise for this.

Thanks

SELECT 
T0.[DocDate],
T0.[DocTime],
T0.[CardName] AS 'Customer Name',

CASE
WHEN T0.ObjType = 13 THEN 'ARIV'
WHEN T0.ObjType = 14 THEN 'ARCR'
ELSE 'Error'
END AS 'Doc Type',

T0.[DocNum] AS 'Doc No.', 
T0.[PaidToDate], 
T0.[DocTotal] - T0.[PaidToDate] AS 'AR', 
T0.[DocTotal] - T0.[DocTotal] AS 'AR Credit', 
T0.[DocTotal] - T0.[VatSum] AS 'Sales Net',
T0.[TotalExpns] AS 'Net Freight',
T0.[VatSum] AS 'G.S.T', 
T0.[DocTotal]
FROM OINV T0 
WHERE T0.[DocDate] >= {?DateFrom} AND  T0.[DocDate] <= {?DateTo}

UNION ALL

SELECT 
T0.[DocDate],
T0.[DocTime],
T0.[CardName] AS 'Customer Name',

CASE
WHEN T0.ObjType = 13 THEN 'ARIV'
WHEN T0.ObjType = 14 THEN 'ARCR'
ELSE 'Error'
END AS 'Doc Type',

T0.[DocNum] AS 'Doc No.', 
(T0.[PaidToDate] - T0.[DocTotal]) * -1 AS 'Paid', 
(T0.[PaidToDate] - T0.[DocTotal]) * -1  AS 'AR',
T0.[PaidToDate] * -1 AS 'AR Credit', 
(T0.[DocTotal] - T0.[VatSum]) * -1 AS 'Sales Net',
T0.[TotalExpns]  * -1 AS 'Net Freight',
T0.[VatSum] * -1 AS 'G.S.T', 
T0.[DocTotal] * -1
FROM ORIN T0
WHERE T0.[DocDate] >= {?DateFrom} AND  T0.[DocDate] <= {?DateTo}

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on Aug 17, 2011 at 10:35 AM

    Hi Stephen,

    Modify you query as below to have Object type column:

    SELECT 
    T0.[DocDate],
    T0.[DocTime],
    T0.[CardName], T0.ObjType, 
    CASE
    WHEN T0.ObjType = 13 THEN 'ARIV'
    WHEN T0.ObjType = 14 THEN 'ARCR'
    ELSE 'Error'
    END AS 'Doc Type',
     
    T0.[DocNum], 
    T0.[PaidToDate], 
    T0.[DocTotal] - T0.[PaidToDate] AS 'AR', 
    T0.[DocTotal] - T0.[DocTotal] AS 'AR Credit', 
    T0.[DocTotal] - T0.[VatSum] AS 'Sales Net',
    T0.[TotalExpns] AS 'Net Freight',
    T0.[VatSum] AS 'G.S.T', 
    T0.[DocTotal]
    FROM OINV T0 
    WHERE T0.[DocDate] >= {?DateFrom} AND  T0.[DocDate] <= {?DateTo}
    
    UNION ALL
    
    SELECT 
    T0.[DocDate],
    T0.[DocTime],
    T0.[CardName] ,
    T0.ObjType,
    CASE
    WHEN T0.ObjType = 13 THEN 'ARIV'
    WHEN T0.ObjType = 14 THEN 'ARCR'
    ELSE 'Error'
    END AS 'Doc Type',
     
    T0.[DocNum], 
    (T0.[PaidToDate] - T0.[DocTotal]) * -1 AS 'Paid', 
    (T0.[PaidToDate] - T0.[DocTotal]) * -1  AS 'AR',
    T0.[PaidToDate] * -1 AS 'AR Credit', 
    (T0.[DocTotal] - T0.[VatSum]) * -1 AS 'Sales Net',
    T0.[TotalExpns]  * -1 AS 'Net Freight',
    T0.[VatSum] * -1 AS 'G.S.T', 
    T0.[DocTotal] * -1
    FROM ORIN T0
    WHERE T0.[DocDate] >= {?DateFrom} AND  T0.[DocDate] <= {?DateTo}

    and while creating hyperlink ,create it base on formula as:

    if ({Command.ObjType})=13 Then
        'http://$b1$/link?table=OINV&key=' + totext({Command.DocNum},0,"","")
    Else
        'http://$b1$/link?table=ORIN&key=' + totext({Command.DocNum},0,"","")
    
    

    Thanks,

    Neetu

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 17, 2011 at 12:53 AM

    ORANGE ARROW

    Disappear with you use PIVOT, UNPIVOT, UNION or UNION ALL operators

    Regards

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.