The Calcformula for the <FIELD> should start with

Hi there I have two flowfields in a table: one flowfield (Code,20) looks up a contact, the other (Text,30) looks up the name of that contact. When I try to filter on the second flowfield I get the error 'The Calcformula for the FLOWFIELD2 should start with ‘SUM(…’. All works well, except for the filtering… What am I missing here ?

That error message typically comes up when one tries to change a value of a flowfield. You might answer that you are not chganging, but only filtering. You are right, and you are not right. When (in a form) a filter is set, and there is no (more) record in the filter, and the form is set to “Populateallfields” and not to delyedinsert, Navision might try to insert that record into the database which leads into exactly the problem described. So please check: Do you get the error also when you are filtering directly in the table and not in a form ? Did you set these flowfields non-editable ?

Hello Thomas I checked the following: - Flowfields are non-editable - PopulateAllFields of the form set to NO - DelayedInsert set to YES The error message does not appear anymore, so that’s great. But now the filtering does not work correctly. When I filter on a certain value, also records are shown that have another value. When I click the line with the value that should not be in the filter, all other lines disappear and only the incorrectly filtered line remains… Strange ? Yes ! Could you please shine another light on this ? Best regards Josh

Hi Gazpacho, firstly just making th efield non editable should be enough. Filtering on a flow field is complex; filtering on a flow field that is calculated based on another flow field is more so. What is happening is the the system is caclulating the first flowfield, then using that value as a part of the formula for the second field which is then being filtered. The first issue is the control number. This is a field that is virtually ignored by most developers, but actually plays a role. If the second flow field has a lower number than the first flow field, then it will try to calculate them in the wrong order, and you will get some odd things happening, depending on exactly where you have current focus. So just clicking the mouse on various firleds will cause erratic changes. Since the code functionality is working prior to filtering, I don’t think this is an issue, but check it anyway. Now the big issue. Lets take your scenario. Lets call the fields ContCode and ContName. You are on a record, and the flow field ContCode returns the value C0025 and the ContName is ‘John Smith’ you then filter ContName on @john* and on the screen you see a number of records whos name starts with john. Now you scroll down, and this forces in Navision a NEXT(1) on the form. The server is now trying to apply filters to find the next record, but prior to that it needs to calcfields and work out what is what. This means that if you have a series of records attached to C0025 things should work fine, but if the ContCode changes, the logic starts to fail. ANd the record shown is somethign differnt to what you expect. Further to that, if instead of the Keyboard, you use the mouse or worse the scroll button, then the nexxt could be a NEXT(3) for example, the fields inbetween are not recacluated, and the result is again wrong. This is a brief explanation of the issue, and the only way you will really understand it is to really do an indepth experiment of how it works. When I first moved from DOS to Windows in Navision, one of the things I did, was to crete many many forms, and experiment with all possible scenarious to learn the fucntions. You really need to do this. By the way. In reality to make this function work as you want, you are going to have to either redesignt he data structure, OR do it with C/SIDE code. Good luck, and if you take this advise,a dn do some experimenting, then feel free to keep those questions coming.

Hi Gazpacho, firstly just making th efield non editable should be enough. Filtering on a flow field is complex; filtering on a flow field that is calculated based on another flow field is more so. What is happening is the the system is caclulating the first flowfield, then using that value as a part of the formula for the second field which is then being filtered. The first issue is the control number. This is a field that is virtually ignored by most developers, but actually plays a role. If the second flow field has a lower number than the first flow field, then it will try to calculate them in the wrong order, and you will get some odd things happening, depending on exactly where you have current focus. So just clicking the mouse on various firleds will cause erratic changes. Since the code functionality is working prior to filtering, I don’t think this is an issue, but check it anyway. Now the big issue. Lets take your scenario. Lets call the fields ContCode and ContName. You are on a record, and the flow field ContCode returns the value C0025 and the ContName is ‘John Smith’ you then filter ContName on @john* and on the screen you see a number of records whos name starts with john. Now you scroll down, and this forces in Navision a NEXT(1) on the form. The server is now trying to apply filters to find the next record, but prior to that it needs to calcfields and work out what is what. This means that if you have a series of records attached to C0025 things should work fine, but if the ContCode changes, the logic starts to fail. ANd the record shown is somethign differnt to what you expect. Further to that, if instead of the Keyboard, you use the mouse or worse the scroll button, then the nexxt could be a NEXT(3) for example, the fields inbetween are not recacluated, and the result is again wrong. This is a brief explanation of the issue, and the only way you will really understand it is to really do an indepth experiment of how it works. When I first moved from DOS to Windows in Navision, one of the things I did, was to crete many many forms, and experiment with all possible scenarious to learn the fucntions. You really need to do this. By the way. In reality to make this function work as you want, you are going to have to either redesignt he data structure, OR do it with C/SIDE code. Good luck, and if you take this advise,a dn do some experimenting, then feel free to keep those questions coming.

Are you applying the filter via code ? In this case you might just need a CurrForm.UPDATE();