SETRANGE & SETFILTER function

knaabis · 2012-01-25

I have report from SalesLine Table and Item Table.
With Function SETRANGE i get record from Table Production Order Header (C/AL Global variable) called ProdDate (AvailableDate).
The problem is - in Production Order Header Table i need to get for respective Item Available Date, but there are many Available dates, because for this Item is many finished Production Orders, but i need only for not finished Production Orders.
How to make filter for this?

mohana_cse06 · 2012-01-25

Can you show your code and filter requirements..

knaabis · 2012-01-25

With this code i get record from Table - Production Order Header.

ProdDate.SETRANGE("Item No.",Item."No.");
IF ProdDate.FIND('-') THEN;

Prod. Order Created Date Item No. Status Available Date
11-368 14.09.2011 36-1046P-32-00A Finished 04.11.2011
12-038 30.12.2011 36-1046P-32-00A Partly Finish 03.02.2012
12-086 10.01.2012 36-1046P-32-00A 09.03.2012

I need to get the last one Available Date - 09.03.2012. because this order Status aren't finished, but if i use only SETRANGE, then i get first one - 04.11.2011.

Filter settings:
Production Order Status = '' (status is empty)

DenSter · 2012-01-25

One thing:
When I see a variable called "ProdDate", I expect that to be a Date type variable, and when I see that it is calling a table method I get irritated because now I have to figure out what table it belongs to, and it is another thing to keep in mind when reviewing the code or debugging the process. When you have a variable that points to the "Production Order Header" table, then you should call it "ProductionOrderHeader", or shorten it up and make it "ProdOrderHdr" or something like that. Calling it "ProdDate" is confusing, especially for people who come in after you that need to troubleshoot issues.

knaabis · 2012-01-25

Thanks for the advice!

But you have ideas regarding my problem? :-k

DenSter · 2012-01-25

Yes, you should study the development training material, and learn about filtering and retrieving data. Take a step by step approach of what you need to do, and think about that for yourself.

1 - retrieve data from Production Order Header, where status does not equal Finished, and filtered on a particular Item number
2 - sort by available date
3 - retrieve the last one

That there translates almost directly into C/AL statements. I'm not going to write your code for you though, so you'll have to figure that out yourself (or maybe someone else won't mind writing your code). Good luck

David_Singleton · 2012-01-25

DenSter wrote:

One thing:
When I see a variable called "ProdDate", I expect that to be a Date type variable, and when I see that it is calling a table method I get irritated because now I have to figure out what table it belongs to, and it is another thing to keep in mind when reviewing the code or debugging the process. When you have a variable that points to the "Production Order Header" table, then you should call it "ProductionOrderHeader", or shorten it up and make it "ProdOrderHdr" or something like that. Calling it "ProdDate" is confusing, especially for people who come in after you that need to troubleshoot issues.

I hope you have plenty of Tylenol at home, since banging your head against the wall can be pain full.

PS wasn't it you that told me I need to stop doing this. ](*,)

Savatage · 2012-01-25

knaabis wrote:
ProdDate.SETRANGE("Item No.",Item."No.");
IF ProdDate.FIND('-') THEN;
I need to get the last one Available Date - 09.03.2012. because this order Status aren't finished, but if i use only SETRANGE, then i get first one - 04.11.2011.

isn't that what ProdDate.FIND('-') does..it find the first record

did you try ProdDate.FIND('+')??

'+' ---> The last record in the table
'-' ---> The first record in the table

You can also throw in the SETCURRENTKEY Function.
Use this function to select a key for a C/SIDE table allowing you to sort the records before the find if needed.

knaabis · 2012-01-30

How i can use there SETRANGE? I need ProdDate.Status:: isEmpty, but how to do this?

CLEAR(ProdDate);
ProdDate.SETRANGE(Status,ProdDate.Status::<>Finished);

I need like this, but this not working...

mohana_cse06 · 2012-01-30

Please read the help documents..

SETRANGE and SETFILTER are the basics for Navision..

knaabis · 2012-01-30

There (Navision Help) I can not find a way to define an empty cell in SETRANGE function.
I need to filter out entries without Finished or Partly Finished in Status field.

mohana_cse06 · 2012-01-30

Use SETFILTER..

knaabis · 2012-01-30

I can't get positive result...
Please help me!

I need like this, but how?

ProdDate.SETRANGE(Status,ProdDate.Status::<>Finished);

David_Singleton · 2012-01-30

knaabis wrote:

I can't get positive result...
Please help me!

I need like this, but how?

ProdDate.SETRANGE(Status,ProdDate.Status::<>Finished);

Here is a step by step guide on how to do this:

(Before doing this you should go to C/AL Globals and rename ProdDate to ProdOrderHeader)

1/ Go to Navision developer code (F9) where you have SETRANGE and press F5.
2/ In the left column find ProdOrderHeader.
3/ In the second column find "Filter"
4/ in the third column find "SETRANGE"
5/ Press F1.
6/ Read understand and learn.

This will show you how to correctly use the SETRANGE function to do what you want. As an alternative you can also do the following

1/ Go to Navision developer code (F9) where you have SETRANGE and press F5.
2/ In the left column find ProdOrderHeader.
3/ In the second column find "Filter"
4/ in the third column find "SETFILTER"
5/ Press F1.
6/ Read understand and learn.

THen you can restructure your code to use SETFILTER, but in this case I feel SETRANGE is the better option.

Also feel free to use F1 as much as you want. It is free.

SETRANGE & SETFILTER function

Comments

Categories

SETRANGE &amp; SETFILTER function

Comments

Categories

SETRANGE & SETFILTER function