Help!!

[Cat8]

I've been working on a db that has a file for client information, a file for job information for each client, and a file for the financial info for each client and/or job. On the financial file I want to show the total income from jobs for all and particular clients for the past week, month, year, and financial year to date. Each job has an automated date entered when the record is created. Does anyone have any models of counting back 7days?

So far I've got it taking 7 away from the day of the date field. But if the day of the date is say "04", then the result is -3. In this case I have made it take the minus amount away from the total days in the current month, then take the month back 1.

This is my omit command:

Month ( Job System::date ) = Job System::Filter Month & Day ( Job System::date ) < Job System::Filter Day or Month ( Job System::date ) = Job System::Filter Month - 1 & Day ( Job System::date ) < Job System::Amount of days in Current Month - Job System::Minus Amount

Then it goes to a layout where the jobs are listed by Job Number and include client name and various other details.

However, what I have done does not show the records I want. Can anyone help me see the error of my ways?

[MarkWilson]

Cat,

Fields:

Key_Week = Get( CurrentDate ) >= d_Date and Get( CurrentDate ) - 7 <= d_Date

Key_Month = Get( CurrentDate ) >= d_Date and Date( Month( Get( CurrentDate ) ) - 1 ; Day( Get( CurrentDate ) ) ; Year( Get( CurrentDate ) ) ) <= d_Date

Key_Year = Get( CurrentDate ) >= d_Date and Date( Month( Get( CurrentDate ) ) ; Day( Get( CurrentDate ) ) ; Year( Get( CurrentDate ) ) - 1 ) <= d_Date

These calculations will return a boolean value to match a Key_1 constant field.

Table Occurances:

Job_System_Week - Key_Week = Key_1 AND Contact_# = Contact_#

Job_System_Month - Key_Month = Key_1 AND Contact_# = Contact_#

Job_System_Year - Key_Year = Key_1 AND Contact_# = Contact_#

Totals:

Sum( Job_System_Week::Total_Sale )

Sum( Job_System_Month::Total_Sale )

Sum( Job_System_Year::Total_Sale )

This may not be the best solution because the Sum( ) funsction creates an unstored result, which requires a recalculation each time it is viewed.

[Cat8]

Thank you for that. Can I just ask a couple of stupid questions?

1) The fields that you listed... Are they to be calculation fields(the calculations being what you wrote next to them)?

2) What in more basic terms do you mean by: "These calculations will return a boolean value to match a Key_1 constant field."?

3) What is d_date mean to represent?

[MarkWilson]

1) Yes.

2) It is common practice to have a constant field, Key_1, with a value of 1 in every table/file. This constant creates a possible link between any record and any other record, including itself, in any other table or file.

In this case it can be used to link selected records to any record in the table.

Example: Record 12, created 8-Jun-04, can be related to record 800 created, 25-Oct-04, by the Job_System_Year relationship because the the boolean result is 1 for the Key_Year calculation in record 800 (less the second criteria of the relationship). The Key_1, left-hand side of the relationship, is 1 and Key_Year, right-hand side, calculation also returns 1. The two values match completing the first requirement of the relationship. The second part is that the Contact_IDs match for two related records.

I did not mention the Job portion from your post but the theory is the same.

3) d_Date is equivalent to "Job System::date".

FMI recommends not using words used by functions, boolean logical, or the like as field names. See the help for "About naming fields."

I use a combination of "_", capitalizations, and prefixes to name my fields.

c - calculation fields

d - date fields

g - globals fields

_ - as a key field or fields related to the record and not the data it contains.