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

shogan@mila.ie

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.

matttrax

Assuming your custom table has an Item No field.

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

Show ItemRec."Shelf/Bin" on the report

DenSter

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.

shogan@mila.ie

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

My number 1 solution is the same as Matt's, we posted it at the same time

shogan@mila.ie

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

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.