FM8.5

I am trying to define the relationship between three tables CONTACTS,
INVOICES and CHEQUE LIST. In my setup (and I do not know whether this
approach is best) I have created a CHEQUE LISTt table separate from
INVOICES or CONTACTS. I track the balance between what I have sold to
a CONTACT through two portals on a CONTACT based layout, one portal
based on Invoices listing Invoice #s and amounts, and another, based
on CHEQUE LIST showing cheques received. On the CHEQUE LIST I manually
assign the cheque amount to an Invoice by writing the Invoice # in a
field called InvoiceReference. Both INVOICES and CHEQUE LIST have
relationships with kp_Contact ID. There is a field that calculates the
difference between two summary fields. I did it this way as sometimes
people short pay, overpay or pay two Invoices at once.

Now I am trying to create a layout that shows as a number the
difference in days between when an Invoice was due (Date Shipped +
Terms) and when it was paid (ChequeDate). Can you recommend how to do
this?

Thanks

In article
<ba209755-097f-45bb-b485-596c7bdf2b8e (AT) j12g2000pri (DOT) googlegroups.com>,
Buckbuck <buck.matthew74 (AT) yahoo (DOT) com> wrote:

It would probably be best to have this calculation done in the Invoices
Table, which means defining a Relationship between the Invoices Table and
the Cheque List Table, based on the invoice number.
i.e.
rel_InvoicePayments
Match records in Invoices with records in Cheque List
when InvoiceNumber = Cheque List::InvoiceReference

Then in the Invoices Table you can define a Calculation Field to work out
the difference in days.
i.e.
DaysDifference Calculation, Number, Unstored
= rel_InvoicePayments::ChequeDate - (DateShipped + Terms)

FileMaker automatically calculates in days when adding or subtracting Date
fields.

This Field could then go in the Invoice Portal.

BUT,
because an invoice might be paid with multiple cheques, you probably want
to use the date that the invoice has been FULLY paid off. Ths can be done
in two ways:

- define the rel_InvoicePayments Relationship to sort the
Related Records by ChequeDate in reverse order so that
the most recent date is first (for each invoice) and
used by the Calculation
OR
- change the Calculation to use the Max function to get
the most recent ChequeDate (for each invoice)
i.e.
DaysDifference Calculation, Number, Unstored
= Max(rel_InvoicePayments::ChequeDate)
- (DateShipped + Terms)

FileMaker automatically uses the Field data from the first Related Record
when a Related Field is referenced, unless an aggregation / summary
function is being used (e.g. Max, Min, Count, etc.)


Helpfull Harry )

On Jun 4, 8:03*pm, your.n... (AT) isp (DOT) com (Your Name) wrote:
The problem I am having with this (assuming I have set it up as you
described) is that the portal shows the same Date shipped in all the
portal lines. This date changes as one moves to another Invoice record
- it shows the ship date of the screened Invoice.

Any ideas what might be wrong?

Thanks

In article
<748898c6-3b86-445c-8964-37efe5e367ce (AT) s1g2000prf (DOT) googlegroups.com>,
Buckbuck <buck.matthew74 (AT) yahoo (DOT) com> wrote:
I can't see how you would get the Date Shipped. The Calculation is
rel_InvoicePayments::ChequeDate - (DateShipped + Terms)
If the other Fields are empty, that would calculate as "-DateShipped",
which makes FileMaker display a "?" since a negative date makes no sense.


The Calculation Field might be defined in the wrong Table - it needs to be
in the Invoices Table and access the Cheque List Field via the correct
Relationship.


You could create a temporary Layout using the Invoices Table which
displays the following Fields (in List View):

DateShipped Terms rel_InvoicePayments::ChequeDate DaysDifference

If these aren't displaying the correct results, then there's something not
right in the set-up.

You could add the InvoiceNumber and rel_InvoicePayments::InvoiceReference
Fields as well to make sure the Relationship is linking the correct
Records.

If the rel_InvoicePayments::ChequeDate isn't showing the correct
information, then it's the Relationship that incorrect.

If the DaysDifference isn't showing the correct result, then the
Calculations is incorrect.


Also check that the DaysDifference Field in the Portal is using the
correct Relationship (the same one as the Portal itself) and that it is
properly inside the Portal row in Layout mode.


Helpfull Harry )