How is the Item Price Linked to an Item in Item Master?

[Former Member]

All,

I know that prices are stored in rows in the ITM1 table along with a PriceList ItemCode fields.

I have one populated price list in my B1 db which is list #1 and I named it Master. The name shows up in OPLN.

In the ITM1 table, however, at present there are 10 rows for each OITM row, keyed by ItemCode and PriceList fields. 2-10 are currently unused.

OK, so my question is this: How is the underlying association formed between the price in the ITM1 table and the Item in the OITM table? I cannot see any foreign key that would associate the OITM row with a particular ITM1 row, yet the Item Master form in B1 knows to show me "Master" as the Price List.

I am looking for the sql to get the price of an Item given a designated OITM row.

What it really boils down to is how do I get the PriceList key for the OITM table select given an OITM row?

Thanks in advance,

Mark

[Former Member · Accepted solution]

Primary key linking OITM and ITM1 by ItemCode, 2nd key by Item1.PriceList=OPLN.ListNum.

[former_member903986 · Accepted solution]

Hello Gordon,    

     How can we know that which Pricelist is Active right now for Particular Item?

Regards,

Amir

[former_member186095]

Hi Mark,

Gordon's answer is correct.

The OITM table linked to ITM1 table by joining the both table item code fields. After that, the ITM1 table is joined with OPLN table by using pricelist field of ITM1 table with listnum of OPLN table.

So, you can try this query to their relationship one another:

select oitm.itemcode, opln.listnum, itm1.price from OITM inner join ITM1 on ITM1.itemcode = OITM.itemcode inner join OPLN on OPLN.listnum = ITM1.pricelist

where OITM.itemcode between '[%1]' and '[%2]'

JM

[Former Member]

Hi Mark,

ItemCode is the key to link two tables.

Thanks,

Gordon

[Former Member]

Well, yes. But that would return 10 rows, because 10 price lists. How does the Item Master form know to return a price from list #1, and not one of the other 9?

[Former Member]

It will automatically display the price with the lowest alphabetic value. You can change the price list name to find it out.

[Former Member]

Ok, so what if I select a different price list for a given Item Master? It would then have to establish a link......how would it do that?

[Former Member]

That is handled by UI to link to a specific price list based on your selection.

[Former Member]

Yes, but in the underlying SQL tables, how is the link established?

[KennedyT21]

Hi Mark,

Try This

select  *

from OITM T0

inner join ITM1 T1 on T1.itemcode = T0.itemcode 

inner join OPLN T2 on T2.listnum = T1.pricelist

Regards

Kennedy