Finance | Project Operations, Human Resources, ...

How to get a reference from sales order to production

(0) Share

Report

Posted on by RadekM

172

I'm trying to find a reference point for a sale order to pin it with a production order but I can't find any possible columns for it.

My task is to get all ProdID that are created from a sale order. I'm working on SalesTable form.

I have the same question (0)

All responses (30)

Answers (0)

Crispin John Augustine 37,081 on at

Like (0)

Report

If you look at the relations in ProdTable, you will find the correct columns for joining with SalesTable and SalesLine..

Was this reply helpful? Yes No
RadekM 172 on at

Like (0)

Report

Look like in Salesline retaion is ProdTableRef class reference. But how can I get into this data with a relation ?

Was this reply helpful? Yes No
Crispin John Augustine 37,081 on at

Like (0)

Report

Not sure, what you saw, but this is how the relation works.

Was this reply helpful? Yes No
RadekM 172 on at

Like (0)

Report

Did look from salesline > Relations > prodtableref > inventrefid , inventreftype

in AOT I got ProdTable > Relations > SalesOrderLine and the relations above you pasted.

How can use it in select statement ?

Could you give me a example for it?

I need something like this. Select prodid, collectrefprodid from prodtable where inventrefid == current.salesid ??

Was this reply helpful? Yes No
Crispin John Augustine 37,081 on at

Like (0)

Report

You already have the tables and relations now.

As to writing a select statement, you have to go through docs.microsoft.com/.../xpp-data-query

Was this reply helpful? Yes No
Sukrut Parab 71,710 Moderator on at

Like (0)

Report

Whatever fields present in the relation , you can use those in where clause while building your query.

Was this reply helpful? Yes No
RadekM 172 on at

Like (0)

Report
Can someone say me why I get '.' expected error at the end in this ?

I need to get all prodid from collectref since the nested statement gives me just the main prodid and collectref

select ProdId, ProdStatus from prodtable where prodtable.CollectRefProdId in (select ProdId, CollectRefProdId, ProdStatus from prodtable join salestable where prodtable.InventRefId == salestable.salesid); Info(strFmt('%1,%2, %3', prodtable.ProdId, prodtable.CollectRefProdId, prodtable.ProdStatus));

Was this reply helpful? Yes No
Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi RadekM,

The select syntax is incorrect. Subqueries are not supported in x++.

In your case use join between all tables.

Was this reply helpful? Yes No
RadekM 172 on at

Like (0)

Report

I dont see how should it work for me. From my first select I got exact that what wanted but now I need to use this value in another select .

Was this reply helpful? Yes No
Suggested answer

Sergei Minozhenko 23,093 on at

Like (0)

Report
Hi RadekM,

You just need to replace "in" with "join". And don't forget to declare all variables.

select prodtable exists join prodtable1 where prodtable1.ProdId = prodtable.CollectRefProdId exists join salestable where prodtable1.InventRefId == salestable.salesid

Was this reply helpful? Yes No