question about relationship with calculation field

[Deep Thought II]

hi all, i seem to have a problem creating a relationship between a calculation field with another calculation field, or a number to a calculation field.

the calculation fields returns numbers. is this something i am supposed to know that calculation fields cannot be used in relationships?

i tried to match a calc field and a calc field and it shows nothing, neither does a calc and a number. it only shows records if the relationship is a "X"...

[bruceR]

There must be some other issue. There is no problem relating calc fields.

[Deep Thought II]

hmm thanks, i'll investigate further...

[Deep Thought II]

ack... i still can't get it work... i attached a small file exampling my structure and layouts. the concept is as follows:

there is an Order form with child records. A date field on the Order form holds the last modification date of its child records by using "Max (child record's modification date)".

then there is a calc field on the Order form that "turns on" (becomes 1) when the last modification date is in the past 7 days from the present.

then there is another table, that shows a list of such "turned on" order numbers by holding a constant value of "1", which supposedly will show all those orders that's "turned on".

i tried making the Alert's constant "1" a Number or a Calc (number), both didn't work. and it shows no records...

can anyone help?

weird.zip

[SpudMan]

Verify that the results of the calculation are both numbers or both text. They can look the same and not be the same. Also, look for any differences in rounding.

[Fitch]

Well, since AlertStatus is and unstored field, that references another unstored field, it's not surprising that it fails when used on the right-hand side of the relationship. It is interesting that FM7 doesn't alert you as FM6 would have that the relationship is invalid.

I haven't worked out the solution, but... We need to get out of the FM6 mindset that relationships can only be "=" (left side equals right side). What I mean in this case is, I think you could get the effect you want by setting up some multiple-criteria keys with greater than ">" or something along those lines.

[Deep Thought II]

thanks... i am informed now.

but i tried all the relationships... and only "X" works

[Ender]

Deep Thought II,

Can you explain more about what you are trying to do with your Alert table. It's not clear why you need a relationship for the alert text. Why not just calculate the Alert text in the Parent table?

If you're trying to view the records that have been modified in the last 7 days, then calculate an AlertDate (Get(currentDate) - 7) in the Alert table and relate to Parent by: Alert::AlertDate <= Child::Last Modification Date.

[Deep Thought II]

Ender... i did what u said, but still didnt work

What i want to do is to have an alert table that shows which orders were modified in the past 7 days. as u know, this will have to be real-time~! so it must use a calculation field.

then in some situations, like as an user logs on into the database, he sees an user control panel and on the side it shows a list of modifications... using a portal... that's what i want to do, instead of opening another window, another layout, and use Find to find out... which works, too, but why not in a nice neat portal on a lay out?

[Ender]

It's a little "weird" because you have to relate to the modification date in Child, but on your Alert you want to show related records from Parent. Luckily this is pretty easy in FM7.

See the attachment.

weird.fp7.zip

[Deep Thought II]

ahh there it is~ thank you so much~