Search code examples
filemaker

FileMaker Pro - Sum of the specific record in the portal

I have a Portal that shows related records from GJ (General Journal). It has two fields inside the portal. First field is DebitTitle, the records that are being save in this field are "Cash in Bank" and "Inventory". Second field is Debit, the records that are being save in this field is the amount in every DebitTitle.

So, my problem is, how can i get the sum of all the "Cash in Bank" record only?

I tried to create a calculation type field: 

If ( GJ::gj_DebitTitle = "Cash in Bank" ; Sum ( GJ::gj_Debit ) )

But it didnt work. The calculation field is getting the sum of the Debit field records.

I also tried to create a script trigger in my portal:

If [ GJ::gj_DebitTitle = “Cash in Bank” ]
    Set field [ USR::gj_SampleSum ; Sum (GJ::gj_Debit) ]
    Commit Records/Request [ Skip data entry validation ; No dialog ]
End If

It didnt work also, it just filter my portal and display all the "Cash in Bank" records.

enter image description here

Solution

  • Your key fields look strange. Seems there are primary and foreign key fields that you don't use. usr_ID and gj_UsrID. Remember that the Sum function works across a group of related records or a record with repeating fields and not across a found set of records. If I understand your need correctly, the sum functions belong in the parent table, not in the portal with related records. Set up 2 calculated fields in GJ that are very similar to what you have:

    DebitIfCashInBank:

    If ( GJ::gj_DebitTitle = "Cash in Bank" ; GJ::gj_Debit )

    Do a sum on those in the USR table: SumDebitIfCashInBank:

    Sum(GJ::DebitIfCashInBank)

    Hope this helps.