Flowfield problem in 2009 only

afarr

We want to mark/unmark posted documents, to decide which documents to export to csv, and keep a record of which ones have already been exported.

Of course, we can’t modify posted documents, so I created the Boolean fields “Export” and "Exported" in the Sales Comment Line table, and then created lookup flowfields in the Sales Invoice Header table. That way, the changes get made in the Sales Comment Line table.

This worked in version 4.03, but not in Nav 2009 – it does not show the value, and when I try to change the field value I get the following error:

The CalcFormula for the Export FlowField in the Sales Invoice Header table must start with 'Sum(...'.

The flowfield has the following formula:

Lookup("Sales Comment Line"."Export" WHERE (Document Type=CONST(Order),No.=FIELD(No.),Document Line No.=CONST(0),Line No.=CONST(0)))

Any suggestions are welcome.

afarr

I thought that the problem could be that the CalcFormula is too complicated, so I created a new table, with just 3 fields:

Document No. Code 20
Export Boolean
Exported Boolean

In the Sales Inv Header table, "Export" field, I changed the CalcFormula to
Lookup("Export Settings"."Export" WHERE (Document No.=FIELD(No.)))

I get the same problems.

Are there lookup flowfields in standard Navision 2009 (or SP1 or R2), that work properly, or am I doing something wrong?

Is there a better way of marking/unmarking posted documents?

matteo_montanari

afarr wrote:

I thought that the problem could be that the CalcFormula is too complicated, so I created a new table, with just 3 fields:

Document No. Code 20
Export Boolean
Exported Boolean

In the Sales Inv Header table, "Export" field, I changed the CalcFormula to
Lookup("Export Settings"."Export" WHERE (Document No.=FIELD(No.)))

I get the same problems.

Are there lookup flowfields in standard Navision 2009 (or SP1 or R2), that work properly, or am I doing something wrong?

Is there a better way of marking/unmarking posted documents?

Hi

Change your formula to use "Exists" method.
something like:

Exist("Sales Comment Line" WHERE (Document Type=CONST(Posted Invoice),No.=FIELD(No.),Line No.=CONST(0),Exported=CONST(Yes)))

bye

Matteo

afarr

Thanks Matteo.

That's a good suggestion, unfortunately I still get the same error.

vijay_g

afarr wrote:

Are there lookup flowfields in standard Navision 2009 (or SP1 or R2), that work properly, or am I doing something wrong?

here is example..... :thumbsdown:

Lookup - table 17(GL Entry) field No. 76(G/L Account Name)
Exist - Table 4 (Currency) field No. 24(Cust. Ledg. Entries in Filter)

afarr

Thanks Vijay.

In the end, we didn't use flowfields, but added the Boolean fields in Sales Inv Header, and then created a codeunit with permissions to modify the table. We made the Sales Invoices form (143) editable, with all fields non-editable except for these new Boolean fields. In the primary key field, I set NotBlank=Yes, otherwise a spurious entry could be created in the form.

This is how standard Nav handles changes to the Cust. Ledg. Entries (Codeunit 103, and Form 25 - OnModify).

An alternative approach would have been to add Boolean fields in the Sales Comment Line table (or in a new table), and then check and change their values through code.

stanp

You could leave flowfield, make it not editable and add a button on the posted documet form that would change the value in Sales Comment Lines (or preferrably in other specific table).
How boring it should be setting Editable=No for all controls except one on the form.
I mean if Posted Document form is not editable let it be so, but add functions that may change some properties of that document. Like in your case - the flag of the export event. If it is stored in a separate not protected table you don't need to set special permissions and dedicate a codeunit to it.

afarr

Thanks Stan, that's a good point.
To make the fields non-editable, I exported to text and used "Find and Replace", but it's still not ideal.