I am looking for a way to get data from another table as the dataitem in the same line as the dataitem. How can this be done in printable reports. Does anyone have an “advanced Report how to” btw? The standard academy stuff is not really giving me enough info .
Please, could you explain your problem a little more detailed? Joerg A. Stryk Apollo-Optik, IT/ERP
You can refer to a Variable or even another dataitem by using TableVar.Fieldname in the SourceExpr. For example if you have a variable Item, you can put Item.Description in the SourceExpr for any dataitem and it will show the Description field for the current record in the Item variable. Chris Krantz NCSD,NCSQL,MCSD,MCSE Microforum Inc. Toronto, Ontario, Canada
If you are planning to use the field quite often, then there are two other solutions that I find useful; 1/ The obvious;…if the field you require has a direct relationship to the table you are in, then create a flow field. Even if there are multiple links, it will generally still work. By this I mean for example, say you need to print the actual Item card Description on a Sales document, create a flow field in table 37 or 111 113 etc. The chance of a Resource and Item with the same number is slim, so normally it should not be a major problem. 2/ It is not too clearly documented (but read the manuals, it is there.) You can use a Function in a table exactly the same as you can use a field. SO what I normally do is create a function that calculates what you need, (remember to exit with the value). Then just put this function name in the source expression of the report. The major advantage of 2, is that as a developer, I can create the code required to calculate a value, and an end user can modify the report, use the code, but not modify it. (Of course you can do this may other ways, codeunits etc. but I have always found this to give the best balance for ease of use for a non developer to work with.) As a side note, in my many years of working with Navision, I have always found that putting code in the tables makes it much easier to maintain and upgrade than anywhere else.
3/ Read Record: If you need the Item description (only) simply do a ITEM.GET (itemno) in the OnAfterGetRec or the sales line and enter “ITEM.Description” as source expression of your report field. — After all, method 2 (function) is the best. Especially if you don’t just want to have a single field but a combination of fields concatenated in one string. Let me give you the following example: In fruit&vegetable market, you not only have an Item description but also additions to it like “kg/lb or pce. per container”, package, certification or country of origin. A sales line for a container containing 10 lb of BIO oranges from Califonia might look like “Oranges bio 10lb, USA, CA” You might also ship these oranges in 2lb bags, 5 per container: “Oranges bio 2*5lb bag, USA, CA” Or you sell 5 water melons from Italy in an IFCO 623: “Water Melon 5 pce, ITAL, IFCO623” Depending on the existence of certain values (like emballage, certification etc.) You might need a function to create the output string for you. To go even further I would state: 2b/ create the function(s) in an external codeunit as the same logic might be needed for Sales-orders, Shipments and Invoices. ------- With best regards from Switzerland Marcus Fabian
Hi Marcus, of course you are 100% correct, but there are more advantages to putting the function in the source table, for example, if you want the end user to be able to put the field on a form, they can do that, again just create a field, then put the function name as the source expression. I have tried all the different ways, but in general, if you can put functionality in the table put it there. Typical functionality that I create is to put the Functions: “Remaining Amount At Date”, “Remaining Qty. At Date” and “Adjusted Cost (Inv. Qty.) at Date”; in Table 32, after this you can very easily make all the inventory valuation reports, Date dependent, also you can create forms with a date filter that will show inventory valuations at a date. Also the end use can create teir own reports. I don’t see many programmers doing this, but I think they should. What do you think? PS if you keep the names of the functions similar to the field names, then you can simply srag the filed onto the form/report, go to source expression, and then add… " At Date" to the end, and it is very fast.