Relating on an unstored calc?

[jhomer]

I have a small problem, im hoping someone can help me.

I have a table of requests that are made to a group (they are assigned a #). there is a status field in this table that is an unstored calculation that looks at start and end dates to determine is the request status is open/pending/closed.

I then have a 1 record table (called flag) that is related to the requests table.

In this table i have a Status constant set to "open" that is related to the status calc in requests. I also have another calcualtion in this table that is set to the users groups # which is related to the target group number stored in request. Thus the relationship between the two looks like

Req::Status = flag::Status

Req::TargetGroup = Flag::UsersGroup

The goal here is to be able to count the open requests for the current users group..

Im haveing all sorts of problems with this and i think its becuase im trying to relate on an unstored calcualtion...

can anyone offer some insight?

[Fitch]

You are correct, you can't use an unstored calc as a relational key.

[jhomer]

So if i dont want to use a find...how does one go about getting the desired result?

[ThatOneGuy]

Not to pick on you, Tom, or to deviate from jhomer's situation ...

You are correct, you can't use an unstored calc as a relational key.

... but may I add "on the child side of a relationship" in the spirit of clarification for other readers?

We can build relationships with Global fields (which I think are "unstored" by definition) or with Unstored fields or calculations, so long as they're on the Parent (or "upstream") side of a relationship.

Do I misunderstand, or am I just buttin' in?... or both? It may not alter jhomer's challenge, but this distinction has helped me many times when working out structure. I hope it might help others.

Respectfully.

[jhomer]

nope seems like a fitting addition...although im still bangin my head on my desk

[Fitch]

In this case the unstored calc is on the child side, so it won't work. I should have been more clear about that.

I think you can achieve what you want if you simply use the dates themselves as the relationship criteria, since they are presumably stored. The unstored status field isn't necessary; you could still use it for displaying the data, but not for the relationship.

[pw]

the best way that I've found to deal with this is to set scripts to control the status.

When the ticket is closed the user would click on a invisible button over the date field that would allow them to enter the closing date and at the end of the script it would set the status based on your business rules.

On the start of the record you can have the default value be opened and set scripts for the other statuses. Thus getting rid of the status field need to look at the unstored calculation.