Calc Field Will Not Hold Value

[clrblue]

I am still working on the ledger I have been building, but I am very close now to completing it. I currently have a situation where a Calculation field called "Debit" will not hold the value I give it. I have worn out my patience with it and will now hope that after some explanation, someone here will be able to help me.

I have three files:

"employees" - Holds all info about the company's employees; each employee has a unique ID. In addition, this file holds the general "schedule" that the employee has agreed to, as well as calculations of how many hours he is working per week and his regular monthly pay. Important fields are:

Weekly Hours (calc) - Holds the number of hours that an employee works based on a schedule they have agreed to

Monthly Payment - this is the field "Regular Payment" from the "fees" file. Holds the employee's regular monthly pay based on the hours they have agreed to work based on the amount in the "Weekly Hours" field

(obviously these will fluctuate, but not here).

"fees" - Holds all info on various fees attributed to employee expenses. Important fields are:

Fee Type - Types of fees, for ex. Regular, Extra Hours, etc.

Cost Per Unit - Cost per Fee Type. Regular is the only different type which has next to it a field called "Weekly Hours" and then fee amounts in the fields "Cost Per Hour" & "Cost per Month"... and which Regular Payment is attributed to that employee is based on their Weekly Hours. The other Fee Types are simply based on their "Cost Per Hour".

"ledger line items" - Is the place where ledger information for the employees will be entered. The important fields are:

Amount (number) - will hold the quantity of the fee or, as in the case of a "Regular" type fee, the Weekly Hours

Employee ID (text) - unique alphanumeric text is entered here manually so that the fee will be linked to a specific employee; this field is the basis of a relationship between the "employees" file and the "ledger line items" files called "ledger line items to employees".

Fee Date (date) - Date fee occurred or is attributed to. Entered manually.

Fee Type (text) - Populated by a value list based on the contents of the field "Fee Type" in the "fees" file. This field is also the basis of the relationship between the "fees" and "ledger line items" files called "ledger line items to fees".

Credit (number; formatted as currency) - will hold any amount attributed to either an account or a line item entered into the ledger.

Entered manually.

Billing Cycle (calc) - automatically figures the billing cycle that a fee will be attributed to based on it's date. Non-editable.

Notes (text) - A misc. field to hold any comments or what have you related to a specific transaction.

Subtotal (calc) - Credit - Debit

Debit (calc) - This is the field that I am having trouble with. It doesn't seem to want to hold any info I give it. I have tried the following calculation:

If (Fee Type = "Regular", ledger line items to employees::Monthly Payment, Amount * ledger line items to fees::Cost Per Unit

---

Nothing shows up in the box for a "Regular" fee, although all other fees calculate properly. I made sure that the ledger line items db could retrieve both the "Weekly Hours" and the "Monthly Payment" from the "employees" file by creating two new dummy calc fields and displaying their contents in them through the relationship "ledger line items to employees".

I must have tried 1500 variations of the above calculation without success. I even tried changing the relationship between the "fees" file and the "line item ledger" file to a new field ("key") I created in each:

Fee Type & Weekly Hours

---

It still didn't work. Any and all help would be appreciated.

Thank you!

KC

[CobaltSky]

It is difficult to respond to your request as you appear to have included a lot of information that has little bearing on the problem, but have not mentioned a number of points which are central to the issue (such as how the relationships between each of your files are defined and why).

Notwithstanding this, the problem seems to lie with the relationship between 'ledger line items' and 'employees' as the difficulty you are having is with the display of the result when the fee type is 'Regular' - and that result is defined in your formula to be sourced from the 'employees' file.

Monthly Payment - this is the field "Regular Payment" from the "fees" file.

If I am correct in understanding that the monthly payment field mirrors the regular payment field in the 'fees' file, I suggest that you try creating an appropriate relationship which links your 'ledger line items' file directly to the 'fees' file, to pick up the monthly payment for regular fee types directly from fees rather than indirectly via the employees file.

As you've not said what fields the existing relationships are based on, it's not possible for me to say whether one of them will suffice, or whether you need a new calculated key field to pick up the appropriate data from 'fees' for this purpose. However once you have selected or created an appropriate relationship, the formula you will then need for your Debit(calc) field will presumably be along the lines of:

If (Fee Type = "Regular", ledger line items to fees::Regular Payment, Amount * ledger line items to fees::Cost Per Unit

---

Hope this is of some help. If not, perhaps you might clarify the relationship structure a little further.