To create your tasks, you'd need these tables and relationship:
Clients::__pkClientID = Tasks::_fkClientID
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
If you enable "allow creation of records via this relationships" for Tasks in this relationship, a portal to Tasks on your client layout will allow you to add new related records simply by entering data into the bottom blank "add row" of this portal. (remove this option from the relationship and the "add row" no longer appears and you have to use other methods for adding records to the portal's table.)
But for your dashboard layout, where presumably you want to see all Tasks for all clients that are "Due", you'd need this relationship:
Dashboard::anyField X Tasks::anyField
To change the = into the Cartesian join operator, double click the relationship line to open a dialog where you can make this change. Literally any field can be selected as a match field on both sides as this operator matches any record in Dashboard to all records in Tasks.
A Portal to Tasks on a layout based on Dashboard will list all tasks records. But you want only those that are "due" for your attention. You can use portal set up to specify a portal filter that omits all Tasks records that are not due. This will likely need more than what I am first going to describe:
a Filter expression of
Tasks::DateDue < Get ( CurrentDate ) - 7
will limit the portal records shown to only those with a dateDue that falls on or before a date 7 days prior to today. But you'll probably want to see task records drop out of this list as you check them off as complete. So you might want to use something like:
( Tasks::DateDue < Get ( CurrentDate ) - 7 ) AND IsEmpty ( Tasks::Complete )
Okay, well I think I'm close now. But it's not working. So on the Client table, I'm already using the field _pkClientID to link to the _fkClientID on my Assets table. I linked the Tasks table _fkClientID to the Client table regardless but not sure if that would be a good thing. I'm assuming I want my Tasks table to include fields like Task Type (email, phone call, etc), Description, Due Date but the Dashboard layout is going to need to pull the Client info from the Client table. Where does that fit into the equation?
Also, so it's clear, I need / want to be able to input the Task Type, Descripton and Due Date on the Clients table... otherwise this doesn't work. Thanks again for your help (and patience)! The lightbulb is about to go off I'm sure!
I linked the Tasks table _fkClientID to the Client table
That's exactly the correct relationship.
Now I'll start from the bottom of your last post and work up:
I need / want to be able to input the Task Type, Description and Due Date fields on the Clients table
That's what portals are for. With this relationship:
You can enable "allow creation of records via this relationship" for Tasks (Double click the relationship line in Manage | Database.) and then add as many Tasks records as you need by entering data into the Task Type, Description and Due Date fields in the blank "add row" of the portal.
but the Dashboard layout is going to need to pull the Client info from the Client table. Where does that fit into the equation?
Remember this relationship?
To put this all together, you could set this up like this:
Then you can add fields from Clients to the portal row of your Tasks portal on the Dashboard layout in order to show information on each client that has at least one Task record that is "Due".
I'm following you. When I said, "I need / want to be able to input the Task Type, Description and Due Date fields on the Clients table" though, what I really meant was, "I don't want to have to use a portal on the Clients table because it's a lot less user friendly than having individual fields for each." I lose the functionality of being able to insert the date field as a calendar drop down as well.
Using the Tasks table seems to have added a layer of complexity I was hoping to avoid but it may be the only way. Would it be possible for the Task, Due Date, and Description fields on the Clients table to populate fields on the Tasks table along with Client Name, Email and phone number for use on the Dashboard Layout? (Talk about complexity!)
In summary, I want to be able to have a portal on the Dashboard that shows all upcoming events (from the Clients table) providing that the event is within 7 days of due. All fields in that portal would also be found on the Client table and would include Name, Email, Phone, the Task, Due Date, and Completed fields.
If I'm not mistaken, what you're having me do involves the use of an additional portal which isn't a very user-friendly method of creating a record (there are no labels after all), for access to an additional table which isn't being populated with all the fields I need. I'm sure I'm missing something here so bear with me. Thanks again for your help!
"I don't want to have to use a portal on the Clients table because it's a lot less user friendly than having individual fields for each." I lose the functionality of being able to insert the date field as a calendar drop down as well.
But that is not the case. you can still insert the date using a calendar drop down and you have a more user friendly interface rather than less. It can, in fact, look and function exactly like setting up a bunch of duplicate fields for entering data for multiple tasks, but with an added scroll bar that removes any built in limit to how many task records you create--making this a more flexible way to work with this data.
Using the Tasks table seems to have added a layer of complexity I was hoping to avoid but it may be the only way.
I don't see it that way. It's actually less complex in many ways than using a bunch of separate fields all in the client table. And it's not really any more complex for the user.
Due Date, and Description fields on the Clients table to populate fields on the Tasks table along with Client Name, Email and phone number for use on the Dashboard Layout? (Talk about complexity!)
Exactly, compared to using a related table with one record for each task, doing this would be nearly impossible using multiple fields in your clients table. Any attempt to set that up would require a very complex script and be very slow to update with new data. Any future design changes also become a nightmare as you'd have revisit this complex script with each change in your design.
If I'm not mistaken,
You are mistaken.
there are no labels after all
You can add any label to any field at any time that you are in layout mode whether or not the field is in a portal. It would be nice if we had the option for FileMaker to automatically add these labels when first setting up the portal, but you can easily add them after the fact--either using the layout text tool and tying them in or by double clicking the field while in layout mode to open the specify fields dialog and selecting the check box for including the field label. You can also skip the step for adding fields to the portal when first adding the portal and use specify fields or the field picker to add the fields just like you would any other field on your layout (just be careful to select the field from the correct table occurrence.) The typical place to add labels for portal fields is to put them above the portal to serve as column labels for the table of data in your portal, but the "table format" is not the only option for how you design the look of your portal.
which isn't being populated with all the fields I need
It would be populated with exactly the fields that you need--the foreign key match to clients, plus the other fields such as Task Type, Description and Due Date that describe that task. You would not add fields that describe the client here as that makes an unnecessary duplicate copy of this data. As I have previously described, with the correct relationship in place, your Dashboard portal row can include fields from Client to show the client name and any other needed details about that client.
We're almost there! How do I make the Date in the portal a drop down calendar? And, how do I make the Completed field a drop down that uses a Task List to choose the status. I'm thinking To-Do, Completed, Pending, etc. Or should the Completed field be a yes or no checkbox of sorts?
FOUND IT! Disregard my last. It was on that silly Inspector.
Alright, final question here... everything seems to be working properly EXCEPT: the only tasks that appear on the Dashboard portal to the Task table are ones that have a task field value of "Call." The others, email, foliow up, whatever, cause the field not to show up.
My ONLY filter option for that portal is ( Tasks::Day Till Due < 7 ).
Does the value of the field with "call" figure in the Day Till Due field in any way?
If you remove all portal filtering as a temporary test, do you then see the other records?
Yep, I got it. I figured out how to do the temporary test... that's been a huge help on portals.
Final question: Is there a way to click something like a "visit this record" button on the portal? Or maybe one of the fields, when clicked on visits the "related record"?
Look up Go to Related Record in FileMaker Help. You can put a button in the portal row that uses this step to pop up the portal row's record on another layout. At your option, you can set up GTRR to open a new window for this or it can just switch layouts.