In FM7 I have a join of four fields; building, floor, room, subroom.

Why break out subroom? Well, I've gone back and forth on that, I wanted it because it makes an FM find unique. But joins seem to fail when there is no subroom. Apparently you can't join to an empty field.

As:

BUILDINGFIELD1::bld <> BUILDINGFIELD2::bld

FLOORFIELD1::fl <> FLOORFIELD2::fl

ROOMFIELD1::rm <> ROOMFIELD2::rm

SUBROOM1::srm <> SUBROOM2::srm

matches and joins table 1 to table 2.

But:

BUILDINGFIELD1::bld <> BUILDINGFIELD2::bld

FLOORFIELD1::fl <> FLOORFIELD2::fl

ROOMFIELD1::rm <> ROOMFIELD2::rm

SUBROOM1:[empty] <> SUBROOM2::[empty]

doesn't seem to match, the empty field problem.

In most databases you can choose to consider an empty field as a null field, that is; it has no value, but is not empty, therefore:

BUILDINGFIELD1::bld <> BUILDINGFIELD2::bld

FLOORFIELD1::fl <> FLOORFIELD2::fl

ROOMFIELD1::rm <> ROOMFIELD2::rm

SUBROOM1::null <> SUBROOM2::null

Would match.

Which would solve my problem, I think, without rebuilding my tables.

How can I get this to work in FM7?

Thanks for any help.

Jerry

"...consider an empty field as a null field, that is; it has no value, but is not empty...

I'm sorry, but this makes no sense to me.

Maybe the IsEmpty() function will help?

Ok, how would using the IsEmpty()function make the join work?

Somehow use it in a script in the Define Database Relationships? I haven't seen that done.

Jerry

You can create a relationship to an empty field, but a <> relationship requires that the child field not be empty or else it will not evaluate. This is a pain; I know. But there is a fairly simple workaround. Create another calculation field of

If( IsEmpty(SubRoom::srm); " "; SubRoom::srm )

and make this your join field.

Thanks.

I've did a workaround, Entered "~"'s in all the empty fields... Well, they're pretty static, not many rooms morph in this building.

Jerry