Do I need a Portal for this??

[duncs]

I am trying to create a Real Estate database. For each record (client) there are approximately 10 date fields which I need to monitor.

Do I need to create a portal to display the list of dates in chronological order for each client? I am also hoping to have a "summary calendar" which lists all the upcoming dates for all clients on one layout, again in chronological order.

Any advice would be grateful, I'm really struggling to work out how I can do this.

[Ender]

Hey Man, don't think of it like you've got to go to the dentist. Portals are a good thing!

But yes, this sounds like an appropriate place for a portal of related Dates.

Your Client table should have some sort of ClientID field with an auto-entered serial number. And the Dates should have a matching ClientID field and a Date field (and possibly additional fields if you wish to track more details about what's happening on those Dates). The two tables would be related via the ClientID fields, and in the relationship definition, you should probably check the option to "Allow creation of records in this table via this relationship" on the Dates side of the relationship. The portal itself can have the Sort option checked, sorting by Date::Date.

It's then going to be possible to have an overview of upcoming Dates for all Clients. This part is a bit more complex, and it will help us to know what kind of interface you have in mind for this.

[duncs]

Thanks For the quick response Ender! Portals aren't quite as scary as the Dentist (I created my first this morning!! (portal that is!)) However they are stilll quite a struggle for me!

I do have a client ID field so hopefully that will make things easier.

I am looking to have a portal area on a tabbed portion of the client layout which will list the upcoming dates for that client in order. They don't need to have any detail other than the field name as each of the date fields are labelled. (i.e Home Inspection Date, Closing Date etc)

The Layout for the summary portal, will again list the dates, and the field names (date names) , as well as the Clients Name, and possibly the purchased home address (another field). The summary needs only to list the addresses (in a portal - vertical - fashion) I'm not going to try to create a calendar or anything like that!

[Ender]

Hmm, your date examples have me changing my recommendation.

You would use a portal if you have a list of things that are all the same kind of thing, like Contact Notes or Scheduled Events.

But a Home Inspection Date and a Closing Date are really about distinct things that happen as part of a sale. They might be searched specifically, sorted specifically, and Status calcs might be determined by the values of each specific date. In this case, it's more appropriate to use separate fields for each different type of Date. These fields would probably reside in a Home Sale or Transaction table, where a Client might have many Home Sale records.

This does complicate the "list of upcoming dates". I don't know how flexible you are on the requirements for this, but would it work to simply show the current Status of the Home Sale, along with the next upcomming date? In any case, I think we'd need to have a little more detail about all the dates types involved, and maybe an example of what you have in mind for a result list.

[duncs]

I'm glad its not just me that thinks this is complicated! What I would like ideally is (I imagine) quite simple, its just programming it that is confounding me!

I would simply like a portal style list that details the dates that are relevant to the particular client. To confuse things slightly, not all dates are used for eash client, ie not everyone chooses to have a home inspection. So I would only want those dates that have data to be displayed. They would need to be listed in chronological order (descending).

Once that is done, I would also like a summary table which takes all of the active dates for all of the clients and lists them chronologically, in order to highlight the next upcoming action.

I don't anticipate having to search, or analyze the dates, simply display them.

Once again, any help would be appreciated!!

[aldipalo]

IMHO I would set up an Event Table. Relate it to your customer table by CustomerID. In the Event Table you have fields: /*of course theses are examples*/

Date_Set

Event >Set as a Value List and as a drop down or pop up

To_Do_Date

Completed_Date

Completed > "Yes"/"No"

/*blah, blah, blah Whatever

Place on Customer Layout as a portal. Make sure you set "Allow creation" in the TOG (Table Occurrence Graph under relationship tab)

Sort the portal in either ascending or descending order using the fields of your choice.

Now you can see all the events related to that customer. You can search on any of the fields in the portal. Set up a script that will take you to the related record in a different layout or whatever.

You can set a script to only view those records where there is an event within a specific date range. You can have a separate Layout that shows all customer events within a specific date range, etc.

HTH