Calculation to show field entries for past 14 days.

[Rotorian]

Hello,

I really strugle with calculations in general, so need help.

I have unique field WO#s that is the relationship between two databases. I am using the wo# field to populate a value list for my user to choose the appropiate WO# on another database. However, I only want the value list to display the WO#s for the past 14 days.

I did nto see a way to create a list by calculation, so my only guess is to create a new field with shows only the last 14days of workorders and then define value list to show from this field??

Thxs.

[T-Square]

In FM7 and up, you could build a relationship based on both WO# and date, using a calculated date field in your second database to form the relationship. I mention this as a reason to consider upgrading.

In earlier versions of FM, you need to create calculation fields in both tables that evaluate to the same desired value (so that they match!). What I would do is create a Boolean (true/false) test for the date: is the date in question within the last 14 days? I would concatenate this with the WO#.

So, in the WO table, I'd have:

Cur_WO [calculated field]

WO_Num & "." & If(WO_date >= Today - 14, 0, 1)

In the second table, I'd have:

Cur_WO [calculated field]

WO_Num & ".1"

I set up the Boolean part as a decimal so that the WO's will still sort in order, and also to set that part aside in my mind. Note that the second calculation doesn't require any If test, since you only want current WO#'s.

Oh yeah--you need to create a relationship based on these two fields!

HTH,

David

[comment]

IIRC, the Today() function recalculates on opening the file, and that can take some time.

I would use a calculation field in the WorkOrders file (result is number) =

OrderDate

On the left side of the relationship, an unsored calculation field (result is text) =

TextToNum(Status(CurrentDate)) & "¶" &

TextToNum(Status(CurrentDate) - 1) & "¶" &

TextToNum(Status(CurrentDate) - 2) & "¶" &

TextToNum(Status(CurrentDate) - 3) & "¶" &

...

...

TextToNum(Status(CurrentDate) - 13) & "¶" &

TextToNum(Status(CurrentDate) - 14)

[Rotorian]

Thxs for the answers. A couple of questoins, on the upgrade bit, are the 5.0 databses importable to newest version?

Second, Comment, in your example:

I would use a calculation field in the WorkOrders file (result is number) =

OrderDate

On the left side of the relationship, an unsored calculation field (result is text) =

TextToNum(Status(CurrentDate)) & "¶" &

TextToNum(Status(CurrentDate) - 1) & "¶" &

TextToNum(Status(CurrentDate) - 2) & "¶" &

TextToNum(Status(CurrentDate) - 3) & "¶" &

...

...

TextToNum(Status(CurrentDate) - 13) & "¶" &

TextToNum(Status(CurrentDate) - 14)

I am not so sure what you mean by "left side of the relationship".

Ty.

[comment]

When you define a relationship, you are asked to match a field from the current file with a field from a related file. The "left side of the relationship" is the field from the current file (as specified in the left pane of the relationship definition).

[T-Square]

Comment--

Your concern about the Today() calc is valid; I am not sure how 14 CurrentDate evaluations work out faster, though.

My own workaround on the sluggish Today() was to have a small file (my Config file, with only 1 record) store the date in a global on startup, which doesn't take much time at all. Then the evaluation went much faster.

Ty: earlier files are importable into 8, but because of the radical changes involved, it's not always a bumpless ride.

David

[comment]

I should say first that I have never used the Today() function, so this is pure theory. The way I see it, an unstored calc returning a list of 14 dates evaluates instantly. If you had 14 records in WorkOrders, it would be a tie. If there were 14,000 of them, it would be a different story.

A global field is of little use here, since the result on the child side needs to be stored.

[T-Square]

Your theory is in fact accurate; I have seen the problem. I see how your approach would work faster.