linking g/l account table with dimension value table

mkpjsr

hi experts

i want to join dimension value table code column by extracting first four digit with g/l account table ledger no programmatically.


can i extract some characters of a table within the setfilter or setrange function during joining the two table.


plz guide me how i will do this..

mohana_cse06

Use COPYSTR function to copy first 4 letters

mkpjsr

plz mention the coding

mohana_cse06

Did you try anything?
Let us know if something doesn't work..

mkpjsr

i am trying to join g/l account table with dimension value table

i am writing in this way
setfilter(copystr(code,1,4),glacno)


it is giving an error a field from record variable is expected

Sog

setfilter(copystr(code,1,4),glacno)

Normally it's setfilter(field,filterstring)
You however are doing: setfilter(string,string)

rsaritzky

This is a two-part reply. First, to answer your question specifically:

I’m making an assumption about your statement “i want to join dimension value table code column by extracting first four digit with g/l account table ledger no programmatically.”

You’ll have to do this in C/AL code – you can’t do it in the lookup properties of a field.

Now - how are you using this functionality? I'm going to make an assumption here (If I assume wrong, please correct me.) For example, you want define a new field in the G/L Account table (or any other table) and set the filter to list any values that begin with the first 4 characters of the account number. My example assumes adding a field to the G/L Account table, but you might be wanting to do some sort of validation in another table, e.g. gen. journal line. In that case, the code would go into THAT table. So, if you define this field, in the OnLookup Trigger, you would write something like this (this also assumes you are looking to filter Dimension 1 values and your Dimension 1 name is “DEPARTMENT”):

Global Variables:
DimensionValue defined as type Record, Subtype “Dimension Value”
DimensionValueForm defined as type Form, Subtype “Dimension Value List” (Form 560)


OnLookup trigger:

DimensionValue.SETRANGE(“Dimension Code”,’DEPARTMENT’);\
// Next line sets filter on the Dimension Value table to anything that matches the first 4 chars of the account no.
// Note the syntax. The first parameter is the field you want to set the filter on. The second parameter allows you to
// create a custom string value for the filter. So assuming the account number is 120010, this sets the filter to 1200*
DimensionValue.SETFILTER(“Code”,’%1’,COPYSTR(“No.”,1,4) + ‘*’);

DimensionValueForm.LOOKUPMODE(TRUE);
DimensionValueForm.SETTABLEVIEW(DimensionValue) ;
IF (DimensionValueForm.RUNMODAL = ACTION::LookupOK) THEN BEGIN
DimensionValueForm.GETRECORD(DimensionValue);
// Assuming you want to store the Dimension Value in this table into a field called "Dimension Value", then something like:
"Dimension Value" := DimensionValue.”Code”;
END;
CLEAR(DimensionValueForm);


Note, that I may be assuming too much. If you just want to SET the value of the Dimension value to the first 4 characters of the account number, coding is much simpler. In fact, you don't have to filter at all - just do a VALIDATE, e.g.

VALIDATE("Global 1 Dimension Value", COPYSTR("No.",1,4));
If the Dimension value doesn't exist, you'll just get an error.


Now, the second part,

Depending on the functionality that you're trying to create, are you familiar with "Default Dimensions" on the Chart of Accounts / G/L Account Card? This allows you to assign any group of dimension values to each account in your chart of accounts. Then, anywhere in NAV that the account no. requires a dimension value, you may only enter/select those values that are on the list for that particular account. If you have a lot of accounts and/or a lot of dimension values, maintaining this list is cumbersome, so you might want to do a modification to update the list of valid dimension values automatically (i.e. any time a dimension value is added or an account is added.) But this might have an advantage in that you modify only the maintenance function itself, and not the functionality in every table where you want to do this type of validation.

Good luck!

Ron