Flowfield calcformula based on date range

sneha

Here is my scenario:

Table1 fields are: Item No. , location code, Rate Effective Date, Rate
Table2 fields are: Item No. , Location Code, Ship Date, Table1Rate. Table1Rate is a flowfield.

Table 1 data is:
Apple, US, 03/01/14,$5
Apple, US,03/15/14,$6

Table 2 data is:
a) Apple, US, 03/04/14,Table1Rate, I need to show it as $5.
b) Apple, US,03/15/14,Table1Rate, I need to show it as $6.

Condition to pull the Rate into Tabl1Rate flowfield is:
Table1.Item No.= Table2.Item No.,Table1.Location Code = Table2.Location Code,Table1.Rate Effective Date <=Table2.Ship Date.

How should I write a calcformula for the above condition?

Reinhard

Hi as you probably noticed, NAV isn't happy to accept < symbol in the flowfield definition.

One thing you might try is to add a new field to the Table 2 called "Price Date Filter"
This will be a text field. OnValidate of the Date field, put in '..' + FORMAT(Date) as the value.
Then your Table 2 data looks like this:

Table 2 data is:
a) Apple, US, 03/04/14,..03/04/14,Table1Rate, I need to show it as $5.
b) Apple, US,03/15/14,..03/15/14,Table1Rate, I need to show it as $6.

and the flowfield definition looks like this:
Condition to pull the Rate into Tabl1Rate flowfield is:
Table1.Item No.= Table2.Item No.,Table1.Location Code = Table2.Location Code,Table1.Rate Effective Date =Table2.Ship Date.


Alternatively, make it a function instead of a flowfield.
This would work well, but it doesn't allow you to filter, but filtering on a flowfield is kind of bad anyways.

- Reinhard

sneha

Thank You Reinhard!
A new function logic worked better than a flowfiled.