Value Is Filter

rohit44x

Problem:


In G/L Account table there is a Balance field..which is a flow field..

It's Calc formula is:

Sum("G/L Entry".Amount WHERE (G/L Account No.=FIELD(No.),G/L Account No.=FIELD(FILTER(Totaling)),Business Unit Code=FIELD(Business Unit Filter),Global Dimension 1 Code=FIELD(Global Dimension 1 Filter),Global Dimension 2 Code=FIELD(Global Dimension 2 Filter)))


It takes Amount field of G/L entry table ,,,,

Here on 2nd row G/L Account No. Field is compared to Totaling and ValueIsFilter is set to TRUE...

One thing is clear that by setting ValueIsFilter to TRUE is that it takes all values from range for eg..1100..1200

but how it compare to G/L Account No. is unclear... :roll:

If you have any idea please do explain...

Andwian

rohit44x wrote:

how it compare to G/L Account No. is unclear

It is:

rohit44x wrote:

Sum("G/L Entry".Amount WHERE (G/L Account No.=FIELD(No.)

kriki

[Topic moved from 'NAV Tips & Tricks' forum to 'NAV/Navision Classic Client' forum]

kine

G/L Account No.=FIELD(FILTER(Totaling)) means take field Totaling and use it as filter on "G/L Account No.". Value in totaling could be something like "10000..200000".

rohit44x

@kine...
that is k... but my problem is following:

G/L Account balance field--->Properties-->Calc Formula-->Table Filter (line no.2)

Field Type Value OnlyMaxLimit ValueIsFilter
G/L Account No. FIELD Totaling No Yes


Let us take account number 1110(Cronus) it has Totaling = ''

So,how Balance is calculated for it...why the above mentioned 2nd condition dosen't return FALSE??

kine

Ok, more details>

There are two filters on same field in the formula:
G/L Account No.=FIELD(No.) and G/L Account No.=FIELD(FILTER(Totaling))

Because the filters are applied in same filter group, the second filter will override the first filter, but only if it is not empty.

Result:

If "Totaling" is empty, the entries are filtered for same account no. as actual record. If Totaling have some value, it will be used as the filter and the first filter will be overwritten by this... ;-) Nice trick, isn't it?

rohit44x

@kine..

thnx for the solution... \:D/

Actually i was taking it different way....i was taking it like this..

G/L Account No.=FIELD(No.)
AND
G/L Account No.=FIELD(FILTER(Totaling))

-->

'1100'='1100' (OUTPUT:TRUE)
AND
'1100'='' (because totalling is empty) (OUTPUT:FALSE)

-->

TRUE
AND
FALSE

-->

FALSE( so...there will be nor record selected from G/L entry)