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) ?
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
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
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 Web: www.mindsource.co.uk
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.
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
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.
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 Web: www.mindsource.co.uk Edited by - David Cox on 2001 Feb 26 11:39:27