Distribution, Manufacturing, and Internet Modules

[jasonitspyder]

I was able to add the UDF for Product Line Description in CI_Item. I rebooted the server and had to login under ABC company, not the company I'm currently using.

However, after adding the UDF, I'm not getting any data from the UDF. I made sure that the UDF points to ProductLine -> ProductLineDesc. I even tried different companies.

I have 2 screen shots below. Am I missing anything here? Please help me out. Thanks!!!

[DFeller]

I am assuming that if you create a new item record everything populates correctly?  I believe that is the issue.

Dawn

[jasonitspyder]

I believe the problem is that only the new items will have the ProductLineDesc info populated.

My question is now: What can I do to my current items so that I can have the UDF populated, without re-creating these items?

[connex]

Use a V/I job to populate the UDF. If you don't own V/I you could use a simple BOI program.

[jasonitspyder]

Hi Dan

Woud like to know how can I do this through a V/I job? Thanks a lot!!

[SRYork]

You really need 2 VI Jobs.  The first will export the CI_item ItemCode and IM_ProductLine ProductLineDesc. Use CI_Item as your main table and include a link definition to the IM_ProductLine table. In your export configuration include the ItemCode and ProductLine columns from CI_Item and the the ProductLineDesc from your IM_ProductLine table link. The easiest would be to export this as a CSV file. Once you've exported the info you need, create an import job to read the ItemCode and update your new UDF with the ProductLineDesc from your CSV file.

BTW, the reason adding this significantly increased the time to run the report is because there was already and outer join included on the report.  Having more than one significantly hinders performance.

Re, adding your UDF, I think the error is being caused because the way you have it defined it is attempting to re-set the CI_Item key to your ItemCode, but it can't because you have that specific record extracted / locked in memory.  Exactly why it would be doing this I have no idea.  

[jasonitspyder]

Thanks GatorGal for the detailed explanation.

Have 1 more question though:

I added my UDF internally in MAS's database (.M4T). By doing this, will it have a performance impact on my MAS performance? In your experience, how big it would be? Currently carrying about 9000 items. Will have more items in the future, possibly up to 20000 items in 1-3 years. Running MAS 200 4.4 PU4.

If performance will be an issue, I would like to know the performance impact on MAS for my 3 possible options below:

1) Add ProductLineDesc in CI_Item (the way I'm doing it now)

2) Add ProductLine in SO_SalesOrderDetail (for my views, I will outerjoin this table with IM_ProductLine to get the ProductLineDesc)

3) Add ProductLineDesc in CI_Item, then add another UDF in SO_SalesOrderDetail to get the UDF_ProductLineDesc from CI_Item

Thanks!

[SRYork · Accepted solution]

Whenever you can limit your links / reads you will have better performance.  Therefore I would go with #3.  In that way, you would not have to link to the CI_Item table if pulling a report that only needs the sales order detail table.

But to be honest, I would be surprised if the CI_Item table were not being read already on complex reports.

It is really hard for me to say what the impact this would have on performance when printing a report that needed to utilize "all" 9000 items or could not be optimized to limit the reads to just the ones you need.

BTW, I assume you know you can map the CI_Item product line description UDF to the SO detail UDF once you have it populated for all your existing items.  You may want to consider extending the mapping to the sales order invoice, then AR Invoice history as well.  That way you will also have it should you need to recreate your info from history.  Just a thought.

I would stay away from option #2 since it includes an outer join.  That way you can save it for later and not be adversely affected if you need it.  As a general rule, avoid outer joins since it causes multiple read to be required since it is a one to many type of link.

[jasonitspyder]

Thanks GatorGal.

Thanks for all the help. Everything worked out wonderfully.