Calc in a portal relationship

[merkaba22]

Still learning the basics:

File One

Job Number

Item

completion date

completiondatecalc IsEmpty(completion date) = 0 result is text

File two

jobnumber

completioncalc constant "1"

portal:

item

I would like the portal to only return those records of file one matched by job number where there is a completion date.

When I try to establish a relationship, completiondatecalc::completioncalc constant, I get an error message saying that using "completiondatecalc" will not work since it is not indexable ....

What am I missing here?

[RalphL]

Is Completion date stored?

I would think that youu would want completiondatecalc to be jobnumber & " " & not IsEmpty(completion date)

And

completioncalc constant to be jobnumber & " 1"

[Lee Smith]

Why not just use the Job number on the Right Side (Related file, your file one) and this calculation for the Left Side of the Relationship in your Calculation Field

completioncalc (Text Result) =

Case(not IsEmpty(completion date), Job Number)

If the date isn't filled in, you calculation will be blank, if it is, then the Job Number will show and complete the relationship in the Current File.

HTH

Lee

[RalphL]

Good idea Lee, much easier than mine.

[merkaba22]

Hey Lee and Ralph! Thanks and sorry for not responding earlier but my PowerBook died last week.

When I tried (what I thought you meant)

completioncalc (Text Result) =

Case(not IsEmpty(File one::completion date), Job Number)

And define a relationship:

CalcDB completioncalc=::JobNumber

The spinning disk appears and I have to force quit -- I have tried this several times on my 4 day old PB 1.5ghz 15" -- what's up here?

When I try Ralph's solution -- I can get it to work in a simple test file but when the completiondate is via relationship in a third file, it won't work and I get an error message, that it will not work since completiondatecalc cannot be indexed (and I made sure that it is not stored in the third file) .... Apparently, the relationship in the calc prevents this; is there a work around?

I made the completiondate field derive from file one and went on to the real DB -- when I create the completioncalc constant, the number "1" is displayed on the the next line of the field as if there was a carriage return -- accordingly the relationship fails. Of course, I have rewitten the field and it persists .... If I reverse the order, ie. calc + job number, it works!

Its been a wierd Sunday )