# Lookup last in FlowField **URL:** \ **Category:** Developers Forum\ **Created:** [February 23, 2001, 8:31am UTC](https://www.dynamicsuser.net/t/lookup-last-in-flowfield/4823 "2001-02-23T08:31:17Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![FormerMember](https://avatars.discourse-cdn.com/v4/letter/f/74df32/32.png) [@FormerMember](https://www.dynamicsuser.net/u/FormerMember)\ **Post date:** [February 23, 2001, 8:31am UTC](https://www.dynamicsuser.net/t/lookup-last-in-flowfield/4823/1 "2001-02-23T08:31:17Z")
I have table1 (Inventory price): Inventory,Price group,Currency, date, Price; Table2: FlowFilters: Inventory filter,Price group filter,Currency filter, date filter; FlowFiled: Price; In FlowField price must be value Price of the last record; Lookup shows only first, max shows max price. HOW CAN I DO THAT ? CAN I CHANGE SORTING ORDER IN TABLE (NOT IN FORM) ? ---
**Author:** ![apertierra](https://avatars.discourse-cdn.com/v4/letter/a/bcef8e/32.png) [@apertierra](https://www.dynamicsuser.net/u/apertierra)\ **Post date:** [February 23, 2001, 10:15am UTC](https://www.dynamicsuser.net/t/lookup-last-in-flowfield/4823/2 "2001-02-23T10:15:08Z")
Maybe in this case a better aproach could be using a function that returns the value you’re searching for instead of using a flowfield. Ie. If you’re trying to obtain the last account movement total instead of showing the calculated value of your flowfield, you can show myrecord.showaccountlasttotal() function… – Alfonso Pertierra apertierra@teleline.es Spain ---
**Author:** ![fabian](https://avatars.discourse-cdn.com/v4/letter/f/f6c823/32.png) [@fabian](https://www.dynamicsuser.net/u/fabian)\ **Post date:** [February 23, 2001, 11:15pm UTC](https://www.dynamicsuser.net/t/lookup-last-in-flowfield/4823/3 "2001-02-23T23:15:12Z")
In the definition of the FlowField “Price” (Property CalcFormula) you should be able to use Method=Max. I didn’t try that but it should return the latest price. Marcus Marcus Fabian phone: +41 79 4397872 m.fabian@thenet.ch ---
**Author:** ![David\_Cox](https://avatars.discourse-cdn.com/v4/letter/d/e79b87/32.png) [@David\_Cox](https://www.dynamicsuser.net/u/David_Cox)\ **Post date:** [February 23, 2001, 11:25pm UTC](https://www.dynamicsuser.net/t/lookup-last-in-flowfield/4823/4 "2001-02-23T23:25:00Z")
Quote: In FlowField price must be value Price of the last record; Add an Entry No. Field to the Table and key and use the Max on that. Mr David Cox MindSource (UK) Limited Navision Solutions Partner Email: [david@mindsource.co.uk](mailto:david@mindsource.co.uk) Web: [www.mindsource.co.uk](http://www.mindsource.co.uk) ---
**Author:** ![FormerMember](https://avatars.discourse-cdn.com/v4/letter/f/74df32/32.png) [@FormerMember](https://www.dynamicsuser.net/u/FormerMember)\ **Post date:** [February 24, 2001, 11:29am UTC](https://www.dynamicsuser.net/t/lookup-last-in-flowfield/4823/5 "2001-02-24T11:29:56Z")
Marcus: Check this out: Inventory#Price group#Currency# date# Price 1#A#USD#01.01.01#5.00 1#A#USD#02.02.02#8.00 1#A#USD#03.03.03#4.00 MAX on Price will return 8.00 I need 4.00 David: Max on Item No will return Item No value I need Price. But idea isn’t bad to add new field item No. and sort on it: Item No#Inventory#Price group#Currency# date# Price 18000#1#A#USD#03.03.03#4.00 19000#1#A#USD#02.02.02#8.00 20000#1#A#USD#01.01.01#5.00 Now I can use Lookup. But code ON Insert triger in this table will be VERY COMPLICATED. I think that keys is very thin thing. Another problem: key A,B is needed, but A must be in ascending order B in descending. ---
**Author:** ![David\_Cox](https://avatars.discourse-cdn.com/v4/letter/d/e79b87/32.png) [@David\_Cox](https://www.dynamicsuser.net/u/David_Cox)\ **Post date:** [February 25, 2001, 5:40pm UTC](https://www.dynamicsuser.net/t/lookup-last-in-flowfield/4823/6 "2001-02-25T17:40:11Z")
Quote: Add an Entry No. Field to the Table and key and use the Max on that. Sorry lets have a quick rethink Polite Note: Try to use Navision Field Names when creating new tables fields etc: and also when asking a question. Use the Name “Entry No.” not “Item No.” Inventory is know as a flowfield on the Item Table use “Item No.” for reference to an Item instead! Table 1 Primary Key:=Entry No#Item No#Price Group Code#Currency Code# date Entry No#Item No#Price Group Code#Currency Code# date 124#1#A#USD#03.03.03 199#1#A#USD#02.02.02 287#1#A#USD#01.01.01 You do not need “Unit Price” in the key for this flowfield and you dont need the Max Table2 Flow Field “Unit Price” Calcformula LOOKUP Table1.“Unit Prce” WHERE “Item No.”=“Item filter”,“Price Group Code”=“Price group filter”,“Currency Code”=“Currency filter”,Date=“Date filter”; You should not need an “Item Filter” field you could link it by “Item No.” = “Item No.” Quote Now I can use Lookup. But code ON Insert triger in this table will be VERY COMPLICATED. Create a C/AL Global (Variable) of your table Add Code to the trigger OnInsert() TableVariable.RESET; IF TableVariable.FIND(’+’)THEN “Entry No.”:=TableVariable.“Entry No.”+1 Else “Entry No.”:=1; Hope the is clearer and is of a help to you! David Cox MindSource (UK) Limited Navision Solutions Partner Edited by - David Cox on 2001 Feb 26 11:36:59 ---
**Author:** ![FormerMember](https://avatars.discourse-cdn.com/v4/letter/f/74df32/32.png) [@FormerMember](https://www.dynamicsuser.net/u/FormerMember)\ **Post date:** [February 26, 2001, 7:05am UTC](https://www.dynamicsuser.net/t/lookup-last-in-flowfield/4823/7 "2001-02-26T07:05:40Z")
Thanks for notes, I’m using Lithuanian version of NF. Sorry for bad translation. About your code. David you forgot something: 1. if user will insert between two records. 2. If renaming date and another fields. I think that the best solution is to use functions, as Alfonso suggested. ---
**Author:** ![David\_Cox](https://avatars.discourse-cdn.com/v4/letter/d/e79b87/32.png) [@David\_Cox](https://www.dynamicsuser.net/u/David_Cox)\ **Post date:** [February 26, 2001, 10:34am UTC](https://www.dynamicsuser.net/t/lookup-last-in-flowfield/4823/8 "2001-02-26T10:34:06Z")
Quote 1. if user will insert between two records. Sorry The Code Example was for OnInsert not OnValidate This means the last Inserted record will always show unless you use a date filter. David Cox MindSource (UK) Limited Navision Solutions Partner Email: [david@mindsource.co.uk](mailto:david@mindsource.co.uk) Web: [www.mindsource.co.uk](http://www.mindsource.co.uk) Edited by - David Cox on 2001 Feb 26 11:39:27