Client >--< Referral >--< Service >--< Staff
I am a Tech, not a FMP expert.
What I am learning is that you can have a Global ID in each Table that relates them all together. Meaning you can put Fields from other Tables on a layout that work. Unfortunately, portals do not do this wide-ranging access - sorta limited to two Tables.
The question is which Table to use for generating a layout to access the fields you need. In some cases, a Join Table is created just for this purpose.
You might be asked for a screenshot of your relationships and a list of the fields you want included in your Find. You are sorta asking about a Dashboard (main display screen of current activity). About Dashboards - http://www.anvildataworks.com/?p=554/
We do need more information here. Exact information on your tables and relationships, how you log an "appointment offered" and how that data changes when it has been "kept". Are all necessary details that we don't yet have.
Apologies for not providing all of the information that is needed.
Here is how the tables relate:
Client to Referrals by the ClientID
Service Activity is related to Referrals by Referrals ID
Staff Activity is related to Service Activity by ServiceActivityID
The process for filling in the appointment goes in certain stages. The user starts on the Client layout which has various fields on it, they click a button which brings them to a Referral Layout.(Showing Table Clients) where they enter what referral they received and from whom in a portal from the referrals table. After this they have to enter the services which will be received from this referral so they click the line of the referral they want to enter service for. This is scripted to show the records related to the portal row they clicked. They click a button which brings them to the Service Activity (showing results from Referrals table) which has a portal from Service Activity Table. Here they enter the various services that will be involved for that referral. They then need to enter the staff activity that will occur within a service. Again, they click the line of the service they want to enter staff activity for and click a button which brings them to the Staff Activity Layout.
The Staff Activity Layout (showing records from Service Activity Table ) has a portal with Staff Activity on it. It shows the following fields:
StaffActivityID, StaffName, Appointment Type, Appointment Date, Appointment Time, and Appointmnet Status.
When a client is entering an appointment in advance of the appointment they just fill in those fields on this layout and leave the Appointment Status field blank. When the appointment date comes they then fill out the status which is a drop down list with Attended, Cancelled or Did Not Attend in it.
Basically I would like to be able to have a button which will bring me to a layout showing all appointments entered for this client (Regardless of what referral, service or staff it was associated with). The purpose of this is that since there are multiple services involved with a client the professional needs to be able to see what appointments are already offered to a client so that they dont schedule an appointment at the same time.
First to check my understanding:
Of course, your field names may differ from what I am using, but it appears that you have these relationships:
client ---< Referrals-------<ServiceActivity-----<StaffActivity
Client::__pkClientID = Referrals::_fkClientID
Referrals::__pkReferralID = ServiceActivity::_fkReferralID
ServiceActivity::__pkServiceActivityID = StaffActivity::_fkServiceActivityID
As I understand it, a record in StaffActivity represents an appointment and that you want to list the records from this table for a specific client where the status field is empty--indicating that this appointment has not yet been kept or cancelled.
Now a recomendation:
There are two ways that you can bring up this list in your pop up window. You can base the window's layout on StaffActivity and use a list or table view to display the records, or you can base the layout on Client and use a portal to StaffActivity to list the same records in a portal.
WIth either option, you can use GoToRelated Records to open a new pop up window listing all appointments for that client. If you use the list/table view option, your script can then enter find mode, specify a lone = in the status field and constrain the found set to drop out past/cancelled appointments. If you use the portal, a portal filter expression can drop out the same records from the portal.
That's great thanks for that. I didn't think it would be so simple!! I think my problem is I over-think things WAY too much. I went with the layout with the portal option.