Showing results for 
Search instead for 
Did you mean: 

Query Challenge!

Former Member
0 Kudos

Can some one help with this query?

Here's the scenario;

There are 2 UDF's that need the same data...

The first UDF (BinID_ItemMaster) is in the Item Master Data Table.

The second UDF (BinID_BOM) is in the BOM at the line item .

When a new Item is created, a bin location is assigned. (BinID_ItemMaster)

I need the (BinID_ItemMaster) to populate the (BinID_BOM) using (I guess) a formatted search.

Thanks in advance for any help,

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

what you need to do is click on system information on the view menu of business one.

the query, assuming the udf on the item master is u_bin will go like this

select u_bin from oitm where itemcode = $[$3.1.0] for browse

you get the $[$3.1.0] from mousing over the itemcode column on the bom, look in the bottom right hand corner and you will see 3 represents the item (matrix) and 1 is the column number where you will get the itemcode from to filter the bin number returned from the item master udf to the bin for the specific bom item row.

to activate the fs click in the binid bom field on the bom line and press alt-shift-f2 to bring up the fs config window, point to the above query you saved in query manager and set it to refresh regularly on exiting the bom line itemcode column, thus when you enter an itemcode on the bom line it will automatically retrieve the bin, provided it exists on the item master user field.

Former Member
0 Kudos

Thanks Philip...That was just too easy to be true. (easy for you that is)

Maybe you can help with this next little set back...I hope it has a simple solution as well.

Now that the BinID is populated on the BOM, can you assist me in sorting the BOM by the BinID?

The problem is; after I sort the BOM by the BinID column it reverts back unless something has been changed in the BOM and the BOM is "Updated".

Is there a way (a FS maybe?) to force the BOM to update after I sort?

Maybe a UDF with a FS with the sole function of triggering a update?

Thanks again for your help...the query worked like a charm

Answers (0)