Many to One problem

[Allan Bowes]

Hello,

we have 2 files "Contacts" and "Invoices", related through a CUSTID field. This field is also used to display all the client details in a file called "Orders"

Within invoices we have many entries for our clients, some of whom run late with payments. We have created a calc field in "Invoices" to identify the invoices that are overdue and want to display the calc field in the Client's record in "Contacts". It will also be displayed in "Orders".

Where a client has 1 invoice only in "Invoices", this works OK, however if a client has more than 1 invoice, some overdue and some not overdue, we cannot get the calc field to display in the corresponding record in "Contacts". I think this is because FileMaker finds the first matching record.

Can anyone please suggest how I can link the overdue calc field directly with the CUSTID so that we can identify those customers who have overdue invoices.

I am sure it will be quite straightforward, but it is tasking my little brain.

Thanks

[Brian C]

You could try creating a new relationship to a new table occurance for orders. So that it will only link to orders that are overdue.

Table Occurance: "Customer"

CustomerID

InvoiceOverdue (calculation field = "overdue")

Table Occurance: "Orders OverDue"

CustomerID

InvoiceStatus

Once you have the above relationship established just base your calculation field on the new "Orders OverDue" table occurance instead of your old Orders table occurance.

[Allan Bowes]

Hi Brian,

many thanks for your reply.

As I am still finding my way around relationships, could you please explain your solution in some more detail for me. As I understand it, we should create 2 new tables in our Orders file, Customer & Orders Overdue, as the details on our customers are stored in a file called "Contacts". Our files were originally created in FMP5, so they are separate files rather than tables within 1 file.

Within these tables we should add the CUSTID field that already exists in "Contacts". We should then create a calculation field "InvoiceOverdue" that = our overdue calculation from the "Invoices file"

Can you please let me know what the "InvoiceStatus" field is and could you please let me know which fields we should be using to create the relationships.

Many thanks

[Allan Bowes]

Hi Brian,

I have read up a bit more about Table Occurances and realise that you are not suggesting making tables.

Can you please let me know which file I should make the Table Occurances in (Contacts or Invoices) and please let me know what the Invoicestatus field should be.

Many thanks

[Brian C]

You will first create a new field in your Customer File called: "InvoiceOverdue". This will be a calculation field with the calculated result of: "OverDue"

You will then create a new table occurance in your Customer file that links to your Orders file and this new table occurance will be called: "Orders OverDue"

Next you will create a relationship between your "Customer" table occurance in the customer file and the "Orders Overdue" table occurance which is also in your customer file and use the following fields in the linking:

"Customer" "Orders OverDue"

CustomerID <-----> CustomerID

InvoiceOverdue <-----> InvoiceStatus

Now your calculation field will look something like this:

if( isvalid(OrdersOverdue::InvoiceStatus) ; "OverDue", "")