Add Count & Sum Fields for Outstanding Fees in ESPOcrm

[carlosptf]

Good morning everyone,
I need assistance with the ESPOcrm tool and hope you can help me.


Context:

• Entity: Associates

• Description: Contains the records of the associates.

• Existing Fields: Name, phone, address, among other registration data.

• Entity: Membership Fees

• Description: Stores the payment records of the associates’ membership fees.

• Existing Fields: Fee amount, status (paid or outstanding), due date.



Relationship:

• I established a one-to-many relationship between Associates and Membership Fees, linking each fee to a specific associate.


My Question:
I would like to create two new fields in the Associates entity:


1. Number of Outstanding Membership Fees:

• Description: Should display the total number of fees that are outstanding for each associate.

2. Sum of Outstanding Membership Fee Amounts:

• Description: Should calculate the sum of the amounts of the outstanding fees for each associate.




Objective:
• To facilitate the monitoring of each associate’s financial obligations directly in their registration record.




What I Have Tried:
• Reviewed the available documentation but couldn’t find a specific solution for this case.

• Tried using aggregation functions but did not achieve the expected results.

• Managed to implement the functionality using the Advanced Pack, however, with this approach, the information is not available in the list, which is essential for my monitoring.


I appreciate your attention in advance, and any guidance or examples on how to implement these fields in a way that the information becomes visible in the list would be greatly welcomed!

[rabii]

you can achieve this using formula, here is what you need to do:

- add the two fields in associate entity (NumberOfOutstandingMembershipFees => type integer) and (SumOfOutstandingMembershipFees => type currency)

and add the formula below to your associate entity administration > entity manager > associate > formula (Before Save Custom Script)

/*
    Make sure to replace membershipFees with the correct name of the relationship
    Also make sure to use the correct name of the field and correct value of the status
*/

numberOfOutstandingMembershipFees = entity\countRelated('membershipFees', 'status=', list('Outstanding'));

sumOfOutstandingMembershipFees = entity\sumRelated('membershipFees', 'feeAmount', 'status=', list('Outstanding'));​ 

I hope this helps

[carlosptf]

Hello, good morning!

First of all, thank you for the explanation provided earlier.

I am encountering an issue when using the formula you shared. When I apply the formula, I receive the following error message:

Error. No primary filter 'statusMensalidade=' for 'CMensalidades'.

Formula Used:

cQtmensalidadesemaberto = entity\countRelated('cMensalidadeLink', 'statusMensalidade=', list('Em aberto'));


Scenario Details:

• cQtmensalidadesemaberto:

• Type: Number

• Entity: Associados (Associates)

• Description: Field that will receive the count of open monthly fees.



• cMensalidadeLink:

• Description: Name of the relationship created between the Associados (Associates) and Receita (Revenue) entities.

• Relationship Type: One-to-Many

• Fields:

• Entity: Associado (Associate)

• Foreign Link: associadoLink

• Link Type: One-to-Many

• Link: cMensalidadeLink

• Foreign Entity: Receita (Revenue)



• statusMensalidade:

• Description: Field in the Receita (Revenue) entity that I use as the basis for counting open monthly fees.

• Type: List




Question:

Could you help me identify where I am making a mistake in the formula or in the configuration of the relationships? I appreciate your assistance in advance!

[rabii]

The links are usually plural unless if you explicitly changed the name to singular. if you can share the names / screenshot of the relationship and entities i will be able to help more. currently i am just assuming things.

the error you got states that there is no filter for the status

Please note that this will not work when creating a new entity as the formula script is triggered before save.

[carlosptf]

If you need any changes or to add more information, feel free to let me know!

[rabii]

My bad the countRelated function only accept filter name. so another workaround we could use record/count function Use the code below instead of the previous one

cQtmensalidadesemaberto = record\count('CMensalidades', 'associadoLinkId=', id, 'statusMensalidade=', list('Em aberto')); 

For sumRelated it should work just fine as long as you provide the correct names.

This code should be used in formula for (Entity: Associados (Associates)).

[carlosptf]

Great, this works well. Thank you very much for the help.

I still have two related questions.

1 - Looking at the formula, I couldn’t understand what 'associadoLinkId=, id.' does within the formula. (Even though it works, I’d like to understand what it actually does.)

2 - What is the alternative to sum the values in the valorDaParcelaEmAberto field, keeping the same filter of 'statusMensalidade=' list('Em aberto')?

[rabii]

Hey,

To anszer your questions:

1- associadoLinkId means that we requesting all the Receita (CMensalidades) hat have associadoLinkId = id of the current Associados record.

2 - what is the valorDaParcelaEmAberto (is the field in Account that will hold the amount of paid membership)? if yes what is the field name in Receita that hold the fee (membership fee)?

[carlosptf]

Thank you for the answer to question 1; now I understand.

As for question 2, I didn’t express myself well, sorry about that. Basically, I couldn’t find the sumRelated function. I wrote the formula:

cValoremabertomensalidades = sumRelated('CMensalidades', 'valorDaParcelaEmAberto', 'associadoLinkId=', id, 'statusMensalidade=', list('Em aberto'), 'tipoDeReceita=', list('Mensalidade'));

And I received the error: Error Unknown function: sumRelated.

[rabii]

entity\sumRelated() however this will not work too because it accept only existing filter. so as a work around you need loop through the related records and add sum to the field.