first tables must be linked throw a joint
So you have a customer layout and you need to see the total amount paid in the related table?
Assuming a relationship such as:
Customer::CustomerID = Payments::CustomerID
You can define Sum ( Payments::Paid ) in the customer table.
Note sure what you mean by this sentence though: "if a status is on payed"
You may need to explain that in more detail here.
What i mean by "if a status is on payed" is that the sum only sums the records where the "status is on payed".
CUSTOMER = Phil PAID = €5,00 STATUS = Payed
CUSTOMER = Michael PAID = €3,50 STATUS = Billed
CUSTOMER = Phil PAID = €3,50 STATUS = Billed
The 'SUM IF' must have the result €5,00.
Define a calculation field, cIfPaid, in the payment table as:
If ( Status = "Paid" ; Paid )
Then, in customer, define the calculation as Sum ( PaymentTable::cIfPaid )
I want to add another date function....
The datefield is like PAID DATE <31-03-2011>.
I want only the records for 2011.
So If ( Status = "Paid" and Paid date is ....... ; Paid )
What is the date function?
Date ( 3 ; 31 ; 2011 )
GetasDate ( "31-03-2011" ) //assumes DDMMYYYY date settings rather than the US settings of MMDDYYYY
You can also calculate today's date using Get ( CurentDate ) but then make sure to use storage options to set the calculation to be Unstored.
I want (only) all the records for 2011. Not 31-3 (that's an example.).
Year(Datefield) = Year ( Get ( CurrentDate ) )
or Year ( DateFIeld ) = YourTable::GlobalYearfield
The second option allows you to set up a global field where you specify the year you want to see. (Useful when you are in the first quarter and want to go back and see stuff from the 4th quarter.)
There are also ways to set up relationships that limit the accessable records by a given date, month or year.
So the function become: If ( Status = "Paid" and Year(Paiddate) = "2011" ; Paid )
That works until the year becomes 2012. That's where the other options that use a global field or Year ( Get (CurrentDate ) ) allow you to get the same results without having to redefine the field with each new year.