Skip to Content
0

How to add a new sales order number to a user defined field

Nov 09, 2016 at 08:54 PM

185

avatar image
Former Member
Hello,


While the user creating a new sales order, I want to see if at the same time the new "Sales Order Number" automatically populate in the user defined field. Can I use a SQL query or any other options. Below is screenshot of the sales order.


Thanks for any help in advance.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

8 Answers

Best Answer
Scott McClure Nov 09, 2016 at 10:33 PM
3

From the image it looks like you already have a Formatted Search on the field so you're on your way.

First, create a simple query in Query Manager that says only “SELECT $[$8.0.0]”.This refers to the Order Number field.

Then, while in the user defined field enter Shift-Alt-F2 (Top Menu/Tools/Customizations Tools/User Defined Value - Setup). This will open a small window that will look something like this when you're done.

This says to execute the query when the BP Name changes as it will when the user enters a Customer number.It can be tied to any field on the screen in this way.This should trigger and fill the UDF Order field when the user enters a customer on a new sales order.

If I understand you, this should work.


Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Scott,
Thank you very much for your help.
1) I created a new query and named it "Order No".


2) I added your query “SELECT $[$8.0.0]” to "Order No" in Query Manager and saved it.

3) But when I reopen the query the format has changed.

Thanks.

0
avatar image
Former Member Nov 14, 2016 at 12:55 PM
1

Hi,

The format is not changing. Keping your sales order form open, if you open this user defined query, then it gets executed. Because FMS is meant to capture the information form the active screen.

Regards

Pradnya

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Pradnya,
Thank you for your comment.

I just created a new Sales Order then checked the "Order No" field in UDF and noticed it was empty. Does it matter to place "Order No" query under General category instead FMS in Query Manager? I am not sure if this is cause of the problem.

Thanks.

0
avatar image
Former Member Nov 15, 2016 at 05:34 AM
1

Hi,

Try $[ORDR.DocNum] instead of $[$8.0.0].

This worked.

Regards

Pradnya

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Pradnya,

Your query ($[ORDR.DocNum]) works now! The only problem is the sales order number didn't populate automatically and I have to click twice on the search icon in order to populate the sales order number. Please see the image.


Thanks.

0
Scott McClure Nov 21, 2016 at 06:59 PM
1

Hi Bill - can you show us the FMS setup screen you have on the field (under shift-alt-F2 when in your new Order No. field)? Not sure what might be getting in its way. I changed mine to fire on change of the Customer Number on my demo database and it's working. I am still using the $[$8.0.0] syntax as well.


Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Scott,

Thanks for your time and effort. Noticed that I used "BP Name Overwritten" for the use defined value that is why your formula did not work. When I selected "Customer/Vendor Code", your formula worked fine.
Thanks again.

0
avatar image
Former Member Nov 15, 2016 at 05:30 AM
0

Hi,

Category does not matter at all. I executed your FMS query through query manager. It is not capturing order number.

Regards

Pradnya

Share
10 |10000 characters needed characters left characters exceeded
Johan Hakkesteegt Nov 22, 2016 at 07:16 AM
0

Hi Bill,

Why would you want to display the order number, that is already prominently visible, again in a user defined field (which is less prominently visible)? It makes no sense, because for any and all purposes you can use the existing DocNum system field. With this solution you are only doubling data in the database.

Did you mean to display the order number this way in a user field on the Delivery Note form? That would make sense to me. Or did you simply want to test the principle?

Regards,

Johan

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Johan,

Thanks for replying to my posting. I need to have SO# when am invoicing. Do you know any method to use so I can auto populate the SO# (using UDF) when I am invoicing?

Thanks.

0
Scott McClure Nov 22, 2016 at 08:29 PM
0

Bill, look at the field Ref1 on OINV. That should tell you the Sales Order number assuming you've used the Copy To or Copy From functions. Too bad we can't surface hidden system fields (or does someone know a way?). If that works you should be able to use the table syntax shown above in a formatted search on the UDF.

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Scott,

You may use the MS SQL Management Studio to access the database. Here you can see all fields and tables.

Once you know the name of the field, you can use it in the FMS query.

Regards,

Johan

0

Thanks Johan, I do understand about being able to find and, with SSMS, check values. My comment about surfacing hidden system fields would mean adding a system field that is not currently on a form to that form. For instance, in this case as Pradnya points out below, the value of the ref document is in the comments. However, comments are not useful for processing so it might be useful to have a field on the screen with the doc number so that you could, for instance, use it in a Boyum customization rule. The field exists in the table as Ref1 but it is not surfaced to the screen.

0

Okay, I see what you mean. The same solution goes, except that you have to note the data type of the system field, and make sure that the UDF is the same or a compatible data type.

0
avatar image
Former Member Nov 23, 2016 at 08:04 AM
0

Hi,

Sorry to have responded late. I was not around and busy in SAP activities.

If you are maintaining SAP standard document trail like SO --> A/R Delivery --> A/R Invoice with the help of copy from or copy to functionality then you will find that complete document trail in the remarks field.

Regards

Pradnya

Share
10 |10000 characters needed characters left characters exceeded