How to see Item Price Lists in the Price Level lookup from the Sales Transaction Entry window using SmartFill

Subject

How to see Item Price Lists in the Price Level lookup from the Sales Transaction Entry window using SmartFill

Resolution:

The steps here will describe how to see Item Price Lists in the Price Level lookup from the Sales Transaction Entry window using SmartFill.

 In this specific example, here are a few prerequisites:

  1. The Items are selling in the Z-US$ Currency.
  2. All Items are set up to use % of List Price in the Item Price List Maintenance window.
  3. List Prices are defined:
    • In the Item Maintenance window if Multicurrency is not registered.
    • In the Item Currency Maintenance window if Multicurrency is registered.

With the above prerequisites in place, here are the steps to follow:

Identify table(s), columns, etc. that will help achieve the results you want to appear in the Price Level lookup

  1. Identify what you need in the lookup. In this example, the Price Lists for the Item I am looking up need to appear in the SQL Query results but only for the Customer and Item Number defined in the Sales Transaction Entry window.
  2. Identify what table(s) store the information you need to appear in the lookup. Take note of the table that stores the most information needed for your lookup. In this example, the Item Master table has the most information needed to link to multiple tables. We also need information from the Item Price List, Item Currency Master, and the RM Customer MSTR table.
  3. Use SQL Scripting, for example, to link the main table to the other tables, multiple fields, convert fields, etc. and then run the SQL Script to verify the end results are what you are looking for.
  4. Once the desired results appear, you can use the SQL Scripting information to edit the spSmartFill Stored Procedure. See comments and special notes in script in the next section.

Create or Alter the spSmartFill stored procedure to include information for the new SmartFill Object you will be creating below.

  1. If the spSmartFill stored procedure does NOT currently exists in your company database, you will want to create it using the script below to add the new Custom SQL SmartFill Object to your Company Database.
  2. If the spSmartFill stored procedure currently exists in your company database, you will want to alter it to add the new Custom SQL SmartFill Object by adding the IF to the END section below before the final GO in the current stored procedure.

USE [TWO]

–Note1: Replace TWO with the Company Database Name you want the SmartFill Custom SQL Object in.

 GO

 /****** Object:  StoredProcedure [dbo].[spSmartFill]    Script Date: 1/25/2018 7:06:25 PM ******/

SET ANSI_NULLS ON

GO 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROC [dbo].[spSmartFill]

       @SmartFillObjectID INT,

       @UserID CHAR(15),

       @CompanyID INT,

       @SearchValue CHAR(50),

       @SearchValue2 CHAR(50),

       @SearchValue3 CHAR(50),

       @SearchValue4 CHAR(50),

       @SearchValue5 CHAR(50),

       @SearchValue6 CHAR(50),

       @SearchValue7 CHAR(50),

       @SearchValue8 CHAR(50),

       @SearchValue9 CHAR(50)

AS

DECLARE @SmartFillObjectName CHAR(50)

SET @SmartFillObjectName = (SELECT SF_Object_Name FROMDYNAMICS.dbo.SF002 WHERE SF_Object_ID = @SmartFillObjectID)

 

IF @SmartFillObjectName = ‘PricePerPriceLevel’ BEGIN

–Note2: PricePerPriceLevel is the name of the Object Name in the SmartFill Object Maintenance Details window.

       INSERT INTO DYNAMICS.dbo.SF001

              (USERID,

              CMPANYID,

              SF_Return_Field_String,

              SF_Return_Field_Account_1,

              SF_Return_Field_Account_2,

              SF_Return_Field_Account_3,

              SF_Return_Field_Account_4,

              SF_Return_Field_Account_5,

              SF_Return_Field_Integer,

              SF_Return_Field_Currency,

              SF_Field_Value_1,

              SF_Field_Value_2,

              SF_Field_Value_3,

              SF_Field_Value_4,

              SF_Field_Value_5,

              SF_Field_Value_6,

              SF_Field_Value_7,

              SF_Field_Value_8,

              SF_Field_Value_9,

              SF_Field_Value_10)

       SELECT

              @UserID,

              @CompanyID,

              IV00108.PRCLEVEL, — Return Field

              ”, –Account Segment 1

              ”, –Account Segment 2

              ”, –Account Segment 3

              ”, –Account Segment 4

              ”, –Account Segment 5

              0,  –Integer Placeholder

              0,  –Currency Placeholder

              IV00101.ITEMNMBR,  –Display Field 1

              IV00101.ITEMDESC,  –Display Field 2

              IV00108.PRCLEVEL,  –Display Field 3

              IV00108.UOFM, –Display Field 4

              convert(numeric(10,2),((IV00105.LISTPRCE *IV00108.UOMPRICE)/100)) as Price, –Display Field 5

              –Note3: In the Display Field 5, two table fields are being used to calculate the Price. Then the Convert function is being used to convert the value to a numeric value with 2 decimals.

IV00108.CURNCYID, –Display Field 6

              RM00101.CUSTNMBR, –Display Field 7

              IV00105.LISTPRCE, –Display Field 8

              IV00108.UOMPRICE, –Display Field 9

              ”  –Display Field 10

       FROM IV00101

join IV00108

on IV00101.ITEMNMBR=IV00108.ITEMNMBR

–Note4: Join Item Master to the Item Price List table to see the Item’s Price List(s).

join RM00101

on IV00108.PRCLEVEL > ”

–Note5: Join the Customer Master for linking the Customer Number to the Customer Number in the Sales Transaction Entry window in the Repository Filter. 

join IV00105

on IV00101.ITEMNMBR=IV00105.ITEMNMBR andIV00105.CURNCYID = IV00108.CURNCYID

–Note6: Join Item Master to the Item Currency Master table if you are registered for Multicurrency and need to see or use the List Price.

 

              WHERE (IV00108.PRCLEVEL LIKE ‘%’ +LTRIM(RTRIM(@SearchValue)) + ‘%’   orSUBSTRING(IV00101.ITEMNMBR, 1, 1) =SUBSTRING(@SearchValue, 1, 1)) and RM00101.CUSTNMBR =LTRIM(RTRIM(@SearchValue2)) and IV00101.ITEMNMBR =LTRIM(RTRIM(@SearchValue3))— Contains search

–Note7: @SearchValue is for the value you enter in the Price Level field, @SearchValue2 is for the first Repository Filter value identified, and @SearchValue3 is for the second Repository Filter value identified. Utilize Repository Filters to pull values from the Window and reduce the number of records that appear in the Select Record window.

–WHERE IV00101.ITEMNMBR LIKE LTRIM(RTRIM(@SearchValue)) + ‘%’        — Begins With search

       RETURN @@ROWCOUNT

END

 

Create a Custom SQL SmartFill Object to use the spSmartFill Stored Procedure to search for the records you need to appear in the lookup

  1. Log into Microsoft Dynamics GP as sa.
  2. Go to Microsoft Dynamics GP | Tools | Setup | SmartFill | Objects.
  3. Click the + button to create a new one.
  4. In the SmartFill Object Maintenance Details window, enter the name of the Custom SQL SmartFill Object from Note1 above. In this example, it is PricePerPriceLevel.
  5. Select the following:
    • Search Type: Contains
    • Product: Microsoft Dynamics GP
    • Lookup Form: IV_Price_Level_Lookup
    • Table Name: IV_Price_Level_Setup
    • Return Field: PriceLevel
    • Return Datatype: String
  6. Click the Table Name expansion button.
  7. In the SmartFill Object Secondary Tables window, click the + button.
  8. In the SmartFill Tables window, select Sales for the Series, select RM_Customer_MSTR for the Table, and then click Select.
  9. In the SmartFill Object Link window, PriceLevel and PriceLevel, and then click Link.
  10. In the SmartFill Object Secondary Tables window, click the + button.
  11. In the SmartFill Tables window, select Sales for the Series, select IV_Item_MSTR for the Table, and then click Select.
  12. In the SmartFill Object Link window, PriceLevel and PriceLevel, and then click Link.
  13. In the SmartFill Object Secondary Tables window, click OK. 
  14. In the SmartFill Object Maintenance window, change the Search Type to Custom SQL.
  15. Enter the following values in the Display Name columns:
    • Item Number
    • Item Description
    • Price Level
    • U of M
    • Price
    • Currency
    • Customer
    • List Price
    • Percentage
    • Space Column (This column has been added as a dummy column so the fields above appear in the Select Record window.)
  16. Mark the Search checkbox for Price Level.
  17. Mark the Display checkbox for all columns.
  18. Click Save.

Create the Repository record to assign the SmartFill Object to a specific Window and Field.

In this example, we will be assigning the PricePerPriceLevel SmartFill Object to the Item Number field in the Sales Transaction Entry window.

  1. Make sure all windows are closed in Microsoft Dynamics GP.
  2. Go to Microsoft Dynamics GP | Tools | Setup | SmartFill | Repository.
  3. In the Repository Maintenance window, click the + button to create a new one.
  4. When the Lookup Creation Wizard opens, click Next.
  5. Select PricePerPriceLevel, in this example, and then click Next.
  6. Go to Transactions | Sales | Sales Transaction Entry.
  7. Select an existing Sales document, expand the line item, and then move your cursor to the Price Level field.
    • Note: You may need to click the buttons a couple of time to get the Sales document selected and to place your cursor in the Price Level field because the Add New Lookup window populates each time you move to a different field/button.
    • This is what the Add New Lookup window show look like, for example: 
  8. In the Add New Lookup window:
    • Click Remove Lookup if you have assigned the lookup to another SmartFill Object, click Yes, click Add Lookup, and then click Done.
    • Otherwise, click Add Lookup, and then click Done.
  9. In the Repository Maintenance window, select the following:
    • Product: Microsoft Dynamics GP
      • Note: If you have given security to the alternate window, the Product would then be for the Product of the alternate window. For example, Omni Price has an alternate window for the Sales Transaction Entry and Sales Item Detail Entry windows. If security was assigned to the Omni Price alternate windows, I would choose Omni Price for the product. 
    • Object: PricePerPriceLevel
  10. Click Redisplay.
  11. Select the record for the SOP_Entry Form and PriceLevel field, and then click the Edit button.
  12. In the Modify Repository Record window, enter the following Filters:
    • Table Name: RM_Customer_MSTR
    • Table Field: Customer Number
    • Operation: Equals
    • Type: Window Field
    • Value: Customer Number
      • Click the Lookup button.
      • In the SmartFill Fields window, select the SOP_Entry Window, the Customer Number Field, and then click Select.
    • Table Name: IV_Item_MSTR
    • Table Field: Item Number
    • Operation: Equals
    • Type: Window Field
    • Value: Item Number
      • Click the Lookup button.
      • In the SmartFill Fields window, select the Line_Scroll Window, the Item Number Field, and then click Select.
  13. Click OK.

Use the new SmartFill Object

  1. Go to Transactions | Sales | Sales Transaction Entry.
  2. Enter the header information and tab to the Item Number field.
  3. Enter the Item Number, and then click the Show Details button.
  4. In the Price Level field:
    • Enter the first character of the Item Number and tab off the field if you want all the Item Number’s Price Lists to appear.
    • Or, enter part of the Price Level and tab off the field if you want only the Prices Lists with the matching Price Levels to appear.
  5. In the Select Record window, you will see what the prices are available: 
  6. Double click or select the Price Level record you want for the line item, and then click Select. Note: If you want the Case price using the above example, you will need to change the U of M on the line item to Case.

 

 

 

 

 

 

All KB Articles

Related Content

More Resources