SAP Business One I Query inventory base on user defined field

Question

I am running a query on a product. I need a field that will look at the user defined field which contains another product and check that stock level. Any help would be great.

Answer 1

I think the key part you're missing here is the naming convention which SAP adpots for user defined fields. Correct me if I'm wrong, but it seems that you're capable of querying these fields from a SQL point of view. UDFs by defuault, will have their column name prefixed with "U_". For example, the UDF 'AnotherProduct' will be referred to in SQL as 'U_AnotherProduct'.

Hope this helps, if not, please explain your problem in some more detail.

Answer 2

What is the main objective?

Are you trying to check on that item because that item is the material/component? or act like a substitution?

Using UDF is quite not feasible as you have to get the link to the Item Master Data, and I have not figured out yet how to do that.

But if you are using it as substitution, why don't you use Alternative Item in Inventory > Item Management > Alternative Items? It will show on Sales Quotation for you to display it to customer. Or during Sales Order, you can get it displayed for alternative should your main item is shortage.

If you are using those item as a component, I suggest you use Bill of Material. During Production Order you will be able to see those component's availability in qty, and also you can have more than one, unlike UDF.

Hope this helps.