Date Field Calc problem

[Ben Ball]

Hi,

I have attached a small solution to highlight a problem that I am having.

Bascially I have a calculation field (DueDateforpayment) in the table called lookup which is calculating a date based upon certain criteria. This calculation is working fine.

what is'nt working is the relationship between the 2 tables. "main" to "lookup"...

I am trying to view all records between the two date fields "Date_from" and "Date_to" (in the main table) where all records will show based upon the date calc field in the "lookup" table.

For some reason I am getting no results showing up. I have checked everything and it seems its something to do with the calculation field. As if I change the calc field to a normal date field it will work ok.. but not if its a calcualted result..

Any ideas?

TIA

temp.zip

[LaRetta]

Ben, your DueDateForPayment in Lookup is an unstored calculation. An unstored calculation cannot be used on the 'child' side of a relationship.

[IdealData]

If the target field in a relationship is unindexed then it cannot be used in a relationship. The calculation CANNOT include:

A related field

Another field that evaluates based on a relationship

A global field

There are some other considerations, but the above are most likely why your relationship won't work when the date is calculated.

[Ben Ball]

OK,

Thanks for that as I now understand why its not working.

I suppose I have to post the value into the lookup table which will allow the calc field to be stored. Then if there any changes that affect the changeable field then I will have to run a script to update the related records.

Edited July 14, 2009 by Guest

[LaRetta]

I'm unsure what you mean, Ben. I didn't have time to consider what you were doing in your file but if you simply 'look up' the current payment terms for a supplier (when you create the Lookup record) then the calculation can be changed to using that new PaymentTerms field instead. Then it can be stored in Storage Options. (see attached).

BTW, why do you have dates in Main that aren't global? Shouldn't each User be able to look up their own date ranges? I assume this was quick demo file and not real one since the names aren't even real but I thought I'd mention the global issue as well. :wink2:

tempREV.zip

[Ben Ball]

Thanks, thats great.. The lookup field is the answer then..