Flow field reference to other table

Business Central/NAV Developers Forum
1

I have the following situation I am hoping someone can assist me with.

I need to create a new flowfield on the customer table. It is a sum on the amount field of the g/l account field. The table filter filters the g/l entry table for source type = customer and source code = customer.“no.”. I also need to filter for a particular G/L Account. Obviously I do not want to hard code this in the table filter (using Type = CONST) so I have added a field to the S&R Setup table in which the user can enter a g/l account no. I would then like to use Type = FIELD or Type = Filter to set my table filter.

I want to use this account no. as a filter in the flow field table filter - but as the flow field is on the customer table and you can not reference another table or use a variable in the flow field table filter, I am stuck.

My CalcFormula is currently Sum(“G/L Entry”.Amount WHERE (Source Type=CONST(Customer),Source Code=FIELD(No.)))

Any suggestions on a way to achieve this?

Thanks in advance.

Craig

2

I guess flowfield wouldnt work, you have to write the code for the filtration and the sum.

3

Sure this is pretty straight forward.

One thing many developers don’t realize, is that a flow field can be used as a part of the definition of another flow field. I use this quite a lot. Its especially useful since you only need table designer and form designer, no code required.

In your case just create a new flow field called “Sales G/L Account” and this is a LOOKUP to the field in the S&R table.

now just add this field as one of the components in the SUM formula that you have.

4

LOL! I had thought of doing this but had discarded the idea as I was sure you could not use a flow field in another flow field. Goes to show you should never assume you know anything!

Thanks David.

As a matter of interest, and in case anyone has a problem with this, when displaying the resulting flowfield on a card form I had to do a CALCFIELDS in OnAfterGetRecord which is normally not required when displaying a flowfield on a form.

Cheers

Craig

5

Never say never with Navision. [:D]

Also with the calcfields, you could put both fields on the form, which will then calculate them correctly just make sure that the first field has a lower control ID than the second, or it can get a bit confusing in List forms sometimes.