Automatically select price list on invoice based on user-defined field ?

[Former Member]

We have a case in which the company has several price lists for each customer. The price list to be used for each invoice is determined on the basis of the customer's end customer.

We were planning on adding a user-defined structured field 'end customer' and a formatted search on the pricelist field that would get its value from a query. However, the problem is that the pricelist field is not visible on the invoice form; it can only be accessed by opening the 'settings' form for the invoice. Now, if I try to add a formatted search query to the pricelist field on the settings form, the 'auto-refresh when fields change' option only offers fields that are related to the master data records, not those on the invoice form. As a result, it seems we cannot update the price list setting on the invoice based on the value in a user-defined field.

Are we doomed to create a separate BP record for each Customer/End Customer combination or is there a better solution to this problem

[Former Member]

Thank you for excellent advice. I have not yet implemented this, but it seems that the solution(s) you are offering are exactly what I was looking for.

Henry

[Former Member]

Hello,

if I understood your problem right, I may have a quite simple solution for your problem:

IMHO you don't need the field pricelist - you just have to do a formatted search to the field "Price without discount" (the field, which is initially filled by the pricelist) on item level along with auto update depending on change of the item number column. The query for the formatted search has to give you the price from the pricelist of your choice depending on the cutomer number, UDF and material number. To determine the correct pricelist you may need a UDT in which you define the relations. If you want to keep the information, where you got the price from (pricelist No.), you can write this info into a separate UDF via formatted search, but this info has to be stored somewhere anyway (e.g. the mentioned UDT).

Hope that helps,

Dierk Meissner

Message was edited by: Dierk Meissner

Message was edited by: Dierk Meissner

[Former Member]

Here is a related solution provided by one of our implementors. This is designed for a separate price list for each line, but it may be possible to adapt it to your use.

Bypassing The One-Price List-Per-Document Limitation in SBO 6.2

Because many businesses have several pricing options for each product, they would most certainly require being able to list items with prices drawn from different price lists on the same order.

However, SBO pulls the price list for the whole document from the business partner record; it can be changed with the Settings function, but still only allows one price list to be used. While the user can manually type in a different price than what is presented for each item, it makes more sense that he should be able to select the price list that should be used for each line item.

To facilitate this choice, a formatted search can be assigned to a Price List column that presents a drop-down list of the price lists in the system. When a price list is selected, a second formatted search displays the corresponding price in the Price column.

The procedure for the formatted search involves the following steps:

Create a user-defined field called PriceLst under Tools/Manage User Fields/Marketing Documents/Rows.

Create a query “SELECT T0.ListName FROM OPLN T0 FOR BROWSE” and attach it to the PriceList field by clicking in the PriceList column and accessing the Tools/Search/Define Formatted Search function. Select Search by Saved Query; do not check Auto-Refresh.

Create a second query that ties the price to the price list displayed in the Price List column:

SELECT T0.Price FROM ITM1 T0 INNER JOIN OPLN T1 ON T0.PriceList = T1.ListNum WHERE T0.ItemCode = $[$38.1.0] and T1.ListName = $[$38.U_PriceLst.0] FOR BROWSE

Click in the Price column of the document and assign the query, with the Tools/Search/Define Formatted Search function. Select Search by Saved Query, Auto-Refresh, When Exiting Altered Column, Price List, and Displayed Saved Value; enter the query name.

The user keys SHIFT + F2 in the price list field to select a price list and display the corresponding price.

[Former Member]

...but you have to assign it to the price without discount column - not the price column - otherwise you get discounts (positive or negative) in the document!