Options

Flowfield problem in 2009 only

afarrafarr Member Posts: 287
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.
Alastair Farrugia

Comments

  • Options
    afarrafarr Member Posts: 287
    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?
    Alastair Farrugia
  • Options
    matteo_montanarimatteo_montanari Member Posts: 189
    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
    Reno Sistemi Navision Developer
  • Options
    afarrafarr Member Posts: 287
    Thanks Matteo.

    That's a good suggestion, unfortunately I still get the same error.
    Alastair Farrugia
  • Options
    vijay_gvijay_g Member Posts: 884
    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)
  • Options
    afarrafarr Member Posts: 287
    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.
    Alastair Farrugia
  • Options
    stanpstanp Member, Microsoft Employee Posts: 13
    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.
    Stan Pesotskiy
    Senior SDE - Dynamics Nav Application - Microsoft

    This posting is provided "AS IS" with no warranties, and confers no rights.
  • Options
    afarrafarr Member Posts: 287
    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.
    Alastair Farrugia
Sign In or Register to comment.