How can I set datefilter

icobani

How can I set a date filter flowfield in code ?

I m trying

EVALUATE(Customer."Date Filter",'0D..010101D');

but it is not run.

Igor_Beeone

Hello,

Customer.SETFILTER("Date Filter",'%1..%2',DateFrom,DateTo);

or

Customer.SETRANGE("Date Filter",DateFrom,DateTo);

or as it seems you're trying to do:

Customer.setfilter("Date Filter",'..%1',DateTo);

---
Flowfilters are using same rules as normal ones.
Br,
Igor Beeone[/code]

ufuk

or for a constant date:
Customer.SETFILTER("Date Filter",'..010101');

Sapphire123

Hi,

I'm trying to do something similar to this... I'm trying to filter the Date Filter for a Flow Field...

The Range I'd like is... WorkDate-30D..WorkDate; e.g. 08/01/07..08/31/07. It only works for me if I hard code it in for the table filter.

I’d like the flow field’s calfunction to look like this:

Sum("Item Ledger Entry"."Invoiced Quantity" WHERE (Item No.=FIELD(No.), Posting Date=FILTER(WorkDate-30D..WorkDate)));

My code looks like this...Item.SETRANGE("Date Filter",StartingDate, EndingDate);

My question is, where do I write this code, under which trigger?

I'm new to Navision programming... any suggestion will help - Thanks!!

-Sapphire

ufuk

Basically (but it depends on the case):

If the date filter doesn't change after the form opens then you can use OnOpen,
If the date filter is not table data (you can filter the flowfilter with a variable) then you can use OnAfterValidate

If the form is,
Card type: OnAfterGetCurrRecord (Item Card etc.)
Tabular type: OnAfterGetRecord (Item List etc.)

There are lots of possibilities

David_Cox

In Code to use the Filter ..01/01/07 or 01/01/07..

BaseDate := 010107D;

..01/01/07
Customer.SETRANGE("Date Filter",0D,BaseDate);

01/01/07..
Customer.SETRANGE("Date Filter",BaseDate,31129999D);

Sapphire123

Hi,

Thanks for the response!
… but, I think I wasn’t clear at explaining what I’m trying to do – and perhaps I’m taking the wrong approach to this problem.

Here is objective:
I’m modifying the “Item List” (tabular) form to display two fields:
Field#1 (No.71) Purchase (Qty)
Field#2 (Custom Field) Purchase (Qty) for the past 30 days

They are both Flow Fields. Purchase (Qty) is already in the Item table, so it was easy to add it to the form and no programming was necessary.
However, Purchase (Qty) for the past 30 days– requires me to set a filter on the date; i.e. I only want to see purchase quantity for the past 30 days.

This is my code to set a range on the Date Filter:
ToDate := WORKDATE;
FromDate := CALCDATE('-30D', ToDate);
ItemRecord.SETRANGE("Date Filter", FromDate, ToDate);

Since the Custom Field will only be using this date filter, I’m thinking of putting this code behind its field trigger, instead of a trigger that affects the entire form, such as OnOpen, etc. Moreover, it doesn’t work when I run the code behind the following triggers… OnAfterValidate, OnAfterGetRecord.

Any suggestions? Also, is there a better way to solve this problem? I’m sure this is an easy fix for someone out there – unfortunately, with limited experience in Navision programming, I don’t know how else to solve it expect via Flow Field. :-k Many thanks in advance!

David_Cox

You have a few options, you could add a second "Date Filter 2", use this in for your custom field.

SETRANGE("Date Filter",0D, Today);
SETRANGE("Date Filter 2", Today-30, Today);
CALCFIELDS("Purchase (Qty)","My Purchase (Qty)");
This will give you the two values, with drill down etc:

But you could also show the values from the same field using two records.
Use a variable Item2 and doing your 30 day filter and calcfields on this.

Item2.GET("No.");
Item2.SETRANGE("Date Filter", Today-30, Today);
Item2.CALCFIELDS("Purchase (Qty)");
Then just add a field with the source expression Item2."Purchase (Qty)"

There will be an overhead as Calculating Flow Fields slow the list forms, so test with lots of data.

Also look at the statistic forms, these use variables and code to calculate various period values.

David

Sapphire123

Hey David,

Thanks again for your solutions… but, I’m running into some problems.
I decided to go with your solution #1, i.e. specifying another Date Filter.

First of all, to clarify…
1. A Flow Filter allows the user to dynamically change the Table Filter part of the Flow Field, right?
2. If there are multiple Flow Fields within a single table using the same Flow Filter, e.g. “Date Filter”, then setting a range on this filter will impact all Flow Fields, correct?

1. Assuming the above is true, I created a new field called, “Date Filter 2”, (Class: Flow Filter, Data Type: Date) in the Item Table.
2. I modified the custom field’s (MyPurchases) calcfunction property to look like this:
Sum("Item Ledger Entry"."Invoiced Quantity" WHERE (Item No.=FIELD(No.), Entry Type=CONST(Purchase),Posting Date=FIELD(Date Filter 2)))
3. Finally, added the following piece of code to Form-OnAfterGetRecord trigger.

ToDate := WORKDATE;
FromDate := CALCDATE('-30D', ToDate);
ItemRecord.SETRANGE("Date Filter 2", FromDate, ToDate);
ItemRecord.CALCFIELDS(MyPurchases);

Unfortunately, it doesn’t seem to like what I’m doing. Date Filter 2 doesn’t seem to be affected by the SetRange function…
I’m sure I’m missing something really obvious here :? – please let me know… thanks a ton!

David_Cox

All what you have said is correct!

Firstly what is ItemRecord?

The form record is rec and your code should be

ToDate := WORKDATE;
FromDate := CALCDATE('-30D', ToDate);
SETRANGE("Date Filter 2", FromDate, ToDate);
CALCFIELDS(MyPurchases);

But all you need is this:

Form - OnOpenForm()
SETRANGE("Date filter 2",WORKDATE-30,WORKDATE);

If this does not work then:
Test your field first, remove the code then drop the field on the form and then add a Filter manually from the menu and see if the value changes.

David

Sapphire123

Thank you so much - It works now..

Btw, ItemRecord was a record type global variable referencing the Item Table, but like you say... I don't need to explicitly reference the Item table...thanks again!!