Flowfield: Lookup Field A, where Field B (date) is max date

geri79

Hello,

I want to lookup the latest price for an item in my table "price history", which contains the fields "item no.", "entering date", "price".

I thought of having a flowfield in the item table which looks up the "price" in this table where "item no."="no." and date=max date

Is there a way to specify the max date?

Workaround:
A different solution could be a normal look up which alway takes the first value in the lookup table. But therefore I'd need to change the sorting to descending. No idea how to do that.

Any suggestions?

Thanks,

geri

DaveT

Hi Geri,

Look at the standard field 7383 Last Phys. Invt. Date on the item table. It's a flowfield using...

Max("Phys. Inventory Ledger Entry"."Posting Date" WHERE (Item No.=FIELD(No.),Phys Invt Counting Period Type=FILTER(' '|Item)))

Is this what you were thinking of?

David_Singleton

geri79 wrote:

Hello,

I want to lookup the latest price for an item in my table "price history", which contains the fields "item no.", "entering date", "price".

I thought of having a flowfield in the item table which looks up the "price" in this table where "item no."="no." and date=max date

Is there a way to specify the max date?

Workaround:
A different solution could be a normal look up which alway takes the first value in the lookup table. But therefore I'd need to change the sorting to descending. No idea how to do that.

Any suggestions?

Thanks,

geri...

Why are'nt you using the standard Navision "Sales Price" functionality?

geri79

Thx for the answers, but that's not what I was looking for...
I want the price, where date is latest date.

I not using standard functionality sales price as I need this functionality for several tables and this was meant as an example.

DaveT

Hi Geri,

Look like you will have to code this by setting filters. If you need to drill down on the value then add code to the ondrilldown trigger.

geri79

Thanks.
I decided to make a "normal" field in item table and link it with table relation to my price table.
In the price table "on vlaidate" I will try to update the field in the item table.

geri

Reinhard

what you can do is create two flow fields.

The first flowfield "Price Entered Date": (it MUST have an ID number smaller than the second flowfield)
MAX("price history"."entering date" WHERE ("Item No." = FIELD("Item No.")))

the second flowfield "Latest Price" references the first one:
LOOKUP("Price History".Price WHERE ("Item No." = FIELD("Item No."),"Entering Date" = FIELD("Price Entered Date")))

sylvainroy

Hi Reinhard,

I was able to work the Sales Price with a Date flowfield with the following formula:

Max("Sales Price"."Starting Date" WHERE (Item No.=FIELD(No.),Sales Type=CONST(All Customers),Currency Code=CONST()))

However, since Starting Date can be entered in the future, I would like to limit the Starting Date to "TODAY" but was not able to put that in the filter. Any idea how you could do that? The "ideal" formula would look like something like this (if it worked!):

Max("Sales Price"."Starting Date" WHERE (Item No.=FIELD(No.),Sales Type=CONST(All Customers),Currency Code=CONST(),Starting Date<=TODAY))

Thanks for any idea or suggestion

Reinhard

The only possibility that I know of is to create another field, this time a flowfilter. You can call it "Present and Future" for example.

On the form, on open, filter this field to "TODAY.."

SETFILTER("Present and Future",FORMAT(TODAY) + '..');

then use this field instead of the function "TODAY" in your flowfield:

Max("Sales Price"."Starting Date" WHERE (Item No.=FIELD(No.),Sales Type=CONST(All Customers),Currency Code=CONST(),Starting Date=FIELD("Present and Future"))

anyways I haven't given it too much thought if this is the best solution for your overall problem... just saying that this code should work in getting the proper information to flow through

absolutelyfreeweb

Reinhard wrote:

(it MUST have an ID number smaller than the second flowfield)

I tested this, and order in table doesn't matter. Order in the form matters.

ssmukhi

Reinhard wrote: »

what you can do is create two flow fields.

The first flowfield "Price Entered Date": (it MUST have an ID number smaller than the second flowfield)
MAX("price history"."entering date" WHERE ("Item No." = FIELD("Item No.")))

the second flowfield "Latest Price" references the first one:
LOOKUP("Price History".Price WHERE ("Item No." = FIELD("Item No."),"Entering Date" = FIELD("Price Entered Date")))

Though you posted this more than 4 years ago, I want to thank you for this as I was having a similar dilemna, and applied your solution and it worked.