Show total from filtered related table

[JakeBake]

I have some experience with Access and SQL but I'm new to Filemaker.

I have a layout which shows a list of projects. One of the fields I want it to show is "total amount of paid invoices for this project up to the end of the last financial year". The invoices are stored on another table so I will need to pick from this table all records where "project id" = "project id" (this is set up as a relationship between the tables) and also "invoice is paid" = yes AND "date invoice paid" < start of last financial year.

My question is how do I go about getting and showing that data? I can get a total easily enough, it's filtering out these specific rows that I'm having trouble with.

I'm assuming this is quite easy but that I'm missing something! Thanks in advance!

[JakeBake]

Is this really hard to do? Or is it so easy no one can be bothered to answer?

[Fenton]

If the existing portal relationship does not have those 2 extra criteria, invoice paid "yes" AND Financial Year current -1 > Date Paid, then you need to build another relationship, and use that for the total.

You can add multiple criteria (lines) to a relationship (you may know that).

In Projects, for the Invoice Paid "yes", you'll need to create a "constant" field, calculation, result Text, can be unstored; with the single word, "yes".

Point that at Invoice_Paid in Invoices.

For the Year, you would create another calculation in Projects for the 1st date of your Financial Year. The operator would be < that date.