Copy CardName into Quotation line by using formatted search

[Former Member]

I'm looking for the right query to copy the CardName from the OCRD table into a quotation line (QUT1) by using a formatted search. The UDF field in the QUT1 is U_Levnaam. In the field U_Levnaam I want to place the formatted search with the query.

I don't no if this is possible, because the field CardName of the OCRD table doesn't have a direct link to the QUT1 table. There is a link between the OCRD and OITM table.

This is how far I came. I have tried other solutions as well but without any result. Probably is has to be a complete other query

+SELECT T0.CardName FROM OCRD T0 INNER JOIN OITM T1 ON T0.CardCode = T1.CardCode Where T0.CardCode = $[OITM.ItemCode]+

So if anyone has a solution your help will be very appreciated.

Regards,

Tim

[Former Member · Accepted solution]

Try this one then:

SELECT T0.CardName FROM OCRD T0

INNER JOIN OITM T1 ON T0.CardCode = T1.CardCode

WHERE T1.ItemCode = $[$38.1.0\]

[Former Member · Accepted solution]

Thanks Gordon,

Your query did the job!

Steffen also thanks for your replies.

Best regards,

Tim

[Former Member]

SELECT $[$54.0.0\] will work for both cases.

Thanks,

Gordon

[Former Member]

Hiho,

your query does not make sense, look here :

SELECT T0.CardName 
FROM OCRD T0 
INNER JOIN OITM T1 ON T0.CardCode = T1.CardCode 
Where T0.CardCode = $[OITM.ItemCode]

What do you do :

First select T0.CardName...from the Table OCRD with a inner join of OITM (that means you request each

record from OITM where the CardCode from OCRD is equal to the CardCode from OITM). Ok thats wrong,

cause you got a CardCode in OCRD, but in OITM is not a CardCode, cause it is the item table.

Your where clause did not match at all. You want to fetch records where a CardCode is equal to a ItemCode.

Ok lets see what do you need :

You want to select a CardName from the OCRD into a line of your quotation. Therefor are 2 possibilities :

1.If your quotation is already saved:

SELECT T0.CardName FROM OCRD TO, QUT1 T1 WHERE T0.CardCode = T1.BaseCard

2.If you creates a new quotation:

SELECT T0.CardName FROM OCRD T0 FROM T0.CardCode = $[$4.0.0]

or this works too

SELECT $[$54.0.0]

Regards Steffen

[Former Member]

Hello Steffen,

Thanks for your reply.

Could you please explain what the $[$4.0.0] and $[$54.0.0] means in your query?

SELECT T0.CardName FROM OCRD T0 FROM T0.CardCode = $[$4.0.0]

I have tried your query but I do not get a result.

Maybe I wasn't clear about my first message.

The result I would like to see is the CardName of the supplier of the article in the quotationline (so not the CardName of the customer).

For example: I enter Article A in the first line of a quotation. This article is supplied by SAP. So I like to see the name SAP in the quotationline.

Tim

[Former Member]

HI,,

$[$xx.xx.xx]

or

$[$Tabel.ColumnName]

this pick up a screen value by using the field id. Caution you only can pick up values from active forms!

OK you did say, that you want to display the supplier. Lets see, what we can do :

SELECT T0.CardName FROM OCRD TO 
INNER JOIN QUT1 T1ON T0.CardCode=T1.CardCode
WHERE T1.ItemCode = $[$QUT1.ItemCode]

Regards Steffen