Hello all,

I'm in a bit of a quandary and would like to ask the experts here for their opinions.

I'm cataloging the placement of art in many rooms of many buildings. The "room" table has ID fields for each of the 4 walls which comprise it. The "wall" table has an ID field for the room. We can't have orphaned walls (walls that aren't assigned to rooms), or rooms without 4 walls, so those ID fields are required.

There's my circle.

It seems like I'm going to have to remove the "not null" requirement for the wall ids in "rooms" because the logical progression of data entry dictates that rooms are added before walls. Removing that not null requirement will allow the the user to save the room record before adding and assigning walls.

An interface is only as smart as its most clueless user, so what would you gurus suggest to prevent orphaned walls? I was thinking about the possibility of forcing a lookup in each of the wall fields in "rooms" after the wall record was saved to make sure it's listed somewhere (and if not to show a selectable list of rooms), but I don't know how to do that.

Would anyone know how to do that, or are there easier ways?

Thanks!

Jasmine

It seems to me that since all 4 walls exist for a room, that you could create the 4 walls via a "New Room" script. Then assign whatever stuff to each.

Thank you very much. I'm new to FMP (but not to db programming), so I'll check out FMP's scripts. Thanks for the lead!

I wonder if the wall table is even really necessary. As Fenton pointed out, every room has four, so why not just create a wall field in the art table. The art record would have the room id and then 1,2,3, or 4 for the wall number.

Thank you very much for your response. The wall table is necessary because each wall also has specific data associated with it.

OK well in that case, I don't see the point of storing the four wall IDs in the the room record. Store the room ID in the wall record instead.