I’m trying to create a new field in the location item table that will provide the sale qty total for the proceeding 12 months. I have a flow field that gives total sales as follows: -Sum(“Item Ledger Entry”.Quantity WHERE (Entry Type=CONST(Sale),Item No.=FIELD(Item No.),Location Code=FIELD(Location Code))) I need to be able to enter a posting date filter that is minus one year to today. I am trying to give users a quick look at the 12 months sales when ever they look at the location item card, or list. I am an end user without developer licence, so I cann’t apply cal code to form to get the same information.
The Posting Date field needs to be added to the list of fields being filtered to calculate the total. This required a modification. Perhaps you may find other flowfields in the Item Location table that will suit your purpose. Try showing all fields on the form and see what flowfields you have available and which are affected by entering a filter for DateFilter. Mark Smart email@example.com icq: 128958106
Take a look at the Sales ($) field in the Item table. It appears to have all the filters defined that you have described. Even if your report is driven by the Location table, you could reference the Item table Sales ($) field for the desired data. Dave Studebaker firstname.lastname@example.org Liberty Grove Software A Navision Services Partner
Originally posted by daves: Take a look at the Sales ($) field in the Item table. It appears to have all the filters defined that you have described. Even if your report is driven by the Location table, you could reference the Item table Sales ($) field for the desired data. Dave Studebaker email@example.com Liberty Grove Software A Navision Services Partner
For a report I can do this without a problem, I’m an end user without a application designer license, so I can not access cal/code in forms. So,I can not apply the Date filter on the form, because I can not put it there in the first place. What I am hoping is to be able to add a flowfield in the underlying Location Item table that can have the date filter automatically put in for me. Then I can place this newly created field on the form I am using. Which in this case is the Location Item list. Then when ever a user opened the Location Item List they could see a running twelve month total sales. Hopes this helps explain what I am trying to do
I think I understand what you want to do and believe it can be done (one way or another). But I’m not sure which form object you are referring to when you say “Item Location Card”. I don’t find that one. What is the object #? If this is a form that is accessed from multiple places, what is your focus (i.e. from where do you expect your user to access this form with the new sales data)? Point of information: Even though you don’t have the rights to Application Designer, you can still create and change a fair amount of C/AL code if you have the other (lesser) Designers. I’m not exactly sure where the license limits are drawn, but I have experimented enough to know that with the Table, Form and Report Designer, you can actually code some meaningful amount of C/AL logic into tables, forms, reports and dataports. Dave Studebaker firstname.lastname@example.org Liberty Grove Software A Navision Services Partner
David M. informed me that Table Designer and Form Designer license rights do not allow any C/AL coding, only Report Designer and that only to Reports and Dataports. I wanted the share David’s information and correct my somewhat misleading preceding statement. Dave Studebaker email@example.com Liberty Grove Software A Navision Services Partner
Add a separate date filter field(that you can autofill if you wish) and your Sales Quantity flowfield to the Location Item table. The CalcFormula should be just what you had, except add (as previously suggested by Mark) Posting Date = Field (new date filter field). Add both the new fields to your Item Location screen. This will work until you try to enter a date filter range, then it will (at least in V2.60) tell you to add a key (Item No.,Variant Code,Location Code,For Type,From Type,Reservation Status,Valid-to Date) to the Reservation Entry table with a SumIndex for Quantity. I didn’t understand how the Reservation Entry table was involved, but I did what I was told. It worked. The downside is that I don’t see a way to autofill the date filter field without at least one line of code in either the table or the form. So my result leaves the user with that responsibility. I hope this is helpful. Dave Studebaker firstname.lastname@example.org Liberty Grove Software A Navision Services Partner