Error when trying to link a Flow Filter to a Normal Field in a Query.

dvdCasey1997
dvdCasey1997 Member Posts: 7
in NAV/Navision Classic Client
Hi All,

I'm a Developer and quite to to using Dynamics NAV. At the moment I'm trying to set up a query which uses the sum of a field on the Item Ledger Entry table, linked to the Item Table (via. Item Number), and the Location Table via. Location Code. The Item table is Linked the Location table using the data item link 'Location Filter=Location.Code'. Once this query is ran, I get the following error: "The following SQL error was unexpected: Invalid column name 'Location Filter'. Statement(s) could not be prepared." My theory is that (in a query) Flow Filters can't be linked to normal fields in a query (or at least can't be used as a Data Item Link). Is my theory correct and if (or not so) is there a solution to this problem?

NOTE: All 3 Data Items are Necessary in my Query to achieve the results I desire.

Many Thanks.

Best Answers

  • dvdCasey1997
    dvdCasey1997 Member Posts: 7
    Answer ✓
    Thank you very much for the suggestion.

    I'm afraid it doesn't solve the issue. I've probably explained this poorly, apologies. What I would like my query to do is show the sum of a certain field from the Item Ledger entry table, for every Item, based on a certain location.

    I think I will just try to create a function using the Item No. & Location Code as parameters to achieve the desired result.

    Many Thanks Again.
  • dvdCasey1997
    dvdCasey1997 Member Posts: 7
    Answer ✓
    Okay, so one of the other members of my Development team was actually able to figure out the issue (I now feel extremely silly :s) it was just a matter of having both the item number, and relevant Item Ledger Entry field as columns in the query which are if the Item Ledger Entry DataItem. Then, to group the relevant ILEs by the Item No. we simply made the Quantity column a method of type sum.

    Apologies for the poor explanation beforehand.

Answers

  • vaprog
    vaprog Member Posts: 1,162
    I'm not clear what you want to achieve with your query. Anyway, a FlowFilter field is a virtual field which might hols a filter during runtime. The fields does not have a value and does not exist on SQL Server.

    You might be able to use a FlowField in the DataItemTableFilter property, and it will be used in calculating FlowFields.

    You might want to link the Location record to the Location Code field in the Item Ledger Entry, and possibly set a filter on that same field in your query, as an Item is not usually associated with any particular Location (other than possibly by a Stock Keeping Unit, and indeed, the Item Ledger Entries).
  • dvdCasey1997
    dvdCasey1997 Member Posts: 7
    Answer ✓
    Thank you very much for the suggestion.

    I'm afraid it doesn't solve the issue. I've probably explained this poorly, apologies. What I would like my query to do is show the sum of a certain field from the Item Ledger entry table, for every Item, based on a certain location.

    I think I will just try to create a function using the Item No. & Location Code as parameters to achieve the desired result.

    Many Thanks Again.
  • dvdCasey1997
    dvdCasey1997 Member Posts: 7
    Answer ✓
    Okay, so one of the other members of my Development team was actually able to figure out the issue (I now feel extremely silly :s) it was just a matter of having both the item number, and relevant Item Ledger Entry field as columns in the query which are if the Item Ledger Entry DataItem. Then, to group the relevant ILEs by the Item No. we simply made the Quantity column a method of type sum.

    Apologies for the poor explanation beforehand.

Categories