how to make an empty field null?

[toolUser]

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

[Vaughan]

"...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?

[toolUser]

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

[-Queue-]

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.

[toolUser]

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