Applying datefilters

Somehow it’s fun. I think that someone with more than 17 years of NAV experience with development should know this… But the last couple of years programming has not been my main task in my job. Except right now, I really need to solve something ASAP. Not enough time to describe everything in details and have it done by our many excellent developers. It’s much quicker to do it my self!

But my issue is that I have a list of records who have two dates: Starting Date and Ending Date. And now I want to make a quick filter to see which records on my list are active (within the date range) and which are inactive.

In SQL it would be rather easy to make a script to solve this:

SELECT * FROM MyList
WHERE (StartingDate < Today) OR (EndingDate > Today)

This would show me the InActive records.

SELECT * FROM MyList
WHERE (StartingDate >= Today) OR ((EndingDate <= Today) and (EndingDate <> ‘’)

This would show the Active records.

But how to do this in NAV?

Hey Eric,

I know its gets frustrating sometimes when you know something but have suffered Skill Fade.

I think you need a setfilter for this one

For the first example

Setfilter(StartingDate,’<%1’,TODAY);

Setfilter(EndingDate,’>%1’,TODAY);

For the second example

Setfilter(StartingDate,’>=%1’,TODAY);

Setfilter(EndingDate,’<=%1&<>%2’,TODAY,0D);

I think that should work off the top of my head, I’ve not actually written it to test it

T

I don’t think it’s that simple because the SETFILTER creates a set of filters that must all be true in order to match records. The code that Tony gave would give you the SQL:

SELECT * FROM MyList
WHERE (StartingDate < Today) AND (EndingDate > Today)

Which is not the same thing as

SELECT * FROM MyList
WHERE (StartingDate < Today) OR (EndingDate > Today)

I think the only way to get a complete record set where you want records that match either one condition or another is to Mark the records that match each of the conditions.

This involves going through the records that match one filter and setting the mark, then going through the records that match the other filter and setting the mark. Then going through all the marked records and doing your processing.

Your right, it could be an issue dependant on the data.

If the date’s were just one field such as “Posting Date” it would be eaiser, just one setfilter.

I would use a temporary table of Marks though.

Apply the first filter, transfer all records to the temp table.

Apply the second filter than do a IF NOT(GET) on the temporary table.

Then working with the temporary table will be easier as there are no longer filters applied to the recordset, it can then be manipulated a lot easier.

T

That’s exactly my problem!!

I really can’t find a way to do this in Dynamics NAV in a way where it can handle this in my mind rather simple and very common statement.

And the suggestion about using MARK isn’t really my favorite way to program, when I have a list of up to 20000-30000 records!

I would love to use a temporary table, it would solve a few of my other issues also. But I’m only using Dynamics NAV 4.00 - and you need NAV 5.0 to be able to define the source table as temporary.

Hi Eric,

The Source Table as temporary as a feature in 5.0 is only to make life easier, you can still use a temporary table in forms and code without it.

Are you trying to show these records on a form, or pass them to a report or something?

T

You can put your records in a temptable and then launch a form on that table (also in pre 5.0) with this command.

FORM.RUNMODAL(FORM::“The Form”,tmpTheTempTable);

Thanks I actually never knew that!

But hey one gotta learn all life! And these last two weeks where I have been programming 200% of my time, I sure have learned a lot about temporary tables. And then apply it to a form with Expand and Collapse at the same time, that has sure been a challangen! But good to find out that the old fox still can write some great code! [;)]