Relationship that shows records NOT in 2 other tables?

[R2D2]

Hi!

 

I have a problem... again...

 

There are 4 tables: Calendar (CAL), DutyWish (DW), DutyNotWanted (DNW) and personnel (PER). If a person wishes to work on a certain day, the record is added to DW-table. And same if he definitely don't want to work to DNW.

 

I wanted to show ALL those names in CAL-table that have NO wishes either in DW or DNW -tables (i.e. no records in either tables). But how can this be done with relationships? The CAL-table have dates and they can connect to dates in DW and DNW -tables. PER -table have names.

 

Thanks in advance!

[Wim Decorte]

Seems to me like you do not both a DW and DNW table.  One table would do, a join between PER and the CAL dates, with a dw and dnw field in the join table.

[R2D2]

Hi!

 

Thanks for reply! I'm afraid also that my model will not work. Maybe I'll just combine those two tables (DW and DNW) to one and add one field to distinguish wheter it is DW or DNW wish. I wonder why I did 2 tables at all... Maybe there was some idea then.

 

But thanks!

[eos]

I wanted to show ALL those names in CAL-table that have NO wishes either in DW or DNW -tables (i.e. no records in either tables). 

 

While Wim's suggestion for consolidating your two tables is very sound, I can't see how this solves your issue.

 

Assuming you have consolidated file Schedule as join table between Staff and Calendar, i.e.

 

Calendar --< Schedule >-- Staff

 

and that you are using IDs as relationship keys, rather than names, try creating a calculation field in Calendar as

Case ( 
  IsEmpty ( Schedule::id_staff ) ; 
  0 ; 
  List ( Schedule::id_staff ) 
)

and create a new relationship with

 

Calendar::cListOfScheduledStaff ≠ Staff_notScheduled::id

 

Now a portal into Staff_notScheduled would show you exactly these staff members.

[R2D2]

Thank you, Eos!

 

I tried your solution and it worked - once. I made a test bed for this and in there it did work. But when I tried to implement it to original file it doesn't. I think that I try to make relationships among wrong files.

So, to be sure, could you explain the correlations with names. I assume:

CAL --- Calendar

DutyWish (DW+DNW) --- Schedule

Personnel --- Staff

 

What is "Staff_notScheduled" you mentioned in that relationship?

[R2D2]

And here is my present relationship model. There is (for testing) both correlation and non-correlation relationships. Neither of these work. (I hate to be so stupid... )

 

[eos]

See if the attached file makes the idea clearer.

RecordsNOTIn2OtherTables_eos.fmp12.zip

[R2D2]

See if the attached file makes the idea clearer.

Ok... That makes it a bit clearer. But what is the difference in Schedule between id and id-staff -fields?

[eos]

what is the difference in Schedule between id and id-staff -fields?

 

One is the table's primary key (every table holding business data should have one), the other one is a foreign key – which you can tell by the fact that one field is defined as an auto-enter serial, and the other one isn't.

[R2D2]

Eos,

 

Thank you! I appreciate your help! Try to figure out that tomorrow. Propably it is best to build the whole system from a scratch again.