Table Relation - with two tables..

Sapphire123

Hi,

I'm trying to convert a SQL query to a table relationship for a field's look up...

The SQL Query involves join between two tables ...

Select dimension value.code
From dimension value, general ledger setup
Where dimension value.dimension code = general ledger setup.shortcut dimension 4;

currently, i've hardcoded AREA which is the 4th Shortcut dimension on the General Ledger Setup card, so my table relation property reads,
"Dimension Value".Code WHERE (Dimension Code=FILTER(AREA))

However, I want the look up to change dynamically depending on the selected Shortcut dimension 4.

Any suggestions please?

kine

You can filter on field "Global Dimension No." which is 1 or 2, but this value is not set for 3 and up global dimensions (you can change that in code)...

Sapphire123

Yes, I noticed that. But - is it not possible to create a table relation using more than one tables?

Also - would you know which code assigns 'Global Dimension No.' to 1, 2, and 0..? My only concern is if I change the Global Dimension No. value for other shortcut dimensions, will it create other issues that i'm not aware of..

Sapphire123

Here's the Solution...

GLSetup.GET;
ShortcutDim4 := GLSetup."Shortcut Dimension 4 Code";
DimValue.SETRANGE(DimValue."Dimension Code", ShortcutDim4);
DimValueList.SETTABLEVIEW(DimValue);
DimValueList.SETRECORD(DimValue);
DimValueList.LOOKUPMODE(TRUE);


IF DimValueList.RUNMODAL = ACTION::LookupOK THEN BEGIN
DimValueList.GETRECORD(DimValue);
"Shortcut Dimension 4 Code" := DimValue.Code;
VALIDATE("Shortcut Dimension 4 Code");
END;


CLEAR(DimValueList);

DaveT

Hi Sapphire,

There may be another way to solve this which I have used before (not in the example).

Create a flowfilter for the dimension code
create a flowfield that is filtered by the flowfilter

get the shortcutdim4 from GL Setup

set the flowfilter by
setfilter( Dimflowfilter, shortcutdim4 );
and your flowfield should work

Worth a try