Daily Calendar Script

[raiders2814]

I've created a database for a law firm, and one of the functions that this database has is the ability to create a daily calendar. The daily calendar is responsible for displaying all of the current day's cases as well as some other information such as last name, first name, location, time, and notes about the cases. The script as is looks like this.

Go to layout["Daily Calendar" (Daily Calendar)]

Set Variable [$Today; Value : Get(CurrentDate)]

Enter Find Mode []

Set Field [Daily Calendar::Hearing Date; $Today]

Set Field [Daily Calendar::Location; Daily Calendar :: Location & "manhattan"] --only displays the cases that are located in Manhattan

Perform Find []

Set Records [Restore; No Dialog]

This script works fine, but there is a kicker.

As I currently have it, the Daily Calendar layout has its own fields to enter data in, and instead I need a relationship with my main table and my secondary table called "Case Info" (I thought I had one, but it doesn't seem to be doing much).

Long story short, because I want to modify this script to display data from both my main table and secondary table on the daily calendar layout (without having to enter data into fields that are in the Daily Calendar table), I need some help modifying the script and the table relationships for that matter as well.

To clarify--Data from my main table would be both the first and last names of the client. Data from my secondary table would be time, location, hearing date, and notes about the case.

Hope this is clear enough to understand because I could really use the help!

[comment]

Hope this is clear enough

I am afraid it's not. It seems you have a Clients table and a Cases table - what is the relationship between them? Can a client have more than one case? Can a case have more than one client?

I am not sure what is the role of the Daily Calendar table. Presumably, a case can have more than one hearing (and perhaps other types of events), so you need a Hearings (or Events) table with fields for CaseID, Date, Time and Location. Finding the events for today and displaying them along with fields from the related tables should accomplish your goal.

[raiders2814]

So currently the main table is called levine. The main table has the list of all the clients in the database, but holds only their biographical information (name, address, phone, and ID labeled ClientID).

The table levine is connected with a table called Case Info. The Case Info table displays all of the specifics about the case such (Summons# of the case, the type of violation, location of the hearing, the fine, etc). This is because one client is capable of having more than one case on different dates etc. The two tables are connected by ClientID identifier.

The Case Info table also has an identifier called CaseID. The CaseID identifies each specific case in the database. These are my two main tables of data input. When employees enter in new clients, it is in these two tables that they will add the new information.

This is where I believe myself to make an error though. I currently have a CaseID identifier in the Daily Calendar layout which is connected to the Case Info's CaseID. So Levine with ClientID connects to Case Info's ClientID, Case Info's CaseID connects to Daily Calendar's CaseID. So when new entries are added to both the levine layout and the CaseInfo table, the script for the daily calendar updates with the new entries. As of right now the Daily calendar function only works on the records that were established before I made the script.

[comment]

So currently the main table is called levine.

If you don't mind, we'll call it the Clients table in this discussion, because that's what it is. And the other table will be Cases - unless you have another table that stores non-info about cases... This is important, so that I can concentrate on the real problem here.

The two tables are connected by ClientID identifier.

Ok, that answers my first question: one client can have many cases, but each case has only one client. I still don't know what's the purpose of your Daily Calendar table and why you need this:

So when new entries are added to both the levine layout Clients table and the CaseCaseInfo Cases table, the script for the daily calendar updates with the new entries.

[raiders2814]

The daily calendar is for the lawyer. It's to get a print out of all the day's current hearings and some other relevant information to those cases. There should be no data input in this table, it should only be there to display information from the other two tables (Clients and Cases).

[comment]

There should be no data input in this table, it should only be there to display information from the other two tables

You don't need another table to display data from other tables. You can use a layout of an existing table, with data from the related table placed on the layout (if the other table has more than one related record, you would use a portal to show them).

I am still not clear on how and where you enter the data regarding a hearing.

[raiders2814]

I'm positive I need all the tables. As a finished product, you have employees enter in new clients into the database. Each client into the client table and each case pertaining to all of the clients in the case table. Each of these tables already has its own layout. The database also has a main menu layout with different buttons that perform scripts for different functions (for the lawyer and the employees). Functions that these buttons do include sending a letter to clients who have hearing dates two weeks from the current date (I already scripted this), displaying case info with portals (already established), and a daily calendar which displays all of the cases that have the hearing date=current date. The layout design for the calendar is a list (obviously in list view) with different rows i.e. first and last name, notes, location, time, etc about the cases that hearing date=current date.

So what I want is to enter data into the two main tables (which already have their own specific layouts not to mention are responsible for storing different types of data), and have the daily calendar layout be responsible for displaying data from the two main tables in list view for the cases that have the hearing date=the current date.

If there is a way to just send you a version of the database to show you what I mean, it would probably make things a lot easier for both of us.

[comment]

Well, I am quite positive that a table whose sole purpose is to duplicate data already existing in other tables is unnecessary.

You haven't answered my question about hearings. It seems you have a hearing date field in the Cases table - this can work only if each case has one hearing date. If that's true, then all you need to do is find the cases whose hearing date is today and show them in a list view (using a layout of the Cases table), along with selected fields from the Clients table.

If there is a way to just send you a version of the database

You can attach it here (after zipping it). Replace any confidential data with dummy entries.

[raiders2814]

I've deleted a bunch of of fields and layouts to keep some of the private information safe

Testdatabase.zip

[comment]

I have a feeling you are confusing tables with layouts. You have the same data repeated in 3 or 4 tables - that's just not right. In addition, your Clients -< Cases relationship cannot work, because you have the ClientID field in Cases auto-entering a serial number. Same thing with the Reminder Letter table: only the ReminderletterID field should be a serial number.

I'd also suggest you pay attention to field types - esp. when you are dealing with dates.

[raiders2814]

If layout isn't the design of the actual table, then I've confused it with something else.

And yes, the 4 tables is my question to you. I need two main tables (Levine and CaseInfo) which are necessary because they hold two different types of data. The other two would be the reminder letter and the daily calendar which have table fields but shouldn't. I need help because the script I need to make for the daily calendar needs to incorporate data from both of the two main tables (which should be as they are) because the merger fields on the layout of the daily calendar are from both the levine table and the case info table. So really the question has a bunch of parts, with the important part being the script.

[comment]

I need help because the script I need to make for the daily calendar needs to incorporate data from both of the two main tables

What I am trying to say is that don't you need this script - and you don't need another table "to incorporate data from both of the two main tables". Unless I am missing a point, you are taking something that is very simple and complicating it enormously. In fact, let me ask you two more questions, because I suspect it could be even simpler:

1. Do you track repeating clients? For example, Joe Smith had a case year ago. Now he comes in with a new case. Will you attach his new case to the existing client record, or will you create a new client record along with the new case record?

2. How many reminder letters do you send for each case?

If layout isn't the design of the actual table, then I've confused it with something else.

A layout is most definitely NOT the design of the actual table. You design the table in Manage Database > Fields. A layout is merely a user-interface element: a device that allows you to see the data in the table's fields. You can have as many layouts of a table as you want, and each layout can show a different subset of the table's fields.

Most importantly for the current discussion, a layout can also show data from related tables - for example, a layout of the Cases table can display the name of the client from the related record in the Clients table.