Having trouble with relationships...

[biggles1212]

My goal is to have a notice system where each staff member reads the message and clicks a button saying they read it. A report is generated where I can see which staff members have read the notice and who is slacking off.

 

I have so far 3 tables.

Staff, Notices and Staff_Notices

 

Staff: ID, Name etc

Notices: ID, Date, Content etc

Staff_Notices: Name, Notice_ID, Status

 

I am very unsure where to go from here. The Staff_Notices table seems to be pulling information from two tables which I haven't done before.

 

Maybe I am on the wrong track. Maybe there is a template that could help me.

Thanks anyone for reading my SOS

[eos]

If you have

 

Staff --< StaffNotices >-- Notices

 

then the join table StaffNotices will have two foreign keys, one for the Staff member, one for the Notice; meaning your outlined table structure …

 

Staff_Notices: Name, Notice_ID, Status

 

will rather look like so:

 

staffNoticesID (primary key), staffID, noticeID (foreign keys), status (or maybe just a number field “read?”, with auto-enter value 0; set to 1 when read), and maybe a date field to indicate when the notice has been read.

 

Use the relationship to either side to display and use the related data; no need to copy anything.

 

On creation of a new notice, you need to create one join table entry per user/notice combination.

 

You could either perform an import of all Staff members into the join table, then set/replace the foreign Notice key with the ID of the new notice; or capture a list all Staff IDs (e.g. via ExecuteSQL()) and create the records in a loop that processes the ID list.

 

Now you can …

 

• create a summarised StaffNotices layout, search for records with status “unread”, then sort by name (related field from Staff) to get a list of “slackers”, or by message (title, date, etc. – related fields from Notices) to see a list of Notices that haven't been read by ass Staff members, etc.

 

• use this found set as a springboard to go the related Staff members or Notices records, and do … well, whatever you need to do.

 

• use portals into the join table on either parent layout to display unread messages (for a staff member) or “slacking” staff (for a notice), and/or use a relationship/portal filter to switch between all staff, busy guys and slackers

 

I am very unsure where to go from here. The Staff_Notices table seems to be pulling information from two tables which I haven't done before.

Maybe I am on the wrong track. Maybe there is a template that could help me.

 

This is very much a run-of-the-mill join table structure, for which there should be lots of helpful explanatory posts and sample files floating around.

[comment]

Staff_Notices: Name, Notice_ID, Status

 

Actually, the Staff_Notices join table needs only these fields:

• Staff_ID

• Notice_ID

 

Optionally, you could add a DateRead (or Timestamp), so that you can track how long it takes to get a message across. The Status field is redundant, because the join table should contain records only for messages that have been read.

[biggles1212]

I have followed everything up to this...

You could either perform an import of all Staff members into the join table, then set/replace the foreign Notice key with the ID of the new notice; or capture a list all Staff IDs (e.g. via ExecuteSQL()) and create the records in a loop that processes the ID list.

 

I understand the import staff records and replacing the foreign 'notice' key but am unsure how to actually achieve this and in terms of the SQL side of things I have zip experience with that.

[eos]

See here for a sample:

StaffNotice_eos.fmp12.zip

[biggles1212]

Thank you so much for helping with this. I couldn't have asked for as much help as you have given me!