Skip to Content
avatar image
Former Member

How to use a query in the Print Layout Designer?

I created a query with the query generator:

SELECT T0.[Discount], T0.[NumOfDays], T2.[DocNum] FROM CDC1 T0, OQUT T2 INNER JOIN OCTG T3 ON T2.GroupNum = T3.GroupNum WHERE T2.[GroupNum]=T3.[GroupNum] AND T0.[CdcCode] = T3.[DiscCode]

The result shows the cash discout (the days and the percentage) of every record.

I need this query, because I want to design a sales order report.

To show the payment conditions with cash discount I need to have the table CDC1 but when I create a new database field in the report I can't see that table.

So I queried the table CDC1....

Then I created a new database field and in the list box "table" I made the following steps:

extras\customizing tools\user defined values - definition\search for saved query in user defined values

But the problem is that I don't get the result of the query into my report.

What's the problem? I don't know what I've done wrong...

please help me

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

8 Answers

  • Best Answer
    avatar image
    Former Member
    Sep 02, 2008 at 11:42 AM

    So, I took the following queries of you:

    SELECT T2.NumofDays FROM OCTG T0 INNER JOIN OCDC

    T1 ON T0.DiscCode = T1.Code INNER JOIN CDC1 T2 ON T1.Code = T2.CdcCode

    WHERE T0.GroupNum = $[ORDR.GroupNum]

    SELECT T2.Discount FROM OCTG T0 INNER JOIN

    OCDC T1 ON T0.DiscCode = T1.Code INNER JOIN CDC1 T2 ON T1.Code = T2.CdcCode

    WHERE T0.GroupNum = $[ORDR.GroupNum]

    I created 2 UDFs in the title of marketing documents and set up the formatted search with the queries.

    I choosed 'Auto refresh when field changes', the field 'payment terms code and 'refresh regulary'

    But now I get an internal error [-1004] [message 131-183]

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 01, 2008 at 02:25 PM

    Meike,

    You are trying to add a Formatted Search in the 'Table' list box in PLD. It will not work...

    If I understand you, you would like to display the result of the query on your print layout? For which transaction are you creating the layout?

    Regards,

    Nat

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 01, 2008 at 02:38 PM

    Hi,

    I am creating the layout for a sales order which will be send to a customer.

    In that order the customer can see the payment conditions.

    It should look like this:

    -


    net price: 1000,00 EUR

    19,00% taxes: 190,00 EUR

    gross price: 1190,00 EUR

    payment conditions:

    payable within 30 days net: 1190,00 EUR

    payable within 14 days with discount of 2 %: 1166,20 EUR

    -


    to get the bold marked digits I need to have access on the following tables:

    OCTG and CDC1

    In the OCTG table I get the days (in this example 30)

    In the CDC1 table I get the days of cash discount and the percentage (in this example 14 and 2)

    but in the sales order layout in PLD only the OCTG table is shown when I create a database field and the CDC1 table isn't.

    So what can I do to get this?

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      I don't think it's possible simply through PLD.

      What you can do is to create User Defined Fields on the Sales Order, and set up Formatted Searches in them to display the days and percentage. Then you can display these UDF's on the layout from the Sales Order table itself.

      Hop it makes sense.

      Regards,

      Nat

  • avatar image
    Former Member
    Sep 02, 2008 at 08:04 AM

    Well, I tried it out but I guess it is too complicated for the sales department to select every time the cash discount days and percentage.

    Is there no other way to get the CDC1 table into the list box of the database field in PLD?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 02, 2008 at 08:54 AM

    I created the queries successfully. But how can I set the formatted search in the UDFs on auto populated?

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      When you set up the FS, choose 'Auto refresh when field changes' and choose the field 'Payment Terms Code', then 'Refresh regularly'.

      Please let me know if it solves the issue.

  • avatar image
    Former Member
    Sep 02, 2008 at 10:37 AM

    I made the following queries:

    SELECT T0.[NumOfDays] FROM CDC1 T0 , OQUT T2 INNER JOIN OCTG T3 ON T2.GroupNum = T3.GroupNum WHERE T2.[GroupNum]=T3.[GroupNum] AND T0.[CdcCode] = T3.[DiscCode]

    SELECT T0.[Discount] FROM CDC1 T0 , OQUT T2 INNER JOIN OCTG T3 ON T2.GroupNum = T3.GroupNum WHERE T2.[GroupNum]=T3.[GroupNum] AND T0.[CdcCode] = T3.[DiscCode]

    but if I change the payment conditions in the order/sales the field doesn't update itself.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Meike,

      1. Is it the column NumofDays or Day in CDC1 where you have the number of days? I think it should be the Day.

      2. Your queries don't seem to be correct, why don't you use mines? If you run them from the query generator you will receive an error message but that's fine as they will trigger information from the order which is opened on your screen. Don't worry about the error just save them and then assign to the formatted search, they will work fine!

  • avatar image
    Former Member
    Sep 02, 2008 at 11:12 AM

    The field "NumofDays" is definitely the correct one. I checked the query result and also the system information showed me, that the field names "NumofDays".

    The problem of your query was $[ORDR.GroupNum] -- it causes an error message, just as you say...

    I will try out your queries again and tell you the result.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 02, 2008 at 11:59 AM
    Oh, I'm sorry. It is $[ORDR.GroupNum]
    Add comment
    10|10000 characters needed characters exceeded