Report on tasks based on upcoming dates

[Sanjai]

Hi All,

I am working on a report but have not been able to produce the required output so far. Here is the sample of the database and the report:

Assume the following database:

Field Names:Item ,Name ,ADate ,BDate ,CDate

Record1 : "001" ,"David" ,#11/25/2004# ,#11/26/2004# ,#12/01/2004#

Record2 : "002" ,"Ivan" ,#12/05/2004# ,#11/25/2004# ,#12/15/2004#

Then the datewise task report should show the results:

Report Header: Item ,Name ,Activity ,Dates

Results : "001" ,"David" ,"ADate" ,#11/25/2004#

Results : "002" ,"Ivan" ,"BDate" ,#11/25/2004#

Results : "001" ,"David" ,"BDate" ,#11/26/2004#

Results : "001" ,"David" ,"CDate" ,#12/01/2004#

Results : "002" ,"Ivan" ,"ADate" ,#12/05/2004#

Results : "002" ,"Ivan" ,"CDate" ,#12/15/2004#

Can we someone help me to produce the required results?

Waiting to see a solution.

Thanks,

--Sanjay

[T-Square]

Sanjay--

Others could probably suggest a way for you to accomplish this with the data structure you have, but I would ask you whether it wouldn't be better to separate the Name/Date combinations out into separate records. Not knowing what information these three dates represent, your output nonetheless suggests that these dates are treated the same. If so, then create a 2-table solution:

People:

ID [number]

Name [text]

...

Dates:

PersonID [number]

Date [date]

<and maybe, if you are distinguishing A B & C>

DateType [text]

Then, build a relationship from Dates to People from PersonID to ID, and build your report in the Dates file, using the linked name field.

Repeating values in a database, I believe, indicates a need to create a new table, because, if you need 3 values today, what's to say you won't need a fourth tomorrow--and then where would you be? In a repeating field environment, you have to redesign the table. With a separate table, you just add a new entry.

HTH,

David

[Sanjai]

Hi David,

Thanks for your suggestion. The three dates in my sample are actually the planned dates for three phases in a project(please consider "Item" field as "Project"). I have a requirement to give a report on upcoming phases between the date range specified by the user. Even if I create a separate table for date representation, since all the dates would belong to one record, after doing a search on the date range, Filemaker Pro never gives the result in the expected format because it does not support the "Union" clause. Hence in my sample example Filemaker Pro always give two records instead of six in the resultset.

I tried to write a sql query in Oracle 9i, MS Access 2000 and it works fine with the Union clause. FYI: here was my query

"select item, name, ADate "Activity" from Item

Union

select item, name, BDate "Activity" from Item

Union

select item, name, CDate "Activity" from Item

order by 3"

Waiting to see your reply.

Thanks,

--Sanjay

[Sanjai]

Hi Everyone,

Haven't been able to figure out any solution so far. Waiting to hear some feedback and help from this group.

Thanks,

--Sanjay

[-Queue-]

David is correct. A separate file should be used to hold the dates. A portal can be used to enter the dates and a report in the related file can be sorted by Date and Name to appear as desired.

See attached for sample.

Sanjai.zip

[Sanjai]

Thanks Sir,

The files which you sent really give a lot of inforamtion of how to accomplish my work. I gave it a try but I still need your help because unlike in the example, the selection of ADate, BDate and CDate will not be done by the users and hence there will not be separate records created for each of them.

The users only have to enter the dates under these columns and then run the report. They doh't want to select anything named Activity. I am ready to use the portal and another file for this purpose.

Waiting to hear from you,

Thanks,

--Sanjay

[Steve T.]

Whenever possible to take out some time, could you please take out some time and have a look on the post "Reports on Tasks based on upcoming dates".

Howdy, Sanjay! PM Inviting me again, eh? Well, let's see... I'm hardly an expert but I think the problem is that you are trying to re-use the same record non-sequentially in a list. Queue and T-Square are probably right and IMO you should use a PEOPLE db and a DATES db. If you aren't comfortable with a relational db with 2 files, you could use 1 db and just have 1 record per date and just select id/name/ABCDate for each date. In other words, make your db DATE-based rather tahn PERSON-based.

Good luck!

--ST

[-Queue-]

Another option would be to have ADate, BDate, and CDate entered, then run a script to create the related records. Any way you do it, you're going to need multiple records per person, whether they are in the same file or a related one, or your report will only ever contain two records.

[Sanjai]

Hi,

Thanks Queue and Steve for taking out your time. Steve, as suggested by you I cannot make my database Date-Based at this point. I have checked with the users and they prefer manually entering dates under the respective dates(ADate, BDate and CDate).

Queue, as you said I can run a script to create the related records, I will try this. However, it could be a problem when the report is executed by more than one user because before creating records I will have to delete all the records in the new report file.

Thanks again to both,

--Sanjay