Distinct Flow Fields

rsummers
rsummers Member Posts: 9
edited 2002-02-26 in Navision Financials
I am trying to set up a count flow field with this goal in mind. Count how many disctinct occurences of a certain set of criteria happens. For example:

Table data:

Sale # Customer #
TS00001 C0000001
TS00001 C0000001
TS00001 C0000002
TS00001 C0000003
TS00001 C0000003


I need a flowfield set up to tell me how many distinct occuances of customer number there are for the sale number X (in this case TS00001). So the flowfield in this occasion should return 3.

In SQL you could use a SELECT DISTINCT, but no SQL is allowed here.

Any suggestions? <img border="0" title="" alt="" src="images/smiles/icon_confused.gif" />

Ron

Comments

  • wonmo
    wonmo Member Posts: 139
    When you add the field set it up as a flowfield in the field properties. Click on the assist button for "CalcFormula" and choose "count" for Method, and the relevant table and filter info. This should work really well for you.
  • wonmo
    wonmo Member Posts: 139
    Oops. I re-read your post and realize that the answer I gave you isn't what you were looking for. But I have an idea. Depending on how important this is to you one way that you could do this would be to create a table with the primary key consisting of the fields Sale# and Customer#. You could then add code to the OnInsert trigger of the table from which you want to calculate distinct customers. This code would just try to find the primary key values in the newly created table and would insert a new record only if the value were not found. Back to the original table with the flowfield, you would just set up the flowfield to point to the newly created table. Hope this helps and next time I'll read the post more carefully.
  • Matt
    Matt Member Posts: 20
    Just remember that Navision's base code does not always fire the OnInsert trigger. For example CodeUnit 333 inserts lines into the Purcase Line table with a simple INSERT, instead of an INSERT(TRUE).

    cheers,
    Matthew Benvenuti
    MCP,NCPS,NCSD,NCSQL,WSSL,WSSD,WSWD

Categories