Statistics and Value Entry Table

fvefve Member Posts: 28
Hi,

We have a customer that uses flowfields in the Customer Table for statistical analysis. This solution came from another Navision Partner that went bankrupt, and we were the lucky ones that inherited this solutioin \:D/

The flowfields have CalcFormula's that calculate several totals based on table Value Entry (5802).

For instance, a flowfield "Sales Amount" in the Customer Table that has the following Calcformula:
Sum("Value Entry"."Sales Amount (Actual)" 
WHERE (Item Ledger Entry Type=CONST(Sale),Source No.=FIELD(No.),Item No.=FIELD(Item Filter),Posting Date=FIELD(Date Filter)))

That way he's able to see the Actual Sales Amount based on multiple filters. So far, this works perfectly.

Now, our client wants 2 new things:
1. He wants an overview in the currency of the Customer. As we all know, Value Entries are in Local Currency only....
2. He wants another filter for the CalcFormula: Ship-to Code.

Both fields are not present in table "Value Entry".
Adding them, gives me an uncertain feeling about the correct working with Value Entries, but I do not see an other option at this moment. ](*,)

Any suggestions???

Comments

  • nunomaianunomaia Member Posts: 1,153
    To solve that problem you must forget flowfields :(

    You must use a statistics form or something to give you that kind of information.
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • ayhan06ayhan06 Member Posts: 210
    you need some customization to do this with standart calulated fields.
    1. "Ship-to code" must be added value entry table.
    2. since In every sales transactrion, this fields must be populated, you need add some piece of code in the posting routings (ie Item Jnl. Post Line codeunit.)
    3. "Ship-to code" field must be added to the key that is used for customer statistics (i dot remember which key you need to use, but i think it starts with "Source Type" and "Source No." fields.)
    4. a flowfield like "Ship-to code filter" must be added to customer table.
    5. calculation formula of needed fields like "Sales amount " must be changed in order to consider "Ship-to code filter" in calculation.

    Additional reporting currency can help you for customers whose currency is same as additional currency.. for other customers, you need customization again. with turkish localization, we have "Applied Currency Factor","Applied Currency Code","Applied Amount" fields in almost every ledger entry table.. this fields are populated as the amounts in currency used in the original document.

    these customizations are not as simple as i mentioned.. if they are really needed, do them with a experienced developer..

    or you can achieve these requirements by creating some report that will run very slowly.
  • MbadMbad Member Posts: 344
    What nunomaia suggest is a good solution. Will get you the data on demand and will not stress the system on general use(flowfields = stress on performance).
Sign In or Register to comment.