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

All responses (6)

Answers (0)

Sort by

Suggested answer

Mohana Yadav 61,248 Super User 2026 Season 2 on at

Like (0)

Report
Copy link

Link copied!

yes, you can use something like the below code

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

SetFilter("Ending Date", '>=%1', "Order Date");
FongFong 5 on at

Like (0)

Report
Copy link

Link copied!

Hi!

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

Thanks
Suggested answer

Mohana Yadav 61,248 Super User 2026 Season 2 on at

Like (0)

Report
Copy link

Link copied!

You mean Starting Date and Ending Date fields?

I dont think they are flowfields
FongFong 5 on at

Like (0)

Report
Copy link

Link copied!

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
Suggested answer

IB-29041624-0 1,191 Moderator on at

Like (0)

Report
Copy link

Link copied!

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.
FongFong 5 on at

Like (0)

Report
Copy link

Link copied!

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