Portal Records and Date Calculations

[kseidule]

I have a portal of task records, containing Done, Description, Date to be done, Time to be done. They are sorted by Done, Date, Time, Description.

OUTSIDE of the portal I want to have text on the layout that says something like:

"There are 4 tasks due today"

"There are 5 tasks past due"

"There are 7 incomplete tasks" <-- I have this one using Summary and Calc fields.

I don't know how to count the records in the portal that are past a certain date and due today.

Any help would be appreciated. I am new at this and would like you thank you for all the stuff I already have found here.

Regards,

Kevin

[ESpringer]

Set up relationships between global fields or calc fields in your home layout's table and any relevant items in the tasks table. You'll need multiple occurrences of your task table. Even though your portal only uses one table occurrence, the other table occurrences are necessary to get your counts.

So, a calc field (c_Today) in the home table holds Get(CurrentDate) and a *double-match* relationship (neat FM7 feature) connects to a duplicate version of the tasks table (called, say TasksDueToday) using a match between c_Today and TasksDueToday::DueDate along with whatever your portal currently uses to match. This table occurrence is there just to help you get the count you need. To count how many items fit this relation, use a calc in the home table like this:

Count(TasksDueToday::DueDate)

So far, this gets ALL tasks with today as due date. To limit it to UNDONE tasks due today, have another field in Tasks table which just shows the due date IF the task is not yet done. Call it, say, c_DueDatePending.

Actually, you can use the same home-table c_Today field to help get the "past due" tasks. Again, use another table occurrence (called Tasks Past Due) and this time make it match whatever basic criteria you need, further constrained by g_Today > TasksPastDue::c_DueDatePending.

It's possible that some folks have better ways of managing the home-table's Today value: you could also set it up as a global field that updates somehow, though I'm not sure about the advantages and disadvantages of that.

[ESpringer]

Perhaps you already realize, but also: to get a neat appearance for the counts outside the portal, insert merge fields into your counts, rather than field-boxes. You may also know about the @@ trick for getting your portal rows to number themselves, so that a count of all tasks in your portal is also available at a glance...

[kseidule]

Thanks! I knew about the merge fields, not about the @@. Much appreciated for the solution. I am working on implementing it right now.

Regards,

Kevin

[kseidule]

OK, the solution you gave me is not working, color me stupid. Let me try and explain more clearly...

I have a contact table with a key field.

Each contact can have multiple tasks associated with them (the task table, also key'd on the contact).

If I make a separate task table to hold these dates, how do I get the data the user entered in task table 1 (as in the due date) also into task table 2, so I can do what you say.

I've attached some screen shots so you can see what my layout looks like and what my relationships look like.

If you look at the screen shot, I want just like the "You have 2 incomplete tasks for this contact" something like "You have NN tasks past due" and "You have NN task due today", etc.

I tried making a separate table but I dont' get how the dates due today get into the table 2. I also tried making a self join relationship that creates a 2nd table and couldn't get that to work either.

Sorry for the stupidity, I am sure at some point the light will go off.

Thanks again!

Kevin

[ESpringer]

Ah, you need to read up on the differences between Tables and Table Occurences. This is a big conceptual stumbling block in FM7. But it's also the backbone of its versatility. The basic deal is that there is only one TABLE caled tasks (in your left "Tables" tab in Define Database) -- only one set of records where info on tasks is being stored. However, because you might need multiple relational paths *to* that same table-worth of information, the "Define Database: Relations" area allows you to create multiple *representations* of the same table. These are each called Table Occurences (though the little button ad text at the bottom left of the graph area misleadingly looks like it's inviting you to add a table). Table Occurrences should be named in ways that help you understand their importance relative to the tables they're connected to in the graph, especially the tables from which you'll be likely to view them.

You may want to read some of the articles in these forums here about table occurrences. Looking at your table graph, it looks like you may have created many more tables than necessary -- instead of storing all your basic info about a contact in one place, you've got tables for what would ordinarily be *fields*. Now, it may sometimes be wise to have phone numbers or somethnig like that in a separate table, since one person may have quite a list of phone numbers of various kinds. Likewise with children. But birthdays? Most people have only one. So, put the birthday info right in your main Contacts table, and put anything else there that generally doesn't have too much complexity.

Then, there will also be just one Table for Tasks, and one connection will just match the Contact_Key between Contacts and Tasks. Another one, though, will use additional global fields or calc fields in Contacts (you'll need to define those fields) in order to create "filtered" variations on that original relationship. Each variation on the relationship will use another Table Occurrence. You'll start out by "drawing" the same relational link as the first one has, but then double-clicking on the "equals"-box along the path to edit the relationship by adding conditions...

Once you are clear on Tables ("base" tables) vs. Table Occurrences, you should be able to set up these multiple "virtual copies" of the tasks table, each one connected to the main contacts table, but constrained by different rules as my first post tries to outline. Feel free to ask more...

[kseidule]

Wow. Thanks so much. I think I need to go do a little cramming here on Tables and Table occurances.

I now get what you are talking about, but I'll go bone up and get this thing working.

But I do get it now, and I will read up and then check it out.

Again, I much appreciate you taking your valuable time to guide me through the forest.

Many Regards,

Kevin