UDF link to system object, field value question

[pcorp01]

Hi, I created a UDF that links purchase order to sales order. I was able to do so, however, I’d like to be able to show the docnum of the purchase order. With the UDF, it shows the row number and not the docnum. Can you pls give me suggestions on how to make this possible? I’m guessing creating another UDF (formatted search) to populate the PO docnum from the linked UDF. Am I correct?

using sap b1 9.2

[Johan_H · Accepted solution]

Hi Pia,

The available fields depend on the chosen table. A document is always registered in two tables, one contains the header data, like DocNum, and the other contains the row data, like LineNum.

So you need to connect the UDF, or a new UDF, to the table that contains the DocNum field. You can turn on System information from the View menu, and hover the mouse arrow over the DocNum field, to find out which table you need (it will be displayed in the information field at the bottom of the left of the B1 client).

Regards,

Johan

[pcorp01 · Accepted solution]

Thanks Johan for the reply. I think I understand the concept, but not sure how to apply it. The table I want to connect my new UDF to, is it the lookup table found in my linked UDF (which is just a lookup table and does not have a name, when I hover the mouse arrow over) or the actual PO window. I'll attach a screenshot for your reference.salesorderscreenshot.jpg

How would I write the FMS query for my udf?

[Johan_H · Accepted solution]

Hi Pia,

You would have to hover the mouse over the required field in the PO window (see screenshot).

Please note that this works for almost all fields in the B1 client, and can be very handy when you need to write a query, for example for a report, or formatted search.

Now the items in a sales order can technically come from multiple Purchase Orders, so your UDF should indeed be no the row level of the Sales Order. If you would like to link the Sales Order row to Purchase Order row, you would have to create two UDFs on the row level of the Sales Order. One UDF would be linked to the OPOR table to get the DocNum, and the other would show the row number (LineNum).

Regards,

Johan

[pcorp01 · Accepted solution]

SELECT T0.DocNum 
FROM dbo.OPOR T0 
WHERE T0.U_PO<br>

Hi Johan, here's what I came up with for my FMS query on my other UDF, but not having much luck (sorry, fairly new to sap and sql). Also, we are keeping the PO link on the header and not at row level (assigning one PO will do). Thanks so much for your help!

U_PO - udf linked to Purchase Order (shows linenum)

U_PONum - udf to get DocNum (need help with FMS query)

[Johan_H · Accepted solution]

Hi Pia,

Okay just a couple of questions:

• how did you get U_PO to show the LineNum?
• how is the PO linked to the Sales Order?

Regards,

Johan

[pcorp01 · Accepted solution]

In UDF management, I set the field data's validation for U_PO as Linked to Entities, selected Link to System Object then Purchase Orders.

It's rare that we get multiple PO's attached to a Sales Order, so we would rather have one PO linked instead of doing it by item/row.

Thanks

[Johan_H · Accepted solution]

Hi Pia,

The U_PO field shows the LineNum, because it is meant to be used on the line level.

With my second question I meant, how does a user normally link the PO to the Sales Order? If we know this, then we can work around the issue by creating our own FMS with query.

Regards,

Johan

[pcorp01 · Accepted solution]

Hi Johan,

We would manually pick the PO from the list. We would click on the lookup button on the U_PO field, type in the PO number we want linked and select it... Not sure if that's what you're asking, please let me know.

Thanks,

Pia

[Johan_H · Accepted solution]

Hi Pia,

That was exactly what I meant, thank you. To make the U_PO field as efficient as possible, we need to try and narrow down the list of POs as much as possible. So a couple more questions please:

• What is the PO/SO relation like, 1/1 or 1/many?
• Can the PO be closed or open, or will it always be open?
• How old can the PO be? Will it always be older, or always be newer than the SO, or either?
• Will all items in the SO always come from the same default supplier?
• Can you think of ways to narrow the list down?

Regards,

Johan

[pcorp01 · Accepted solution]

Hi Johan,

• What is the PO/SO relation like, 1/1 or 1/many? 1/1
• Can the PO be closed or open, or will it always be open? I think it will always be open but to be safe it can be either.
• How old can the PO be? Will it always be older, or always be newer than the SO, or either? It will be older, although usually they will have the same date, as we create the PO first then the Sales Order.
• Will all items in the SO always come from the same default supplier? in Most cases yes, that's why we would like to just limit 1/1 linking.
• Can you think of ways to narrow the list down? Maybe just the date range? Any recent PO within the week ?

I appreciate the help!

Thanks,

Pia

[Johan_H · Accepted solution]

Hi Pia,

Allright, the process is reasonably straightforward. Please do the following (in a test database first, to make sure that it works as expected):

1. If you know how to create a user defined field, please skip to step 4, otherwise go to: Tools > Customization tools > User-defined fields - Management
2. Select the Marketing Documents >Title level, and press the Add key.
3. Create the user defined field, with Type alphanumeric, default length 10 is fine, Title PO, and a description.
4. Go to: Tools > Queries > Query Generator and press the Execute button
5. Click the pencil icon on the query result form, empty the query field, and copy-paste the following query:

SELECT T0.[DocNum]
 , T0.[DocDate]
 , T0.[CardCode]
 , T0.[CardName]
 , (select count(*) from POR1 where DocEntry = T0.DocEntry) AS [Nr of Rows in PO]
FROM OPOR T0
/* The number -4 determines how many
 days old POs will be shown.
 Change this number to get more or
 less days worth of POs */
WHERE T0.[DocDate] BETWEEN cast(floor(cast(DATEADD(DAY, -4, GETDATE()) as float)) as datetime)
/* The number 0 determines how many
 days into the future POs will be shown.
 Change this number to get more or less days worth of POs */
AND cast(floor(cast(DATEADD(DAY, 0, GETDATE()) as float)) as datetime)
/* Please note that the first number (-4)
 cannot be larger than the second number (0) */

6. Save the query (I recommend creating a separate category named 'Formatted Searches')

7. Open the Sales Order form

8. Click in the new User Defined field (PO), and press ALT + SHIFT + F2 on your keyboard

9. Choose the Search in Existing User-Defined Values according to Saved Query option.

10. Press the Open Saved Query button, select the new query you just saved, and press the OK button

11. Create a new Sales Order

12. Click the user defined field, and press SHIFT+F2

13. Choose the relevant purchase order, and Bob's your uncle.

Please note that I do not know if you will see the yellow drill down button with the user defined field.

Regards,

Johan

[pcorp01 · Accepted solution]

That worked Johan! Exactly what I was looking for, it also eliminated creating additional UDF's. Thank you so much! I appreciate the helpful tips as well. Will mark this solved and closed.

Cheers!

Pia