FM 8.5

I have a problem with a calculation.

I have three tables: INVOICES, CONTACTS, CHEQUES.

I have table occurrence based on the relationships:

INVOICES;kf_Contact_ID & CONTACTS;kp_Contact_ID
CHEQUES;kf_Invoice_ID & INVOICES;kp_Invoice_ID

In CHEQUES I created a field: Invoice Reference, that uses a drop down
value list based on INVOICES kp_Invoice_ID, and this allows me to
associate a particular Cheque to a particular Invoice - It is not a
completely accurate systems as sometimes a Cheque covers several
Invoices. I do not know a better approach.

I have a portal on the CONTACT layout based on Cheques that displays a
series of fields the most important of which is the LatePayment
calculation. This calculation indicates to me how tardy a client is
paying Invoices.

The portal fields are:

kp_Invoice_ID (Invoices)
Invoice Reference (Cheques)
Date Shipped (Invoices)
Date Due (Invoices)
Date Paid (Cheques)
Terms (Invoices)
LatePayment (Cheques)

My LatePayment field has a calculation (number result) that goes:
CHEQUES/DatePaid - INVOICES/DateDue, the DateDue being a calculation,
INVOICES/DateShipped + Terms. The result of this is that the Late
Payment field shows only one figure for all records.

From my presentation here is it obvious or does it suggest where my
mistake is?

Thanks
Matthew

If you mean that the result is the same through all records then my feeling
is the result calc might be set as a global. (Yes, even calcs can be set to
hold the value as a global)

--
Keep well / Hou je goed

Ursus

On 10/06/10 12:03 PM, Buckbuck wrote:

it needs a PaymentLines table which is a child of Payments (i.e.
cheques) by payment_line_id

'cheques' is a misnomer of sorts, payments may be EFT...







Invoice Reference (Cheques) is really invoice_id



what do the calc results look like in the Payments (cheques) table
records? THat will differentiate if it is the portal rel, or the actual
calc. Perhaps not such a risk here with a 'short'rel, but I mention it
because with hopped rels it can be a portal field rel definition.

with an issue such as this always review the source fields in native a
state first, i.e look at the actual data, not a downstream representaion
(portal)

global calc result as Ursaus suggested seems likely; that will readily
show in the payents (cheques) in table view

Is the payments to invoice rel actually invoice_id to invoice_id ?

In article
<c3784f92-8298-4ef1-8688-5fbd73c60ef8 (AT) j36g2000prj (DOT) googlegroups.com>,
Buckbuck <buck.matthew74 (AT) yahoo (DOT) com> wrote:


There's a couple of things here that look peculiar to me ...

There doesn't seem to be a Relationship for Contacts -> Cheques, so I
can't see how you can have "a Portal on a Contact Layout based on
Cheques". ?|


A Portal can't have Fields from multiple Tables, only Fields from one
Table via a Relationship ... unless by "Date Shipped (Invoices)" you mean
a Field in the Cheques Table that simply copies the data from the Invoices
Table via a Cheques -> Invoices Relationship. ?|


I can't quite work out how / where you're getting data. The only ting I
can think of is that you're trying to "double-jump" relationships (e.g.
get data from Invoices to use in Cheques without a Cheques -> Invoice
Relationship by instead jumping through Contacts), but that doesn't work.

To get a Late Payment calculation you'll need to have a Relationship
between Cheques and Invoices based on the Invoices_ID Fields (which you do
seem to have) - which Table the Late Payments calculation goes in will
depend on where / how you want the data stored and displayed.


Helpfull Harry )