You have two overlapping questions posted here.
Yes a join table makes sense linking your messages table to a table of users where you'd have one record for each user. The "read", "unread" status that you ask about would be a field in this join table.
Yes I'm sorry.
How to count the "unread" messages, and what field hold this values knowing that every user could have a different number?
I have just such a message system set up in the solution that I maintain and update. In our case, it makes it easy for a user to click a button to pop up the record that serves as the context of the message. (Users are contacting a department for assistance with purchases that they make via a Purchase Order system.)
As to counting unread messages, much depends on exactly what you are trying to do. I've already indicated that you'd store the read/unread status in a field in your join table. From there you can count the records that are "unread" via that join table from many different contexts--whether you want a total over the whole table or just the unread records for a given user.
I suggest explaining in more detail exactly what sort of counts you need.
I explain you in detail.
I have a dashboard for every departement. The users access his dashboard with both a pc and ipad, so there are 2 kind of dashboard per departement. For Pcs there are two box with "events" and "messages" there are two portals linked to events and messages. Now the user can insert new message or event from a popup menu that is linked to a ui table with global fiels. (The ui is in french)
If the user click on "Envoyer a TOUS" that it means "Send to ALL", a script set every checkboxes to 1 and then "ENREGISTRER MESSAGE" button create variables and go to the Message table and create a new message and set fields to 1 (every message record have Title, Date....and 21 field to hold this value. If a recipient is unchecked, the script set "" to a field.
Now The ipad version of the dashboard not have portals but boxes like this:
The number should show the "unread" message and every user dashboard could have differents number.
In a db structure like you said USER----<UserMessages>-----MESSAGES how can i count the number of unread messages per user?
What i think to do, in my structure, is to count all records that have "" values in field for every departements, but i don't know how to count records that have a specified value ("" or Unread in this case).
With your structure how can achieve that?
Do you really want limit your project by doing a messaging system in ... FileMaker?
FileMaker is an excellent database no doubt, but messaging should, IMHO, be more generic.
When I wrote a real-time messaging system several years back, I used a method where any application that could send POST requests (including FileMaker, but not limited to FileMaker) enabled a group of users to get pop-ups in their browser and even see the last five messages and reply to any of them.
Software cost of that messaging system? Zero. Software cost for 50 users to use the system over 10 years? Zero.
We used all open source tools and a MySQL back-end to store all messages. We created the messaging system in three weeks. Customers still use the system today.
Just food for thought.
HOPE THIS HELPS.
ExecuteSQL ( "SELECT Count (* ) FROM UserMessages
\"_fkUserID\" = ? And
Status = 'Unread' ";
"" ; "" ; Users::UserID )
is one option.
A relationship to a different occurrence of UserMessages could also be used:
Users::__pkUserID = UserMessage|UnRead::_fkUserID AND
Users::constUnread = UserMEssage|UnRead::Status
constUnread would be a calculation field with "Unread" as its calculation expression.
Count ( UserMessage|UnRead::Status )
Will return the number of unread messages
Thank you very much.
I will try this way.
Il giorno sab 3 dic 2016 alle 18:24 philmodjunk <firstname.lastname@example.org> ha
reply from philmodjunk
in Discussions - View the full discussion
And next time please open a single thread for the same issue (complex).
Sure and thanks for all your help.
Il giorno sab 3 dic 2016 alle 18:40 erolst <email@example.com> ha
reply from erolst
in Discussions - View the full discussion