Combining functions in a database calculation or summary

This forum is a self-help system for the exchange of ideas among AppleWorks users.

Combining functions in a database calculation or summary

Postby AndrewNZ » Tue Jul 05, 2011 2:51 am

Hi,

I'll give a bit of background to my situation before describing the advice I'm looking for here.

I've used AW a fair bit in the past, but since I've been working in a job which has Windows machines and uses a complex custom-written relational database for its main tool, I use it less often than I did, so I am a little rusty. It is the shortcomings of the complex relational database (which I do not have admin rights to, I'm a humble user of it) that has led me back to AW and its database module. Fortunately, I have a copy of AW for Windows on my machine at work.

Essentially, I am a complaints investigator, and the relational DB provided by my employer is used to keep track of correspondence and various metadata associated with the investigation of the complaint. However, although it is called a Complaints Management System (CMS), it does a very poor job of helping investigators manage their caseload. I've previously tried to create an Excel spreadsheet that would help me manage my work, but it is not a database (or I am not sufficiently skilled with Excel) and I cannot get it to provide me with what I want.

So, my problem.

I have complaints (cases) assigned to me to investigate. These cases have differing priorities, which means differing time limits for completion of the investigation. The priorities and time limits are set out below:

J (jurisdictional issues) - time limit = 1 month
D (discretion not to investigate) - time limit = 3 months
1 - time limit = 4 months
2 - time limit = 6 months
3 - time limit = 12 months

The priority field in my AW DB is currently a 'pop-up menu' field, but I could change this to another type if this would be advisable.

I have a date-type field in which I enter the date the complaint was received. I have an calculation-type field in which I calculate the age of the complaint using the function:
=(NOW()-'DateReceived')

This returns an age in days, which I input into a separate calculation field that divides the result of 'age in days' by 30, to give a rough approximation of the number of months old the complaint is. This latter field is the one that I display in the main data entry view of the database.

Given the priority assigned to the complaint, and the age of it, I am either 'On Target' or 'Over Target' for dealing with it.

Each month I have a meeting with my manager to review progress, and since the reports that can be extracted from the CMS are not very useful for this purpose, I want my AW database to help me generate reports that can form the basis of discussion in these meetings.

Part A of My Query

At present, I have a manually operated field, in the form of a pop-up menu, to indicate for each record (complaint) whether I am over or on target for completion of the investigation. I would like to make this an calculated field, so that the database keeps track of this for me automatically. Unfortunately, I'm either too dense, or not good enough with the syntax of AW functions, to be able to write a function which essentially does the following:

IF 'Priority'=1 AND 'Age in Months'<4 THEN 'On Target' ELSE 'Over Target'

It seems likely that the 'IF' function is what I'm after, but I haven't been able to get the syntax right to combine 'Priority' and 'Age in Months' in the format provided in the AW help, which is:
=IF(logical,true value,false value)

Getting the syntax of this function right would be a good start. However, I suspect that I have a larger problem in that I have 5 priorities to measure against and I need the overall calculation (that produces the 'On Target' or 'Over Target' results) to report in one result field. This means nesting the functions that look at the priority and age. I've tried and failed to get the syntax of such nesting right, so advice on this is doubly welcome.

I have a sneaking suspicion that unless the answer is much more concise than I imagine it to be, there will be an additional problem: AW has a character limit for the length of a function string in a calculation field which may be too short for my needs.


Part B of my Query

Even if the problem above cannot be solved, and I have to manually enter whether a complaint is 'On Target' or 'Over Target', I have a separate but related question for the reports I have to present to my manager.

On these reports, I would like to have a summary at the top (in a grand-summary part, I assume) which gives a count of how many complaints are 'On Target' or 'Over Target' for each priority. Below that is a sub-summary part, ordered by the type of action that must next be taken in the investigation. (Getting that right may be a subject of a separate post at a later date.) The report must, of course, only report on those complaints that are still being investigated ('open complaints') and not those which have been completed ('closed complaints'). So, I need a function in a summary field which will present an accurate count of the number of open complaints that are 'on target' for priority J (with separate summary fields that do the same thing for priorities D, 1, 2, and 3), and a separate set of summary fields that will present a count of open complaints that are 'over target' for each type of priority.

My guess is that it is easier to have the summary field simply count all the complaints that are on or over target for a given priority, regardless of whether they are open or closed, since the report could be based on a saved search (match) which strips out all closed complaints. But if I'm off-beam with that idea, I'm happy to have another way of cracking this nut.

Many thanks in advance to any AWUG members that take the time to try and help me with this.

Andrew
AndrewNZ
 
Posts: 2
Joined: Tue Jul 05, 2011 1:40 am
Location: Wellington, NZ

Re: Combining functions in a database calculation or summary

Postby Barry » Tue Jul 05, 2011 1:34 pm

Hi Andrew,

Part A:

I've done some restating of the question, and distributed the calculations among several fields, not all of which need to be included on the visible layout.


Field List:
Priority: Popup menu with six items: 00, J, D, 1, 2, 3 Defaults to 00
TL (time limit in months): Calculation: CHOOSE('Priority',0,1,3,4,6,12)
Received: Date. Option: Automatically insert today's date (ie. Date Record created) (may be edited)
Target Date: Calculation:
DATE(YEAR('Received')+INT(MONTH('Received')+'TL')/12,MOD(MONTH('Received')+'TL',12),DAY('Received'))
Remaining (Days): Calculation: INT('Target Date'+1-NOW()) (see revision below)
Completed: Checkbox. Set default value to Unchecked.
Completed Date: Date: Entered manually
OnTarget: Calculation: IF('Completed',IF('Completed Date'<'Target Date',"On Target","Over"),IF(INT(NOW())>'Target Date',"Over","On Target"))

The On Target field shows the current status of the case while still open, and the state on the closing date when it was closed.

Having done that, I was unhappy with the Remaining field continuing to calculate an ever larger negative number after the case had been closed. Hence this revision below:

Remaining: Calculation: IF('Completed','Target Date'-'Completed Date',INT('Target Date'+1-NOW()))

Shows the number of days remaining while the case is open, the days remaining at completion if the case has been marked Completed and the completion date entered.


Part B:

You will need Subsummary parts when sorted on Completed and on On Target. Fields can appear on a Layout only once, so each subsummary part will need a separate Summary type field for each field to be summarized.

Regards,
Barry
Barry
 
Posts: 1390
Joined: Mon Sep 30, 2002 12:54 am
Location: 48°50' N 123° 30'W


Return to AppleWorks Help

cron
AppleWorks Users Group Logo iWork Users Group Logo