Report: Adding a field that looks up Item.'Shelf/Bin No.'

shogan@mila.ie
shogan@mila.ie Member Posts: 113
edited 2009-11-19 in Navision Attain
Hi everyone,

I have a basic report that looks up a custom table called Overload Locations, that values are committed to.

In the report that is based on this table, I'd like a field that 'looks up' the Item table's Shelf/Bin No. field in real-time, i.e. the values should always refer to what is current in the Item table for the Shelf/Bin No. field.

I don't want to commit the value of the Shelf/Bin No. field to the Overload Locations table.

If it helps, the DataItem info of the report is:
DataItem Name
Overload Locations <Overload Locations>


I am sure this is a very simple request, but I am just learning at the moment. I have exported the table and report into a .fob file, in case it helps.

;)

Answers

  • matttrax
    matttrax Member Posts: 2,309
    Assuming your custom table has an Item No field.

    OnAfterGetRecord:
    ItemRec.GET("Item No.");

    Show ItemRec."Shelf/Bin" on the report
    Matt Traxinger - ArcherPoint
  • DenSter
    DenSter Member Posts: 8,307
    Assuming you have a field called "Item No." in the Overload Location table, you could do it two ways:
    1 - program it, and set the textbox on the section to the Item's "Shelf/Bin" field 
    // Item is a record variable
// in the OnAfterGetRecord trigger of the Overload Location dataitem
Item.GET("Overload Location"."Item No.");

    2 - add a lookup flowfield to the Overload Location table that looks up the Shelf/Bin field based on the Item No. Make the field the same data type, set it to non-editable, set the fieldclass to flowfield, and enter "Lookup(Item."Shelf/Bin No." WHERE (No.=FIELD(Item No.)))". All you need to do then is add the field to your section, and put a CALCFIELDScommand in the OnAfterGetRecord trigger.

    If it is just for this report I would go with the code, if you're also going to use the lookup flowfield elsewhere go for the flowfield.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • shogan@mila.ie
    shogan@mila.ie Member Posts: 113
    I decided to use @matttrax's solution, as this gives me scope to expand to other fields on the item card if they are required in the report, simply.

    DenSter - I might try your solution on another report that I feel programming would be more applicable.

    Thanks guys for your help.
  • DenSter
    DenSter Member Posts: 8,307
    My number 1 solution is the same as Matt's, we posted it at the same time
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • shogan@mila.ie
    shogan@mila.ie Member Posts: 113
    Yes, I thought that but I didn't want to say anything in case I was wrong :)

    Again thank you DenSter, but your no. 2 solution might be useful for something else... I'll keep you posted :)
  • DenSter
    DenSter Member Posts: 8,307
    You're welcome, glad to hear you got that to work. Good choice, if it's for one report you don't really want to add fields to tables.
    Daniel Rimmelzwaan
    RIS Plus, LLC

Categories