Hi Guys,

Doing a GV and need to incorporate a field from a linked or joined Table. I have grouped my invoice line items by category, but I need to display the category description instead of the category numbers to make the report complete.

Help..

Hello Edward,

You can't current;y do that without our assistance. You need a composite table that links the OELINE to the IC Category file. Are you generating this report of the current or historical order entry file?

If all you want to do is have a category description, it is possible to use a Named Items file in order to provide the 'Translation'. You could enter them manually via the Named Items function, and then use a Calculated Column to display the descriptions.

I am linking Adagio Invoices - Q/I History Detail Master table.

This is a report which has to be re-produced on the fly ((many many times)) can not ask clients to key in the Category Description.

Hello Edward,

How many Categories does the database have? How often are new Categories added?

Read about the =GETNAMEDITEM() function and create a list of the form (in a file or through the define Named items menu choice):

[NamedItems]
ABC=Category Description 1
DEF=Category Description 2

and then use the calculated column =GETNAMEDITEM({Item Category}).

Can you send me some details on how to connect the named items file- fields to my workspace.

What I did was put category code and description.

Calculated Worksheet is where I am coming up on the challenge.
Reading the manual again

The file containing the "named items" can be specified on the command line ("/n parameter") or in the defaults dialog.

Andrew can you send me a demo gv using the function in a calculated column. Just can not seem to get it to work. ((This one has stumped me.))

Hello Edward,

Here is a ZIP contianing a GV workspace and an Excel spreadsheet to show you how this works. Here's what you need to know:

• There are two Views in the workspace, one showing IN history detail lines that contain a Category, and a simple "DUMP" of the IC Category file. The history detail lines have a formula that displays a category description using the GETNAMEDITEM() function and selects only those lines that actually contain a category code.
• You need to create a file of the form of CatList.GVNames. An easy way to do this is to ExcelDirect the Category View, Copy the third column (Headed "[NamedItems]") and paste it into a text file with a GVNames extension. Load this file into your GridView workspace using Edit | Named Items (F3). (Its name can be specified on the command line so the user does not have to remember to do this using the "/N" parameter).
• Maintain as necessary.

Hopefully you can figure this out from the building blocks provided here.

Hi Edward,

Just a few notes:
<1> I'm not quite sure why he did it, but I don't believe that the "Cat" prefix that Andrew used in the actual list in the NamedItems text file "CatList.GVNames" in his demo example is "required". Now don't get me wrong, it is required due to the way he has configured the demo, I just did not want you to think that the prefix was a requirement in all uses of this function. Not that you would have thought that but some might.
<2> You'll definitely want to use the /n parameter but bear in mind that updating the text file and refreshing the loaded list during a GV session will not be totally automatic/dynamic so the users may need to be aware of this.
<3> Because of item <2> the questions raised above as to how often the IC categories have to be updated/maintained would be quite important to the effectiveness of the solution

Pete

Hi Peter,

You're right, the "Cat" prefix is not necessary for the solution, but I prefixed the string from the Category file with "Cat" so that it is obvious in the named item file that this refers to a category code (on the assumption that there may be other values in the file). In addition, it lets you trap an empty value with having to use an IF() statement.

Cat=** Empty entry **

since GETNAMEDITEM() returns and empty string if the value isn't found, you could also use:

which would highlight when the table needs updating.

Thank you Andrew, I now understand, had a bit of a glitch as I had not updated my test bed machine with the latest Adagio Invoice release version 9.0 so the INSalesDtls.gv was not opening.

Can't beat having a practical demo file.

Real time support like this sets Adagio Accounting into a class by itself.

One Love...