Adding multiple Unit of Measure in an Inventory Item through SQL

(0) Share

Report

Posted on by Community Member

Hello

I am trying to add multiple Unit of Measure in Inventory Item through SQL under one Schedule ID. What should be the SQL query. Suppose I want to add Day and Week for every item (they already have hours as OFM) how should I proceed (please assume that I have already created Day and Week).

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Community Member on at

Like (0)

Report

Please note that the base will remain the same.

Was this reply helpful? Yes No
Mahmoud Saadi 32,738 on at

Like (0)

Report

Initially , would I ask about the purpose of adding them through SQL ?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

There are 50 inventory items and 10 UFMs to assign to each one

Was this reply helpful? Yes No
Suggested answer

Mahmoud Saadi 32,738 on at

Like (0)

Report

Okay, lets get things clear.

The assignment of item to the unit of measurement is done while creating the item, that means, you will create one unit of measurement schedule including the different levels of these UOMs and assign this one UOM schedule to each item.

Don't consider the SQL method, the efforts you will put to check your data and scripts is far more than just adding them from the application level. Create a simple macro which can be run in a matter of few minutes.

Let us know if you need any further assistance,

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Oh I forgot that this is a new implementation and there is no record in the system yet. Just wondering if this is going to work

Insert into IV00108 (UOFM)

select distinct UOFM from IV40202

where ITEMNMBR = (select ITEMNMBR from IV00101)

but it is showing some error.

Any suggestion..?

Was this reply helpful? Yes No
Suggested answer

Mahmoud Saadi 32,738 on at

Like (0)

Report
John

What is this ? This is logically incorrect script. Here are the errors:

You cannot use this type of insert between IV00108 and IV40202, they are different tables (IV00108 is the item price list, while the IV40202 is the item UOM detail setup).

IV40202 doesn't include an item number, you include a "Where" after it, it will give an error

As for the IV00101, what if the sub query returned more than one item number ? The equal will give an error.

I don't forget the logic of connecting these tables in such an insert statement at all :)

Even if this is a new implementation, the tables have logic which should not be incorrectly passed from the back end, it will result with critical issues.

Just do it from the application level with a simple Macro, it is safe and definitely accurate.

Your feedback is highly appreciated,

Was this reply helpful? Yes No
Mahmoud Saadi 32,738 on at

Like (0)

Report

John

What is the status of this case ?

Please feel free to close the case if its fulfilled by verifying the proper answer.

Was this reply helpful? Yes No
Suggested answer

Frank Hamelly | MVP... 46,623 Super User 2025 Season 2 on at

Like (0)

Report

John, Mahmoud provides excellent advice, as always. You might also consider using the Item Class to assign UOM to your items. Setup your Unit of Measure schedule, assign it to a Class ID (along with any other common parameters), and assign the Class to the items when you set them up. The items will inherit the Class parameters.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Frank and AlSaadi, Finally I did it manually. Took a bit of time but I used copy field and It makes it a little faster. I do appreciate your efforts.

Was this reply helpful? Yes No