Small and medium business | Business Central, N...

Sales Price Date Range FIlter

(0) Share

Report

Posted on by FongFong

I am creating a page extension and I would like to reference a field to sales price page. But there are multiple sales price of the same item with different "Start Date" and "End Date". Is it possible to use "Order Date" to filter to the correct sales price. E.g like "Order Date" >= "Starting Date" || "Order Date" <= "Ending Date".

Thanks

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

Mohana Yadav 61,001 Super User 2025 Season 2 on at

Like (0)

Report

yes, you can use something like the below code

SetFilter("Starting Date", '<=%1', "Order Date");

SetFilter("Ending Date", '>=%1', "Order Date");

Was this reply helpful? Yes No
FongFong 5 on at

Like (0)

Report

Hi!

Thanks for the reply. The sales price field is a flowfield. Can the Setfilter function be used with a flowfield?

Thanks

Was this reply helpful? Yes No
Suggested answer

Mohana Yadav 61,001 Super User 2025 Season 2 on at

Like (0)

Report

You mean Starting Date and Ending Date fields?

I dont think they are flowfields

Was this reply helpful? Yes No
FongFong 5 on at

Like (0)

Report

Hi,

I am currently using flowfield to lookup the sales price in the sales price page. In addition I would also like to have a filter like "Order Date" >= "Starting Date" || "Order Date" <= "Ending Date" to filter the correct price based on the "Order Date" .

Is this possible?

Thanks

Was this reply helpful? Yes No
Suggested answer

Inge M. Bruvik 1,111 Moderator on at

Like (0)

Report

Is it a customer specific sales price you are trying to display?

I would recommend you look into this codeunit codeunit 7001 "Price Calculation Mgt." and see if you can find a function there that will get you the price you want to display instead of using flow fields only on dates.

Was this reply helpful? Yes No
FongFong 5 on at

Like (0)

Report

Yes. Its a specific sales price that I am trying to get. Below is my current code using flowfield in table extension:

tableextension 60300 SalesLineExt extends "Sales Line"

{

fields

{

// Add changes to table fields here

field(60300; SellingPrice; Decimal)

{

Caption = 'Selling Price';

FieldClass = FlowField;

CalcFormula = lookup("Sales Price"."Unit Price"

where("Item No." = field("No."),

"Variant Code" = field("Variant Code"),

"Currency Code" = field("Currency Code"),

"Sales Code" = field("Sell-to Customer No.")));

}

field(60320; OrderDate; Date)

{

Caption = 'Order Date';

FieldClass = FlowField;

CalcFormula = lookup("Sales Header"."Order Date" where("No." = field("Document No.")));

}

}

Selling Price field will always return the first row of the price page. I would like to add in the Order Date filter so for e.g: Order Date = 06/02/2021, then it will return the price of the first row. If Order Date = 06/07/2022, then it will return the price of the third row.

Thanks

Was this reply helpful? Yes No